From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Muhammad Shariq Muzaffar <shariq77@yahoo.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-novice@postgresql.org
Subject: Re: [SQL] SERIAL does not ROLLBACK
Date: Tue, 11 Mar 2003 23:34:42 -0800 (PST)
Message-ID: <20030311232934.V49459-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <20030312072122.12363.qmail@web41112.mail.yahoo.com>
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, shariq77@yahoo.com, pgsql-novice@postgresql.org
Subject: Re: [SQL] SERIAL does not ROLLBACK
In-Reply-To: <20030311232934.V49459-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox