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