Re: Referential integrity vulnerability in 8.3.3

Sergey Konoplev wrote:
>>> Yes it is. But it the way to break integrity cos rows from table2 still
>>> refer to deleted rows from table1. So it conflicts with
>>> ideology isn't it?
>> Yes, but I'm not sure you could have a sensible behaviour-modifying BEFORE
>> trigger without this loophole. Don't forget, ordinary users can't work
>> around this - you need suitable permissions.
>>
>> You could rewrite PG's foreign-key code to check the referencing table after
>> the delete is supposed to have taken place, and make sure it has. That's
>> going to halve the speed of all your foreign-key checks though.
>>
>
> I'm not sure I've understood you right, sorry. Does "rewrite PG's
> foreign-key code" mean DDL? If it does how could I do this?

No, I was saying that to change this you'd have to alter PostgreSQL's
source-code.

You'd also have the issue of what to do with other triggers. You'd need
some priority level setting to allow some triggers to override other
triggers, but not the reverse.

If you really want to suppress deletion from table2 while enforcing
deletion via foreign-key you're best off with something like:

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.

--
   Richard Huxton
   Archonet Ltd