Re: arbitrary number of values from a sequence - Mailing list pgsql-general

From Gyozo Papp
Subject Re: arbitrary number of values from a sequence
Date
Msg-id 015801c0d4c0$28c0b0a0$3b46c5d5@jaguar
Whole thread Raw
In response to arbitrary number of values from a sequence  ("Gyozo Papp" <pgerzson@freestart.hu>)
Responses Re: arbitrary number of values from a sequence
List pgsql-general
Thanks for your fast reply.

> No good, because there's no interlock being held between the nextval()
> and the setval().  So, while process A is busy adding 5 to the nextval()
> result it got, process B could sneak in and do a nextval().  It will
> then be allocated one of the values that A thinks it's reserved.  After
> the setval() occurs, there's not even any way to see anything's wrong.

That's exactly what I'm worried about.

> I do not think you can avoid calling nextval() 5 times, in the current
> implementation; nor can you assume you will get 5 consecutive values.

> However, you might be able to improve efficiency by setting the 'cache'
> value of the sequence to be more than one.  Read the caution about
> 'cache' on the CREATE SEQUENCE manual page first...

I've already read the manual, but I'm confused a bit.
(from the manual page)
    " Furthermore, although multiple backends are guaranteed to allocate distinct sequence values, the values may be
generatedout of sequence when all the backends are considered. (For example, with a cache setting of 10, backend A
mightreserve values 1..10 and return nextval=1, then backend B might reserve values 11..20 and return nextval=11 before
backendA has generated nextval=2.) " 

Does it mean that it can't be ensured that returning values of nextval() are consecutive ones?
does it help me if I set the transaction isolation level to serializable or lock the table of the sequence?


thank in advance,

Papp Gyozo
- pgerzson@freestart.hu


pgsql-general by date:

Previous
From: Joel Burton
Date:
Subject: Re: a primer on trigger?
Next
From: Andy Koch
Date:
Subject: debugging Pl/Tcl