From: Bruce Bantos <anon@mgfairfax.rr.com>
To: Ryan Kirkpatrick <pgsql@rkirkpat.net>
Cc: pgsql-general@postgresql.org
Subject: Re: Sequences do not obey transactions...
Date: Tue, 20 Jun 2000 23:12:38 -0400
Message-ID: <00d101bfdb2e$8e6c3d20$0200a8c0@RSGROUP> (raw)
References: <Pine.LNX.4.10.10006202027300.10276-100000@excelsior.rkirkpat.net>
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/|
> --------------------------------------------------------------------------
-
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: anon@mgfairfax.rr.com, pgsql@rkirkpat.net, pgsql-general@postgresql.org
Subject: Re: Sequences do not obey transactions...
In-Reply-To: <00d101bfdb2e$8e6c3d20$0200a8c0@RSGROUP>
References: <Pine.LNX.4.10.10006202027300.10276-100000@excelsior.rkirkpat.net> <00d101bfdb2e$8e6c3d20$0200a8c0@RSGROUP>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox