pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCREATE SEQUENCE fails in plpgsql function
4+ messages / 4 participants
[nested] [flat]
* CREATE SEQUENCE fails in plpgsql function
@ 2003-06-30 14:50 Erik Erkelens <erik_erkelens@yahoo.com>
2003-07-01 04:49 ` Re: CREATE SEQUENCE fails in plpgsql function Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-07-01 13:33 ` Re: CREATE SEQUENCE fails in plpgsql function Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Erik Erkelens @ 2003-06-30 14:50 UTC (permalink / raw)
To: pgsql-sql
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;
--CREATE SEQUENCE 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CREATE SEQUENCE fails in plpgsql function
2003-06-30 14:50 CREATE SEQUENCE fails in plpgsql function Erik Erkelens <erik_erkelens@yahoo.com>
@ 2003-07-01 04:49 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2003-07-01 04:49 UTC (permalink / raw)
To: Erik Erkelens <erik_erkelens@yahoo.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CREATE SEQUENCE fails in plpgsql function
2003-06-30 14:50 CREATE SEQUENCE fails in plpgsql function Erik Erkelens <erik_erkelens@yahoo.com>
@ 2003-07-01 13:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-01 13:53 ` Re: CREATE SEQUENCE fails in plpgsql function Rod Taylor <rbt@rbt.ca>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2003-07-01 13:33 UTC (permalink / raw)
To: Erik Erkelens <erik_erkelens@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CREATE SEQUENCE fails in plpgsql function
2003-06-30 14:50 CREATE SEQUENCE fails in plpgsql function Erik Erkelens <erik_erkelens@yahoo.com>
2003-07-01 13:33 ` Re: CREATE SEQUENCE fails in plpgsql function Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-01 13:53 ` Rod Taylor <rbt@rbt.ca>
0 siblings, 0 replies; 4+ messages in thread
From: Rod Taylor @ 2003-07-01 13:53 UTC (permalink / raw)
To: Erik Erkelens <erik_erkelens@yahoo.com>; +Cc: pgsql-sql
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
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-07-01 13:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-30 14:50 CREATE SEQUENCE fails in plpgsql function Erik Erkelens <erik_erkelens@yahoo.com>
2003-07-01 04:49 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-07-01 13:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-01 13:53 ` Rod Taylor <rbt@rbt.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox