基于 postgresql 公司 id 的序列

Question

我有一个包含公司及其产品的数据库，我想要每个公司拥有单独的产品 ID 序列。

我知道postgresql做不到这个，唯一的办法就是每个公司都有一个单独的序列，但这很麻烦。

我想到了一个解决方案，用一个单独的表来保存序列

CREATE TABLE "sequence"
(
  "table" character varying(25),
  company_id integer DEFAULT 0,
  "value" integer
)

“表”将包含序列的表名，例如产品、类别等。

value 将保存将用于插入的 product_id 的实际序列数据

我将使用 UPDATE ... RETURNING 值；获取产品编号

我想知道这个解决方案是否有效？

使用行级锁定，只有同一公司的用户在同一个表中添加行时才需要等待获得锁定，我认为这减少了竞争条件问题。

有没有更好的办法解决这个问题？

我不想对所有公司的产品表使用一个序列，因为产品 ID 之间的差异会很大，我想对用户保持简单。

Answer 1

您可以在公司表中嵌入一个计数器:

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 ... RETURNING 获取给定公司的下一个产品 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');

请务必在产品值和 next_product_id(company INT) 的参数中使用相同的公司 ID。