主键值未正确递增 [英] Primary Key Value Not Incrementing Correctly

问题描述

我有一个django模型，其id开始奇怪地增加。

I have a django model that started to have its ids increment strangely.

该列的postgres定义（从Django Model生成）：

The postgres definition of the column (generated from Django Model):

id | integer | not null default nextval('billing_invoice_id_seq'::regclass)

tpg=> SELECT MAX(id) FROM billing_invoice;
  max  
-------
 16260

然后，我通过django管理员创建了一条新记录：

Then I created a new record through the django admin:

tpg=> SELECT MAX(id) FROM billing_invoice;
  max  
-------
 17223

tpg=> SELECT nextval('billing_invoice_id_seq');
 nextval 
---------
   17224

然后我创建了一条新记录，该记录跳过了17224的值，并插入了主键17225：

Then I created a new record which skipped the 17224 value and was inserted with primary key of 17225:

tpg=> SELECT nextval('billing_invoice_id_seq');
 nextval 
---------
   17226

有人知道为什么会这样吗？该应用程序此时不关心，因为id仍在递增，但是在最后几个新对象中，PK在一个插入中从427-> 4357跳过，然后在2个对象中跳至8378，它跳到97xx，然后在3个对象中跳了

Does anyone have any idea why this is occurring? The application doesn't care at this point because the ids are still incrementing, but over the last couple new objects the PKs have skipped from 427 -> 4357 in one insert and then to 8378 in 2 objects it jumped to 97xx then in 3 objects it jumped to 14k.

推荐答案

从序列中默认使用的序列列是 从不 保证为无缝隙。保证它们是唯一和升序（已定义），并且可安全同时使用。

从序列中提取了一个数字，该数字已回滚，该数字已被烧毁，不再使用... ... 每个文档：

Serial columns taking their default from sequences are never guaranteed to be gapless. They are guaranteed to be unique and ascending (as defined) and safe for concurrent use.
If a transaction that has drawn a number from the sequence is rolled back, the number is burnt and not used again ... Per documentation:

注意：因为 smallserial ， serial 和 bigserial 是使用
序列实现的，可能存在漏洞或列中出现的值序列
中的间隔，即使从未删除任何行也是如此。即使从未成功将包含该值的行
插入表
列中，也仍然用完从序列中分配的值
。例如，如果插入事务回滚，则可能会发生这种情况。
请参见nextval（） = nofollow>第9.16节以获取详细信息。

Note: Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still "used up" even if a row containing that value is never successfully inserted into the table column. This may happen, for example, if the inserting transaction rolls back. See nextval() in Section 9.16 for details.

如果您看到诸如 427之类的较大空白-> 4357 ，则表明存在严重问题。

If you see large gaps like 427 -> 4357, then this indicates a serious problem. Either some other column (or any process) is drawing from the same sequence, or you have a problem with your application logic, somehow burning a lot of serial IDs.

典型的情况是，其他某些列（或任何过程）是从相同的序列中提取的，或者您的应用程序逻辑存在问题，从而以某种方式刻录了许多序列ID。

Typical candidates are loops gone wrong or transactions that never got committed.

这篇关于主键值未正确递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！