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@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.


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@Yahoo.com #