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

On Mon, Aug 20, 2012 at 5:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Craig James <cjames@emolecules.com> writes:
>> I want to do this:
>
>>     select setval('object_id_seq', nextval('object_id_seq') + 1000, false);
>
>> Now suppose two processes do this simultaneously.  Maybe they're in
>> transactions, maybe they're not.  Are they guaranteed to get distinct
>> blocks of IDs?
>
> No, because the setval and the nextval are not indivisible.
>
>> Or is it possible that each will execute nextval() and
>> get N and N+1 respectively, and then do setval() to N+1000 and N+1001,
>> resulting in two overlapping blocks.
>
> Exactly.
>
>> If the answer is, "This won't work," then what's a better way to do this?
>
> AFAIK the only way at the moment is
>
> * acquire some advisory lock that by convention you use for this sequence
> * advance the sequence
> * release advisory lock
>
> There have been previous discussions of this type of problem, eg
> http://archives.postgresql.org/pgsql-hackers/2011-09/msg01031.php
> but the topic doesn't seem to have come up quite often enough to
> motivate anybody to do anything about it.  Your particular case could be
> handled by a variant of nextval() with a number-of-times-to-advance
> argument, but I'm not sure if that's enough for other scenarios.
>
>                         regards, tom lane

So here's what I came up with.  I'm no PLPGSQL guru, but it seemed
pretty straightforward.

create or replace function nextval_block(bsize integer default 1)
    returns bigint as $nextval_block$
  declare
    bstart bigint;
  begin
    perform pg_advisory_lock(1);
    select into bstart nextval('my_seq');
    perform setval('my_seq', bstart + bsize, false);
    perform pg_advisory_unlock(1);
    return bstart;
  end;
$nextval_block$ language plpgsql;

As long as I ensure that every application uses nextval_block()
instead of nextval() to access this sequence, I think this will do
what I want.

testdb=> select nextval_block();
 nextval_block
---------------
             1
(1 row)

testdb=> select nextval_block();
 nextval_block
---------------
             2
(1 row)


testdb=> select nextval_block(1000);
 nextval_block
---------------
             3
(1 row)

testdb=> select nextval_block(1000);
 nextval_block
---------------
          1003
(1 row)

testdb=> select nextval_block(1000);
 nextval_block
---------------
          2003
(1 row)

Using pgsql's \timing directive, it says it's roughly 0.45 msec per
request with the client and server are on the same machines, and 0.55
msec per request when the client and server are different machines.
Not bad.

Thanks for your help!
Craig