pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew G. Hammond <drew@xyzzy.dhs.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Matthew Price <pricem@juno.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Changing sequence cache
Date: Tue, 5 Mar 2002 01:51:38 -0500
Message-ID: <20020305065138.GA2554@xyzzy.dhs.org> (raw)
In-Reply-To: <9164.1015283793@sss.pgh.pa.us>
References: <20020304.130237.19811.60399@wm7.jersey.juno.com>
	<9164.1015283793@sss.pgh.pa.us>

On Mon, Mar 04, 2002 at 06:16:33PM -0500, Tom Lane wrote:
> Matthew Price <pricem@juno.com> writes:
> > I already have a sequence named customers_custid_seq
> > I would like to increase the cache setting some.  How can i alter the cache after the sequence has already been created?
> 
> 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 
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 := \'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;

-- 
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 
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me

view thread (4+ messages)

Message-ID: <20020305065138.GA2554@xyzzy.dhs.org>
Permalink:  ../20020305065138.GA2554@xyzzy.dhs.org/
Also on:    postgresql.org/message-id/20020305065138.GA2554@xyzzy.dhs.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: drew@xyzzy.dhs.org, tgl@sss.pgh.pa.us, pricem@juno.com
  Subject: Re: Changing sequence cache
  In-Reply-To: <20020305065138.GA2554@xyzzy.dhs.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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