Skip site navigation (1) Skip section navigation (2)

Re: Does setval(nextval()+N) generate unique blocks of IDs?

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Craig James <cjames(at)emolecules(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Does setval(nextval()+N) generate unique blocks of IDs?
Date: 2012-08-21 21:06:56
Message-ID: CAOR=d=2nhSRSo51ywB2PG9cZqTpAYkqLVtxo4CaGUhwsk+RMzA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Aug 21, 2012 at 2:59 PM, Craig James <cjames(at)emolecules(dot)com> wrote:
> On Tue, Aug 21, 2012 at 1:03 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>> That seems unnecessarily complex.  how about this:
>>
>> create sequence s;
>> select array_agg (a.b) from (select nextval('s') as b from
>> generate_series(1,1000)) as a;
>>
>> Then you just iterate that array for the ids you need.
>
> For brevity I didn't explain the use-case in detail.  I need a series
> of IDs that are unique across a cluster of servers and across time
> (years and decades).  The blocksize might be anywhere from 1 to
> 100000.  One server is the master and issues all IDs.
>
> I don't want to iterate over an array to get the values because it's
> inefficient: if the blocksize is large (say, 100000 items), it will
> require 100000 select() statements.  The solution using an advisory
> lock along with setvalue() is nice because the application only makes
> one select() statement and gets a block of IDs that are guaranteed to
> be unique across the cluster.

Ahhh ok.  Yeah that's why I said early on I wasn't really sure of your
use case, cause that really can make all the difference.  Good to
know.


In response to

pgsql-performance by date

Next:From: Tomas VondraDate: 2012-08-21 21:08:09
Subject: Re: average query performance measuring
Previous:From: Craig JamesDate: 2012-08-21 20:59:06
Subject: Re: Does setval(nextval()+N) generate unique blocks of IDs?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group