> On 12/9/2005 8:27 PM, Stephan Szabo wrote:
> > 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.
>
> Yes, you can deliberately break referential integrity with that. But you
> know what? I think the overall waste of performance and developer time,
> required to "fix" this rather exotic (and idiotic) problem, is too high
> to seriously consider it.
Well, the case that brought up the original question was one where the
before trigger updated rows that were going to be affected by the cascaded
delete. Before this worked by accident, now it gives an error (even
though the key wasn't changed due to some other possibilities of violation
forcing the check). The problem is that if we're not consistent about
what violation cases are acceptable, it's hard to diagnose if something is
an actual bug or merely an acceptable side effect. :)