Thanks. As your example demonstrates, automating a rebuild of indices via cron/perl/DBI is pretty straighforward.
Unfortunately, reliability and
usability concerns in my application strongly suggest that the corrupted index really needs to be identified and
rebuiltin real-time to allow the
original failed queries to be automatically retried to success, albeit a bit slower. Taking the system off-line to
rebuildthe corrupted index is *very*
undesirable. If I could get a true error indication in real-time from DBI/DBD::Pg that this was the problem, I think I
couldrebuild the index on the
fly. Not sure enough PG error information and/or codes are being returned to sufficiently identify the problem,
though.
Cheers.
Ed
Moray McConnachie wrote:
> With all this talk of rebuilding indices, the following script does it
> for me:
>
> #!/bin/sh
> pg_dump -s databasename > filename
> perl -pi -e 'unless (/CREATE.*INDEX/) {s/.*//;chomp;}' filename
> perl -pi.create -e 's/CREATE.*?INDEX(.*?\s)ON.*/DROP INDEX$1\;/i;'
> filename
> mv filename filename.drop
> psql -d databasename -c '\i filename.drop'
> psql -d databasename -c '\i filename.create'
>
> Of course there are bound to be a million ways of scripting this
> without perl, but using awk or similar.
>
> You could cron this up - I don't know if regular rebuilding
> of indices helps minimise corruption?
>
> Yours,
> Moray
>
> ----------------------------------------------------------------------
> ----------------
> Moray.McConnachie@computing-services.oxford.ac.uk
>
> ----------------------------------------------------------------------
> Name: Re [GENERAL] FATAL 1 my bits moved
rightoff the end of theworld!.txt
> Re [GENERAL] FATAL 1 my bits moved right off the end of theworld!.txt Type: Plain Text (text/plain)
> Encoding: quoted-printable
