CODEWITHSUNDEEP
djangopostgresqlauto-increment
dm03514
dm03514

Reputation: 55932

Primary Key Value Not Incrementing Correctly

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

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

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

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

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.

Upvotes: 2

Views: 2184

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656301

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:

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.

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.

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

Upvotes: 10

Related Questions