基于PostgreSQL公司ID的序列 [英] postgresql company id based sequence

问题描述

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

I have a database with companies and their products, I want for each company to have a separate product id sequence.

我知道postgresql可以不这样做，唯一的方法是为每个公司设置一个单独的序列，但这很麻烦。

I know that postgresql can't do this, the only way is to have a separate sequence for each company but this is cumbersome.

我考虑了一个解决方案，即使用一个单独的表来容纳序列

I thought about a solution to have a separate table to hold the sequences

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

table将是序列的表名，例如产品，类别等。

"table" will be holt the table name for the sequence, such as products, categories etc.

，而值将保留将用于插入时product_id的实际序列数据

and value will hold the actual sequence data that will be used for product_id on inserts

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

I will use UPDATE ... RETURNING value; to get a product id

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

I was wondering is this solution efficient?

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

With row level locking, only users of same company adding rows in the same table will have to wait to get a lock and I think that reduces race condition problems.

是否有更好的方法来解决此问题？

Is there a better way to solve this problem?

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

I don't want to use a sequence for products table for all companies because the difference between product id's will be to big, I want to keep it simple for the users.

推荐答案

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

You could just embed a counter in your companies table:

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：

Then, use UPDATE ... RETURNING to get the next product ID for a given company:

> 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作为子查询。

Oh noes! It seems you can't (as of PostgreSQL 9.1devel) use UPDATE ... RETURNING as a subquery.

好消息是，这不是问题！只需创建一个存储过程来执行增量/返回部分：

The good news is, it's not a problem! Just create a stored procedure that does the increment/return part:

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');

请务必在产品值和<$ c $的参数中使用相同的公司ID c> next_product_id（公司INT）。

这篇关于基于PostgreSQL公司ID的序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！