Re: [HACKERS] [ADMIN] how to alter sequence.
I don't think you can drop/recreate the sequence because the dependency code knows other tables depend on it. --- Rajesh Kumar Mallah. wrote: Doesn't dropping and recreating the sequence suit the bill ? whats' the major advantage to implement em as a command? At least one thing from which all of us can benifit in PgSQL is replication. I just hope 7.4 give us some sort of master/slave replication. Regds Mallah. On Wednesday 04 December 2002 11:53 pm, Bruce Momjian wrote: Oliver Elphick wrote: On Wed, 2002-12-04 at 12:29, raja kumar thatte wrote: Hai friends, I have a sequence called raj_seq with max value 3000. ... now i wanted to increase the max value of the raj_seq to 999. How to do this change? If i drop and recreate the raj_seq, then i have to recreate the table and all triggers working on that table.But it is not an acceptable solution. So with out droping raj_seq , how do I solve this problem. Unfortunately there doesn't seem to be any easy way to do this. There is no ALTER SEQUENCE command and you can't use UPDATE on a sequence. Gee, I thought they could just update the sequence table, but I see: test= update yy set max_value = 100; ERROR: You can't change sequence relation yy Hackers: Could this be a TODO item for 7.4? Added to TODO: * Add ALTER SEQUENCE to modify min/max/increment/cache/cycle values -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [ADMIN] how to alter sequence.
On 4 Dec 2002 at 20:41, Hannu Krosing wrote: hannu=# update seq set max_value = 99; ERROR: You can't change sequence relation seq hannu=# update pg_class set relkind = 'r' where relname = 'seq'; UPDATE 1 hannu=# update seq set max_value = 99; UPDATE 1 hannu=# update pg_class set relkind = 'S' where relname = 'seq'; UPDATE 1 hannu=# select * from seq; sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---++--+---+---+-+-+---+--- seq | 1 |1 |99 | 1 | 1 | 1 | f | f That makes me wonder. If sequense is treated like a single column single row table and it's value is guarenteed to be increasing even in case of aborted transaction, is it correct to say that postgresql already has nested transactions, albeit dormant? Bye Shridhar -- Zero Defects, n.: The result of shutting down a production line. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [ADMIN] how to alter sequence.
Shridhar Daithankar kirjutas K, 04.12.2002 kell 20:51: On 4 Dec 2002 at 20:41, Hannu Krosing wrote: hannu=# update seq set max_value = 99; ERROR: You can't change sequence relation seq hannu=# update pg_class set relkind = 'r' where relname = 'seq'; UPDATE 1 hannu=# update seq set max_value = 99; UPDATE 1 hannu=# update pg_class set relkind = 'S' where relname = 'seq'; UPDATE 1 hannu=# select * from seq; sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---++--+---+---+-+-+---+--- seq | 1 |1 |99 | 1 | 1 | 1 | f | f I just discovered that changing these numbers does not change how the sequence behaves ;( Even after restarting the backend! Sorry! That makes me wonder. If sequense is treated like a single column single row table and it's value is guarenteed to be increasing even in case of aborted transaction, is it correct to say that postgresql already has nested transactions, albeit dormant? No. Sequences live outside of transactions. I have no idea why there is also a ingle column single row table created. The output of \d command is also weird, for all sequences I get: hannu=# \d seq Sequence public.seq Column | Type ---+- sequence_name | name last_value| bigint increment_by | bigint max_value | bigint min_value | bigint cache_value | bigint log_cnt | bigint is_cycled | boolean is_called | boolean with only the Sequence name changing ... --- Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [ADMIN] how to alter sequence.
On Wed, 2002-12-04 at 09:06, Oliver Elphick wrote: On Wed, 2002-12-04 at 12:29, raja kumar thatte wrote: Hai friends, I have a sequence called raj_seq with max value 3000. ... now i wanted to increase the max value of the raj_seq to 999. How to do this change? If i drop and recreate the raj_seq, then i have to recreate the table and all triggers working on that table.But it is not an acceptable solution. So with out droping raj_seq , how do I solve this problem. Unfortunately there doesn't seem to be any easy way to do this. There is no ALTER SEQUENCE command and you can't use UPDATE on a sequence. Hackers: Could this be a TODO item for 7.4? I'm hoping to do that one sooner than later, unless Neil beats me to it. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [ADMIN] how to alter sequence.
On Wed, 2002-12-04 at 12:29, raja kumar thatte wrote: Hai friends, I have a sequence called raj_seq with max value 3000. ... now i wanted to increase the max value of the raj_seq to 999. How to do this change? If i drop and recreate the raj_seq, then i have to recreate the table and all triggers working on that table.But it is not an acceptable solution. So with out droping raj_seq , how do I solve this problem. Unfortunately there doesn't seem to be any easy way to do this. There is no ALTER SEQUENCE command and you can't use UPDATE on a sequence. Hackers: Could this be a TODO item for 7.4? The easiest way to do this at present is probably to dump the database, edit the dump to change the sequence max_value and then recreate the database from the edited dump. I presume you used CREATE SEQUENCE in order to get such a low max_value. If it were created from a SERIAL datatype, you would also have to edit the table definition to use a pre-created sequence. There is no means of specifying a max_value using SERIAL. -- Oliver Elphick [EMAIL PROTECTED] LFIX Limited ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [ADMIN] how to alter sequence.
Oliver Elphick kirjutas K, 04.12.2002 kell 19:06: On Wed, 2002-12-04 at 12:29, raja kumar thatte wrote: Hai friends, I have a sequence called raj_seq with max value 3000. ... now i wanted to increase the max value of the raj_seq to 999. How to do this change? If i drop and recreate the raj_seq, then i have to recreate the table and all triggers working on that table.But it is not an acceptable solution. So with out droping raj_seq , how do I solve this problem. Unfortunately there doesn't seem to be any easy way to do this. There is no ALTER SEQUENCE command and you can't use UPDATE on a sequence. Hackers: Could this be a TODO item for 7.4? This seems to work - as an example why we need the TODO ;) hannu=# update seq set max_value = 99; ERROR: You can't change sequence relation seq hannu=# update pg_class set relkind = 'r' where relname = 'seq'; UPDATE 1 hannu=# update seq set max_value = 99; UPDATE 1 hannu=# update pg_class set relkind = 'S' where relname = 'seq'; UPDATE 1 hannu=# select * from seq; sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---++--+---+---+-+-+---+--- seq | 1 |1 |99 | 1 | 1 | 1 | f | f (1 row) I can't really recommend it, because it may (or may not ;) have some unwanted behaviours as well; The easiest way to do this at present is probably to dump the database, edit the dump to change the sequence max_value and then recreate the database from the edited dump. I presume you used CREATE SEQUENCE in order to get such a low max_value. If it were created from a SERIAL datatype, you would also have to edit the table definition to use a pre-created sequence. There is no means of specifying a max_value using SERIAL. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [ADMIN] how to alter sequence.
Around 20:41 on Dec 4, 2002, Hannu Krosing said:
What's wrong with this:
dustin=# create sequence test_seq;
CREATE SEQUENCE
dustin=# select nextval('test_seq');
nextval
-
1
(1 row)
dustin=# select setval('test_seq', );
setval
(1 row)
dustin=# select nextval('test_seq');
nextval
-
1
(1 row)
# Oliver Elphick kirjutas K, 04.12.2002 kell 19:06:
# On Wed, 2002-12-04 at 12:29, raja kumar thatte wrote:
# Hai friends,
# I have a sequence called raj_seq with max value 3000.
# ...
# now i wanted to increase the max value of the raj_seq
# to 999.
# How to do this change?
# If i drop and recreate the raj_seq, then i have to
# recreate the table and all triggers working on that
# table.But it is not an acceptable solution.
# So with out droping raj_seq , how do I solve this
# problem.
#
# Unfortunately there doesn't seem to be any easy way to do this. There
# is no ALTER SEQUENCE command and you can't use UPDATE on a sequence.
#
# Hackers: Could this be a TODO item for 7.4?
#
# This seems to work - as an example why we need the TODO ;)
#
# hannu=# update seq set max_value = 99;
# ERROR: You can't change sequence relation seq
# hannu=# update pg_class set relkind = 'r' where relname = 'seq';
# UPDATE 1
# hannu=# update seq set max_value = 99;
# UPDATE 1
# hannu=# update pg_class set relkind = 'S' where relname = 'seq';
# UPDATE 1
# hannu=# select * from seq;
# sequence_name | last_value | increment_by | max_value | min_value |
# cache_value | log_cnt | is_cycled | is_called
#
---++--+---+---+-+-+---+---
# seq | 1 |1 |99 | 1
# | 1 | 1 | f | f
# (1 row)
#
# I can't really recommend it, because it may (or may not ;) have some
# unwanted behaviours as well;
#
#
#
# The easiest way to do this at present is probably to dump the database,
# edit the dump to change the sequence max_value and then recreate the
# database from the edited dump. I presume you used CREATE SEQUENCE in
# order to get such a low max_value. If it were created from a SERIAL
# datatype, you would also have to edit the table definition to use a
# pre-created sequence. There is no means of specifying a max_value using
# SERIAL.
# --
# Hannu Krosing [EMAIL PROTECTED]
#
# ---(end of broadcast)---
# TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
#
#
--
SPY My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings [EMAIL PROTECTED]
|Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L___ I hope the answer won't upset her.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [ADMIN] how to alter sequence.
On Wed, Dec 04, 2002 at 09:33:52AM -0800, Dustin Sallings wrote:
Around 20:41 on Dec 4, 2002, Hannu Krosing said:
What's wrong with this:
dustin=# create sequence test_seq;
CREATE SEQUENCE
dustin=# select nextval('test_seq');
nextval
-
1
(1 row)
dustin=# select setval('test_seq', );
setval
(1 row)
dustin=# select nextval('test_seq');
nextval
-
1
(1 row)
Dustin --
The thread here is about how to raise the *max* value for the sequence,
not how to set the current value higher. The sequence in question was
created with a too-low maximum value (see help on CREATE SEQUENCE for
options); the user now wants to raise it.
- J.
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [ADMIN] how to alter sequence.
Around 12:44 on Dec 4, 2002, Joel Burton said: # The thread here is about how to raise the *max* value for the sequence, # not how to set the current value higher. The sequence in question was # created with a too-low maximum value (see help on CREATE SEQUENCE for # options); the user now wants to raise it. Ahh, OK. Seemed too obvious. :) -- SPY My girlfriend asked me which one I like better. pub 1024/3CAE01D5 1994/11/03 Dustin Sallings [EMAIL PROTECTED] |Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE L___ I hope the answer won't upset her. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [ADMIN] how to alter sequence.
On Wed, 2002-12-04 at 17:33, Dustin Sallings wrote:
What's wrong with this:
dustin=# create sequence test_seq;
CREATE SEQUENCE
dustin=# select nextval('test_seq');
nextval
-
1
(1 row)
dustin=# select setval('test_seq', );
setval
(1 row)
dustin=# select nextval('test_seq');
nextval
-
1
(1 row)
It's not the issue. The original question was how to change the upper
limit of the sequence's range, not its current value.
junk=# create sequence foo_seq maxvalue 3000;
CREATE SEQUENCE
junk=# select nextval('foo_seq');
nextval
-
1
(1 row)
junk=# select setval('foo_seq', 99);
ERROR: foo_seq.setval: value 99 is out of bounds (1,3000)
--
Oliver Elphick [EMAIL PROTECTED]
LFIX Limited
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] [ADMIN] how to alter sequence.
Oliver Elphick wrote: On Wed, 2002-12-04 at 12:29, raja kumar thatte wrote: Hai friends, I have a sequence called raj_seq with max value 3000. ... now i wanted to increase the max value of the raj_seq to 999. How to do this change? If i drop and recreate the raj_seq, then i have to recreate the table and all triggers working on that table.But it is not an acceptable solution. So with out droping raj_seq , how do I solve this problem. Unfortunately there doesn't seem to be any easy way to do this. There is no ALTER SEQUENCE command and you can't use UPDATE on a sequence. Gee, I thought they could just update the sequence table, but I see: test= update yy set max_value = 100; ERROR: You can't change sequence relation yy Hackers: Could this be a TODO item for 7.4? Added to TODO: * Add ALTER SEQUENCE to modify min/max/increment/cache/cycle values -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
