Date: Tue, 11 Mar 2003 23:34:42 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: =?iso-8859-1?q?Muhammad=20Shariq=20Muzaffar?= <shariq77@yahoo.com>
Cc: <pgsql-sql@postgresql.org>, <pgsql-novice@postgresql.org>
Subject: Re: [SQL] SERIAL does not ROLLBACK
In-Reply-To: <20030312072122.12363.qmail@web41112.mail.yahoo.com>
Message-ID: <20030311232934.V49459-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Wed, 12 Mar 2003, [iso-8859-1] Muhammad Shariq Muzaffar wrote:

> hi
> i have a table named 'mytable' with a column of type
> serial. After inserting tuples in a transaction, when
> i rollback the transaction the serial number does not
> come back to its original value and next time when i
> insert the data, it start with the incremented value.
> WHY?? how can i make it to come back to its orignial
> value in case of rollback.

That's pretty much the point, so you can't easily. Serials (despite their
name) are really intended to give unique values not sequential ones.
Rolling back the value causes some issues with concurrency, and I think ou
can already pretty much implement a rollback one yourself with functions
and locks.

Specifically, if you have two concurrent transactions that want to get a
value from the serial, what happens?  If the second transaction waits for
the first, you have poor concurrency.  If it doesn't, then what happens
if the first gets say 1 and the second 2 and then the first rolls back?