postgresql - postgresql sequence next val是否与插入顺序一致？

Question

给定一个包含 id bigint default next_val('foo_sequence') 的表

我可以假定 id 的顺序包含插入顺序吗？

我的意思是后来插入的 id 总是比之前插入的 id 大。

我正在尝试计算并保存增量连续行数，

我是这样做的

SELECT count(*) as seq_no from foo where id < some_id;
// get the seq no

UPDATE foo SET seq_no = seq_no_above + 1 WHERE id = some_id;

但它有时会给出重复的 seq_no 值，如果 id 包含插入顺序，则它不应有重复值。

Answer 1

从最简单和最纯粹的意义上说，是的。不过，这取决于您所说的“早先”和“晚些”是什么意思，因为您必须考虑开启交易和关闭交易。如果一个事务还没有被提交，那么从理论上讲，一个记录可能会晚些时候出现，并带有一个更早的 ID。

ID 是在插入发生时分配的，但记录只有在提交记录后才会显示。因此，如果提交顺序不同，您可能会看到一些奇怪的行为，具体取决于您的用例的严格程度。

即

Open Transaction A
Insert records 1,2

Open Transaction B
Insert records 3,4
Close transaction B

Select * (get 3,4)

Close transaction A

Select * (get 1,2,3,4)

您还必须担心是否认为它们是顺序的缓存。来自(很好)Postgres docs :

Furthermore, although multiple sessions are guaranteed to allocate distinct sequence values, the values might be generated out of sequence when all the sessions are considered. For example, with a cache setting of 10, session A might reserve values 1..10 and return nextval=1, then session B might reserve values 11..20 and return nextval=11 before session A has generated nextval=2. Thus, with a cache setting of one it is safe to assume that nextval values are generated sequentially; with a cache setting greater than one you should only assume that the nextval values are all distinct, not that they are generated purely sequentially. Also, last_value will reflect the latest value reserved by any session, whether or not it has yet been returned by nextval.

最后要注意的是，拥有适当权限的人总是可以将序列重置为不同的值，这显然会给事情带来麻烦。

编辑:

为了解决上述用例，您肯定想使用序列(并且可能还添加 NOT NULL/PRIMARY KEY 约束，以确保唯一性)。至少在 pgAdmin 中，您可以通过设置数据类型 serial 来完成所有这些。尽管我已经提到了注意事项，但出于 99% 的实际目的，您可以获得所需的唯一性和顺序排序(因此 sequences)。

无论如何，我们都需要查看示例数据来确认您看到重复的原因 (how to create a reproducible example)。我假设您看到的重复出现在 seq_no 而不是 id 中，这说明问题出在您的查询上。如果 id 中有重复项，那么您还有其他问题，这可以解释 seq_no 中的重复项。

序列对于数据中的事务定义要好得多(它们会为您处理唯一性，在并发性方面表现良好，并且不会导致重复...而且您在大多数情况下都可以按顺序排序)。对于唯一键，它们最好与 NOT NULL 和 PRIMARY KEY 或 UNIQUE 约束一起使用。

但是如果你需要一个完美的增量，最好做如下的事情:

select *, row_number() over (order by value) as id
from foo
;

Postgres window functions非常强大，但绝对不是用于使用顺序键插入数据的标准。它们对于事后报告、分析和复杂查询更有用。