Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: [GENERAL] database corruption?

Re: [GENERAL] database corruption?

From
bryanh@giraffe-data.com (Bryan Henderson)
Date:
> 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

Re: [GENERAL] database corruption?

From
Ed Loehr
Date:
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