Thread: Sequences do not obey transactions...
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/ |
---------------------------------------------------------------------------
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/ | > -------------------------------------------------------------------------- - >
Ryan Kirkpatrick wrote: > Ryan, This issue has been asked & answered MANY times, once a week perhaps. I'll copy here what a folk answered once "You can't. Sequences are not designed for continuity, they are designed for uniqueness. If you want to have a set of contiguous numbers, in ascending order, then you will probably have to write a trigger to insert the next value, which it has to scan the table to work out. And you have to decide what to do in case of deletions: do you reuse the number on the next insert (add complexity and run-time to the code), or just carry on anyway, meaning that you have holes in your sequence, in which case, you could have used a sequence anyway, probably. Depending on the number of expected rows in the table, you may find that the time to insert doesn't justify having contiguous numbers. For each insert, the minimum you are going to get away with is a full table scan." And I add one of my own: It is not really necessary to have continuity in nearly all apps. Your question is valid anyhow, but ask yourself: How does Oracle resolve this? How would I program it myself by hand? And there you'll understand the issue deeply. My regards, Haroldo. > 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). > -- ----------------------+------------------------ Haroldo Stenger | hstenger@ieee.org Montevideo, Uruguay. | hstenger@adinet.com.uy ----------------------+------------------------ Visit UYLUG Web Site: http://www.linux.org.uy -----------------------------------------------
Haroldo Stenger wrote: > And I add one of my own: It is not really necessary to have continuity in nearly > all apps. Your question is valid anyhow, but ask yourself: How does Oracle > resolve this? How would I program it myself by hand? And there you'll understand > the issue deeply. How funny it is to quote myself :9 I'd like to add this: When one has to number paper forms (invoices for instance), one must be careful about holes in sequences. What I do, is to have two sequences. One for the user, and one for the system. When the user has confirmed all her data, I'll COPY to another table the data, which NOW doesn't have a chance to cancel. Well, not so sure, but 99% of aborts, are user aborts. So this may help. Note to Bruce (or current FAQ mantainer): Please, add both the answer to the very question, and this addition of my own to the FAQ. I would have loved to find it somewhere, when I didn't know what to do. Regards, Haroldo. -- > ----------------------+------------------------ > Haroldo Stenger | hstenger@ieee.org > Montevideo, Uruguay. | hstenger@adinet.com.uy > ----------------------+------------------------ > Visit UYLUG Web Site: http://www.linux.org.uy > ----------------------------------------------- -- ----------------------+------------------------ Haroldo Stenger | hstenger@ieee.org Montevideo, Uruguay. | hstenger@adinet.com.uy ----------------------+------------------------ Visit UYLUG Web Site: http://www.linux.org.uy -----------------------------------------------
On Wed, 21 Jun 2000, Haroldo Stenger wrote:
> This issue has been asked & answered MANY times, once a week perhaps. I'll copy
> here what a folk answered once
Hmm.. Then if the email archive searchs on the web site had been
working I would not have had to was the list's time. <subtle hint to the
maintainer of the email list archives :>
As for the comments by people that pgsql's sequence behavior is
well documented, please tell me where! I have looked through the HTML does
and the FAQ that comes with Pgsql 7.0.2 and found no mention of it.
> "You can't. Sequences are not designed for continuity, they are designed for
> uniqueness.
Now that I think about it (again w/other people's explainations
taken into account) pgsql's behavior now makes sense to me. I was just
looking for a continuous sequence of unique numbers and thought a sequence
might be handy. Guess not. :( Thanks for everyone's explaination.
> Haroldo Stenger wrote:
> > And I add one of my own: It is not really necessary to have continuity in nearly
> > all apps. Your question is valid anyhow, but ask yourself: How does Oracle
> > resolve this? How would I program it myself by hand? And there you'll understand
> > the issue deeply.
>
> How funny it is to quote myself :9 I'd like to add this: When one has to number
> paper forms (invoices for instance), one must be careful about holes in
> sequences. What I do, is to have two sequences. One for the user, and one for
> the system. When the user has confirmed all her data, I'll COPY to another table
> the data, which NOW doesn't have a chance to cancel. Well, not so sure, but 99%
> of aborts, are user aborts. So this may help.
That is similar to what I was attempting to do. I am making an
inventory database where each piece of computer equipment has a unique
number assigned to it. A sequence would save me having to figure out what
number was next. But if it had holes in the sequence, then I would end up
wasting the sequential labels I had already printed. :(
Given my situtation, I think I will just use the label sheet to
tell me which number is next and enter it in from there. Low-tech, but
should work. :)
> Note to Bruce (or current FAQ mantainer): Please, add both the answer to the
> very question, and this addition of my own to the FAQ. I would have loved to
> find it somewhere, when I didn't know what to do.
Yes, please do. I always search the documentation and email
list archives (if available) before asking a question. Guess in this case
I hit a question that fell through the cracks before making it into the
FAQ.
Once again, thanks for everyone's help.
---------------------------------------------------------------------------
| "For to me to live is Christ, and to die is gain." |
| --- Philippians 1:21 (KJV) |
---------------------------------------------------------------------------
| Ryan Kirkpatrick | Boulder, Colorado | http://www.rkirkpat.net/ |
---------------------------------------------------------------------------