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
Fri, 10 Oct 2003 19:08:44 +0900 (JST)
Curt Sampson <cjs@cynic.net>
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, Pennsylvania 19073

Fri, 24 Oct 2003 00:07:25 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Broken Constraint Checking in Functions
On Fri, 24 Oct 2003, Bruce Momjian wrote:

> 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:

Had it been on the SET CONSTRAINT page, it would have saved me several
hours of debugging. I found the entry only after tracking down the
problem and creating a simple test case to demonstrate it.

We document other bugs on this page, e.g.:

    Currently, only foreign key constraints are affected by this
    setting. Check and unique constraints are always effectively
    initially immediate not deferrable.

So why not document this one as well?

> 	* 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.

My big question is, should we expect that anybody reading the
documentation also has to go through the TODO list to see if there are
bugs on the list not mentioned in the manual?

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

Fri, 24 Oct 2003 13:29:59 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Broken Constraint Checking in Functions
Curt Sampson wrote:
> On Fri, 24 Oct 2003, Bruce Momjian wrote:
> 
> > 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:
> 
> Had it been on the SET CONSTRAINT page, it would have saved me several
> hours of debugging. I found the entry only after tracking down the
> problem and creating a simple test case to demonstrate it.
> 
> We document other bugs on this page, e.g.:
> 
>     Currently, only foreign key constraints are affected by this
>     setting. Check and unique constraints are always effectively
>     initially immediate not deferrable.
> 
> So why not document this one as well?

Does it belong on the SET CONSTRAINT page, the CREATE TABLE page, or the
CREATE FUNCTION page?

> > 	* 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.
> 
> My big question is, should we expect that anybody reading the
> documentation also has to go through the TODO list to see if there are
> bugs on the list not mentioned in the manual?

This is more of a "this is a clear bug" rather than an judgement call or
something.  I can add it, but we do expect most people to read the TODO
list because it shows our shortcomings.  Can I get anyone else to
recommend adding it to the CREATE CONSTRAINT manual page?

-- 
  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, Pennsylvania 19073

Fri, 24 Oct 2003 00:39:29 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Broken Constraint Checking in Functions
On Fri, 24 Oct 2003, Bruce Momjian wrote:

> Does it belong on the SET CONSTRAINT page, the CREATE TABLE page, or the
> CREATE FUNCTION page?

Hard to say. Possibly all three. I'd defer to your judgement on
this. I'd say I have a slight preference for putting it on the SET
CONSTRAINT and CREATE FUNCTION pages. Or another option would be to
create a section 2.4.6 in the User Guide to hold details on constraint
operations, and suggest on all three pages above that you consult this
section to find out just how constraints are going to work. Then we need
update only one place when a bug is fixed.

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

Fri, 24 Oct 2003 13:52:38 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Broken Constraint Checking in Functions