Re: pg_dump and ON DELETE CASCADE problem

On Tuesday 15 December 2009 2:33:39 pm CG wrote:

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

Interesting, though something is still bothering me. To quote from one of your
posts upstream;

"That was the same failure I got the previous night. I go to the live database
and rows with that key are /not/ in either one of those tables. They /were/ in
the tables at one point. I have an ON DELETE trigger that copies deleted rows
into another table, so I can see that a row with that key once existed in those
tables."

Would seem that the rows where deleted and should not be there when the table
was reindexed. Are the 'new' rows you are seeing also in the delete table?

--
Adrian Klaver
aklaver(at)comcast(dot)net