From: Chris Browne <cbbrowne@acm.org>
Subject: Re: Sequences, values still increased
Date: Wed, 19 Jul 2006 08:47:37 -0400
Organization: cbbrowne Computing Inc
Lines: 33
Message-ID: <60psg1ahhy.fsf@dba2.int.libertyrms.com>
References: <e9l4as$2bok$1@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.19 (linux)
Cancel-Lock: sha1:6NyZ8zCFnfdXMMBuxrlg0v5kRoE=
To: pgsql-sql@postgresql.org

Paul Maier <paul.meier@t-online.de> writes:
> Hello everybody,
>
> Why does...
>
> BEGIN;
> SELECT nextval('test.test_seq') AS id;
> ROLLBACK;
>
> ...still increase the sequence after aborting the transaction? This
> nextval-command should be reverted after rolling back, right? Same with an
> ABORT:
>
> BEGIN;
> SELECT nextval('test.test_seq') AS id;
> ABORT;
>
> Can anybody help me with this? Thanks a lot!

nextval does NOT roll back.

As per the Fine Documentation on Sequence Manipulation Functions:

"Important: To avoid blocking of concurrent transactions that obtain
numbers from the same sequence, a nextval operation is never rolled
back; that is, once a value has been fetched it is considered used,
even if the transaction that did the nextval later aborts. This means
that aborted transactions may leave unused "holes" in the sequence of
assigned values. setval operations are never rolled back, either."
-- 
output = ("cbbrowne" "@" "ntlug.org")
http://cbbrowne.com/info/lsf.html
I'm as confused as a baby in a topless bar.