Re: Rollback & Nextval fails

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(at)ucs(dot)co(dot)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