Re: Referential integrity vulnerability in 8.3.3

Sergey Konoplev wrote:
> > CREATE OR REPLACE FUNCTION fktrigfn() RETURNS TRIGGER AS $$
> > BEGIN
> >    PERFORM 1 FROM table1 WHERE a = OLD.aref;
> >    IF FOUND THEN
> >        RAISE NOTICE 'aborting delete for %', OLD.aref;
> >        RETURN NULL;
> >    ELSE
> >        RAISE NOTICE 'allowing delete for %', OLD.aref;
> >        RETURN OLD;
> >    END IF;
> > END;
> > $$ LANGUAGE plpgsql;
> >
> > That should be OK, because the row should always be marked as removed from
> > table1 before the delete cascades.
>
> Well, your solution doesn't solve the main problem that sounds like
> "Table2 contains rows with FK fields refer to deleted rows from table1
> when ON DELETE action of the FKs is CASCADE". The only additional
> thing fktrigfn() does is informing about "zombie" rows appearance in
> logs.
>
>
It does work around the problem.  The perform line sets found to true if
the row exists in the referred table and returns the NULL to prevent the
delete without crashing the transaction.  If it doesn't find the row in
the referred table, then it assumes it must be in a foreign key
cascading delete and returns OLD so that the rest of the delete happens.

i.e. the sequence of events is

1. statement delete from table1 where pk=blah
2. the row is removed from table1
3. attempt delete on table2
4. fktrigfn fires
5. found is set to false by the perform
6. old is returned
7. the row is removed from table2

as opposed to

1. statement delete from table2 where pk=foo
2. fktrigfn fires
3. found is set to true by the perform
4. null is returned
5. nothing changes

You would need to work the same logic into where you return null in your
real trigger.

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au