Re: [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 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>