From: Haroldo Stenger <hstenger@adinet.com.uy>
To: Ryan Kirkpatrick <pgsql@rkirkpat.net>
To: pgsql-general@postgresql.org
Subject: Re: Sequences do not obey transactions...
Date: Wed, 21 Jun 2000 02:08:35 -0300
Message-ID: <39504DD3.6744F245@adinet.com.uy> (raw)
References: <Pine.LNX.4.10.10006202027300.10276-100000@excelsior.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
-----------------------------------------------
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: hstenger@adinet.com.uy, pgsql@rkirkpat.net, pgsql-general@postgresql.org
Subject: Re: Sequences do not obey transactions...
In-Reply-To: <39504DD3.6744F245@adinet.com.uy>
References: <Pine.LNX.4.10.10006202027300.10276-100000@excelsior.rkirkpat.net> <39504DD3.6744F245@adinet.com.uy>
* 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