Re: [BUGS] ON DELETE CASCADE with multiple paths

[Max Khon]

Stephan Szabo wrote:
> On Thu, 17 May 2007, Tom Lane wrote:
> 
>> Max Khon <[EMAIL PROTECTED]> writes:
>>> "delete from foo" fails:
>>> ERROR: update or delete on table "bar" violates foreign key constraint
>>> "foobar_fk0" on table "foobar"
>>> SQL state: 23503
>>> Detail: Key (bar_id)=(1) is still referenced from table "foobar".
>>> Context: SQL statement "DELETE FROM ONLY "public"."bar" WHERE "foo_id" = $1"
>> I see no bug here.  There is no guarantee about the order in which
>> constraints are applied.
> 
> Except that SQL92 at least does seem to say in 11.8 that "All rows that
> are marked for deletion are effectively deleted at the end of the
> SQL-statement, prior to the checking of any integrity constraints." I
> think that likely makes our behavior wrong, but I'm not really sure how to
> get there from what we have now.

Is it sufficient to execute ON DELETE CASCADE and ON DELETE SET
NULL/DEFAULT triggers before other triggers?

Attached patch solves the problem for me.

-- 
Max Khon
PEM Platform Team Leader
SWsoft, Inc.
E-mail: [EMAIL PROTECTED]
Web Site: http://swsoft.com/

--- src/backend/commands/trigger.c.orig 2007-05-21 15:45:53.000000000 +0700
+++ src/backend/commands/trigger.c      2007-05-21 15:51:42.000000000 +0700
@@ -1989,6 +1989,24 @@
 }
 
 
+static bool
+afterTriggerCascadeOrSetXXX(Oid tgfoid)
+{
+       switch (tgfoid)
+       {
+               case F_RI_FKEY_CASCADE_DEL:
+               case F_RI_FKEY_CASCADE_UPD:
+               case F_RI_FKEY_SETNULL_DEL:
+               case F_RI_FKEY_SETNULL_UPD:
+               case F_RI_FKEY_SETDEFAULT_DEL:
+               case F_RI_FKEY_SETDEFAULT_UPD:
+                       return true;
+
+               default:
+                       return false;
+       }
+}
+
 /* ----------
  * afterTriggerAddEvent()
  *
@@ -1996,7 +2014,7 @@
  * ----------
  */
 static void
-afterTriggerAddEvent(AfterTriggerEvent event)
+afterTriggerAddEvent(AfterTriggerEvent event, Oid tgfoid)
 {
        AfterTriggerEventList *events;
 
@@ -2012,6 +2030,11 @@
                events->head = event;
                events->tail = event;
        }
+       else if (afterTriggerCascadeOrSetXXX(tgfoid))
+       {
+               event->ate_next = events->head;
+               events->head = event;
+       }
        else
        {
                events->tail->ate_next = event;
@@ -3178,6 +3201,6 @@
                /*
                 * Add the new event to the queue.
                 */
-               afterTriggerAddEvent(new_event);
+               afterTriggerAddEvent(new_event, trigger->tgfoid);
        }
 }

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate