Re: [GENERAL] database corruption?

> 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.

--
Bryan Henderson Phone 415-505-3367
Olympia, Washington