How can I create a gapless sequence? 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.
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.