Thread: 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
Using SELECT as DDL/DML statement is wrong (was RE: reinitialize a sequence?)
From
"Edmar Wiggers"
Date:
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