database - Postgres 动态创建序列

Question

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

目前，我有一个“报告”表，其中包含所有已提交的报告，其中有一个“id”字段，该字段是表上的串行主键。

我指定的要求是用户需要能够为他们的报告指定前缀和数字以开始计数。例如，用户应该能够说他们的报告从 ABC-100 开始，然后下一个是 ABC-101、ABC-102，依此类推。

我想实现这个的方法是，当用户创建一个帐户时，他可以指定前缀和起始编号，我将创建一个 postgres 序列，其中包含指定的前缀名称和 minValue用户希望报告开始的编号。

然后当用户提交新报告时，我可以将 report_number 标记为 nextval(prefix_sequence)。从理论上讲，这会起作用，但我对 postgres 还很陌生，我需要一些建议和反馈，看看这是对序列的良好使用，还是有更好的方法。

Answer 1

这是一个您可能不需要序列的关键优势的领域——它们可以被多个事务并发使用。您可能也不希望出现相应的缺点，即序列中的间隙是正常的。如果您有并发事务、回滚等，那么得到类似 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。这将起作用，因为 UPDATE 相关 account_sequences 行的每个事务都会锁定它所持有的行，直到它提交或回滚。其他尝试为同一帐户获取 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) ... 的子查询的幼稚方法。它是完全并发不安全的，如果同时运行多个事务，它会产生错误的结果或错误。而且速度很慢。