Re: Restart a sequence regularly

"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:
> Good point. I'm guessing if you need a way to make other users wait,
> not get an error, you'll need to use a funtion with a security definer
> that will sleep or something during that period.

What you'd want is to take out an exclusive lock on the sequence.

[ fools around... ] Hmm, we don't let you do LOCK TABLE on a sequence,
which is perhaps overly restrictive, but you can get the same effect
with any ALTER TABLE command that works on a sequence. For instance
a no-op ALTER OWNER:

Session 1:

regression=# create sequence s;
CREATE SEQUENCE
regression=# begin;
BEGIN
regression=# alter table s owner to postgres;
ALTER TABLE

Session 2;

regression=# select nextval('s');
[ hangs ... ]

Session 1:

regression=# alter sequence s restart with 42;
ALTER SEQUENCE
regression=# commit;
COMMIT

Session 2:

nextval
---------
42
(1 row)

regards, tom lane