Hi all,
I've recently been using some older version of postgres on linux
systems, and one of the most annoying problems i've come across is to
do with sequence values not being updated when a database backup is
restored. This is because the insert and copy routines used to restore
the data into tables do not trigger the nextval function on insert, and
so you get a lot of annoying errors about duplicate keys if your ID
fields are based on sequences.
Anyway, if you know what im talking about then im sure you understand
how annoying it is. I'm pretty sure the problem is fixed now in
postgres 8, however older systems such as 7.4 and 7.3.10 do not
correctly update sequence values when restoring from backups.
So in short (or not so short), i've written a simple bash script that
will connect to a given database, get a list of all the sequences for
that database, and then update the current value of the sequence to be
one greater than the max value of the corresponding ID field for that
table.
The script should be run as your postgres user on a linux-ish system
with bash.
Code follows. Watch out for wrapped sentences when copying (!).
-------------------------
#!/bin/bash
usage="Description:\nA script to update every sequence in a postgres
database to the current\n
value of the count of the number of rows in a corresponding table\n
NB: this script assumes that the sequence includes the name of the
table\n
at the start of the sequence name, and parses the sequence name as
such\n"
if [ $# -lt 1 ];
then
echo -e "$0: Error: Please enter the name of the database to
connect to. \n"
echo -e "Usage: $0 <database>\n"
echo -e $usage
exit 1;
fi
database="$1"
sequences=`echo "\ds" | psql -t ${database} | cut -d "|" -f2`
for i in $sequences
do
table=`echo $i | cut -d "_" -f1`
idfield=`echo "\d \"${table}\"" | psql -t ${database} | grep $i
| cut -d "|" -f1 | sed -e 's/ //g'`
countsql="select max(\"$idfield\") from \"$table\""
tablecount=`echo $countsql | psql -t ${database} | sed -e 's/
//g'`
updateseq="select setval('\"$i\"', (select max(\"$idfield\")
from \"$table\")+1);"
newval=`echo $updateseq | psql -t ${database} | sed -e 's/
//g'`
echo "Table $table: $idfield has sequence: $i, with max value:
$tablecount, updated to $newval"
done
-------------------------
Hopefully this may solve someone some grief.
Cheers,
Matt
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
