Horton

Thread

  1. how to alter sequence.

    Raja Kumar Thatte <trajakumar@yahoo.com> — 2002-12-04T12:29:00Z

    Hai friends,
I have a sequence called raj_seq with max value 3000.
I have a table, with one field haveing
nextvalu('raj_seq') as default value.;
now i wanted to increase the max value of the raj_seq
to 9999999.
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.
Thanks in advance.
have a nice time.
raja



__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

  2. Re: how to alter sequence.

    Dan Langille <dan@langille.org> — 2002-12-04T12:44:25Z

    On 4 Dec 2002 at 4:29, raja kumar thatte wrote:

> Hai friends,
> I have a sequence called raj_seq with max value 3000.
> I have a table, with one field haveing
> nextvalu('raj_seq') as default value.;
> now i wanted to increase the max value of the raj_seq
> to 9999999.
> How to do this change?

http://www.postgresql.org/idocs/index.php?functions-sequence.html
Look for setval
-- 
Dan Langille : http://www.langille.org/

  3. Re: how to alter sequence.

    Raja Kumar Thatte <trajakumar@yahoo.com> — 2002-12-04T13:20:28Z

    Hai friend,
Thanks. But I wanted to change the maximum value but
not current value. How to change the maximum value of
a sequence

raja


--- Dan Langille <dan@langille.org> wrote:
> On 4 Dec 2002 at 4:29, raja kumar thatte wrote:
> 
> > Hai friends,
> > I have a sequence called raj_seq with max value
> 3000.
> > I have a table, with one field haveing
> > nextvalu('raj_seq') as default value.;
> > now i wanted to increase the max value of the
> raj_seq
> > to 9999999.
> > How to do this change?
> 
>
http://www.postgresql.org/idocs/index.php?functions-sequence.html
> Look for setval
> -- 
> Dan Langille : http://www.langille.org/
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

  4. Re: how to alter sequence.

    Dan Langille <dan@langille.org> — 2002-12-04T13:27:28Z

    On 4 Dec 2002 at 5:20, raja kumar thatte wrote:

> Hai friend,
> Thanks. But I wanted to change the maximum value but
> not current value. How to change the maximum value of
> a sequence

I'm sorry.  I shouldn't reply to questions first thing in the 
morning.

That I don't know, but I'm sure it can be done.... someone else will 
know.
-- 
Dan Langille : http://www.langille.org/

  5. Re: how to alter sequence.

    Egon Reetz <reetz@usco.de> — 2002-12-04T13:38:18Z

    Drop the sequence and recreate with a min. value greater your last value and
the new max. value.

Egon


raja kumar thatte wrote:

> Hai friends,
> I have a sequence called raj_seq with max value 3000.
> I have a table, with one field haveing
> nextvalu('raj_seq') as default value.;
> now i wanted to increase the max value of the raj_seq
> to 9999999.
> 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.
> Thanks in advance.
> have a nice time.
> raja
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

  6. Re: how to alter sequence.

    Egon Reetz <reetz@usco.de> — 2002-12-04T13:47:43Z

    I meant start value (inst. min.).

Egon Reetz wrote:

> Drop the sequence and recreate with a min. value greater your last value and
> the new max. value.
>
> Egon
>
> raja kumar thatte wrote:
>
> > Hai friends,
> > I have a sequence called raj_seq with max value 3000.
> > I have a table, with one field haveing
> > nextvalu('raj_seq') as default value.;
> > now i wanted to increase the max value of the raj_seq
> > to 9999999.
> > 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.
> > Thanks in advance.
> > have a nice time.
> > raja
> >
> > __________________________________________________
> > Do you Yahoo!?
> > Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> > http://mailplus.yahoo.com
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

  7. Re: how to alter sequence.

    Raja Kumar Thatte <trajakumar@yahoo.com> — 2002-12-04T13:59:54Z

    Thsnkd
--- Egon Reetz <reetz@usco.de> wrote:
> I meant start value (inst. min.).
> 
> Egon Reetz wrote:
> 
> > Drop the sequence and recreate with a min. value
> greater your last value and
> > the new max. value.
> >
> > Egon
> >
> > raja kumar thatte wrote:
> >
> > > Hai friends,
> > > I have a sequence called raj_seq with max value
> 3000.
> > > I have a table, with one field haveing
> > > nextvalu('raj_seq') as default value.;
> > > now i wanted to increase the max value of the
> raj_seq
> > > to 9999999.
> > > 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.
> > > Thanks in advance.
> > > have a nice time.
> > > raja
> > >
> > >
> __________________________________________________
> > > Do you Yahoo!?
> > > Yahoo! Mail Plus - Powerful. Affordable. Sign up
> now.
> > > http://mailplus.yahoo.com
> > >
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> >
> http://www.postgresql.org/users-lounge/docs/faq.html
> 


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

  8. Re: how to alter sequence.

    Raja Kumar Thatte <trajakumar@yahoo.com> — 2002-12-04T14:00:03Z

    Thanks
