Sequence Question

[Oscar Tuscon]

I'm looking at ways to get batches of sequence values s faster. I don't want to set cache or increment to a large number for a variety of reasons. I need to grab id's in batches of varying numbers at various times, from 1 to several thousand at once.
Doing it 1 at a time works, but more time goes into it than I'd like. I tried setting cache_value high but the database roundtrips were eating more time that I wanted to see; I only saw a 25% improvement in average time.

SO... is the following approach safe? That is, will this be atomic, or is there a possibility that another connection could squeeze in a select nextval() between the select nextval() and the setval below?
If it's safe I'd do this and take the sequences as the new currval - #I asked for (1500 or whatever).

mydb=# select setval('my_id_s eq', (select nextval('my_id_ seq')+1500));

Thanks
Oscar
_______________ _______________ _______________ _______________ _
The BMW E30 community on the web---> http://www.bmwe30.net

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Prabu Subroto]

kv=# create sequence sales_salesid_s eq;
CREATE SEQUENCE
kv=# select setval('sales_s alesid_seq', (select
max(salesid) from sales)+1);
setval
--------
16
(1 row)
kv=# alter table sales alter column salesid set
default nextval('sales_ serialid_
seq');
ALTER TABLE

Oscar Tuscon <ob****@bmwe30. net> wrote:

I'm looking at ways to get batches of sequence
values s faster. I don't want to set cache or
increment to a large number for a variety of
reasons. I need to grab id's in batches of varying
numbers at various times, from 1 to several thousand
at once.
Doing it 1 at a time works, but more time goes into
it than I'd like. I tried setting cache_value high
but the database roundtrips were eating more time
that I wanted to see; I only saw a 25% improvement
in average time.

SO... is the following approach safe? That is, will
this be atomic, or is there a possibility that
another connection could squeeze in a select
nextval() between the select nextval() and the
setval below?
If it's safe I'd do this and take the sequences as
the new currval - #I asked for (1500 or whatever).

mydb=# select setval('my_id_s eq', (select
nextval('my_id_ seq')+1500));

Thanks
Oscar
_______________ _______________ _______________ _______________ _ The BMW E30 community on the web--->
http://www.bmwe30.net

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the
unregister command
(send "unregister YourEmailAddres sHere" to
ma*******@postg resql.org)

_______________ _______________ ____
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Greg Stark]

Oscar Tuscon <ob****@bmwe30. net> writes:

I'm looking at ways to get batches of sequence values s faster. I don't want to set cache or increment to a large number for a variety of reasons. I need to grab id's in batches of varying numbers at various times, from 1 to several thousand at once.
Doing it 1 at a time works, but more time goes into it than I'd like. I tried setting cache_value high but the database roundtrips were eating more time that I wanted to see; I only saw a 25% improvement in average time.

SO... is the following approach safe? That is, will this be atomic, or is there a possibility that another connection could squeeze in a select nextval() between the select nextval() and the setval below?
If it's safe I'd do this and take the sequences as the new currval - #I asked for (1500 or whatever).

mydb=# select setval('my_id_s eq', (select nextval('my_id_ seq')+1500));

I'm not sure but I don't think that's safe since nextval doesn't lock the
sequence until the setval occurs. Though it might be unlikely to actually
occur in real life.

You could create a table with as many entries as you will ever need and then
select nextval() from that table and read all the records you get. That will
avoid the many round trips but it will still be slow since you will still have
to move all those individual values to your client.

Perhaps you should give up on the idea of using sequences at all.

If you have a table with one record (or one record per similar application).
You could "SELECT n FROM counters FOR UPDATE" the value in the record, then
"UPDATE counters SET n = n+?". It would serialize your accesses which would be
bad if you had lots of clients doing small increments, but if you have few
clients doing large increments it shouldn't be a problem.

Do make sure to vacuum this table frequently though.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly