PostgreSQL动态创建序列

Question

PostgreSQL动态创建序列

databasepostgresqldatabase-design

4

我正在编写一款应用程序，在该应用程序中有多个用户可以上传报告。

目前，我有一个“报告”表格，其中包含所有提交的报告，该表格具有一个“ID”字段，它是表格上的序列主键。

我的要求规定，用户需要能够指定其报告的前缀和编号以开始计数。例如，用户应该能够说他们的报告从ABC-100开始，然后下一个是ABC-101，ABC-102等等。

我考虑实现这一点的方式是当用户创建帐户时，他可以指定前缀和起始数字，然后我将使用所指定的前缀名称和用户想要报告开始的数字作为minValue创建一个postgres序列。

然后，当用户提交新报告时，我可以将报告号标记为nextval（prefix_sequence）。理论上，这将有效，但我对postgres相当陌生，我想听听是否有关于序列的更好的建议和反馈。

- aloisbarreras

这些报告一定要按顺序吗？因为序列并不能保证...错误、重启和其他问题可能会导致序列中出现间隙。 - Joe Love

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Craig Ringer · Accepted Answer

这是一个你可能不需要序列的主要优势——它们可以被多个事务同时使用的领域。你也可能不想要相应的缺点，即序列中存在间隙是正常的。如果你有并发事务、回滚等，很正常会得到像1、2、4、7、8、12...这样的输出。

在这种情况下，使用计数器会更好。当用户创建账户时，在account_sequences表中创建一个行，如(account_id, counter)。不要将其存储在主账户表中，因为你需要频繁锁定和更新它，并希望最小化VACUUM工作量。

例如：

CREATE TABLE account_sequences
(
    account_id integer PRIMARY KEY REFERENCES account(id),
    counter integer NOT NULL DEFAULT 1,
);

现在编写一个简单的 LANGUAGE SQL 函数，如下：

CREATE OR REPLACE FUNCTION account_get_next_id(integer)
RETURNS integer VOLATILE LANGUAGE sql AS
$$
UPDATE account_sequences
SET counter = counter + 1
WHERE account_id = $1
RETURNING counter
$$;

您可以使用此方法代替nextval。

这将奏效，因为对相关的account_sequences行进行UPDATE的每个事务都会锁定它所持有的行，直到它提交或回滚。尝试获取相同帐户ID的其他事务将等待它完成。

要了解更多信息，请搜索“postgresql无间断序列”。

如果您想要，可以使您的SQL函数也获取前缀，并使用format将其与生成的值连接起来，然后返回一个text结果。如果您将prefix text NOT NULL列放入您的account_sequences表中，这将更容易实现，这样您就可以执行以下操作：

CREATE OR REPLACE FUNCTION account_get_next_id(integer)
RETURNS text VOLATILE LANGUAGE sql AS
$$
UPDATE account_sequences
SET counter = counter + 1
WHERE account_id = $1
RETURNING format('%s%s', prefix, counter)
$$;

顺便提一下，不要采用使用子查询和 SELECT max(id) ... 的天真方式。这种方式完全不支持并发，如果多个事务同时运行，它会产生错误的结果或错误。此外，它还很慢。