Either I am missing something or I found a bug in PostgreSQL.
Hopefully it is the former. :)
Simply, I am trying to use a sequence to generate unique id
numbers for a table. Now, a number of the fields in this table have 'check
constraints'. What happens, is if I attempt to insert a row into the table
that fails to meet the constraints and is rejected, the sequence is still
incremented. Therefore, for each failed insert, a hole results in my id
number sequence. While this is not fatal, it is very annoying.
I even tried wrapping a BEGIN / END around a failing insert and
the sequence still incremented. It appears that whenever the 'nextval'
function is called, no matter where, in a failing insert, inside an
aborted transaction, etc..., the changes it makes to the sequence are
permanent.
So is this supposed to be this way, or did I stumble across a bug?
If the former, would some one please explain why this is this way (and
possibly add it to the documenation). Thanks.
PS. The mailing list search engines on the pgsql web site are
broken. They either find nothing, no matter what search terms one enters,
or complain about not being able to find the needed tables (relations).
---------------------------------------------------------------------------
| "For to me to live is Christ, and to die is gain." |
| --- Philippians 1:21 (KJV) |
---------------------------------------------------------------------------
| Ryan Kirkpatrick | Boulder, Colorado | http://www.rkirkpat.net/ |
---------------------------------------------------------------------------