Re: Heads up: 7.3.3 this Wednesday

scott.marlowe wrote:
> On Tue, 20 May 2003, Tom Lane wrote:
>
>
>>"scott.marlowe" <scott.marlowe@ihs.com> writes:
>>
>>>I'd be glad to test it, but we don't have any issues with fk deadlocks
>>>since our load is 99% read, 1% write, and most of the tables with fks on
>>>them only have a handful of writers, so any testing I would do would
>>>probably just be the "we used it in production and it didn't die" kind of
>>>testing.
>>
>>That's what I'm looking for mostly: that it does not have any adverse
>>side-effects.
>
>
> So where's that patch again?  The search function of the mail archives is
> broken, so I can't seem to find it.
>

Attached.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #
*** ri_triggers.c.orig    Fri Apr  4 10:41:45 2003
--- ri_triggers.c    Sun Apr  6 12:36:54 2003
***************
*** 391,403 ****
      }

      /*
!      * Note: We cannot avoid the check on UPDATE, even if old and new key
!      * are the same. Otherwise, someone could DELETE the PK that consists
!      * of the DEFAULT values, and if there are any references, a ON DELETE
!      * SET DEFAULT action would update the references to exactly these
!      * values but we wouldn't see that weired case (this is the only place
!      * to see it).
       */
      if (SPI_connect() != SPI_OK_CONNECT)
          elog(WARNING, "SPI_connect() failed in RI_FKey_check()");

--- 391,409 ----
      }

      /*
!      * No need to check anything if old and new references are the
!      * same on UPDATE.
       */
+     if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+     {
+         if (ri_KeysEqual(fk_rel, old_row, new_row, &qkey,
+                          RI_KEYPAIR_FK_IDX))
+         {
+             heap_close(pk_rel, RowShareLock);
+             return PointerGetDatum(NULL);
+         }
+     }
+
      if (SPI_connect() != SPI_OK_CONNECT)
          elog(WARNING, "SPI_connect() failed in RI_FKey_check()");

***************
*** 2787,2792 ****
--- 2793,2808 ----

              heap_close(fk_rel, RowExclusiveLock);

+             /*
+              * In the case we delete the row who's key is equal to the
+              * default values AND a referencing row in the foreign key
+              * table exists, we would just have updated it to the same
+              * values. We need to do another lookup now and in case a
+              * reference exists, abort the operation. That is already
+              * implemented in the NO ACTION trigger.
+              */
+             RI_FKey_noaction_del(fcinfo);
+
              return PointerGetDatum(NULL);

              /*
***************
*** 3077,3082 ****
--- 3093,3108 ----
                  elog(WARNING, "SPI_finish() failed in RI_FKey_setdefault_upd()");

              heap_close(fk_rel, RowExclusiveLock);
+
+             /*
+              * In the case we updated the row who's key was equal to the
+              * default values AND a referencing row in the foreign key
+              * table exists, we would just have updated it to the same
+              * values. We need to do another lookup now and in case a
+              * reference exists, abort the operation. That is already
+              * implemented in the NO ACTION trigger.
+              */
+             RI_FKey_noaction_upd(fcinfo);

              return PointerGetDatum(NULL);

*** ri_triggers.c.orig    Mon Nov 12 01:09:09 2001
--- ri_triggers.c    Sun Apr  6 13:36:42 2003
***************
*** 354,366 ****
      }

      /*
!      * Note: We cannot avoid the check on UPDATE, even if old and new key
!      * are the same. Otherwise, someone could DELETE the PK that consists
!      * of the DEFAULT values, and if there are any references, a ON DELETE
!      * SET DEFAULT action would update the references to exactly these
!      * values but we wouldn't see that weired case (this is the only place
!      * to see it).
       */
      if (SPI_connect() != SPI_OK_CONNECT)
          elog(NOTICE, "SPI_connect() failed in RI_FKey_check()");

--- 354,372 ----
      }

      /*
!      * No need to check anything if old and new references are the
!      * same on UPDATE.
       */
