Re: BUG #6226: Broken foreign key stored on database (parent deleted with children still readable, BUG#6225 Update)

Daniel Cristian Cruz <danielcristian(at)gmail(dot)com> writes:
> 2011/10/5 Alvaro Herrera <alvherre(at)commandprompt(dot)com>
>> Well, some people say it's a bug, others say it's not; and even if it
>> is, changing it means backwards incompatible behavior, so *if* it is
>> patched, it will only change the behavior on a future release, not
>> whatever you're using.

> Well, I'll try to explain better, because it could be a bug, since after
> deleting and cascading, a deleted row is still visible and has a foreign key
> constraint violated with no error message.

This schema arrangement is circular. Consider what happens:

1. You issue a DELETE for the event row with id_event = 1.

2. This fires (as AFTER triggers) commands to do these things:

a. DELETE FROM repetition_conf WHERE id_event = 1.
b. UPDATE repetition SET id_event = NULL WHERE id_event = 1.
(This one does nothing since there is no such row in repetition.)

I don't recall whether we have any particular guarantees about the
order these two things get done in, but in this example it doesn't
matter.

3. The DELETE on repetition_conf deletes the lone repetition_conf row,
and then fires an AFTER trigger to do

DELETE FROM repetition WHERE id_repetition_conf = 1.

4. That DELETE finds it should delete the lone repetition row ...
but wait! *Before* it can delete that row, it must fire the
BEFORE DELETE trigger.

5. The trigger function issues

DELETE FROM event WHERE id_event = 2.

6. This fires (as AFTER triggers) commands to do these things:

a. DELETE FROM repetition_conf WHERE id_event = 2.
(This does nothing.)
b. UPDATE repetition SET id_event = NULL WHERE id_event = 2.
(This one finds and updates the lone repetition row.)

7. Now we exit the trigger function and are back at the step-3 DELETE
command. It was supposed to delete the repetition row.
But by now, that row has already been updated, and under Postgres'
visibility rules that means the previously-started delete can't
delete it. If it did delete it, it might be deleting data other
than what you intended, since the row is now different from what
it was when it was selected for deletion.

In my view, this whole thing is just an example of the hazards of
defining BEFORE triggers that can change the state of the target row.
That's best avoided, because there simply isn't any good semantics
for it. But generally the rule in Postgres is first action past the
post wins, and in this case it's the UPDATE SET NULL that gets done
first.

The rough rule of thumb is that BEFORE triggers are good for adjusting
the contents of the row that's about to be stored, while AFTER triggers
are what to use to propagate changes elsewhere. I'm not sure that
changing the BEFORE trigger to AFTER would make all your problems go
away, but it'd be worth a try.

Another point worth making here is that in Postgres, triggers run at a
lower level than foreign key constraints (since FKs are implemented
using triggers). It *is possible* to cause an FK constraint to be
violated, if you install a trigger that interferes with the operation of
the FK action, which is what's happening here. We don't consider that a
bug but a feature. The only way to prevent it would be to not fire
triggers for updates caused by FK actions, which would be a cure worse
than the disease.

regards, tom lane