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@cynic.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@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073