Re: pg_dump and ON DELETE CASCADE problem

--- On Fri, 12/11/09, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:

> From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
> To: cgg007(at)yahoo(dot)com
> Cc: pgsql-general(at)postgresql(dot)org, "Adrian Klaver" <aklaver(at)comcast(dot)net>, "Craig Ringer" <craig(at)postnewspapers(dot)com(dot)au>
> Date: Friday, December 11, 2009, 1:17 PM
> On Thu, Dec 10, 2009 at 1:21 PM, CG
> <cgg007(at)yahoo(dot)com>
> wrote:
> >
> > Thanks for the suggestion. I'm not sure what you mean
> when you say I should restore to a file. Do you mean I
> should dump the database to an SQL file instead of the
> "compressed" format?
> >
> > What do you think I will find?
> >
> > In the database dump, it is including a row that
> should be marked as deleted. I can select on that key in the
> production database and get zero rows, and I can select on
> that key in the restored database and find the row. When I
> ignore errors the data is restored, but the foreign key
> can't be created (and that is the only error I encounter).
> The presence of the data in the dump can not be contested...
> :)
>
> This could be a corrupted index problem maybe?  If you
> do this:
>
> set enable_indexscan=off;
> select * from table where key=value;
>
> does it still not show up?
>

Bingo. Showed right up. I did a reindex, and now it shows up searching via sequential scan or index scan.

So that's pretty scary to have a corrupted index. Once I reindexed, I'm able to see /a lot/ of data I couldn't before. This is the first time in 9 years that I've been bitten by PostgreSQL, and this one HURT.

PostgreSQL didn't crash, so there was no indication of failure until the demp-reload. To quote from the masters: Although in theory this should never happen, in practice indexes may become corrupted due to software bugs or hardware failures. I'm reasonably certain that the hardware for the server is sound. No crashes, no alarms... That leaves sofware bugs.

We're running PostgreSQL 8.4.1. I don't see any smoking gun bugfixes in 8.4.2, but we'll upgrade ASAP anyway...

What are your suggestions for how to proceed?