Re: Get block of N numbers from sequence

On Tue, May 19, 2009 at 9:32 AM, Thomas Guettler <hv@tbz-pariv.de> wrote:
>
>
> hubert depesz lubaczewski schrieb:
>> On Tue, May 19, 2009 at 01:45:17PM +0200, Thomas Guettler wrote:
>>> how can you get N numbers (without holes) from a sequence?
>>
>> alter sequence XXX increment by 1000;
>> select nextval('XXX');
>> alter sequence XXX increment by 1;
>
> If other processes run nextval() between "increment by 1000" and "increment by 1",
> they leave big holes in the sequence.

This is only works if everyone does it this way.  If anybody throws a
nextval() without locking the sequence first you have a race.  Also,
since alter sequence takes a full lock your concurrency is zero.

Probably the best general way to attack this problem is using advisory
locks.  note the code below is untested.

create or replace function my_nextval(_seq text, _count int, _v out
bigint) returns bigint as
$$
  begin
    if _count = 1 then
      perform pg_advisory_lock_shared(999);
      _v := nextval(_seq);
      perform pg_advisory_unlock_shared(999);
    else
      perform pg_advisory_lock(999);
      _v := nextval(_seq);
      perform setval(_seq, _v + _count);
      perform pg_advisory_unlock(999);
    end if;
  end;
$$ language plpgsql;

This complexity is worthwhile if you need to intermix standard
nextval() with block allocations.  the number 999 has no significance
except as a lock id...see the docs on advisory locks.  The advantage
here is that nextval() calls do not block each other and the full lock
is extremely short term, so you can safely call this during longer
running transaction.

merlin