Обсуждение: Broken Constraint Checking in Functions
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
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
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 alllight. --XTC
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, Pennsylvania19073
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 alllight. --XTC