pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
reinitialize a sequence?
5+ messages / 4 participants
[nested] [flat]

* reinitialize a sequence?
@ 2000-12-04 23:30  Bruno Boettcher <bboett@erm1.u-strasbg.fr>
  0 siblings, 2 replies; 5+ messages in thread

From: Bruno Boettcher @ 2000-12-04 23:30 UTC (permalink / raw)
  To: pgsql-sql

hello,
played a bit around with the serial type....
wanted to adapt my already existing tables to this system...
dumped the DB, created the new tables, loaded old data in....

all works, until i try to insert something without specifying the id
field (that's the now sequential field). And the reason is that the
sequene is set to 1.....

is there a simple way to tell all sequences to take the max value +1 of
their respective tables? (a bit like the vacuum command?)

i tryed to set the value by hand, but did something wrong :D the command
didn't completed:
fibu=> update journal_id_seq set last_value=1187;
ERROR:  You can't change sequence relation journal_id_seq

surely something real stupid again, but i never used sequences till
now... and there's no example in the docu.... (BTW would be nice if this
was addressed in the documentation)



-- 
ciao bboett
==============================================================
bboett@earthling.net
http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett
===============================================================
the total amount of intelligence on earth is constant.
human population is growing....



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* reinitialize a sequence?
@ 2000-12-04 23:51  Dan Lyke <danlyke@flutterby.com>
  parent: Bruno Boettcher <bboett@erm1.u-strasbg.fr>
  1 sibling, 1 reply; 5+ messages in thread

From: Dan Lyke @ 2000-12-04 23:51 UTC (permalink / raw)
  To: pgsql-sql

Bruno Boettcher writes:
> is there a simple way to tell all sequences to take the max value +1 of
> their respective tables? (a bit like the vacuum command?)

This is completely gross, but what I've done:


#!/usr/bin/perl -w
use strict;
use DBI;
my ($dbh);
sub BEGIN
{
    $dbh = DBI->connect('DBI:Pg:dbname=xxxx',
			'zzzzzzz',
			'zzzzz')
	or die $DBI::errstr;
}
sub END
{
    $dbh->disconnect;
}


sub UpdateSequenceFor($)
{
    my ($table) = @_;
    my ($sql,$sth,$id,$row);

    $sql = "SELECT max(id) FROM $table";
    $sth = $dbh->prepare($sql) or die $dbh->errstr."\n$sql\n";
    $sth->execute or die $sth->errstr."\n$sql\n";
    if ($id = $sth->fetchrow_arrayref)
    {
	$id = $id->[0];
	$sql = "SELECT nextval('".$table."_id_seq')";
	$sth = $dbh->prepare($sql) or die $dbh->errstr."\n$sql\n";
	$sth->execute or die $sth->errstr."\n$sql\n";
	while (($row = $sth->fetchrow_arrayref) && ($row->[0] < $id))
	{
	    $sth = $dbh->prepare($sql) or die $dbh->errstr."\n$sql\n";
	    $sth->execute or die $sth->errstr."\n$sql\n";
	}
    }
}

# update the sequence for each table:
foreach ('users','blogentries','blogcomments','blogcommenthistory')
{
    UpdateSequenceFor($_);
}



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: reinitialize a sequence?
@ 2000-12-05 00:31  Mike Castle <dalgoda@ix.netcom.com>
  parent: Bruno Boettcher <bboett@erm1.u-strasbg.fr>
  1 sibling, 0 replies; 5+ messages in thread

From: Mike Castle @ 2000-12-05 00:31 UTC (permalink / raw)
  To: pgsql-sql


setval();

Sometimes it's good to read files in the source tree (such as HISTORY).

mrc
-- 
       Mike Castle       Life is like a clock:  You can work constantly
  dalgoda@ix.netcom.com  and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day.  -- mrc
    We are all of us living in the shadow of Manhattan.  -- Watchmen



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: reinitialize a sequence?
@ 2000-12-05 16:10  Ross J. Reedstrom <reedstrm@rice.edu>
  parent: Dan Lyke <danlyke@flutterby.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Ross J. Reedstrom @ 2000-12-05 16:10 UTC (permalink / raw)
  To: pgsql-sql

