Re: CREATE SEQUENCE fails in plpgsql function

On Tue, 2003-07-01 at 13:33, Tom Lane wrote:
> Erik Erkelens <erik_erkelens@yahoo.com> writes:
> >     DECLARE
> >         new_max_records ALIAS FOR $1;
> >     BEGIN
> >         CREATE SEQUENCE my_sequence MAXVALUE new_max_records CYCLE;
>
> > ERROR:  parser: parse error at or near "$1" at character 39
>
> You'll need to use EXECUTE to construct and execute that CREATE
> SEQUENCE.  Utility statements generally don't accept runtime parameters,
> which is what the plpgsql variable looks like to the main parser.
>
> > Also, if there is a better mechanism to implement
> > this, I'm all ears...
>
> There's an ALTER SEQUENCE command in CVS tip, though I'm not sure
> I trust it in concurrent-usage scenarios :-(

It shouldn't be trusted anymore than setval() should be. That is,
changes take place immediately.

Seems to me you might be better off just creating a 'count' table.
Update the single row when it changes.  By dropping / recreating the
sequence you've already blocked concurrent transactions.  The single row
would have less to vacuum, where the sequence has quite a bit more.

Another alternative is to use setval() on the sequence BUT first pull a
FOR UPDATE lock on some blocking row (for concurrency reasons).

SELECT * FROM pg_class WHERE relname = 'sequence name' FOR UPDATE;
SELECT setval(<max number>);

This would work equally well with ALTER SEQUENCE in 7.4.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc