postgresql公司id为基础的序列
问题描述:
我有一个数据库与公司及其产品,我想为每个 公司有一个单独的产品id序列。postgresql公司id为基础的序列
我知道postgresql无法做到这一点,唯一的办法是为每家公司分别设置一个序列,但这很麻烦。
予想到的解决方案有一个单独的表来保存序列
CREATE TABLE "sequence"
(
"table" character varying(25),
company_id integer DEFAULT 0,
"value" integer
)
“表”将是霍尔特为序列表的名称,诸如产品,类别等等
和值将保存将用于插入的product_id的实际序列数据
我将使用UPDATE ... RETURNING value;获得产品编号
我在想这个解决方案是否高效?
使用行级别锁定,只有同一公司的用户在同一个表中添加行将不得不等待获得一个锁,我认为这可以减少竞争条件问题。
有没有更好的方法来解决这个问题?
我不想为所有公司使用产品表的顺序,因为产品ID的差别会很大,我想让用户保持简单。
答
你可以只嵌入在你的公司表计数器:
CREATE TABLE companies (
id SERIAL PRIMARY KEY,
name TEXT,
product_id INT DEFAULT 0
);
CREATE TABLE products (
company INT REFERENCES companies(id),
product_id INT,
PRIMARY KEY (company, product_id),
name TEXT
);
INSERT INTO companies (id, name) VALUES (1, 'Acme Corporation');
INSERT INTO companies (id, name) VALUES (2, 'Umbrella Corporation');
然后,使用UPDATE ...返回获取下一个产品ID为特定公司:
> INSERT INTO products VALUES (1, (UPDATE companies SET product_id = product_id+1 WHERE id=$1 RETURNING product_id), 'Anvil');
ERROR: syntax error at or near "companies"
LINE 1: INSERT INTO products VALUES (1, (UPDATE companies SET produc...
^
哦不一!看来你不能(从PostgreSQL 9.1devel)使用UPDATE ... RETURNING作为子查询。
好消息是,这不是问题!只要创建一个存储过程,做增量/返回部分:
CREATE FUNCTION next_product_id(company INT) RETURNS INT
AS $$
UPDATE companies SET product_id = product_id+1 WHERE id=$1 RETURNING product_id
$$ LANGUAGE 'sql';
现在插入是小菜一碟:
INSERT INTO products VALUES (1, next_product_id(1), 'Anvil');
INSERT INTO products VALUES (1, next_product_id(1), 'Dynamite');
INSERT INTO products VALUES (2, next_product_id(2), 'Umbrella');
INSERT INTO products VALUES (1, next_product_id(1), 'Explosive tennis balls');
一定要使用相同的公司ID在这两个产品价值和参数为next_product_id(company INT)
。
答
根据您拥有的公司数量,您可以为每家公司创建一个序列。通过在product_id列中设置为默认值的函数来查询它。
或者,此函数可以简单地执行SELECT FOR UPDATE并更新表的值。我认为应该是非常高效的。
请注意,这是不(据我所知)原子:companies.product_id可能会增加没有相应的插入产品。 – 2014-09-22 17:28:23