--- Egon Reetz <reetz@usco.de> wrote:
> I meant start value (inst. min.).
> 
> Egon Reetz wrote:
> 
> > Drop the sequence and recreate with a min. value
> greater your last value and
> > the new max. value.
> >
> > Egon
> >
> > raja kumar thatte wrote:
> >
> > > Hai friends,
> > > I have a sequence called raj_seq with max value
> 3000.
> > > I have a table, with one field haveing
> > > nextvalu('raj_seq') as default value.;
> > > now i wanted to increase the max value of the
> raj_seq
> > > to 9999999.
> > > 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.
> > > Thanks in advance.
> > > have a nice time.
> > > raja
> > >
> > >
> __________________________________________________
> > > Do you Yahoo!?
> > > Yahoo! Mail Plus - Powerful. Affordable. Sign up
> now.
> > > http://mailplus.yahoo.com
> > >
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> >
> http://www.postgresql.org/users-lounge/docs/faq.html
> 


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

  9. Re: how to alter sequence.

    Oliver Elphick <olly@lfix.co.uk> — 2002-12-04T14:06:52Z

    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 9999999.
> 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 <olly@lfix.co.uk>
LFIX Limited

  10. Re: [HACKERS] how to alter sequence.

    Hannu Krosing <hannu@tm.ee> — 2002-12-04T15:41:59Z

    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 9999999.
> > 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 <hannu@tm.ee>

  11. Re: [ADMIN] how to alter sequence.

    Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-12-04T15:51:24Z

    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.

  12. Re: [ADMIN] how to alter sequence.

    Hannu Krosing <hannu@tm.ee> — 2002-12-04T15:57:26Z

    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

  13. Re: [ADMIN] how to alter sequence.

    Rod Taylor <rbt@rbt.ca> — 2002-12-04T16:18:55Z

    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 9999999.
> > 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 <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

  14. Re: [HACKERS] how to alter sequence.

    Dustin Sallings <dustin@spy.net> — 2002-12-04T17:33:52Z

    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', 9999);
 setval
--------
   9999
(1 row)

dustin=# select nextval('test_seq');
 nextval
---------
   10000
