Re: Broken Constraint Checking in Functions

I am not sure we should add something to the SET CONSTRAINT page on
this. Our current behavior is clearly a bug, and for that reason
belongs more on the TODO list, where it already is:

* Have AFTER triggers execute after the appropriate SQL statement in a
function, not at the end of the function

The big question is whether this entry is clear enough for people to
understand it could bite them.

---------------------------------------------------------------------------

Curt Sampson wrote:
>
> 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(at)cynic(dot)net> +81 90 7737 2974 http://www.NetBSD.org
> Don't you know, in this new Dark Age, we're all light. --XTC

Content-Description:

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073