From: | Ed Loehr <eloehr(at)austin(dot)rr(dot)com> |
---|---|
To: | Bryan Henderson <bryanh(at)giraffe-data(dot)com> |
Cc: | pgsql-general(at)hub(dot)org |
Subject: | Re: [GENERAL] database corruption? |
Date: | 2000-03-07 03:44:39 |
Message-ID: | 38C47B27.8D3722C7@austin.rr.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Zot O'Connor | 2000-03-07 04:47:21 | change owner of database -- answer |
Previous Message | Bruce Momjian | 2000-03-07 03:00:31 | Re: [GENERAL] FOREIGN KEY syntax |