Re: Sequences do not obey transactions...

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(at)ieee(dot)org
Montevideo, Uruguay. | hstenger(at)adinet(dot)com(dot)uy
----------------------+------------------------
Visit UYLUG Web Site: http://www.linux.org.uy
-----------------------------------------------