Thread: Can I prevent a sequence from being updated when a transaction fail?

Hello All! :)

I have a sequence that I use to get an unique default value for an
attribute in one of my tables. The sequence is incremented by one each
time I insert a new tuple in the table. Excellent! :)

An example:
Given the sequence 'serial' and the relation 'test':

Table test
----------
number : integer default nextval('serial')
whatever : text

Table 'test' is constrained by having the attribute 'whatever' being a
member of the set {'foo', 'bar'}.

Now I execute the following transaction:

BEGIN;
   INSERT INTO test (whatever) values('Mars bar');
ROLLBACK;

This INSERT is obviously illegal given the constraint on attribute
'whatever'.  BUT, even after the rollback, the value of the sequence
'serial' remains incremented by the failing INSERT statement??!

How come sequences are not rolled back when a transaction is aborted, and
can I do anything to prevent a failing statement from updating the
involved sequence?


--

Thomas Holmgren
Institute for Computer Science
University of Aalborg
Denmark

Thomas Holmgren wrote:
> How come sequences are not rolled back when a transaction is aborted, and
> can I do anything to prevent a failing statement from updating the
> involved sequence?

No. You can't. To achieve what you want, the sequence counter should be
locked. That would mean that parallel insertions in the same table from
other transactions, should wait for the first to end. And that is not
acceptable.

If you don care about it, i.e., either your aplication never inserts
parallelly in a table, or a second insert can wait for the first to end,
then use your own counter in an auxiliary table. This will do what you
asked.

Haroldo.