Re: [HACKERS] how to alter sequence.

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(at)candle(dot)pha(dot)pa(dot)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(at)rice(dot)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