Answering part of my own questions, it appears the latest thinking on this problem (in
http://www.deja.com/getdoc.xp?AN=542994084) is that it stems from indexed columns greater
than half a page (~4K), and/or a previous insert having been interrupted by elog(ERROR) or
a backend crash. The latter explanation seems more plausible, as I do not have any
columns approaching 512 bytes, much less 4K. Glad to hear it may be coming from an error
rather than normal usage, though it'd be nice to optionally have corrupted indices
automatically rebuilt.
Cheers.
Ed
Ed Loehr wrote:
> Bruce Momjian wrote:
>
> > > Peter Eisentraut wrote:
> > >
> > > > This is getting to be our favourite error message...
> > > >
> > > > It is caused by a corrupted B-Tree index. Drop and recreate that one.
> > >
> > > Thanks. Unfortunately, the lack of context to the error message makes it difficult
> > > to identify which index is "that one." The message was last showing up during the
> > > process of dropping/recreating a series of triggers and functions via "psql -f"
> > > without any table inserts/updates.
> >
> > I have fixed 7.0 so it will show the index name.
>
> It sounds like this B-tree index corruption happens often enough to gain fame. I am
> curious as to what the full impact of this is on a production database. Would I be
> correct in assuming are consequences are (1) an unusable index, resulting in (2)
> inaccessible data, resulting in (3) an unusable database until the index is dropped and
> recreated?
>
> If the least drastic corrective solution is to drop and recreate the index, that leaves
> me wondering:
> 1) Does anyone know what is causing the corrupted B-tree?
> 2) How difficult would it be to automate the process of index rebuilding at the
> point the corruption is detected? How could that be done otherwise in an automated
> fashion?
> 3) What are other people doing to deal with this?
>
> Cheers.
> Ed