On Mon, Dec 04, 2000 at 03:51:35PM -0800, Dan Lyke wrote:
> Bruno Boettcher writes:
> > is there a simple way to tell all sequences to take the max value +1 of
> > their respective tables? (a bit like the vacuum command?)
> 
> This is completely gross, but what I've done:
> 

Hmm, what I usually do is something like:

SELECT setval('tablename_serfield_seq',max(serfield)) FROM tablename;


To do that in one fell swoop is not trivial, since there's no easy way to
extract the automatically generated sequence name from the system
tables.

Well, I couldn't resist the challenge, so here's a crufty example, but better
than the perl that was here (I think ;-)

The following psql will generate psql statements to reset all your sequences
to the maximum value. This is for 7.0.2, and depends critically on exactly
how the default value for the 'serial' type is constructed. 


select 'SELECT setval(\'"' || 
        substr(adsrc,10,(length(adsrc) - 17)) || 
		'"\', max("' || attname || '")) FROM "' || 
		relname || '";' 
		from pg_class c, 
		     pg_attribute a, 
			 pg_attrdef d 
	    where c.oid=d.adrelid and 
		      a.attrelid=c.oid and 
			  d.adnum=a.attnum and 
			  d.adsrc ~ ('nextval\\(\''||relname);

Use this by redirecting output to a file, then reading in that file, as so:

me@mycomputer:~$ psql mydb

mydb=# \t
Showing only tuples.
mydb=# \o sequence_reset.sql
mydb=# select 'SELECT setval(\'"' || substr(adsrc,10,(length(adsrc) - 17)) || '"\', max("' || attname || '")) FROM "' || relname || '";' from pg_class c, pg_attribute a, pg_attrdef d where c.oid=d.adrelid and a.attrelid=c.oid and d.adnum=a.attnum and d.adsrc ~ ('nextval\\(\''||relname);
mydb=# \o
mydb=# \i sequence_reset.sql

<output showing resets occuring goes here>

You might get some errors for empty tables, since '0' is out of bounds
for sequences.

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: reinitialize a sequence?
@ 2000-12-05 17:27  Ross J. Reedstrom <reedstrm@rice.edu>
  parent: Ross J. Reedstrom <reedstrm@rice.edu>
  0 siblings, 0 replies; 5+ messages in thread

From: Ross J. Reedstrom @ 2000-12-05 17:27 UTC (permalink / raw)
  To: pgsql-sql

Following up to myself:

There is in fact a bug in 7.0.2 that's been fixed in 7.0.3 which causes
my select below not to work. The name of the sequence is now stored
with double quotes around it, to handle the mixed case names problem,
so the query needs to look like (untested: note added double quote to
last where clause):

select 'SELECT setval(\'"' || 
        substr(adsrc,10,(length(adsrc) - 17)) || 
		'"\', max("' || attname || '")) FROM "' || 
		relname || '";' 
		from pg_class c, 
		     pg_attribute a, 
			 pg_attrdef d 
	    where c.oid=d.adrelid and 
		      a.attrelid=c.oid and 
			  d.adnum=a.attnum and 
			  d.adsrc ~ ('nextval\\(\'"'||relname);


On Tue, Dec 05, 2000 at 10:10:12AM -0600, Ross J. Reedstrom wrote:
> On Mon, Dec 04, 2000 at 03:51:35PM -0800, Dan Lyke wrote:
> > Bruno Boettcher writes:
> > > is there a simple way to tell all sequences to take the max value +1 of
> > > their respective tables? (a bit like the vacuum command?)
> > 
> > This is completely gross, but what I've done:
> > 
> 
> Hmm, what I usually do is something like:
> 
> SELECT setval('tablename_serfield_seq',max(serfield)) FROM tablename;
> 
> 

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2000-12-05 17:27 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-04 23:30 reinitialize a sequence? Bruno Boettcher <bboett@erm1.u-strasbg.fr>
2000-12-04 23:51 ` Dan Lyke <danlyke@flutterby.com>
2000-12-05 16:10   ` Ross J. Reedstrom <reedstrm@rice.edu>
2000-12-05 17:27     ` Ross J. Reedstrom <reedstrm@rice.edu>
2000-12-05 00:31 ` Mike Castle <dalgoda@ix.netcom.com>

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