X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 53C12D1B298 for ; Thu, 5 Aug 2004 12:07:54 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 82215-10 for ; Thu, 5 Aug 2004 15:07:44 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id 02C86D1B285 for ; Thu, 5 Aug 2004 12:07:43 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1BsjqG-0004Si-00; Thu, 05 Aug 2004 11:07:40 -0400 To: pgsql-general@postgresql.org Subject: Re: Sequence Question References: <20040805041942.0D6513953@sitemail.everyone.net> In-Reply-To: <20040805041942.0D6513953@sitemail.everyone.net> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 05 Aug 2004 11:07:40 -0400 Message-ID: <87r7qlipxf.fsf@stark.xeocode.com> Lines: 31 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/145 X-Sequence-Number: 63700 Oscar Tuscon 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_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