4

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.

Improve this question
1
  • 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, 2015 at 9:09

1 Answer 1

Reset to default
11

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.

Improve this answer
2
  • 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, 2015 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, 2015 at 3:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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