Re: pg_dump and ON DELETE CASCADE problem

----- "CG" <cgg007(at)yahoo(dot)com> wrote:

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

Would it be possible to see the table schemas and indices ?

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

Adrian Klaver
aklaver(at)comcast(dot)net