Re: Foreign key trigger timing bug?

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(at)megazone(dot)bigpanda(dot)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.

Jan

>
>> 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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #