On Fri, 9 Dec 2005, Jan Wieck wrote:
> On 12/8/2005 8:53 PM, Tom Lane wrote:
>
> > Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> >> Yeah. I really don't understand it, but it appears to me to be explicitly
> >> different in the spec for on delete cascade even compared to the rest of
> >> the referential actions.
> >
> >>> One problem I see is, what do we do if the BEFORE
> >>> trigger then returns NULL (to skip the delete). The cascaded operations
> >>> are already done. Do we have to execute the cascaded deletes in a
> >>> subtransaction or do we disallow the skip in this case?
> >
> >> I think we'd have disallow skipping. Especially since skipping would
> >> probably end up with a violated constraint.
> >
> > That seems to me to be a sufficient reason to not follow the spec in
> > this respect. A BEFORE trigger should be run BEFORE anything happens,
> > full stop. I can't think of any good reason why the spec's semantics
> > are better. (It's not like our triggers are exactly spec-compatible
> > anyway.)
>
> It doesn't lead to a violated constraint. bar references foo on delete
> cascade, now delete from foo will first delete from bar, then the before
> trigger on foo skips the delete.
That's not the right case I think.
Pseudo example:
create table a
create table b references a on delete cascade
create before trigger on b that sometimes skips a delete to b
insert into a and b.
delete from a
-- AFAICS, you can end up with a row in b that no longer has its
associated row in a since the a row will be deleted but there's no
guarantee its referencing rows in b will have successfully been deleted.
> And besides, as the other post (Trigger preventing delete causes
> circumvention of FK) in GENERAL shows, triggers can break RI anyway.
Yeah, although fixing the cases where the trigger interacted badly with
before triggers was the point of the posts that started this. The original
problem was with a case where it acted differently, but it's fairly
related.