Re: Get block of N numbers from sequence

Merlin Moncure wrote:
> On Tue, May 19, 2009 at 9:32 AM, Thomas Guettler <hv(at)tbz-pariv(dot)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.

If you want to be REALLY sure your sequence is never accessed without
being locked first, you can deny rights to access it to the usual users,
and write a Pl/PgSQL SECURITY DEFINER function to do all manipulation of
the sequence.

The sample function you posted could be trivially adjusted to operate
SECURITY DEFINER and would suit the purpose. Note that I haven't
examined this in great depth for security issues, and there may be
things I'm missing about the safe use of SECURITY DEFINER functions.

create or replace function my_nextval_for_seqname(
_count int, _v out bigint) returns bigint as
$$
declare
-- Hard code sequence name; we're running SECURITY DEFINER
-- and don't want the caller to be able to mess with any
-- sequence they choose to.
_seq text := 'seqname';
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
VOLATILE
STRICT
SECURITY DEFINER;

--
Craig Ringer