(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 9999999.
# > > 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 <hannu@tm.ee>
#
# ---------------------------(end of broadcast)---------------------------
# TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
#
#

--
SPY                      My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    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. ____________

  15. Re: [HACKERS] how to alter sequence.

    Joel Burton <joel@joelburton.com> — 2002-12-04T17:44:50Z

    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', 9999);
>  setval
> --------
>    9999
> (1 row)
> 
> dustin=# select nextval('test_seq');
>  nextval
> ---------
>    10000
> (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  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

  16. Re: [HACKERS] how to alter sequence.

    Dustin Sallings <dustin@spy.net> — 2002-12-04T17:50:20Z

    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 <dustin@spy.net>
|    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. ____________

  17. Re: [HACKERS] how to alter sequence.

    Oliver Elphick <olly@lfix.co.uk> — 2002-12-04T17:54:07Z

    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', 9999);
>  setval
> --------
>    9999
> (1 row)
> 
> dustin=# select nextval('test_seq');
>  nextval
> ---------
>    10000
> (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', 999999);
        ERROR:  foo_seq.setval: value 999999 is out of bounds (1,3000)

-- 
Oliver Elphick <olly@lfix.co.uk>
LFIX Limited

  18. Re: how to alter sequence.

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-04T18:23:40Z

    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 9999999.
> > 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
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  19. Re: how to alter sequence.

    Raja Kumar Thatte <trajakumar@yahoo.com> — 2002-12-05T05:09:15Z

    Thanks everybody.


--- Bruce Momjian <pgman@candle.pha.pa.us> 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 9999999.
> > > 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
>   pgman@candle.pha.pa.us               |  (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 6: Have you searched our list archives?
> 
> http://archives.postgresql.org


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

  20. Re: how to alter sequence.

    mallah@trade-india.com — 2002-12-05T12:38:25Z

    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 9999999.
> > > 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.

  21. Re: how to alter sequence.

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-05T19:18:15Z

    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 9999999.
> > > > 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
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  22. Re: [HACKERS] how to alter sequence.

    Ross Reedstrom <reedstrm@rice.edu> — 2002-12-07T05:04:09Z

    On Thu, Dec 05, 2002 at 02:18:15PM -0500, Bruce Momjian wrote:
> 
> I don't think you can drop/recreate the sequence because the dependency
> code knows other tables depend on it.

Actually, I don't think the current dependency code notices if you use a
sequence in a default clause (other than via the special SERIAL type):
you'll just get a broken table, I think. Since Raj's sequence _has_ a
maxvalue set, I assume it was hand created. Hmm, seems you don't even get
a borken table, just a NOTICE, in 7.2, and you don't even get that in 7.3.

Regardless, I _have_ come up with a work around,  based on my read
of the sequence code, I don't think this will create any pits to fall
into. I don't see any real need for it though, since drop/create seems
to handle it.

As DB superuser, do:

test=# create SEQUENCE foo maxvalue 10000;
CREATE
test=# select setval('raj_seq',3000);
 setval 
--------
   3000
(1 row)

test=# select setval('raj_seq',20000);
ERROR:  raj_seq.setval: value 20000 is out of bounds (1,10000)
test=# update pg_class set relkind='r' where relname='raj_seq';
UPDATE 1
test=# update raj_seq set max_value=100000;
UPDATE 1
test=# vacuum full raj_seq;
VACUUM
test=# update pg_class set relkind='S' where relname='raj_seq';
UPDATE 1
test=# select setval('raj_seq',20000);
 setval 
--------
  20000
(1 row)


Ross

> 
> ---------------------------------------------------------------------------
> 
> 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 9999999.
> > > > > 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
>   pgman@candle.pha.pa.us               |  (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

-- 
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Research Scientist                                  phone: 713-348-6166
The Connexions Project      http://cnx./rice.edu      fax: 713-348-6182
Rice University MS-39
Houston, TX 77005

  23. Re: [HACKERS] how to alter sequence.

    Raja Kumar Thatte <trajakumar@yahoo.com> — 2002-12-07T09:58:53Z

    Thanks---Good Suggestion.
I think it will solve my problem.
raja
--- "Ross J. Reedstrom" <reedstrm@rice.edu> wrote:
> On Thu, Dec 05, 2002 at 02:18:15PM -0500, Bruce
> Momjian wrote:
> > 
> > I don't think you can drop/recreate the sequence
> because the dependency
> > code knows other tables depend on it.
> 
> Actually, I don't think the current dependency code
> notices if you use a
> sequence in a default clause (other than via the
> special SERIAL type):
> you'll just get a broken table, I think. Since Raj's
> sequence _has_ a
> maxvalue set, I assume it was hand created. Hmm,
> seems you don't even get
> a borken table, just a NOTICE, in 7.2, and you don't
> even get that in 7.3.
> 
> Regardless, I _have_ come up with a work around, 
> based on my read
> of the sequence code, I don't think this will create
> any pits to fall
> into. I don't see any real need for it though, since
> drop/create seems
> to handle it.
> 
> As DB superuser, do:
> 
> test=# create SEQUENCE foo maxvalue 10000;
> CREATE
> test=# select setval('raj_seq',3000);
>  setval 
> --------
>    3000
> (1 row)
> 
> test=# select setval('raj_seq',20000);
> ERROR:  raj_seq.setval: value 20000 is out of bounds
> (1,10000)
> test=# update pg_class set relkind='r' where
> relname='raj_seq';
> UPDATE 1
> test=# update raj_seq set max_value=100000;
> UPDATE 1
> test=# vacuum full raj_seq;
> VACUUM
> test=# update pg_class set relkind='S' where
> relname='raj_seq';
> UPDATE 1
> test=# select setval('raj_seq',20000);
>  setval 
> --------
>   20000
> (1 row)
> 
> 
> Ross
> 
> > 
> >
>
---------------------------------------------------------------------------
> > 
> > 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 9999999.
> > > > > > 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
> >   pgman@candle.pha.pa.us               |  (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
> 
> -- 
> Ross Reedstrom, Ph.D.                               
>  reedstrm@rice.edu
> Research Scientist                                 
> phone: 713-348-6166
> The Connexions Project      http://cnx./rice.edu    
>  fax: 713-348-6182
> Rice University MS-39
> Houston, TX 77005


=====

Raja Kumar Thatte, Research Engineer,TMN-Group,C-DOT, 71/1, Sneha Complex, Miller Road, Bangalore-560052. 

Phone:080-2389351/354, 2263399Ext362

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com