Обсуждение: SQL standard changed behavior of ON UPDATE SET NULL/SET DEFAULT?

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

I wrote:
> 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?

If anyone is up for actually trying this, here is a script to test the
behavior in question:

create table pp (f1 int, f2 int, primary key (f1,f2));
create table cmssn (f1 int, f2 int,   foreign key (f1,f2) references pp(f1,f2) on update set null);
create table cmfsn (f1 int, f2 int,   foreign key (f1,f2) references pp(f1,f2) match full on update set null);
create table cmssd (f1 int default 0, f2 int default 0,   foreign key (f1,f2) references pp(f1,f2) on update set
default);
create table cmfsd (f1 int default 0, f2 int default 0,   foreign key (f1,f2) references pp(f1,f2) match full on update
setdefault);
 

insert into pp values (11, 22);
insert into pp values (11, 0);
insert into pp values (0, 0);

insert into cmssn values (11, 22);
insert into cmfsn values (11, 22);
insert into cmssd values (11, 22);
insert into cmfsd values (11, 22);

update pp set f2 = f2 + 1 where f2 > 0;

select * from cmssn;
select * from cmfsn;
select * from cmssd;
select * from cmfsd;

In Postgres this produces
f1 | f2 
----+----11 |   
(1 row)
f1 | f2 
----+----   |   
(1 row)
f1 | f2 
----+----11 |  0
(1 row)
f1 | f2 
----+---- 0 |  0
(1 row)

which shows that we are self-consistent but not actually compliant with
either old or new wordings of the spec :-(

The only other SQL DB I have handy is mysql 5.5.24, which shows up
pretty unimpressively: it gives a syntax error on the cmssd definition,
which would be all right because the manual says the innodb storage
engine doesn't support SET DEFAULT, except it *doesn't* give a syntax
error for creating cmfsd.  Then, the update fails claiming that cmfsn's
FK constraint is violated, so they evidently don't implement that case
correctly.  After removing cmfsn, the update fails again claiming that
cmfsd's FK constraint is violated, so yeah they are telling the truth
when they say SET DEFAULT doesn't work.  The upshot is that only the
MATCH SIMPLE SET NULL case works at all in current mysql, and that
produces the result

mysql> select * from cmssn;
+------+------+
| f1   | f2   |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

so they are nulling all the referencing columns in this case, which
matches the more recent specs but is clearly contrary to SQL92.

Anybody have DB2, or something else that might be thought to be pretty
close to spec-compliant?
        regards, tom lane

On 16 June 2012 21:18, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Anybody have DB2, or something else that might be thought to be pretty
> close to spec-compliant?
>

I have an Oracle DB, but they're not exactly known for spec
compliance. In fact they dodge this entire issue by not supporting ON
UPDATE actions at all :-)

Regards,
Dean

