Postgres Pro
Downloads
Home   >   mailing lists

Thread: Changing sequence cache

Changing sequence cache

From
Matthew Price
Date:
Howdy,

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?

Trying:
update customer_custid_seq set cache=5;
Gives this:
ERROR:  You can't change sequence relation customer_custid_seq

Thanks for any help,
Matthew



________________________________________________________________
GET INTERNET ACCESS FROM JUNO!
Juno offers FREE or PREMIUM Internet access for less!
Join Juno today!  For your FREE software, visit:
http://dl.www.juno.com/get/web/.

Re: Changing sequence cache

From
"Dan Langille"
Date:
On 4 Mar 2002 at 18:02, Matthew Price wrote:

> 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?
> 
> Trying:
> update customer_custid_seq set cache=5;
> Gives this:
> ERROR:  You can't change sequence relation customer_custid_seq

test=# create sequence mprice;
CREATE

test=# select nextval('mprice');nextval
---------      1
(1 row)

test=# select setval('mprice', 23);setval
--------    23
(1 row)

test=# select nextval('mprice');nextval
---------     24
(1 row)

test=#
-- 
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples

Re: Changing sequence cache

From
Tom Lane
Date:
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.
        regards, tom lane

Re: Changing sequence cache

From
"Andrew G. Hammond"
Date:
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