Re: [GENERAL] database corruption?

Bryan Henderson wrote:
>
> > NOTICE:  Index error_interface_idx: NUMBER OF INDEX' TUPLES (226766)
> >          IS NOT THE SAME AS HEAP' (226765)
> ...
> > NOTICE:  Index pg_class_relname_index: NUMBER OF INDEX' TUPLES (74)
> >          IS NOT THE SAME AS HEAP' (75)
> ...
> >IIRC, I think the problem and solution is basically the same:
> >corrupted index needing drop/rebuild.
>
> I wouldn't jump to that conclusion.  In my experience with this message
> (and I've had more than I'd like), it is the table that is corrupted while
> the index is fine.  Rebuilding the index makes the error messages stop,
> but I've still lost data.
>
> I've actually only seen the one where the index is larger than the table.
> It happens sometimes when I update a table (which is a delete and insert),
> and Postgres does not mark the page that the inserted row goes into as
> allocated.  This means the updated row gets lost, but the index entry is
> still there.
>
> And I haven't seen it on anything newer than Release 6.1, so I'm not saying
> this is the same problem.  Just an example of how it might not be the index
> that has the problem.
>
> One way to gather more information is to find out which row is present
> in one but not the other, and you can do this by doing two SELECTs,
> with OIDs: one with a WHERE clause that makes Postgres use the index
> (use EXPLAIN to see) and one that doesn't, and compare the results.
> Where the index is larger than the table, the index scan query will
> show the same row twice, except with the wrong key field value for one.
> Where the index is smaller than the table, the index scan query will
> be missing a row.

Hmmm.  Here's a bit more history for the puzzle and maybe another
possible remedy/workaround/cleanup...

    http://www.deja.com/getdoc.xp?AN=471547594
    http://www.deja.com/getdoc.xp?AN=575819776

Regards,
Ed Loehr