X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 3EBD630F616
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  1 Jul 2003 13:53:13 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 72720-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  1 Jul 2003 10:53:01 -0300 (ADT)
Received: from tomts13-srv.bellnexxia.net (tomts13.bellnexxia.net
	[209.226.175.34])
	by svr1.postgresql.org (Postfix) with ESMTP id 402C4310B2E
	for <pgsql-sql@postgresql.org>; Tue,  1 Jul 2003 10:53:00 -0300 (ADT)
Received: from [192.168.1.200] ([216.209.81.133]) by
 tomts13-srv.bellnexxia.net
	(InterMail vM.5.01.05.32 201-253-122-126-132-20030307) with ESMTP id
	<20030701135241.NKVC23042.tomts13-srv.bellnexxia.net@[192.168.1.200]>;
	Tue, 1 Jul 2003 09:52:41 -0400
Subject: Re: CREATE SEQUENCE fails in plpgsql function
From: Rod Taylor <rbt@rbt.ca>
To: Erik Erkelens <erik_erkelens@yahoo.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <2454.1057066433@sss.pgh.pa.us>
References: <20030630145012.88168.qmail@web41707.mail.yahoo.com>
	<2454.1057066433@sss.pgh.pa.us>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-qc6Ykxmku6Aie8ZyUIvL"
Message-Id: <1057067587.28657.143.camel@jester>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.0 
Date: 01 Jul 2003 13:53:08 +0000
X-Archive-Number: 200307/11
X-Sequence-Number: 14125

--=-qc6Ykxmku6Aie8ZyUIvL
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Tue, 2003-07-01 at 13:33, Tom Lane wrote:
> Erik Erkelens <erik_erkelens@yahoo.com> writes:
> >     DECLARE=20
> >         new_max_records ALIAS FOR $1;
> >     BEGIN
> >         CREATE SEQUENCE my_sequence MAXVALUE new_max_records CYCLE;
>=20
> > ERROR:  parser: parse error at or near "$1" at character 39
>=20
> 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.
>=20
> > Also, if there is a better mechanism to implement
> > this, I'm all ears...
>=20
> 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.=20
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 =3D 'sequence name' FOR UPDATE;
SELECT setval(<max number>);

This would work equally well with ALTER SEQUENCE in 7.4.

--=20
Rod Taylor <rbt@rbt.ca>

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

--=-qc6Ykxmku6Aie8ZyUIvL
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/AZJD6DETLow6vwwRAkIVAJ9DAxAy2INweDIOc9GRlgF9Q3GkCACfU4GE
AE2fh4LcPjDcv2vs7wZE8pw=
=mpFF
-----END PGP SIGNATURE-----

--=-qc6Ykxmku6Aie8ZyUIvL--