Re: Transactions + sequences

On Fri, 22 Sep 2000, Neil Conway wrote:

> Consider the following (PgSQL 7.0.2):
>
> CREATE SEQUENCE foo;
> SELECT nextval('foo');
> -- returns 1
> BEGIN WORK;
> SELECT nextval('foo');
> -- returns 2
> ABORT WORK;
> SELECT nextval('foo');
> -- returns 3
>
> As you can see, even though the transaction has been aborted, the
> sequence is still incremented. Is there any way to work around this?
> Are there any plans to change this behavior in the future?

Not really.  There's a locking issue involved in rolling back a sequence.
In the current implementation if two backends want to call nextval, they
each get a value and don't have to worry about whether the other one will
rollback.  If you wanted the one that called nextval second to not skip a
number if the other aborts, you'd have to wait until it has aborted or
committed to know what number to return.