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

From: Thomas Holmgren <thm(at)cs(dot)auc(dot)dk>
To: pgsql-general(at)postgresql(dot)org(dot)pgsql-questions(at)postgresql(dot)org
Subject: Can I prevent a sequence from being updated when a transaction fail?
Date: 2000-06-04 22:02:47
Message-ID: Pine.GSO.4.21.0006050001190.1138-100000@luke.cs.auc.dk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Haroldo Stenger 2000-06-05 00:09:09 Re: Can I prevent a sequence from being updated when a transaction fail?
Previous Message Charles Tassell 2000-06-04 19:04:53 Re: Compiling Error