Re: Sequences do not obey transactions...

That is not a bug, it is well documented behavior. PostgreSQL will NOT roll
back a sequence for any reason, regardless of whether it is in a transaction
that has been rolled back. Think of how you would have to code a sequence to
support that type of behavior. In the case of multiple clients drawing
numbers from that sequence, you would have to lock the sequence and make the
others wait until your transaction is completed. Not scalable. If you are
not worried about multiple clients, then your best bet is to create a "next
number" table and lock, increment, unlock the table yourself.

My experience is that even doing it this way, you occasionally get "holes"
in the next number table due to exceptions, system crashes, etc. Your best
bet is to learn to code you application to live with sequences, knowing that
they are sequential but not necessarily continuous.

>
> 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/
|
> --------------------------------------------------------------------------
-
>