pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Rollback & Nextval fails
2+ messages / 2 participants
[nested] [flat]

* Rollback & Nextval fails
@ 2000-05-29 08:54  Eriksson, Fredrik <Fredrik_Eriksson@NAI.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Eriksson, Fredrik @ 2000-05-29 08:54 UTC (permalink / raw)
  To: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>

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?

Cheers,

Fredrik



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Rollback & Nextval fails
@ 2000-05-29 09:50  Grant Finnemore <gaf@ucs.co.za>
  parent: Eriksson, Fredrik <Fredrik_Eriksson@NAI.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Grant Finnemore @ 2000-05-29 09:50 UTC (permalink / raw)
  To: pgsql-sql@hub.org; +Cc: Eriksson, Fredrik <Fredrik_Eriksson@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






^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2000-05-29 09:50 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-29 08:54 Rollback & Nextval fails Eriksson, Fredrik <Fredrik_Eriksson@NAI.com>
2000-05-29 09:50 ` Grant Finnemore <gaf@ucs.co.za>

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