Re: Restart a sequence regularly

On 11/22/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
Thanks for your reply.
But, the owner of the sequence originally is postgres. Does it work?

--
Kathy Lo