Thread: Sequence Question

Sequence Question

From
Oscar Tuscon
Date:
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
numberfor a variety of reasons. I need to grab id's in batches of varying numbers at various times, from 1 to several
thousandat 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
roundtripswere 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
couldsqueeze 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_seq', (select nextval('my_id_seq')+1500));

Thanks
Oscar


_____________________________________________________________
The BMW E30 community on the web---> http://www.bmwe30.net

Re: Sequence Question

From
Prabu Subroto
Date:
kv=# create sequence sales_salesid_seq;
CREATE SEQUENCE
kv=# select setval('sales_salesid_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 <obtuse@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_seq', (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 YourEmailAddressHere" to
> majordomo@postgresql.org)
>




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

Re: Sequence Question

From
Greg Stark
Date:
Oscar Tuscon <obtuse@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
numberfor a variety of reasons. I need to grab id's in batches of varying numbers at various times, from 1 to several
thousandat 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
databaseroundtrips 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
couldsqueeze 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_seq', (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

Re: Sequence Question

From
Oscar Tuscon
Date:
>>>>
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
<<<<

Thanks, I figured that, but was hoping otherwise. I realize that the timing would make it unlikely, but unfortunately I
need100% guaranteed. I have an alternative in that I control the accessing clients (my app) and can apply a lock to
preventit from happening. 

I found the average select nextval() call was taking 2ms, which seems a bit slow to me. Throw in the fsync I suppose
andthat'd explain it. 
Interestingly, in the tests I ran the minimum select nextval() was 400us, and the max was 35ms, with an average of 2ms.
Thiswas on a DL380 dual 2.4G processors, 2.5G RAM, 5x10k SCSI drives, and no load - pretty much idle (well, a processes
checkingfor entries in a command table 10 times per second). 

Oscar




_____________________________________________________________
The BMW E30 community on the web---> http://www.bmwe30.net