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.
推荐答案
序列用于生成唯一标识符-
不一定是严格顺序。如果两个
并发数据库客户端都尝试从
序列中获取值(使用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屋!