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