Re: ON DELETE CASCADE with multiple paths

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.