3 ms·

Thank you for uncovering this edge case. To summarize, Postgres reserves a batch of 32 serial numbers from its sequence objects. Then, in the case of a crash, o

Thank you for uncovering this edge case. To summarize, Postgres reserves a batch of 32 serial numbers from its sequence objects. Then, in the case of a crash, or the promotion of a "follower", that batch is lost. I consider ID numbers somewhat opaque, like GUIDs but maybe not that opaque. Fretting about gaps in ID numbers can cause hair loss. This is just one of many ways gaps can happen. It is just an artifact of "sequences", the database object in Postgres that autogenerates the "next" ID number for a column --- automatically set up if you declare a column of type "serial", but that's all a serial column is. Serial just means: make the column of type integer, and create a sequence for it, and set the column's default value to nextval(sequence). You can mitigate gaps somewhat, like if you're testing and retesting a bunch of inserts, rolling back each time. Well, after a few tests, the next number in the sequence is far beyond the last number in the table. So you can call another sequence function, setval, to reset it. Something like: select setval('sequence_name', (select max(id) from table)); Further reading: https://www.postgresql.org/docs/current/functions-sequence.html https://www.postgresql.org/docs/current/functions-sequence.h...

merb 5y ago
btw. it's easy to use FOR UPDATE to create custom sequences in a table that do not have the batch reservation feature. it's just slower if you can afford that. the problem with resetting the sequence is that it would need a full blown serilizable transaction which is worse thatn just using for update and read commited.
- combatentropy 5y ago
  Right. It isn't something I recommend running in production before every insert. It's just something I have done sometimes, like as part of a big database change, and only to "reference" tables. Like suppose there is a table of 10 rows to populate a dropdown menu. Now I need to add an 11th, but for some reason the sequence got out of wack, and the next sequence value is 15. So I manually set it back to 11.
marcosdumay 5y ago
At this point, you can just add a key to the column, set it to max(column) + 1 in an update, and forget about the sequence. Either way, it's better done in a column that isn't the primary key.
CodesInChaos 5y ago
That's what I'd run as a one time cleanup in the OP's place, to minimize the customer visible impact, since it avoids the gap for everybody who was affected by the skip but didn't have an incident since then.

merb 5y ago

btw. it's easy to use FOR UPDATE to create custom sequences in a table that do not have the batch reservation feature. it's just slower if you can afford that. the problem with resetting the sequence is that it would need a full blown serilizable transaction which is worse thatn just using for update and read commited.

combatentropy 5y ago
Right. It isn't something I recommend running in production before every insert. It's just something I have done sometimes, like as part of a big database change, and only to "reference" tables. Like suppose there is a table of 10 rows to populate a dropdown menu. Now I need to add an 11th, but for some reason the sequence got out of wack, and the next sequence value is 15. So I manually set it back to 11.

marcosdumay 5y ago

At this point, you can just add a key to the column, set it to max(column) + 1 in an update, and forget about the sequence. Either way, it's better done in a column that isn't the primary key.

CodesInChaos 5y ago

That's what I'd run as a one time cleanup in the OP's place, to minimize the customer visible impact, since it avoids the gap for everybody who was affected by the skip but didn't have an incident since then.