Re: Changing sequence cache

On Mon, Mar 04, 2002 at 06:16:33PM -0500, Tom Lane wrote:
> Matthew Price <pricem(at)juno(dot)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(at)xyzzy(dot)dhs(dot)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