Re: pg_dump and ON DELETE CASCADE problem

--- On Tue, 12/15/09, Adrian Klaver <aklaver(at)comcast(dot)net> wrote:

> From: Adrian Klaver <aklaver(at)comcast(dot)net>
> Subject: Re: [GENERAL] pg_dump and ON DELETE CASCADE problem
> To: cgg007(at)yahoo(dot)com
> Cc: "postgresql listserv" <pgsql-general(at)postgresql(dot)org>, "Craig Ringer" <craig(at)postnewspapers(dot)com(dot)au>, "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
> Date: Tuesday, December 15, 2009, 6:53 PM
> 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?
>

select foo from bar where baz = 'key';

I was mistaken when I said that the row was not in the table. If I had an index on baz, and the index was corrupted, that SQL would return 0 rows leading me to believe that there were no rows in the table with that key.

And, the reason for that row remaining in the database after its foreign keyed parent row was deleted was because the delete operation was depending on the index to find the rows to delete, and that index was corrupt.

Of course, I had no idea that the index was corrupt when I made my first post.

On the table that has the "martian" row, there is no delete storage. Since the data in the table is trigger-generated for FTI searches, there's no reason to keep that data around.

I'm still faced with the unpleasant and unresolved issue of why the index was corrupted in the first place.