Thread: Transactions + sequences

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?

Just wondering, it's not a life or death matter.

Thanks in advance,

Neil

--
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed

Secrecy is the beginning of tyranny.
        -- Heinlein

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.