Re: CREATE SEQUENCE fails in plpgsql function

On Tue, 2003-07-01 at 13:33, Tom Lane wrote:
> Erik Erkelens <erik_erkelens(at)yahoo(dot)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(at)rbt(dot)ca>

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