Re: Restart a sequence regularly

"Scott Marlowe" <scott.marlowe@gmail.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