Re: Foreign Key & Rule confusion WAS: Lost Trigger(s)?

"Rod Taylor" <rod(dot)taylor(at)inquent(dot)com> writes:
> I must apologize, I was copying from one screen to another due to
> network outage and gave a bad example -- missed the most important
> part.

> There should have been an AS ON DELETE TO junk DO INSTEAD NOTHING;
> rule.

Ah so. With that in place, I see what you are talking about:

regression=# DELETE FROM junk_parent WHERE col = 1;
ERROR: SPI_execp() failed in RI_FKey_cascade_del()
regression=# DELETE FROM junk_parent WHERE col = 2;
ERROR: SPI_execp() failed in RI_FKey_cascade_del()

> The RI_FKey_cascade_del() trigger fails on the second delete attempt.
> To me it should ignore the error if there wasn't anything to delete in
> the first place.

Well, I think the issue is something different. Right now, referential
integrity triggers are implemented as issuing actual queries --- which
are subject to rule rewrites. It strikes me that perhaps this is wrong,
and a referential integrity operation should proceed without regard to
rules.

If you think that rules indeed should be able to affect referential
integrity updates, then it would probably be better that neither of
these examples fail (ie, the RI triggers should not complain about their
queries having been rewritten to nothing).

I don't see a good argument for raising an error on the first delete and
not the second. Either ref integrity is subject to rules, or it's not.

Next question: should a trigger be able to defeat an RI update? That
can happen now, too.

regards, tom lane