1

Observation: I have a serial index on a table in a PostgreSQL instance, and I execute a function that inserts a new record into that table. The function fails (reason not important), and the transaction is rolled back...but the index incremented and was not rolled back. This results in unused sequence values, e.g. a table with indexes 1, 2, 4, 5, 7 etc. with no deletes.

Question: Is there any way to reclaim the lost sequence values, or prevent the failed function from incrementing the index?

I know that there are alternative ways to mitigate this potential problem, but I am still curious.

  • Just a note on terminology: there is no such thing as a serial index. There is the serial (and bigserial) pseudotype, which means integer DEFAULT nextval('sequence_name'::regclass). The auto-generated values come from the sequence - and, of course, you can build an index on such a column. – dezso Nov 2 '15 at 9:09
7

This results in unused sequence values

That's totally normal, and by design. It's also not the only way you can get such gaps. See the note on nextval in the manual.

Is there any way to reclaim the lost sequence values

No.

or prevent the failed function from incrementing the sequence?

Don't use a SEQUENCE. Instead, if you must have gapless values, use an ID-generating table via UPDATE ... RETURNING. See gapless sequences for more info.

Note that it's not possible to have concurrent inserts without possible gaps. You get one or the other. So think very hard about whether you really need gapless.

For most applications you should just use a SEQUENCE (SERIAL) and design for the fact that gaps are possible and normal.

  • Thank you for the thorough answer! I do not need gapless, I just hadn't observed that behaviour before and got curious. – Piers Mana Nov 2 '15 at 3:06
  • 2
    @LPP It's a pretty fundamental requirement for concurrent inserts with surrogate keys. Otherwise, if there are multiple tx's inserting at once, and one in the middle (as far as the numeric sequence of values) rolls back, you'd have to roll back all the later ones too. What a mess. And that's only in a simple one-insert-per-transaction case; it gets way worse. – Craig Ringer Nov 2 '15 at 3:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.