From: Grant Finnemore <gaf@ucs.co.za>
To: pgsql-sql@hub.org
Cc: Eriksson, Fredrik <Fredrik_Eriksson@NAI.com>
Subject: Re: Rollback & Nextval fails
Date: Mon, 29 May 2000 11:50:10 +0200
Message-ID: <39323D52.4613A1D6@ucs.co.za> (raw)
References: <890BFCE1028AD211833700A0C9F2770C27E0B1@dns-155-111.dhcp.nai.com>
Fredrik,
> I have been trying the following SQL code :
>
> BEGIN;
> INSERT INTO table VALUES ( NEXTVAL('serial'), 'Data' );
> ROLLBACK;
>
> And the insert function is rolled back but the serial sequence isn't. Hav I
> misunderstood the functionality of rollback or is this a bug? Is there
> someway to get the functionality that rollsback everything?
>
It is not a bug. In order to enable concurrent users access to the nextval()
function, every call to that function will increment the counter. Should a
client rollback, we cannot re-use the number(s) allocated to them, as other
clients might already have been allocated higher numbers. This means that a
full table scan would be required to allocate "blank" numbers inside the
sequence. Even this would not solve the issue of contiguous numbering in the
table, as at any point in time, "blanks" might exist.
If you *really* need a sequence with no unused numbers, you might consider
creating a table using a single row as the current sequence number. Use either
SQL or a stored proc. to lock the row and increment the value for the counter
on each next value that you require. This has the effect of serializing every
client update transaction where this scheme is used. Can you afford that?
Regards,
Grant
--
> Poorly planned software requires a genius to write it
> and a hero to use it.
Grant Finnemore BSc(Eng) (mailto:gaf@ucs.co.za)
Software Engineer Universal Computer Services
Tel (+27)(11)712-1366 PO Box 31266 Braamfontein 2017, South Africa
Cell (+27)(82)604-5536 20th Floor, 209 Smit St., Braamfontein
Fax (+27)(11)339-3421 Johannesburg, South Africa
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: gaf@ucs.co.za, pgsql-sql@hub.org, Fredrik_Eriksson@NAI.com
Subject: Re: Rollback & Nextval fails
In-Reply-To: <39323D52.4613A1D6@ucs.co.za>
References: <890BFCE1028AD211833700A0C9F2770C27E0B1@dns-155-111.dhcp.nai.com> <39323D52.4613A1D6@ucs.co.za>
* 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