2012/6/16 Tom Lane <tgl@sss.pgh.pa.us>:
> I wrote:
>> 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?
>
> If anyone is up for actually trying this, here is a script to test the
> behavior in question:
>
> create table pp (f1 int, f2 int, primary key (f1,f2));
> create table cmssn (f1 int, f2 int,
>    foreign key (f1,f2) references pp(f1,f2) on update set null);
> create table cmfsn (f1 int, f2 int,
>    foreign key (f1,f2) references pp(f1,f2) match full on update set null);
> create table cmssd (f1 int default 0, f2 int default 0,
>    foreign key (f1,f2) references pp(f1,f2) on update set default);
> create table cmfsd (f1 int default 0, f2 int default 0,
>    foreign key (f1,f2) references pp(f1,f2) match full on update set default);
>
> insert into pp values (11, 22);
> insert into pp values (11, 0);
> insert into pp values (0, 0);
>
> insert into cmssn values (11, 22);
> insert into cmfsn values (11, 22);
> insert into cmssd values (11, 22);
> insert into cmfsd values (11, 22);
>
> update pp set f2 = f2 + 1 where f2 > 0;
>
> select * from cmssn;
> select * from cmfsn;
> select * from cmssd;
> select * from cmfsd;
>
> In Postgres this produces
>
>  f1 | f2
> ----+----
>  11 |
> (1 row)
>
>  f1 | f2
> ----+----
>    |
> (1 row)
>
>  f1 | f2
> ----+----
>  11 |  0
> (1 row)
>
>  f1 | f2
> ----+----
>  0 |  0
> (1 row)
>
> which shows that we are self-consistent but not actually compliant with
> either old or new wordings of the spec :-(
>
> The only other SQL DB I have handy is mysql 5.5.24, which shows up
> pretty unimpressively: it gives a syntax error on the cmssd definition,
> which would be all right because the manual says the innodb storage
> engine doesn't support SET DEFAULT, except it *doesn't* give a syntax
> error for creating cmfsd.  Then, the update fails claiming that cmfsn's
> FK constraint is violated, so they evidently don't implement that case
> correctly.  After removing cmfsn, the update fails again claiming that
> cmfsd's FK constraint is violated, so yeah they are telling the truth
> when they say SET DEFAULT doesn't work.  The upshot is that only the
> MATCH SIMPLE SET NULL case works at all in current mysql, and that
> produces the result
>
> mysql> select * from cmssn;
> +------+------+
> | f1   | f2   |
> +------+------+
> | NULL | NULL |
> +------+------+
> 1 row in set (0.00 sec)
>
> so they are nulling all the referencing columns in this case, which
> matches the more recent specs but is clearly contrary to SQL92.
>
> Anybody have DB2, or something else that might be thought to be pretty
> close to spec-compliant?


I tryed in a MS SQL Server 2012 via SQLFiddle [1]. I could only create
'cmssn' and 'cmssd' tables because as I can see in [2] MS SQL Server
2012 doesn't supports MATCH syntax.

The result was:

select * from cmssn;
F1       |  F2
(null)   |  (null)

select * from cmssd;
F1 |    F2
0   |    0

The test is in [3], and there you can try other RDBMS, just create the
schema on the left panel and testing selects on the right.

[1] http://sqlfiddle.com
[2] http://msdn.microsoft.com/en-us/library/ms174979.aspx
[3] http://sqlfiddle.com/#!6/ac7db/1

Regards.
--
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br

I wrote:
> Anybody have DB2, or something else that might be thought to be pretty
> close to spec-compliant?

Remarkably enough, the DB2 10.1 manuals at www.ibm.com say that it
doesn't support ON UPDATE SET NULL or ON UPDATE SET DEFAULT.  I'm
disappointed in them :-(.  But anyway it seems that we'll not get
that much guidance from looking at other SQL implementations, and
what precedents there are suggest that people are using the set-all-
the-columns interpretation.

After reflection it seems clear to me that set-all-the-columns is
in fact an improvement for the SET DEFAULT case, regardless of match
style.  If we set only some of them, you get a mishmash of old and
new column values which is rather unlikely to match any row of the
referenced table.  If we always set all of them, then (at least for
constant default values) only one "fallback" entry is required in
the referenced table.  This can be seen in my example script upthread,
where I had to make a bogus referenceable entry "11, 0" to prevent
an RI failure on the MATCH SIMPLE update.  Having just the one fallback
entry "0, 0" definitely seems saner from an application standpoint.

I'm less sold on set-all-the-columns for the MATCH SIMPLE SET NULL
case.  In this match style, setting any referencing column to null
is sufficient to prevent an RI failure, and it could be argued that
zapping all of them discards data that might be useful.  But it does
have the advantage of predictability.

From an implementation standpoint, set-all-the-columns is definitely
easier to deal with: we won't need ri_OneKeyEqual at all any more,
and RI_FKey_setnull_upd no longer has the problem of having to deal
with variant plans depending on which columns it needs to zap.
So I'm attracted to it on that basis, but I don't want to let
implementation concerns drive the decision.

On balance I think we ought to switch to set-all-the-columns, though
only in 9.3+ --- a back-patched behavioral change doesn't seem like a
good idea.

Any objections, or anyone want to do more research before we decide?
        regards, tom lane

I wrote:
> On balance I think we ought to switch to set-all-the-columns, though
> only in 9.3+ --- a back-patched behavioral change doesn't seem like a
> good idea.

And here is a draft patch for that.  I was interested to find that the
documentation already claims that all columns are set in the relevant
cases (so the docs changes here are just wordsmithing and
clarification).

            regards, tom lane

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index ea840fb8468f7d1d65b572d9880d74dd0178e143..013dc7c4dacdc56bbdfd001f6a0b615ecbead84b 100644
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
*************** CREATE TABLE t1 (
*** 735,741 ****
     </para>

     <para>
!     A table can contain more than one foreign key constraint.  This is
      used to implement many-to-many relationships between tables.  Say
      you have tables about products and orders, but now you want to
      allow one order to contain possibly many products (which the
--- 735,741 ----
     </para>

     <para>
!     A table can have more than one foreign key constraint.  This is
      used to implement many-to-many relationships between tables.  Say
      you have tables about products and orders, but now you want to
      allow one order to contain possibly many products (which the
*************** CREATE TABLE order_items (
*** 827,837 ****
      row(s) referencing it should be automatically deleted as well.
      There are two other options:
      <literal>SET NULL</literal> and <literal>SET DEFAULT</literal>.
!     These cause the referencing columns to be set to nulls or default
      values, respectively, when the referenced row is deleted.
      Note that these do not excuse you from observing any constraints.
      For example, if an action specifies <literal>SET DEFAULT</literal>
!     but the default value would not satisfy the foreign key, the
      operation will fail.
     </para>

--- 827,838 ----
      row(s) referencing it should be automatically deleted as well.
      There are two other options:
      <literal>SET NULL</literal> and <literal>SET DEFAULT</literal>.
!     These cause the referencing column(s) in the referencing row(s)
!     to be set to nulls or their default
      values, respectively, when the referenced row is deleted.
      Note that these do not excuse you from observing any constraints.
      For example, if an action specifies <literal>SET DEFAULT</literal>
!     but the default value would not satisfy the foreign key constraint, the
      operation will fail.
     </para>

*************** CREATE TABLE order_items (
*** 839,851 ****
      Analogous to <literal>ON DELETE</literal> there is also
      <literal>ON UPDATE</literal> which is invoked when a referenced
      column is changed (updated).  The possible actions are the same.
     </para>

     <para>
      Since a <command>DELETE</command> of a row from the referenced table
      or an <command>UPDATE</command> of a referenced column will require
      a scan of the referencing table for rows matching the old value, it
!     is often a good idea to index the referencing columns.  Because this
      is not always needed, and there are many choices available on how
      to index, declaration of a foreign key constraint does not
      automatically create an index on the referencing columns.
--- 840,869 ----
      Analogous to <literal>ON DELETE</literal> there is also
      <literal>ON UPDATE</literal> which is invoked when a referenced
      column is changed (updated).  The possible actions are the same.
+     In this case, <literal>CASCADE</> means that the updated values of the
+     referenced column(s) should be copied into the referencing row(s).
     </para>

     <para>
+     Normally, a referencing row need not satisfy the foreign key constraint
+     if any of its referencing columns are null.  If <literal>MATCH FULL</>
+     is added to the foreign key declaration, a referencing row escapes
+     satisfying the constraint only if all its referencing columns are null
+     (so a mix of null and non-null values is guaranteed to fail a
+     <literal>MATCH FULL</> constraint).  If you don't want referencing rows
+     to be able to avoid satisfying the foreign key constraint, declare the
+     referencing column(s) as <literal>NOT NULL</>.
+    </para>
+
+    <para>
+     A foreign key must reference columns that either are a primary key or
+     form a unique constraint.  This means that the referenced columns always
+     have an index (the one underlying the primary key or unique constraint);
+     so checks on whether a referencing row has a match will be efficient.
      Since a <command>DELETE</command> of a row from the referenced table
      or an <command>UPDATE</command> of a referenced column will require
      a scan of the referencing table for rows matching the old value, it
!     is often a good idea to index the referencing columns too.  Because this
      is not always needed, and there are many choices available on how
      to index, declaration of a foreign key constraint does not
      automatically create an index on the referencing columns.
*************** CREATE TABLE order_items (
*** 853,867 ****

     <para>
      More information about updating and deleting data is in <xref
!     linkend="dml">.
!    </para>
!
!    <para>
!     Finally, we should mention that a foreign key must reference
!     columns that either are a primary key or form a unique constraint.
!     If the foreign key references a unique constraint, there are some
!     additional possibilities regarding how null values are matched.
!     These are explained in the reference documentation for
      <xref linkend="sql-createtable">.
     </para>
    </sect2>
--- 871,878 ----

     <para>
      More information about updating and deleting data is in <xref
!     linkend="dml">.  Also see the description of foreign key constraint
!     syntax in the reference documentation for
      <xref linkend="sql-createtable">.
     </para>
    </sect2>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 1aea7885844cb4671beb4ccd65363dbe4d580ef7..1d7d31287f478a60f611e4edefb882d6b648e1af 100644
*** a/doc/src/sgml/ref/create_table.sgml
--- b/doc/src/sgml/ref/create_table.sgml
*************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY
*** 585,592 ****
        These clauses specify a foreign key constraint, which requires
        that a group of one or more columns of the new table must only
        contain values that match values in the referenced
!       column(s) of some row of the referenced table.  If <replaceable
!       class="parameter">refcolumn</replaceable> is omitted, the
        primary key of the <replaceable class="parameter">reftable</replaceable>
        is used.  The referenced columns must be the columns of a non-deferrable
        unique or primary key constraint in the referenced table.  Note that
--- 585,592 ----
        These clauses specify a foreign key constraint, which requires
        that a group of one or more columns of the new table must only
        contain values that match values in the referenced
!       column(s) of some row of the referenced table.  If the <replaceable
!       class="parameter">refcolumn</replaceable> list is omitted, the
        primary key of the <replaceable class="parameter">reftable</replaceable>
        is used.  The referenced columns must be the columns of a non-deferrable
        unique or primary key constraint in the referenced table.  Note that
*************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY
*** 599,610 ****
        values of the referenced table and referenced columns using the
        given match type.  There are three match types: <literal>MATCH
        FULL</>, <literal>MATCH PARTIAL</>, and <literal>MATCH
!       SIMPLE</literal>, which is also the default.  <literal>MATCH
        FULL</> will not allow one column of a multicolumn foreign key
!       to be null unless all foreign key columns are null.
!       <literal>MATCH SIMPLE</literal> allows some foreign key columns
!       to be null while other parts of the foreign key are not
!       null. <literal>MATCH PARTIAL</> is not yet implemented.
       </para>

       <para>
--- 599,614 ----
        values of the referenced table and referenced columns using the
        given match type.  There are three match types: <literal>MATCH
        FULL</>, <literal>MATCH PARTIAL</>, and <literal>MATCH
!       SIMPLE</literal> (which is the default).  <literal>MATCH
        FULL</> will not allow one column of a multicolumn foreign key
!       to be null unless all foreign key columns are null; if they are all
!       null, the row is not required to have a match in the referenced table.
!       <literal>MATCH SIMPLE</literal> allows any of the foreign key columns
!       to be null; if any of them are null, the row is not required to have a
!       match in the referenced table.
!       <literal>MATCH PARTIAL</> is not yet implemented.
!       (Of course, <literal>NOT NULL</> constraints can be applied to the
!       referencing column(s) to prevent these cases from arising.)
       </para>

       <para>
*************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY
*** 652,659 ****
          <listitem>
           <para>
            Delete any rows referencing the deleted row, or update the
!           value of the referencing column to the new value of the
!           referenced column, respectively.
           </para>
          </listitem>
         </varlistentry>
--- 656,663 ----
          <listitem>
           <para>
            Delete any rows referencing the deleted row, or update the
!           values of the referencing column(s) to the new values of the
!           referenced columns, respectively.
           </para>
          </listitem>
         </varlistentry>
*************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY
*** 672,677 ****
--- 676,683 ----
          <listitem>
           <para>
            Set the referencing column(s) to their default values.
+           (There must be a row in the referenced table matching the default
+           values, if they are not null, or the operation will fail.)
           </para>
          </listitem>
         </varlistentry>
*************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY
*** 680,687 ****

       <para>
        If the referenced column(s) are changed frequently, it might be wise to
!       add an index to the foreign key column so that referential actions
!       associated with the foreign key column can be performed more
        efficiently.
       </para>
      </listitem>
--- 686,693 ----

       <para>
        If the referenced column(s) are changed frequently, it might be wise to
!       add an index to the referencing column(s) so that referential actions
!       associated with the foreign key constraint can be performed more
        efficiently.
       </para>
      </listitem>
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index ac252b8de272e5cbf1509a8390af88f209e59f6a..f1fe5fa7f68e27e64c73cd25803619dbe21b76cb 100644
*** a/src/backend/utils/adt/ri_triggers.c
--- b/src/backend/utils/adt/ri_triggers.c
*************** static void ri_BuildQueryKeyPkCheck(RI_Q
*** 207,217 ****
                          int32 constr_queryno);
  static bool ri_KeysEqual(Relation rel, HeapTuple oldtup, HeapTuple newtup,
               const RI_ConstraintInfo *riinfo, bool rel_is_pk);
- static bool ri_AllKeysUnequal(Relation rel, HeapTuple oldtup, HeapTuple newtup,
-                   const RI_ConstraintInfo *riinfo, bool rel_is_pk);
- static bool ri_OneKeyEqual(Relation rel, int column,
-                HeapTuple oldtup, HeapTuple newtup,
-                const RI_ConstraintInfo *riinfo, bool rel_is_pk);
  static bool ri_AttributesEqual(Oid eq_opr, Oid typeid,
                     Datum oldvalue, Datum newvalue);
  static bool ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
--- 207,212 ----
*************** RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
*** 1950,1956 ****
      RI_QueryKey qkey;
      SPIPlanPtr    qplan;
      int            i;
-     bool        use_cached_query;

      /*
       * Check that this is a valid trigger call on the right time and event.
--- 1945,1950 ----
*************** RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
*** 1985,1991 ****
              /* ----------
               * SQL3 11.9 <referential constraint definition>
               *    General rules 7) a) ii) 2):
!              *        MATCH FULL
               *            ... ON UPDATE SET NULL
               * ----------
               */
--- 1979,1985 ----
              /* ----------
               * SQL3 11.9 <referential constraint definition>
               *    General rules 7) a) ii) 2):
!              *        MATCH SIMPLE/FULL
               *            ... ON UPDATE SET NULL
               * ----------
               */
*************** RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
*** 2027,2054 ****
                  elog(ERROR, "SPI_connect failed");

              /*
-              * "MATCH SIMPLE" only changes columns corresponding to the
-              * referenced columns that have changed in pk_rel.    This means the
-              * "SET attrn=NULL [, attrn=NULL]" string will be change as well.
-              * In this case, we need to build a temporary plan rather than use
-              * our cached plan, unless the update happens to change all
-              * columns in the key.    Fortunately, for the most common case of a
-              * single-column foreign key, this will be true.
-              *
-              * In case you're wondering, the inequality check works because we
-              * know that the old key value has no NULLs (see above).
-              */
-
-             use_cached_query = (riinfo.confmatchtype == FKCONSTR_MATCH_FULL) ||
-                 ri_AllKeysUnequal(pk_rel, old_row, new_row,
-                                   &riinfo, true);
-
-             /*
               * Fetch or prepare a saved plan for the set null update operation
-              * if possible, or build a temporary plan if not.
               */
!             if (!use_cached_query ||
!                 (qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
              {
                  StringInfoData querybuf;
                  StringInfoData qualbuf;
--- 2021,2029 ----
                  elog(ERROR, "SPI_connect failed");

              /*
               * Fetch or prepare a saved plan for the set null update operation
               */
!             if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
              {
                  StringInfoData querybuf;
                  StringInfoData qualbuf;
*************** RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
*** 2080,2116 ****

                      quoteOneName(attname,
                                   RIAttName(fk_rel, riinfo.fk_attnums[i]));
!
!                     /*
!                      * MATCH SIMPLE - only change columns corresponding
!                      * to changed columns in pk_rel's key
!                      */
!                     if (riinfo.confmatchtype == FKCONSTR_MATCH_FULL ||
!                         !ri_OneKeyEqual(pk_rel, i, old_row, new_row,
!                                         &riinfo, true))
!                     {
!                         appendStringInfo(&querybuf,
!                                          "%s %s = NULL",
!                                          querysep, attname);
!                         querysep = ",";
!                     }
                      sprintf(paramname, "$%d", i + 1);
                      ri_GenerateQual(&qualbuf, qualsep,
                                      paramname, pk_type,
                                      riinfo.pf_eq_oprs[i],
                                      attname, fk_type);
                      qualsep = "AND";
                      queryoids[i] = pk_type;
                  }
                  appendStringInfoString(&querybuf, qualbuf.data);

!                 /*
!                  * Prepare the plan.  Save it only if we're building the
!                  * "standard" plan.
!                  */
                  qplan = ri_PlanCheck(querybuf.data, riinfo.nkeys, queryoids,
!                                      &qkey, fk_rel, pk_rel,
!                                      use_cached_query);
              }

              /*
--- 2055,2077 ----

                      quoteOneName(attname,
                                   RIAttName(fk_rel, riinfo.fk_attnums[i]));
!                     appendStringInfo(&querybuf,
!                                      "%s %s = NULL",
!                                      querysep, attname);
                      sprintf(paramname, "$%d", i + 1);
                      ri_GenerateQual(&qualbuf, qualsep,
                                      paramname, pk_type,
                                      riinfo.pf_eq_oprs[i],
                                      attname, fk_type);
+                     querysep = ",";
                      qualsep = "AND";
                      queryoids[i] = pk_type;
                  }
                  appendStringInfoString(&querybuf, qualbuf.data);

!                 /* Prepare and save the plan */
                  qplan = ri_PlanCheck(querybuf.data, riinfo.nkeys, queryoids,
!                                      &qkey, fk_rel, pk_rel, true);
              }

              /*
*************** RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
*** 2463,2487 ****

                      quoteOneName(attname,
                                   RIAttName(fk_rel, riinfo.fk_attnums[i]));
!
!                     /*
!                      * MATCH SIMPLE - only change columns corresponding
!                      * to changed columns in pk_rel's key
!                      */
!                     if (riinfo.confmatchtype == FKCONSTR_MATCH_FULL ||
!                         !ri_OneKeyEqual(pk_rel, i, old_row, new_row,
!                                         &riinfo, true))
!                     {
!                         appendStringInfo(&querybuf,
!                                          "%s %s = DEFAULT",
!                                          querysep, attname);
!                         querysep = ",";
!                     }
                      sprintf(paramname, "$%d", i + 1);
                      ri_GenerateQual(&qualbuf, qualsep,
                                      paramname, pk_type,
                                      riinfo.pf_eq_oprs[i],
                                      attname, fk_type);
                      qualsep = "AND";
                      queryoids[i] = pk_type;
                  }
--- 2424,2438 ----

                      quoteOneName(attname,
                                   RIAttName(fk_rel, riinfo.fk_attnums[i]));
!                     appendStringInfo(&querybuf,
!                                      "%s %s = DEFAULT",
!                                      querysep, attname);
                      sprintf(paramname, "$%d", i + 1);
                      ri_GenerateQual(&qualbuf, qualsep,
                                      paramname, pk_type,
                                      riinfo.pf_eq_oprs[i],
                                      attname, fk_type);
+                     querysep = ",";
                      qualsep = "AND";
                      queryoids[i] = pk_type;
                  }
*************** ri_KeysEqual(Relation rel, HeapTuple old
*** 3858,3977 ****


  /* ----------
-  * ri_AllKeysUnequal -
-  *
-  *    Check if all key values in OLD and NEW are not equal.
-  * ----------
-  */
- static bool
- ri_AllKeysUnequal(Relation rel, HeapTuple oldtup, HeapTuple newtup,
-                   const RI_ConstraintInfo *riinfo, bool rel_is_pk)
- {
-     TupleDesc    tupdesc = RelationGetDescr(rel);
-     const int16 *attnums;
-     const Oid  *eq_oprs;
-     int            i;
-
-     if (rel_is_pk)
-     {
-         attnums = riinfo->pk_attnums;
-         eq_oprs = riinfo->pp_eq_oprs;
-     }
-     else
-     {
-         attnums = riinfo->fk_attnums;
-         eq_oprs = riinfo->ff_eq_oprs;
-     }
-
-     for (i = 0; i < riinfo->nkeys; i++)
-     {
-         Datum        oldvalue;
-         Datum        newvalue;
-         bool        isnull;
-
-         /*
-          * Get one attribute's oldvalue. If it is NULL - they're not equal.
-          */
-         oldvalue = SPI_getbinval(oldtup, tupdesc, attnums[i], &isnull);
-         if (isnull)
-             continue;
-
-         /*
-          * Get one attribute's newvalue. If it is NULL - they're not equal.
-          */
-         newvalue = SPI_getbinval(newtup, tupdesc, attnums[i], &isnull);
-         if (isnull)
-             continue;
-
-         /*
-          * Compare them with the appropriate equality operator.
-          */
-         if (ri_AttributesEqual(eq_oprs[i], RIAttType(rel, attnums[i]),
-                                oldvalue, newvalue))
-             return false;        /* found two equal items */
-     }
-
-     return true;
- }
-
-
- /* ----------
-  * ri_OneKeyEqual -
-  *
-  *    Check if one key value in OLD and NEW is equal.  Note column is indexed
-  *    from zero.
-  *
-  *    ri_KeysEqual could call this but would run a bit slower.  For
-  *    now, let's duplicate the code.
-  * ----------
-  */
- static bool
- ri_OneKeyEqual(Relation rel, int column, HeapTuple oldtup, HeapTuple newtup,
-                const RI_ConstraintInfo *riinfo, bool rel_is_pk)
- {
-     TupleDesc    tupdesc = RelationGetDescr(rel);
-     const int16 *attnums;
-     const Oid  *eq_oprs;
-     Datum        oldvalue;
-     Datum        newvalue;
-     bool        isnull;
-
-     if (rel_is_pk)
-     {
-         attnums = riinfo->pk_attnums;
-         eq_oprs = riinfo->pp_eq_oprs;
-     }
-     else
-     {
-         attnums = riinfo->fk_attnums;
-         eq_oprs = riinfo->ff_eq_oprs;
-     }
-
-     /*
-      * Get one attribute's oldvalue. If it is NULL - they're not equal.
-      */
-     oldvalue = SPI_getbinval(oldtup, tupdesc, attnums[column], &isnull);
-     if (isnull)
-         return false;
-
-     /*
-      * Get one attribute's newvalue. If it is NULL - they're not equal.
-      */
-     newvalue = SPI_getbinval(newtup, tupdesc, attnums[column], &isnull);
-     if (isnull)
-         return false;
-
-     /*
-      * Compare them with the appropriate equality operator.
-      */
-     if (!ri_AttributesEqual(eq_oprs[column], RIAttType(rel, attnums[column]),
-                             oldvalue, newvalue))
-         return false;
-
-     return true;
- }
-
- /* ----------
   * ri_AttributesEqual -
   *
   *    Call the appropriate equality comparison operator for two values.
--- 3809,3814 ----
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index f86b39f396ba090443283883bccfc4a098558887..305dfe20d47eaaa1343d338292ddcceb4e209ad1 100644
*** a/src/test/regress/expected/foreign_key.out
--- b/src/test/regress/expected/foreign_key.out
*************** SELECT * from FKTABLE;
*** 557,563 ****
        2 |        |      3 |      3
          |      2 |      7 |      4
          |      3 |      4 |      5
!       1 |        |      3 |      1
  (6 rows)

  -- Try to delete something that should set default
--- 557,563 ----
        2 |        |      3 |      3
          |      2 |      7 |      4
          |      3 |      4 |      5
!         |        |        |      1
  (6 rows)

  -- Try to delete something that should set default
*************** SELECT * from FKTABLE;
*** 578,584 ****
        2 |        |      3 |      3
          |      2 |      7 |      4
          |      3 |      4 |      5
!       1 |        |      3 |      1
        0 |        |        |      1
  (6 rows)

--- 578,584 ----
        2 |        |      3 |      3
          |      2 |      7 |      4
          |      3 |      4 |      5
!         |        |        |      1
        0 |        |        |      1
  (6 rows)

*************** SELECT * from FKTABLE;
*** 599,605 ****
        2 |        |      3 |      3
          |      2 |      7 |      4
          |      3 |      4 |      5
!       1 |        |      3 |      1
        0 |        |        |      1
  (6 rows)

--- 599,605 ----
        2 |        |      3 |      3
          |      2 |      7 |      4
          |      3 |      4 |      5
!         |        |        |      1
        0 |        |        |      1
  (6 rows)

*************** DROP TABLE PKTABLE;
*** 608,614 ****
  -- set default update / set null delete
  CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
! CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int, ftest4 int,  CONSTRAINT constrname3
              FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
              ON DELETE SET NULL ON UPDATE SET DEFAULT);
  -- Insert Primary Key values
--- 608,614 ----
  -- set default update / set null delete
  CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
! CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int DEFAULT -2, ftest4 int, CONSTRAINT
constrname3
              FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
              ON DELETE SET NULL ON UPDATE SET DEFAULT);
  -- Insert Primary Key values
*************** SELECT * from FKTABLE;
*** 645,653 ****
  -- Try to update something that will fail
  UPDATE PKTABLE set ptest2=5 where ptest2=2;
  ERROR:  insert or update on table "fktable" violates foreign key constraint "constrname3"
! DETAIL:  Key (ftest1, ftest2, ftest3)=(1, -1, 3) is not present in table "pktable".
  -- Try to update something that will set default
! UPDATE PKTABLE set ptest1=0, ptest2=5, ptest3=10 where ptest2=2;
  UPDATE PKTABLE set ptest2=10 where ptest2=4;
  -- Try to update something that should not set default
  UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
--- 645,653 ----
  -- Try to update something that will fail
  UPDATE PKTABLE set ptest2=5 where ptest2=2;
  ERROR:  insert or update on table "fktable" violates foreign key constraint "constrname3"
! DETAIL:  Key (ftest1, ftest2, ftest3)=(0, -1, -2) is not present in table "pktable".
  -- Try to update something that will set default
! UPDATE PKTABLE set ptest1=0, ptest2=-1, ptest3=-2 where ptest2=2;
  UPDATE PKTABLE set ptest2=10 where ptest2=4;
  -- Try to update something that should not set default
  UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1;
*************** SELECT * from PKTABLE;
*** 657,663 ****
  --------+--------+--------+--------
        2 |      3 |      4 | test3
        2 |     -1 |      5 | test5
!       0 |      5 |     10 | test1
        2 |     10 |      5 | test4
        1 |      2 |      3 | test2
  (5 rows)
--- 657,663 ----
  --------+--------+--------+--------
        2 |      3 |      4 | test3
        2 |     -1 |      5 | test5
!       0 |     -1 |     -2 | test1
        2 |     10 |      5 | test4
        1 |      2 |      3 | test2
  (5 rows)
*************** SELECT * from FKTABLE;
*** 670,677 ****
        2 |        |      3 |      3
          |      2 |      7 |      4
          |      3 |      4 |      5
!       0 |     -1 |        |      1
!       2 |     -1 |      5 |      1
  (7 rows)

  -- Try to delete something that should set null
--- 670,677 ----
        2 |        |      3 |      3
          |      2 |      7 |      4
          |      3 |      4 |      5
!       0 |     -1 |     -2 |      1
!       0 |     -1 |     -2 |      1
  (7 rows)

  -- Try to delete something that should set null
*************** SELECT * from PKTABLE;
*** 681,687 ****
   ptest1 | ptest2 | ptest3 | ptest4
  --------+--------+--------+--------
        2 |     -1 |      5 | test5
!       0 |      5 |     10 | test1
        2 |     10 |      5 | test4
        1 |      2 |      3 | test2
  (4 rows)
--- 681,687 ----
   ptest1 | ptest2 | ptest3 | ptest4
  --------+--------+--------+--------
        2 |     -1 |      5 | test5
!       0 |     -1 |     -2 | test1
        2 |     10 |      5 | test4
        1 |      2 |      3 | test2
  (4 rows)
*************** SELECT * from FKTABLE;
*** 693,710 ****
        2 |        |      3 |      3
          |      2 |      7 |      4
          |      3 |      4 |      5
!       0 |     -1 |        |      1
!       2 |     -1 |      5 |      1
          |        |        |      1
  (7 rows)

  -- Try to delete something that should not set null
! DELETE FROM PKTABLE where ptest2=5;
  -- Show PKTABLE and FKTABLE
  SELECT * from PKTABLE;
   ptest1 | ptest2 | ptest3 | ptest4
  --------+--------+--------+--------
!       2 |     -1 |      5 | test5
        2 |     10 |      5 | test4
        1 |      2 |      3 | test2
  (3 rows)
--- 693,710 ----
        2 |        |      3 |      3
          |      2 |      7 |      4
          |      3 |      4 |      5
!       0 |     -1 |     -2 |      1
!       0 |     -1 |     -2 |      1
          |        |        |      1
  (7 rows)

  -- Try to delete something that should not set null
! DELETE FROM PKTABLE where ptest2=-1 and ptest3=5;
  -- Show PKTABLE and FKTABLE
  SELECT * from PKTABLE;
   ptest1 | ptest2 | ptest3 | ptest4
  --------+--------+--------+--------
!       0 |     -1 |     -2 | test1
        2 |     10 |      5 | test4
        1 |      2 |      3 | test2
  (3 rows)
*************** SELECT * from FKTABLE;
*** 716,723 ****
        2 |        |      3 |      3
          |      2 |      7 |      4
          |      3 |      4 |      5
!       0 |     -1 |        |      1
!       2 |     -1 |      5 |      1
          |        |        |      1
  (7 rows)

--- 716,723 ----
        2 |        |      3 |      3
          |      2 |      7 |      4
          |      3 |      4 |      5
!       0 |     -1 |     -2 |      1
!       0 |     -1 |     -2 |      1
          |        |        |      1
  (7 rows)

diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 2c4c0c5606a45ee9889a4e8cb0ef462c6f8469f4..874d9f7a331b4d30fe70c4262aa538aa17526807 100644
*** a/src/test/regress/sql/foreign_key.sql
--- b/src/test/regress/sql/foreign_key.sql
*************** DROP TABLE PKTABLE;
*** 367,373 ****

  -- set default update / set null delete
  CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
! CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int, ftest4 int,  CONSTRAINT constrname3
              FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
              ON DELETE SET NULL ON UPDATE SET DEFAULT);

--- 367,373 ----

  -- set default update / set null delete
  CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) );
! CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int DEFAULT -2, ftest4 int, CONSTRAINT
constrname3
              FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE
              ON DELETE SET NULL ON UPDATE SET DEFAULT);

*************** SELECT * from FKTABLE;
*** 397,403 ****
  UPDATE PKTABLE set ptest2=5 where ptest2=2;

  -- Try to update something that will set default
! UPDATE PKTABLE set ptest1=0, ptest2=5, ptest3=10 where ptest2=2;
  UPDATE PKTABLE set ptest2=10 where ptest2=4;

  -- Try to update something that should not set default
--- 397,403 ----
  UPDATE PKTABLE set ptest2=5 where ptest2=2;

  -- Try to update something that will set default
! UPDATE PKTABLE set ptest1=0, ptest2=-1, ptest3=-2 where ptest2=2;
  UPDATE PKTABLE set ptest2=10 where ptest2=4;

  -- Try to update something that should not set default
*************** SELECT * from PKTABLE;
*** 415,421 ****
  SELECT * from FKTABLE;

  -- Try to delete something that should not set null
! DELETE FROM PKTABLE where ptest2=5;

  -- Show PKTABLE and FKTABLE
  SELECT * from PKTABLE;
--- 415,421 ----
  SELECT * from FKTABLE;

  -- Try to delete something that should not set null
! DELETE FROM PKTABLE where ptest2=-1 and ptest3=5;

  -- Show PKTABLE and FKTABLE
  SELECT * from PKTABLE;