Re: [GENERAL] "FATAL 1: my bits moved right off the end of theworld!"

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 rebuilt in real-time to allow the
original failed queries to be automatically retried to success, albeit a bit slower. Taking the system off-line to rebuild the 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 could rebuild 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(dot)McConnachie(at)computing-services(dot)oxford(dot)ac(dot)uk
>
> ----------------------------------------------------------------------
> Name: Re [GENERAL] FATAL 1 my bits moved right off 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