Re: Restart a sequence regularly

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: "Marco Colombo" <marco(at)esi(dot)it>, pgsql-general(at)postgresql(dot)org
Subject: Re: Restart a sequence regularly
Date: 2007-11-21 18:23:39
Message-ID: 14910.1195669419@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message SHARMILA JOTHIRAJAH 2007-11-21 18:29:41 Re: Postgres table size
Previous Message David Fetter 2007-11-21 18:21:12 Re: loading a funtion script from a file