Thread: arbitrary number of values from a sequence
hello,
I'd like to know if there is any possible solution to allocate arbitrary number of *subsequent values* from a sequence.
I mean it in the following way:
ie.: The sequence 'seq' is at 1234 for the current ...
=#select nextval('seq');
nextval
-------
1234
and I need 5 five subsequent values (from 1234 to 1238), - in other words - a range with a length of 5 from the current
value.I thought this simple trick could do the job :
=#select setval('seq', (select nextval('seq')) + 5);
My question:
is it good if 'seq' can be used concurrently by multiple backends, or is there any other point from that it can be
dangerous/ harmful?
Papp Gyozo
- pgerzson@freestart.hu
"Gyozo Papp" <pgerzson@freestart.hu> writes:
> and I need 5 five subsequent values (from 1234 to 1238), - in other
> words - a range with a length of 5 from the current value. I thought
> this simple trick could do the job :
> =#select setval('seq', (select nextval('seq')) + 5);
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.
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...
regards, tom lane
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
"Gyozo Papp" <pgerzson@freestart.hu> writes:
> Does it mean that it can't be ensured that returning values of
> nextval() are consecutive ones?
I think it would be folly to assume that, even with a cache setting
equal to the number of values you intend to fetch. If the cache gets
out of sync with your requests (say, because a transaction aborted after
fetching just some of the 5 values) then subsequent transactions would
reload the cache partway through, and in that case you could get
non-consecutive results.
> does it help me if I set the transaction isolation level to
> serializable or lock the table of the sequence?
No. Why do you need such a thing, anyway? If you are always allocating
groups of 5 IDs, why don't you just pretend each nextval() gives you
five items instead of one? You could simply multiply the returned value
by 5. Or set the sequence's increment to 5.
regards, tom lane
Ok. I'm looking for another solution. The reason why I'm not dealing with sequence's increment is that there is no way to set an appropiate limit, sometimes I need 5, sometimes 17. Thanks for your help, Papp Gyozo - pgerzson@freestart.hu ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Gyozo Papp" <pgerzson@freestart.hu> Cc: <pgsql-general@postgresql.org> Sent: 2001. május 4. 20:26 Subject: Re: [GENERAL] arbitrary number of values from a sequence > "Gyozo Papp" <pgerzson@freestart.hu> writes: > > Does it mean that it can't be ensured that returning values of > > nextval() are consecutive ones? > > I think it would be folly to assume that, even with a cache setting > equal to the number of values you intend to fetch. If the cache gets > out of sync with your requests (say, because a transaction aborted after > fetching just some of the 5 values) then subsequent transactions would > reload the cache partway through, and in that case you could get > non-consecutive results. > > > does it help me if I set the transaction isolation level to > > serializable or lock the table of the sequence? > > No. Why do you need such a thing, anyway? If you are always allocating > groups of 5 IDs, why don't you just pretend each nextval() gives you > five items instead of one? You could simply multiply the returned value > by 5. Or set the sequence's increment to 5. > > regards, tom lane
On Sat, May 05, 2001 at 02:33:22PM +0200, Gyozo Papp wrote:
> Ok. I'm looking for another solution.
>
> The reason why I'm not dealing with sequence's increment is that
> there is no way to set an appropiate limit, sometimes I need 5, sometimes 17.
CREATE TABLE myseq (
val integer NOT NULL;
);
psuedocode:
func (integer howmany)
...
BEGIN TRANSACTION;
oldval := SELECT val FROM myseq FOR UPDATE;
newval := oldval + howmany;
UPDATE myseq SET val = newval;
COMMIT;
for i := oldval; i < newval; incr i
do stuff
...
You might want to use LOCK instead of FOR UPDATE since its behavior
depends on the TRANSACTION ISOLATION LEVEL.
--
Eric G. Miller <egm2@jps.net>