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 <[EMAIL PROTECTED]> 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. #
#================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
