Postgresql错误的串行自动增量 [英] Postgresql wrong auto-increment for serial

问题描述

我在postgresql上遇到问题，我认为在postgresql中存在错误，我错误地实现了某些东西。

I have a problem on postgresql which I think there is a bug in the postgresql, I wrongly implement something.

有一个包含 colmn1（主键）， colmn2（唯一）， colmn3 ，...

There is a table including colmn1(primary key), colmn2(unique), colmn3, ...

插入一行后，如果我尝试另一个具有现有 colmn2 值的插入我按预期得到了重复值错误。但是，在尝试失败之后， colmn1 的下一个值是
，尽管没有插入，但它的值增加了1，所以我得到的行具有ID序列，如 1,2,4,6,9。（3,5,6,7,8 用于失败的试验）。

After an insertion of a row, if I try another insertion with an existing colmn2 value I am getting a duplicate value error as I expected. But after this unsuccesful try, colmn1's next value is incremented by 1 although there is no insertion so i am getting rows with id sequences like , 1,2,4,6,9.(3,5,6,7,8 goes for unsuccessful trials).

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

I need help from the ones who can explain this weird behaviour.

此信息可能有用：我使用了 在tableName（lower（column1））上创建唯一索引查询设置唯一约束。

This information may be useful: I used "create unique index on tableName (lower(column1)) " query to set unique constraint.

推荐答案

请参见 PostgreSQL序列常见问题解答：

序列用于生成唯一标识符-
不一定是严格顺序。如果两个
并发数据库客户端都尝试从
序列中获取值（使用nextval（）），则每个客户端将获得不同的序列
值。如果这些客户之一随后中止交易，则
为该客户生成的序列值将不被使用，
会在序列中产生缺口。

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.

要解决此问题，必须付出明显的性能
罚款。有关更多信息，请参阅《通用位通讯》中的Elein Mustein的 主键的无间隙序列。

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.

这篇关于Postgresql错误的串行自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！