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. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
