Thread: CREATE SEQUENCE fails in plpgsql function

Hi,

I need a table to hold the last n INSERTs into it. 

To keep track of how many entries there are, and to
provide a unique id to order the records in the table,
I use a sequence. A trigger function deletes and entry
on an insert if the table is full.  

The number n maybe changed, so I implemented this
PL/PgSQL function:

CREATE OR REPLACE FUNCTION set_max_records(integer)
RETURNS integer AS '   DECLARE        new_max_records ALIAS FOR $1;   BEGIN       DROP SEQUENCE my_sequence;
--CREATESEQUENCE my_sequence MAXVALUE 4
 
CYCLE;       CREATE SEQUENCE my_sequence MAXVALUE
new_max_records CYCLE;       RETURN 0;   END;   
' LANGUAGE 'plpgsql';

(I left out the part where the table is shrunk and
renumbered if n goes down).


rdb=# select set_max_records(3);
LOG:  query: CREATE SEQUENCE my_sequence MAXVALUE  $1 
CYCLE
LOG:  statement: select set_max_records(3);
WARNING:  Error occurred while executing PL/pgSQL
function set_max_records
LOG:  statement: select set_max_records(3);
WARNING:  line 6 at SQL statement
LOG:  statement: select set_max_records(3);
DEBUG:  AbortCurrentTransaction
ERROR:  parser: parse error at or near "$1" at
character 39

If I don't use the variable new_max_records, it works
(the commented out line). What could be the problem ?

Also, if there is a better mechanism to implement
this, I'm all ears...

Erik Erkelens.

__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

On Mon, 30 Jun 2003, Erik Erkelens wrote:

>         new_max_records ALIAS FOR $1;
>     BEGIN
>         DROP SEQUENCE my_sequence;
>         --CREATE SEQUENCE my_sequence MAXVALUE 4
> CYCLE;
>         CREATE SEQUENCE my_sequence MAXVALUE
> new_max_records CYCLE;

Most of the creates/drops/etc... don't directly work with
variables/arguments.  You can probably do this with execute
however.

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 :-(
        regards, tom lane

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