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.
serial
(andbigserial
) pseudotype, which meansinteger 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.