Received: from xyzzy.lan.internal (turbo-137.kingston.net [216.123.190.137]) by postgresql.org (Postfix) with ESMTP id A0B16475D17 for ; Tue, 5 Mar 2002 01:52:09 -0500 (EST) Received: from drew by xyzzy.lan.internal with local (Exim 3.33 #1 (Debian)) id 16i8nT-0000kS-00; Tue, 05 Mar 2002 01:51:39 -0500 Date: Tue, 5 Mar 2002 01:51:38 -0500 To: Tom Lane Cc: Matthew Price , pgsql-sql@postgresql.org Subject: Re: Changing sequence cache Message-ID: <20020305065138.GA2554@xyzzy.dhs.org> References: <20020304.130237.19811.60399@wm7.jersey.juno.com> <9164.1015283793@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="oyUTqETQ0mS9luUI" Content-Disposition: inline In-Reply-To: <9164.1015283793@sss.pgh.pa.us> User-Agent: Mutt/1.3.27i From: "Andrew G. Hammond" X-Fnord: +++ath X-WebTV-Stationery: Standard; BGColor=black; TextColor=black X-Message-Flag: Message text blocked: ANTI-MICROSOFT OPINIONS X-BeenThere: drew@xyzzy.dhs.org X-Archive-Number: 200203/54 X-Sequence-Number: 6936 --oyUTqETQ0mS9luUI Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Mon, Mar 04, 2002 at 06:16:33PM -0500, Tom Lane wrote: > Matthew Price writes: > > I already have a sequence named customers_custid_seq > > I would like to increase the cache setting some. How can i alter the c= ache after the sequence has already been created? >=20 > You can't, but there's no reason you shouldn't drop and recreate the > sequence. I had to do something very similar to this once, on a running production=20 database. I believe that I used something like this: DROP FUNCTION my_modify_sequence(); CREATE FUNCTION my_modify_sequence() RETURNS integer AS ' DECLARE sequence_value INTEGER; create_string TEXT :=3D \'CREATE SEQUENCE foo_foo_id_seq START \'; BEGIN SELECT INTO sequence_value nextval(\'foo_foo_id_seq\'); RAISE NOTICE \'Sequence value is currently %\', sequence_value; EXECUTE \'DROP SEQUENCE foo_foo_id_seq\'; EXECUTE create_string || sequence_value; RETURN sequence_value; END; ' LANGUAGE 'plpgsql'; -- do the deed BEGIN; LOCK TABLE foo IN ACCESS EXCLUSIVE MODE; SELECT my_modify_sequence(); COMMIT; --=20 Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/ 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481= =20 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me --oyUTqETQ0mS9luUI Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iEYEARECAAYFAjyEavoACgkQCT73CrRXhLGaiACfXubD8X9dz55vZ6XrvKpNms/R D+MAoIa2OnaLIa/KcJR9xB5TqnlUfCPf =rpqC -----END PGP SIGNATURE----- --oyUTqETQ0mS9luUI--