From 3284e342a27f699f3908204f6c76492faeabd525 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 21 May 2003 18:14:46 +0000 Subject: [PATCH] Back-patch Jan's fix to avoid primary key lookup (and lock) if foreign key does not change on UPDATE. --- src/backend/utils/adt/ri_triggers.c | 40 +++++++++++++++++++---- src/test/regress/expected/foreign_key.out | 2 +- 2 files changed, 34 insertions(+), 8 deletions(-) diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index 50b2d28d87..539dee726b 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -17,7 +17,7 @@ * * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * - * $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.43.2.2 2003/04/26 22:21:58 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.43.2.3 2003/05/21 18:14:46 tgl Exp $ * * ---------- */ @@ -395,13 +395,19 @@ RI_FKey_check(PG_FUNCTION_ARGS) } /* - * 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). + * 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()"); @@ -2741,6 +2747,16 @@ RI_FKey_setdefault_del(PG_FUNCTION_ARGS) 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); /* @@ -3010,6 +3026,16 @@ RI_FKey_setdefault_upd(PG_FUNCTION_ARGS) 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); /* diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index a68a406fda..3c2ede0aaa 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -882,7 +882,7 @@ delete from pktable where base1=2; ERROR: $1 referential integrity violation - key in pktable still referenced from pktable -- fails (1,1) is being referenced (twice) update pktable set base1=3 where base1=1; -ERROR: $1 referential integrity violation - key referenced from pktable not found in pktable +ERROR: $1 referential integrity violation - key in pktable still referenced from pktable -- this sequence of two deletes will work, since after the first there will be no (2,*) references delete from pktable where base2=2; delete from pktable where base1=2; -- 2.50.0