pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedChanging sequence cache
4+ messages / 4 participants
[nested] [flat]
* Changing sequence cache
@ 2002-03-04 18:02 Matthew Price <pricem@juno.com>
0 siblings, 2 replies; 4+ messages in thread
From: Matthew Price @ 2002-03-04 18:02 UTC (permalink / raw)
To: pgsql-sql
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/.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Changing sequence cache
@ 2002-03-04 18:32 Dan Langille <dan@langille.org>
parent: Matthew Price <pricem@juno.com>
1 sibling, 0 replies; 4+ messages in thread
From: Dan Langille @ 2002-03-04 18:32 UTC (permalink / raw)
To: Matthew Price <pricem@juno.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Changing sequence cache
@ 2002-03-04 23:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Matthew Price <pricem@juno.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2002-03-04 23:16 UTC (permalink / raw)
To: Matthew Price <pricem@juno.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Changing sequence cache
@ 2002-03-05 06:51 Andrew G. Hammond <drew@xyzzy.dhs.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Andrew G. Hammond @ 2002-03-05 06:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew Price <pricem@juno.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-03-05 06:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-04 18:02 Changing sequence cache Matthew Price <pricem@juno.com>
2002-03-04 18:32 ` Dan Langille <dan@langille.org>
2002-03-04 23:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-03-05 06:51 ` Andrew G. Hammond <drew@xyzzy.dhs.org>
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