Sequence Question

From: Oscar Tuscon <obtuse(at)bmwe30(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Sequence Question
Date: 2004-08-05 04:19:41
Message-ID: 20040805041942.0D6513953@sitemail.everyone.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2004-08-05 06:32:51 Re: Test case for bug fix in 7.4.3
Previous Message Bruce Momjian 2004-08-05 03:59:48 Re: Test case for bug fix in 7.4.3