sql - Postgresql 错误的串行自动递增

Question

我在 postgresql 上有一个问题，我认为 postgresql 中有一个错误，我错误地实现了一些东西。

有一个表包括colmn1(primary key), colmn2(unique), colmn3, ...

插入一行后，如果我尝试使用现有的 colmn2 值进行另一次插入，我会如预期的那样收到重复值错误。但是在这次不成功的尝试之后，colmn1 的下一个值是虽然没有插入，但增加了 1，所以我得到的行具有 id 序列，例如 1,2,4,6,9。(3,5,6,7,8 用于不成功的试验) .

我需要能够解释这种奇怪行为的人的帮助。

此信息可能有用:我使用“create unique index on tableName (lower(column1))”查询来设置唯一约束。

Answer 1

参见 PostgreSQL sequence FAQ :

Sequences are intended for generating unique identifiers — not necessarily identifiers that are strictly sequential. If two concurrent database clients both attempt to get a value from a sequence (using nextval()), each client will get a different sequence value. If one of those clients subsequently aborts their transaction, the sequence value that was generated for that client will be unused, creating a gap in the sequence.

This can't easily be fixed without incurring a significant performance penalty. For more information, see Elein Mustein's "Gapless Sequences for Primary Keys" in the General Bits Newsletter.