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)。

请注意，这是不（据我所知）原子：companies.product_id可能会增加没有相应的插入产品。 – 2014-09-22 17:28:23

答

根据您拥有的公司数量，您可以为每家公司创建一个序列。通过在product_id列中设置为默认值的函数来查询它。

或者，此函数可以简单地执行SELECT FOR UPDATE并更新表的值。我认为应该是非常高效的。

postgresql公司id为基础的序列

相关推荐