+     if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+     {
+         if (ri_KeysEqual(fk_rel, old_row, new_row, &qkey,
+                          RI_KEYPAIR_FK_IDX))
+         {
+             heap_close(pk_rel, NoLock);
+             return PointerGetDatum(NULL);
+         }
+     }
+
      if (SPI_connect() != SPI_OK_CONNECT)
          elog(NOTICE, "SPI_connect() failed in RI_FKey_check()");

***************
*** 2447,2452 ****
--- 2453,2468 ----
              if (SPI_finish() != SPI_OK_FINISH)
                  elog(NOTICE, "SPI_finish() failed in RI_FKey_setdefault_del()");

+             /*
+              * In the case we delete the row who's key is equal to the
+              * default values AND a referencing row in the foreign key
+              * table exists, we would just have updated it to the same
+              * values. We need to do another lookup now and in case a
+              * reference exists, abort the operation. That is already
+              * implemented in the NO ACTION trigger.
+              */
+             RI_FKey_noaction_del(fcinfo);
+
              return PointerGetDatum(NULL);

              /*
***************
*** 2722,2727 ****
--- 2738,2753 ----

              if (SPI_finish() != SPI_OK_FINISH)
                  elog(NOTICE, "SPI_finish() failed in RI_FKey_setdefault_upd()");
+
+             /*
+              * In the case we updated the row who's key was equal to the
+              * default values AND a referencing row in the foreign key
+              * table exists, we would just have updated it to the same
+              * values. We need to do another lookup now and in case a
+              * reference exists, abort the operation. That is already
+              * implemented in the NO ACTION trigger.
+              */
+             RI_FKey_noaction_upd(fcinfo);

              return PointerGetDatum(NULL);

*** ri_triggers.c.orig    Sat Mar 29 14:33:24 2003
--- ri_triggers.c    Sun Apr  6 14:01:27 2003
***************
*** 395,407 ****
      }

      /*
!      * Note: We cannot avoid the check on UPDATE, even if old and new key
!      * are the same. Otherwise, someone could DELETE the PK that consists
!      * of the DEFAULT values, and if there are any references, a ON DELETE
!      * SET DEFAULT action would update the references to exactly these
!      * values but we wouldn't see that weird case (this is the only place
!      * to see it).
       */
      if (SPI_connect() != SPI_OK_CONNECT)
          elog(ERROR, "SPI_connect() failed in RI_FKey_check()");

--- 395,413 ----
      }

      /*
!      * No need to check anything if old and new references are the
!      * same on UPDATE.
       */
+     if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+     {
+         if (ri_KeysEqual(fk_rel, old_row, new_row, &qkey,
+                          RI_KEYPAIR_FK_IDX))
+         {
+             heap_close(pk_rel, RowShareLock);
+             return PointerGetDatum(NULL);
+         }
+     }
+
      if (SPI_connect() != SPI_OK_CONNECT)
          elog(ERROR, "SPI_connect() failed in RI_FKey_check()");

***************
*** 2397,2402 ****
--- 2403,2418 ----

              heap_close(fk_rel, RowExclusiveLock);

+             /*
+              * In the case we delete the row who's key is equal to the
+              * default values AND a referencing row in the foreign key
+              * table exists, we would just have updated it to the same
+              * values. We need to do another lookup now and in case a
+              * reference exists, abort the operation. That is already
+              * implemented in the NO ACTION trigger.
+              */
+             RI_FKey_noaction_del(fcinfo);
+
              return PointerGetDatum(NULL);

              /*
***************
*** 2634,2639 ****
--- 2650,2665 ----
                  elog(ERROR, "SPI_finish() failed in RI_FKey_setdefault_upd()");

              heap_close(fk_rel, RowExclusiveLock);
+
+             /*
+              * In the case we updated the row who's key was equal to the
+              * default values AND a referencing row in the foreign key
+              * table exists, we would just have updated it to the same
+              * values. We need to do another lookup now and in case a
+              * reference exists, abort the operation. That is already
+              * implemented in the NO ACTION trigger.
+              */
+             RI_FKey_noaction_upd(fcinfo);

              return PointerGetDatum(NULL);