Postgres Pro
Downloads
Home   >   mailing lists

Thread: ON DELETE CASCADE with multiple paths

ON DELETE CASCADE with multiple paths

From
Max Khon
Date:
Hi!

Suppose the following schema:

create table foo(foo_id integer primary key);
create table bar(bar_id integer primary key, foo_id integer,
    constraint bar_fk0 foreign key (foo_id) references foo(foo_id)
    on delete cascade);
create table foobar(foobar_id serial primary key, foo_id integer, bar_id
integer);

alter table foobar add constraint foobar_fk1 foreign key (bar_id)
references bar(bar_id) on delete cascade;
alter table foobar add constraint foobar_fk0 foreign key (foo_id)
references bar(bar_id);

And data:

insert into foo(foo_id) values(1);
insert into bar(bar_id, foo_id) values(1, 1);
insert into foobar(foo_id, bar_id) values(1, 1);

The following statement does work:

delete from foo;

All is ok. The row in foobar is deleted because of ON DELETE CASCADE fk
constraints bar_fk0 and foobar_fk1.

After altering the schema:

alter table foobar drop constraint foobar_fk0;
alter table foobar drop constraint foobar_fk1;

alter table foobar add constraint foobar_fk0 foreign key (foo_id)
references bar(bar_id);
alter table foobar add constraint foobar_fk1 foreign key (bar_id)
references bar(bar_id) on delete cascade;

(note that constraints are now added in different order)

and the same data:

insert into foo(foo_id) values(1);
insert into bar(bar_id, foo_id) values(1, 1);
insert into foobar(foo_id, bar_id) values(1, 1);

"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"

PostgreSQL version: any (I tested on 8.2.4 for Win32 and 8.1.3 for Linux)

--
Max Khon
PEM Platform Team Leader
SWsoft, Inc.
E-mail: mkhon@swsoft.com
Web Site: http://swsoft.com/

Re: ON DELETE CASCADE with multiple paths

From
Tom Lane
Date:
Max Khon <mkhon@swsoft.com> 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.

            regards, tom lane

Re: ON DELETE CASCADE with multiple paths

From
Stephan Szabo
Date:
On Thu, 17 May 2007, Tom Lane wrote:

> Max Khon <mkhon@swsoft.com> 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.

Re: ON DELETE CASCADE with multiple paths

From
Max Khon
Date:
Hi!

Stephan Szabo wrote:

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

Just for the record: it works on Oracle 8i (and I assume it works on
later versions) and SQL Server 2005.

--
Max Khon
PEM Platform Team Leader
SWsoft, Inc.
E-mail: mkhon@swsoft.com
Web Site: http://swsoft.com/

Re: ON DELETE CASCADE with multiple paths

From
Max Khon
Date:
Stephan Szabo wrote:
> On Thu, 17 May 2007, Tom Lane wrote:
>
>> Max Khon <mkhon@swsoft.com> 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: mkhon@swsoft.com
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);
     }
 }

Re: ON DELETE CASCADE with multiple paths

From
Stephan Szabo
Date:
On Mon, 21 May 2007, Max Khon wrote:

> Stephan Szabo wrote:
> > On Thu, 17 May 2007, Tom Lane wrote:
> >
> >> Max Khon <mkhon@swsoft.com> 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?

Hmm, I'm not sure. I'm not sure if that's sufficient and that it doesn't
add any holes, but we can check that. At least I think on set default
triggers we'd need to do something with the check performed from inside
the trigger.

Re: ON DELETE CASCADE with multiple paths

From
Max Khon
Date:
Stephan Szabo wrote:

>>>>> "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?
>
> Hmm, I'm not sure. I'm not sure if that's sufficient and that it doesn't
> add any holes, but we can check that.

> At least I think on set default
> triggers we'd need to do something with the check performed from inside
> the trigger.

What's wrong with this check? Can you please elaborate?

btw does postgresql project have bugzilla or something like that? I
can't find it on http://postgresql.org/

--
Max Khon
PEM Platform Team Leader
SWsoft, Inc.
E-mail: mkhon@swsoft.com
Web Site: http://swsoft.com/

Re: ON DELETE CASCADE with multiple paths

From
Stephan Szabo
Date:
On Tue, 22 May 2007, Max Khon wrote:

> Stephan Szabo wrote:
>
> >>>>> "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?
> >
> > Hmm, I'm not sure. I'm not sure if that's sufficient and that it doesn't
> > add any holes, but we can check that.
>
> > At least I think on set default
> > triggers we'd need to do something with the check performed from inside
> > the trigger.
>
> What's wrong with this check? Can you please elaborate?

IIRC, at the end of those we call the function that performs the no action
check which does the basic constraint check to cover a hole where the row
might not actually change key values (which would elide the check caused
by the cascaded update that set default) but for which the referenced key
is gone. The problem is that given this report, we shouldn't check at the
end of the update cascade, but instead need to postpone that check until
any other referential actions have occured.

If we're forcing the referential actions to occur first, that might be
solvable by having on * set default actually have both the current set
default function and the no action function as separate triggers.

> btw does postgresql project have bugzilla or something like that? I
> can't find it on http://postgresql.org/

Not really, the -bugs list (and archive) is pretty much the current
archive.