So it seems I got bitten today by this to-do list item:
Have AFTER triggers execute after the appropriate SQL statement in a function, not at the end of the function
Under normal circumstances, delaying this stuff until the end of the
function doesn't bother me; in fact I've even used it to get around the
fact that SET CONSTRAINTS won't let you delay non-referential constraint
checks.
However, it seems that cascading deletes are also delayed, which leads to
a pretty serious problem. The following code:
INSERT INTO master (master_id) VALUES (400);INSERT INTO dependent (master_id) VALUES (400);DELETE FROM master WHERE
master_id= 400;
works just fine outside a function, but inside a function it fails with
ERROR: $1 referential integrity violation - key referenced from dependent not found in master
It seems that the integrity check for the dependent is happening before the
cascaded delete, but the check is operating not on the data at the time of
the statement, but the data as it stands after the statement following the
one that triggered the check. Ouch!
Having spent the better part of a day tracking down this problem
(because of course, as always, it only decides to appear in one's own
code after it's gotten quite complex), I think for a start it would
be a really, really good idea to put something about this in the
documentation for the 7.4 release. Probably the SET CONSTRAINTS page
would be a good place to have it, or at least a pointer to it.
In the long run, of course, I'd like to see a fix, but preferably after
we fix the system to allow delay of non-referential constraints as well,
since I am use this "bug" now in production code to delay constraint
checking for non-referential constraints. (You might even document that
workaround in the SET CONSTRAINTS manual page, with an appropriate
warning, if one seems necessary.)
I've attached a short shell script that will demonstrate the problem.
cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.NetBSD.org Don't you know, in this new Dark Age, we're
alllight. --XTC