Обсуждение: reinitialize a sequence?

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

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($_);
}

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
 

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

If and when stored procedures are supported, there should be some way to
prevent functions called in a SELECT statement to modify the database
(create, insert, etc.).

It is confusing (and wrong IMHO) to use statements like

SELECT setval('tablename_serfield_seq',max(serfield)) FROM tablename;
(which is used to reset a sequence)

That should be done with

EXECUTE procedure(tablename_name,sequence_name);
(not sure if execute is the right keyword)

Yours sincerely,

Edmar Wiggers
BRASMAP Information Systems
+55 48 9960 2752

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