SQL standard changed behavior of ON UPDATE SET NULL/SET DEFAULT? - Mailing list pgsql-hackers
From | Tom Lane |
---|---|
Subject | SQL standard changed behavior of ON UPDATE SET NULL/SET DEFAULT? |
Date | |
Msg-id | 6456.1339872740@sss.pgh.pa.us Whole thread Raw |
Responses |
Re: SQL standard changed behavior of ON UPDATE SET NULL/SET DEFAULT?
|
List | pgsql-hackers |
While looking at Vik Reykja's pending patch to improve the FK triggers by skipping processing when a NULL column didn't change, I started to wonder whether that really had no user-visible semantic effect. In particular, in ON UPDATE SET NULL/SET DEFAULT cases, it seemed like this could change the set of referencing columns that get set to NULL or to their defaults. So the next question was which behavior actually conforms to the SQL standard, and the answer to that is ... disturbing. The code in ri_triggers.c was written against SQL92's definition of ON UPDATE SET NULL/SET DEFAULT, which is (ignoring the MATCH PARTIAL case, which we don't implement): 6) If an <update rule> is specified and a non-null value of a ref- erenced column in the referenced tableis updated to a value that is distinct from the current value of that column, then a) If <match type> is not specified or if FULL is specified, then ii) If the <update rule> specifies SET NULL, then Case: 1) If <match type> is not specified, then in all matching rows the referencing column thatcorresponds with the referenced column is set to the null value. 2) If <match type> specifies FULL, then in all matching rows each referencing column isset to the null value. iii) If the <update rule> specifies SET DEFAULT, then in all matching rows the referencing columnthat corresponds with the referenced column is set to the default value specified inthe General Rules of Subclause 11.5, "<default clause>". Note that only in the MATCH FULL + SET NULL case does it say to set *all* the referencing columns in each matching row. Otherwise, you are only supposed to change columns that correspond to referenced columns that were changed. It's notable that SET NULL and SET DEFAULT have different behaviors here. On the other hand, in SQL:2008 I find (some boilerplate text omitted): 10) If a non-null value of a referenced column RC in thereferenced table is updated to a value that is distinct from thecurrentvalue of RC, then, for every member F of the subtablefamily of the referencing table: Case: a) If M specifies SIMPLE or FULL, then Case: ii) If UR specifies SET NULL, then Case: 1) If M specifies SIMPLE, then each matching row MR in F is paired with the candidate replacement rowNMR, formed by copying MR and setting each referencing column in the copy to the null value. MR is identifiedfor replacement by NMR in F. 2) If M specifies FULL, then each matching row MR in F is paired with the candidate replacement rowNMR, formed by copying MR and setting each referencing column in the copy to the null value. MR is identifiedfor replacement by NMR in F. iii) If UR specifies SET DEFAULT, then each matching row MR in F is paired with the candidate replacement row NMR,formed by copying MR and setting each referencing column in the copy to the default value specified in the GeneralRules of Subclause 11.5, "<default clause>". MR is identified for replacement by NMR in F. So far as I can see, this says to set *all* referencing columns to nulls or their defaults, in all four cases, whether the corresponding referenced column was one that changed or not. This is very clearly different from what SQL92 says. It's also rather curious that they distinguish two "cases" for SET NULL when the texts are exactly alike. It looks to me like this change occurred in SQL:2003, although SQL:1999's version of the text is such badly written pseudo-mathematical gobbledygook that it's a bit hard to tell which behavior they meant. However, neither of those specs list any change in referential constraint behavior as being an acknowledged incompatibility with the prior standard. Have the SQL committee simply failed to notice that in whacking this text around they changed the meaning? Which behavior is actually implemented by other RDBMSes? regards, tom lane
pgsql-hackers by date: