Received: from dagny.flutterby.com ([209.140.49.28]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eB4NtuG61792 for ; Mon, 4 Dec 2000 18:55:56 -0500 (EST) (envelope-from danlyke@flutterby.com) Received: from wynand.flutterby.com (adsl-64-163-66-178.dsl.snfc21.pacbell.net [64.163.66.178]) by dagny.flutterby.com (8.9.3/8.9.3/Debian/GNU) with ESMTP id PAA22390 for ; Mon, 4 Dec 2000 15:30:00 -0800 Received: (from danlyke@localhost) by wynand.flutterby.com (8.9.3/8.9.3/Debian 8.9.3-21) id PAA02295; Mon, 4 Dec 2000 15:51:35 -0800 From: Dan Lyke MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-ID: <14892.11783.659931.212551@wynand.flutterby.com> Date: Mon, 4 Dec 2000 15:51:35 -0800 (PST) To: pgsql-sql@postgresql.org Subject: reinitialize a sequence? In-Reply-To: <20001205003034.D31987@erm1.u-strasbg.fr> References: <20001205003034.D31987@erm1.u-strasbg.fr> X-Mailer: VM 6.72 under 21.1 (patch 10) "Capitol Reef" XEmacs Lucid X-Archive-Number: 200012/28 X-Sequence-Number: 359 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($_); }