This patch implements an idea discussed on -hackers recently: if an UPDATE on a table with a foreign key does not modify any of the table's foreign key columns, we can avoid enqueueing the foreign queue check trigger.

I basically just moved the logic for the "are the keys equal?" test from the FK trigger itself into the code that enqueues the trigger. I then removed the keys-are-equal check from the FK trigger. I also had to change (somewhat awkwardly) RI_FKey_keyequal_upd() to work for updates on either the PK table or the FK table. I also removed the bogus TriggerData argument from RI_FKey_keyequal_upd(), since AFAICS it is no needed and merely adds confusion.


This patch does cause one change to the regression test output:

*** ./expected/foreign_key.out  Fri May 27 23:58:54 2005
--- ./results/foreign_key.out   Sat May 28 00:46:20 2005
***************
*** 911,918 ****
DETAIL: Key (base1,ptest1)=(2,2) is still referenced from table "pktable".
  -- fails (1,1) is being referenced (twice)
  update pktable set base1=3 where base1=1;
! ERROR: insert or update on table "pktable" violates foreign key constraint "pktable_base2_fkey"
! DETAIL:  Key (base2,ptest2)=(1,1) is not present in table "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;
--- 911,918 ----
DETAIL: Key (base1,ptest1)=(2,2) is still referenced from table "pktable".
  -- fails (1,1) is being referenced (twice)
  update pktable set base1=3 where base1=1;
! ERROR: update or delete on "pktable" violates foreign key constraint "pktable_base2_fkey" on "pktable" ! DETAIL: Key (base1,ptest1)=(1,1) is still referenced from table "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;

I found this a bit strange: on the one hand I think the new error message is actually more sensible, but I'm not sure what caused the change in behavior. I'll have more of a think about this tomorrow.

-Neil
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.157
diff -c -r1.157 tablecmds.c
*** src/backend/commands/tablecmds.c	10 May 2005 13:16:26 -0000	1.157
--- src/backend/commands/tablecmds.c	27 May 2005 13:58:54 -0000
***************
*** 1596,1603 ****
  		case F_RI_FKEY_NOACTION_UPD:
  			return RI_TRIGGER_PK;
  
! 		case F_RI_FKEY_CHECK_INS:
! 		case F_RI_FKEY_CHECK_UPD:
  			return RI_TRIGGER_FK;
  	}
  
--- 1596,1602 ----
  		case F_RI_FKEY_NOACTION_UPD:
  			return RI_TRIGGER_PK;
  
! 		case F_RI_FKEY_CHECK:
  			return RI_TRIGGER_FK;
  	}
  
***************
*** 4305,4313 ****
  		return;
  
  	/*
! 	 * Scan through each tuple, calling RI_FKey_check_ins (insert trigger)
! 	 * as if that tuple had just been inserted.  If any of those fail, it
! 	 * should ereport(ERROR) and that's that.
  	 */
  	MemSet(&trig, 0, sizeof(trig));
  	trig.tgoid = InvalidOid;
--- 4304,4312 ----
  		return;
  
  	/*
! 	 * Scan through each tuple, calling RI_FKey_check (insert trigger)
! 	 * as if that tuple had just been inserted.  If any of those fail,
! 	 * it should ereport(ERROR) and that's that.
  	 */
  	MemSet(&trig, 0, sizeof(trig));
  	trig.tgoid = InvalidOid;
***************
*** 4359,4365 ****
  		MemSet(&fcinfo, 0, sizeof(fcinfo));
  
  		/*
! 		 * We assume RI_FKey_check_ins won't look at flinfo...
  		 */
  		trigdata.type = T_TriggerData;
  		trigdata.tg_event = TRIGGER_EVENT_INSERT | TRIGGER_EVENT_ROW;
--- 4358,4364 ----
  		MemSet(&fcinfo, 0, sizeof(fcinfo));
  
  		/*
! 		 * We assume RI_FKey_check won't look at flinfo...
  		 */
  		trigdata.type = T_TriggerData;
  		trigdata.tg_event = TRIGGER_EVENT_INSERT | TRIGGER_EVENT_ROW;
***************
*** 4372,4378 ****
  
  		fcinfo.context = (Node *) &trigdata;
  
! 		RI_FKey_check_ins(&fcinfo);
  	}
  
  	heap_endscan(scan);
--- 4371,4377 ----
  
  		fcinfo.context = (Node *) &trigdata;
  
! 		RI_FKey_check(&fcinfo);
  	}
  
  	heap_endscan(scan);
***************
*** 4420,4426 ****
  	fk_trigger = makeNode(CreateTrigStmt);
  	fk_trigger->trigname = fkconstraint->constr_name;
  	fk_trigger->relation = myRel;
! 	fk_trigger->funcname = SystemFuncName("RI_FKey_check_ins");
  	fk_trigger->before = false;
  	fk_trigger->row = true;
  	fk_trigger->actions[0] = 'i';
--- 4419,4425 ----
  	fk_trigger = makeNode(CreateTrigStmt);
  	fk_trigger->trigname = fkconstraint->constr_name;
  	fk_trigger->relation = myRel;
! 	fk_trigger->funcname = SystemFuncName("RI_FKey_check");
  	fk_trigger->before = false;
  	fk_trigger->row = true;
  	fk_trigger->actions[0] = 'i';
Index: src/backend/commands/trigger.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/commands/trigger.c,v
retrieving revision 1.188
diff -c -r1.188 trigger.c
*** src/backend/commands/trigger.c	6 May 2005 17:24:53 -0000	1.188
--- src/backend/commands/trigger.c	29 May 2005 13:10:25 -0000
***************
*** 111,117 ****
  		bool		needconstrrelid = false;
  		void	   *elem = NULL;
  
! 		if (strncmp(strVal(lfirst(list_tail((stmt->funcname)))), "RI_FKey_check_", 14) == 0)
  		{
  			/* A trigger on FK table. */
  			needconstrrelid = true;
--- 111,117 ----
  		bool		needconstrrelid = false;
  		void	   *elem = NULL;
  
! 		if (strncmp(strVal(lfirst(list_tail((stmt->funcname)))), "RI_FKey_check", 13) == 0)
  		{
  			/* A trigger on FK table. */
  			needconstrrelid = true;
***************
*** 2994,3009 ****
  			continue;
  
  		/*
! 		 * If it is an RI UPDATE trigger, and the referenced keys have
! 		 * not changed, short-circuit queuing of the event; there's no
! 		 * need to fire the trigger.
  		 */
  		if ((event & TRIGGER_EVENT_OPMASK) == TRIGGER_EVENT_UPDATE)
  		{
! 			bool		is_ri_trigger;
  
  			switch (trigger->tgfoid)
  			{
  				case F_RI_FKEY_NOACTION_UPD:
  				case F_RI_FKEY_CASCADE_UPD:
  				case F_RI_FKEY_RESTRICT_UPD:
--- 2994,3010 ----
  			continue;
  
  		/*
! 		 * If this is an UPDATE of a PK table or FK table that does
! 		 * not change the PK or FK respectively, we can skip queuing
! 		 * the event: there is no need to fire the trigger.
  		 */
  		if ((event & TRIGGER_EVENT_OPMASK) == TRIGGER_EVENT_UPDATE)
  		{
! 			bool		is_ri_trigger = false;
  
  			switch (trigger->tgfoid)
  			{
+ 				case F_RI_FKEY_CHECK:
  				case F_RI_FKEY_NOACTION_UPD:
  				case F_RI_FKEY_CASCADE_UPD:
  				case F_RI_FKEY_RESTRICT_UPD:
***************
*** 3011,3043 ****
  				case F_RI_FKEY_SETDEFAULT_UPD:
  					is_ri_trigger = true;
  					break;
- 
- 				default:
- 					is_ri_trigger = false;
- 					break;
  			}
  
  			if (is_ri_trigger)
  			{
! 				TriggerData LocTriggerData;
! 
! 				LocTriggerData.type = T_TriggerData;
! 				LocTriggerData.tg_event =
! 					TRIGGER_EVENT_UPDATE | TRIGGER_EVENT_ROW;
! 				LocTriggerData.tg_relation = rel;
! 				LocTriggerData.tg_trigtuple = oldtup;
! 				LocTriggerData.tg_newtuple = newtup;
! 				LocTriggerData.tg_trigger = trigger;
! 				/*
! 				 * We do not currently know which buffers the passed tuples
! 				 * are in, but it does not matter because RI_FKey_keyequal_upd
! 				 * does not care.  We could expand the API of this function
! 				 * if it becomes necessary to set these fields accurately.
! 				 */
! 				LocTriggerData.tg_trigtuplebuf = InvalidBuffer;
! 				LocTriggerData.tg_newtuplebuf = InvalidBuffer;
! 
! 				if (RI_FKey_keyequal_upd(&LocTriggerData))
  				{
  					/* key unchanged, so skip queuing this event */
  					continue;
--- 3012,3035 ----
  				case F_RI_FKEY_SETDEFAULT_UPD:
  					is_ri_trigger = true;
  					break;
  			}
  
+ 			/*
+ 			 * There is one exception when updating FK tables: if the
+ 			 * updated row was inserted by our own transaction and the
+ 			 * FK is deferred, we still need to fire the trigger. This
+ 			 * is because our UPDATE will invalidate the INSERT so the
+ 			 * end-of-transaction INSERT RI trigger will not do
+ 			 * anything, so we have to do the check for the UPDATE
+ 			 * anyway.
+ 			 */
+ 			if (trigger->tgfoid == F_RI_FKEY_CHECK &&
+ 				HeapTupleHeaderGetXmin(oldtup->t_data) == GetCurrentTransactionId())
+ 				is_ri_trigger = false;
+ 
  			if (is_ri_trigger)
  			{
! 				if (RI_FKey_keyequal_upd(trigger, rel, oldtup, newtup))
  				{
  					/* key unchanged, so skip queuing this event */
  					continue;
Index: src/backend/utils/adt/ri_triggers.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/utils/adt/ri_triggers.c,v
retrieving revision 1.78
diff -c -r1.78 ri_triggers.c
*** src/backend/utils/adt/ri_triggers.c	29 May 2005 04:23:05 -0000	1.78
--- src/backend/utils/adt/ri_triggers.c	29 May 2005 13:22:09 -0000
***************
*** 38,47 ****
  #include "optimizer/planmain.h"
  #include "parser/parse_oper.h"
  #include "rewrite/rewriteHandler.h"
- #include "utils/lsyscache.h"
- #include "utils/typcache.h"
  #include "utils/acl.h"
  #include "utils/guc.h"
  #include "miscadmin.h"
  
  
--- 38,48 ----
  #include "optimizer/planmain.h"
  #include "parser/parse_oper.h"
  #include "rewrite/rewriteHandler.h"
  #include "utils/acl.h"
+ #include "utils/fmgroids.h"
  #include "utils/guc.h"
+ #include "utils/lsyscache.h"
+ #include "utils/typcache.h"
  #include "miscadmin.h"
  
  
***************
*** 176,182 ****
   *	Check foreign key existence (combined for INSERT and UPDATE).
   * ----------
   */
! static Datum
  RI_FKey_check(PG_FUNCTION_ARGS)
  {
  	TriggerData *trigdata = (TriggerData *) fcinfo->context;
--- 177,183 ----
   *	Check foreign key existence (combined for INSERT and UPDATE).
   * ----------
   */
! Datum
  RI_FKey_check(PG_FUNCTION_ARGS)
  {
  	TriggerData *trigdata = (TriggerData *) fcinfo->context;
***************
*** 375,396 ****
  			break;
  	}
  
- 	/*
- 	 * No need to check anything if old and new references are the same on
- 	 * UPDATE.
- 	 */
- 	if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
- 	{
- 		if (HeapTupleHeaderGetXmin(old_row->t_data) !=
- 			GetCurrentTransactionId() &&
- 			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");
  
--- 376,381 ----
***************
*** 455,486 ****
  
  
  /* ----------
-  * RI_FKey_check_ins -
-  *
-  *	Check foreign key existence at insert event on FK table.
-  * ----------
-  */
- Datum
- RI_FKey_check_ins(PG_FUNCTION_ARGS)
- {
- 	return RI_FKey_check(fcinfo);
- }
- 
- 
- /* ----------
-  * RI_FKey_check_upd -
-  *
-  *	Check foreign key existence at update event on FK table.
-  * ----------
-  */
- Datum
- RI_FKey_check_upd(PG_FUNCTION_ARGS)
- {
- 	return RI_FKey_check(fcinfo);
- }
- 
- 
- /* ----------
   * ri_Check_Pk_Match
   *
   *	Check for matching value of old pk row in current state for
--- 440,445 ----
***************
*** 2005,2012 ****
  					 * corresponding to changed columns in pk_rel's key
  					 */
  					if (match_type == RI_MATCH_TYPE_FULL ||
! 					  !ri_OneKeyEqual(pk_rel, i, old_row, new_row, &qkey,
! 									  RI_KEYPAIR_PK_IDX))
  					{
  						snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), "%s %s = NULL",
  								 querysep, attname);
--- 1964,1971 ----
  					 * corresponding to changed columns in pk_rel's key
  					 */
  					if (match_type == RI_MATCH_TYPE_FULL ||
! 						!ri_OneKeyEqual(pk_rel, i, old_row, new_row, &qkey,
! 										RI_KEYPAIR_PK_IDX))
  					{
  						snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), "%s %s = NULL",
  								 querysep, attname);
***************
*** 2016,2022 ****
  							 qualsep, attname, i + 1);
  					qualsep = "AND";
  					queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
! 									 qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
  				}
  				strcat(querystr, qualstr);
  
--- 1975,1981 ----
  							 qualsep, attname, i + 1);
  					qualsep = "AND";
  					queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
! 												 qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
  				}
  				strcat(querystr, qualstr);
  
***************
*** 2453,2480 ****
  /* ----------
   * RI_FKey_keyequal_upd -
   *
!  *	Check if we have a key change on update.
!  *
!  *	This is not a real trigger procedure. It is used by the AFTER
   *	trigger queue manager to detect "triggered data change violation".
   * ----------
   */
  bool
! RI_FKey_keyequal_upd(TriggerData *trigdata)
  {
  	int			tgnargs;
  	char	  **tgargs;
  	Relation	fk_rel;
  	Relation	pk_rel;
- 	HeapTuple	new_row;
- 	HeapTuple	old_row;
  	RI_QueryKey qkey;
  
  	/*
  	 * Check for the correct # of call arguments
  	 */
! 	tgnargs = trigdata->tg_trigger->tgnargs;
! 	tgargs = trigdata->tg_trigger->tgargs;
  	if (tgnargs < 4 ||
  		tgnargs > RI_MAX_ARGUMENTS ||
  		(tgnargs % 2) != 0)
--- 2412,2437 ----
  /* ----------
   * RI_FKey_keyequal_upd -
   *
!  *	Check if we have a key change on update. It is used by the AFTER
   *	trigger queue manager to detect "triggered data change violation".
   * ----------
   */
  bool
! RI_FKey_keyequal_upd(Trigger *trigger, Relation rel, HeapTuple old_row, HeapTuple new_row)
  {
  	int			tgnargs;
  	char	  **tgargs;
  	Relation	fk_rel;
  	Relation	pk_rel;
  	RI_QueryKey qkey;
+ 	bool		fk_update;
+ 	bool		result;
  
  	/*
  	 * Check for the correct # of call arguments
  	 */
! 	tgnargs = trigger->tgnargs;
! 	tgargs = trigger->tgargs;
  	if (tgnargs < 4 ||
  		tgnargs > RI_MAX_ARGUMENTS ||
  		(tgnargs % 2) != 0)
***************
*** 2490,2548 ****
  		return true;
  
  	/*
! 	 * Get the relation descriptors of the FK and PK tables and the new
! 	 * and old tuple.
! 	 *
! 	 * Use minimal locking for fk_rel here.
  	 */
! 	if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid))
  		ereport(ERROR,
  				(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
! 		errmsg("no target table given for trigger \"%s\" on table \"%s\"",
! 			   trigdata->tg_trigger->tgname,
! 			   RelationGetRelationName(trigdata->tg_relation)),
! 				 errhint("Remove this referential integrity trigger and its mates, then do ALTER TABLE ADD CONSTRAINT.")));
! 
! 	fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, AccessShareLock);
! 	pk_rel = trigdata->tg_relation;
! 	new_row = trigdata->tg_newtuple;
! 	old_row = trigdata->tg_trigtuple;
! 
! 	switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
  	{
! 			/*
! 			 * MATCH <UNSPECIFIED>
! 			 */
! 		case RI_MATCH_TYPE_UNSPECIFIED:
! 		case RI_MATCH_TYPE_FULL:
! 			ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
! 								 RI_PLAN_KEYEQUAL_UPD,
! 								 fk_rel, pk_rel,
! 								 tgnargs, tgargs);
! 
! 			heap_close(fk_rel, AccessShareLock);
  
! 			/*
! 			 * Return if key's are equal
! 			 */
! 			return ri_KeysEqual(pk_rel, old_row, new_row, &qkey,
! 								RI_KEYPAIR_PK_IDX);
  
! 			/*
! 			 * Handle MATCH PARTIAL set null delete.
! 			 */
! 		case RI_MATCH_TYPE_PARTIAL:
! 			ereport(ERROR,
! 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! 					 errmsg("MATCH PARTIAL not yet implemented")));
! 			break;
! 	}
  
  	/*
! 	 * Never reached
  	 */
! 	elog(ERROR, "invalid match_type");
! 	return false;
  }
  
  
--- 2447,2499 ----
  		return true;
  
  	/*
! 	 * This can be invoked for both UPDATEs to a PK table or an FK
! 	 * table. "rel" always refers to the relation on which the UPDATE
! 	 * occurred.
  	 */
! 	if (!OidIsValid(trigger->tgconstrrelid))
  		ereport(ERROR,
  				(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
! 				 errmsg("no target table given for trigger \"%s\" on table \"%s\"",
! 						trigger->tgname,
! 						RelationGetRelationName(rel)),
! 				 errhint("Remove this referential integrity trigger and its mates, "
! 						 "then do ALTER TABLE ADD CONSTRAINT.")));
! 
! 	if (trigger->tgfoid == F_RI_FKEY_CHECK)
! 	{
! 		/* Update on FK table */
! 		fk_update = true;
! 		fk_rel = rel;
! 		pk_rel = heap_open(trigger->tgconstrrelid, AccessShareLock);
! 	}
! 	else
  	{
! 		/* Update on PK table */
! 		fk_update = false;
! 		pk_rel = rel;
! 		fk_rel = heap_open(trigger->tgconstrrelid, AccessShareLock);
! 	}
  
! 	if (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]) == RI_MATCH_TYPE_PARTIAL)
! 		ereport(ERROR,
! 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! 				 errmsg("MATCH PARTIAL not yet implemented")));
  
! 	/* MATCH <UNSPECIFIED> and MATCH <FULL> */
! 	ri_BuildQueryKeyFull(&qkey, trigger->tgoid, RI_PLAN_KEYEQUAL_UPD,
! 						 fk_rel, pk_rel, tgnargs, tgargs);
  
  	/*
! 	 * Return if keys are equal
  	 */
! 	result = ri_KeysEqual(rel, old_row, new_row, &qkey,
! 						  fk_update ? RI_KEYPAIR_FK_IDX : RI_KEYPAIR_PK_IDX);
! 	if (fk_update)
! 		heap_close(pk_rel, AccessShareLock);
! 	else
! 		heap_close(fk_rel, AccessShareLock);
! 	return result;
  }
  
  
***************
*** 2871,2877 ****
  	/*
  	 * Initialize the key and fill in type, oid's and number of keypairs
  	 */
! 	memset((void *) key, 0, sizeof(RI_QueryKey));
  	key->constr_type = RI_MATCH_TYPE_FULL;
  	key->constr_id = constr_id;
  	key->constr_queryno = constr_queryno;
--- 2822,2828 ----
  	/*
  	 * Initialize the key and fill in type, oid's and number of keypairs
  	 */
! 	memset(key, 0, sizeof(RI_QueryKey));
  	key->constr_type = RI_MATCH_TYPE_FULL;
  	key->constr_id = constr_id;
  	key->constr_queryno = constr_queryno;
***************
*** 3489,3495 ****
  	for (i = 0; i < key->nkeypairs; i++)
  	{
  		/*
! 		 * Get one attributes oldvalue. If it is NULL - they're not equal.
  		 */
  		oldvalue = SPI_getbinval(oldtup, rel->rd_att,
  								 key->keypair[i][pairidx], &isnull);
--- 3440,3446 ----
  	for (i = 0; i < key->nkeypairs; i++)
  	{
  		/*
! 		 * Get one attribute's oldvalue. If it is NULL - they're not equal.
  		 */
  		oldvalue = SPI_getbinval(oldtup, rel->rd_att,
  								 key->keypair[i][pairidx], &isnull);
***************
*** 3497,3503 ****
  			return false;
  
  		/*
! 		 * Get one attributes oldvalue. If it is NULL - they're not equal.
  		 */
  		newvalue = SPI_getbinval(newtup, rel->rd_att,
  								 key->keypair[i][pairidx], &isnull);
--- 3448,3454 ----
  			return false;
  
  		/*
! 		 * Get one attribute's oldvalue. If it is NULL - they're not equal.
  		 */
  		newvalue = SPI_getbinval(newtup, rel->rd_att,
  								 key->keypair[i][pairidx], &isnull);
***************
*** 3505,3511 ****
  			return false;
  
  		/*
! 		 * Get the attributes type OID and call the '=' operator to
  		 * compare the values.
  		 */
  		typeid = SPI_gettypeid(rel->rd_att, key->keypair[i][pairidx]);
--- 3456,3462 ----
  			return false;
  
  		/*
! 		 * Get the attribute's type OID and call the '=' operator to
  		 * compare the values.
  		 */
  		typeid = SPI_gettypeid(rel->rd_att, key->keypair[i][pairidx]);
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.363
diff -c -r1.363 pg_proc.h
*** src/include/catalog/pg_proc.h	20 May 2005 01:29:55 -0000	1.363
--- src/include/catalog/pg_proc.h	27 May 2005 13:58:54 -0000
***************
*** 2261,2269 ****
  
  
  /* Generic referential integrity constraint triggers */
! DATA(insert OID = 1644 (  RI_FKey_check_ins		PGNSP PGUID 12 f f t f v 0 2279 "" _null_ _null_ _null_	RI_FKey_check_ins - _null_ ));
! DESCR("referential integrity FOREIGN KEY ... REFERENCES");
! DATA(insert OID = 1645 (  RI_FKey_check_upd		PGNSP PGUID 12 f f t f v 0 2279 "" _null_ _null_ _null_	RI_FKey_check_upd - _null_ ));
  DESCR("referential integrity FOREIGN KEY ... REFERENCES");
  DATA(insert OID = 1646 (  RI_FKey_cascade_del	PGNSP PGUID 12 f f t f v 0 2279 "" _null_ _null_ _null_	RI_FKey_cascade_del - _null_ ));
  DESCR("referential integrity ON DELETE CASCADE");
--- 2261,2267 ----
  
  
  /* Generic referential integrity constraint triggers */
! DATA(insert OID = 1644 (  RI_FKey_check			PGNSP PGUID 12 f f t f v 0 2279 "" _null_ _null_ _null_	RI_FKey_check - _null_ ));
  DESCR("referential integrity FOREIGN KEY ... REFERENCES");
  DATA(insert OID = 1646 (  RI_FKey_cascade_del	PGNSP PGUID 12 f f t f v 0 2279 "" _null_ _null_ _null_	RI_FKey_cascade_del - _null_ ));
  DESCR("referential integrity ON DELETE CASCADE");
Index: src/include/commands/trigger.h
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/include/commands/trigger.h,v
retrieving revision 1.53
diff -c -r1.53 trigger.h
*** src/include/commands/trigger.h	11 Apr 2005 19:51:15 -0000	1.53
--- src/include/commands/trigger.h	27 May 2005 13:58:54 -0000
***************
*** 168,174 ****
  /*
   * in utils/adt/ri_triggers.c
   */
! extern bool RI_FKey_keyequal_upd(TriggerData *trigdata);
  extern bool RI_Initial_Check(FkConstraint *fkconstraint,
  				 Relation rel,
  				 Relation pkrel);
--- 168,175 ----
  /*
   * in utils/adt/ri_triggers.c
   */
! extern bool RI_FKey_keyequal_upd(Trigger *trigger, Relation rel,
! 								 HeapTuple old_row, HeapTuple new_row);
  extern bool RI_Initial_Check(FkConstraint *fkconstraint,
  				 Relation rel,
  				 Relation pkrel);
Index: src/include/utils/builtins.h
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.257
diff -c -r1.257 builtins.h
*** src/include/utils/builtins.h	27 May 2005 00:57:49 -0000	1.257
--- src/include/utils/builtins.h	27 May 2005 13:58:54 -0000
***************
*** 782,789 ****
  extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
  
  /* ri_triggers.c */
! extern Datum RI_FKey_check_ins(PG_FUNCTION_ARGS);
! extern Datum RI_FKey_check_upd(PG_FUNCTION_ARGS);
  extern Datum RI_FKey_noaction_del(PG_FUNCTION_ARGS);
  extern Datum RI_FKey_noaction_upd(PG_FUNCTION_ARGS);
  extern Datum RI_FKey_cascade_del(PG_FUNCTION_ARGS);
--- 782,788 ----
  extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
  
  /* ri_triggers.c */
! extern Datum RI_FKey_check(PG_FUNCTION_ARGS);
  extern Datum RI_FKey_noaction_del(PG_FUNCTION_ARGS);
  extern Datum RI_FKey_noaction_upd(PG_FUNCTION_ARGS);
  extern Datum RI_FKey_cascade_del(PG_FUNCTION_ARGS);
Index: src/test/regress/expected/foreign_key.out
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/test/regress/expected/foreign_key.out,v
retrieving revision 1.38
diff -c -r1.38 foreign_key.out
*** src/test/regress/expected/foreign_key.out	13 Oct 2004 01:22:31 -0000	1.38
--- src/test/regress/expected/foreign_key.out	27 May 2005 13:58:54 -0000
***************
*** 911,918 ****
  DETAIL:  Key (base1,ptest1)=(2,2) is still referenced from table "pktable".
  -- fails (1,1) is being referenced (twice)
  update pktable set base1=3 where base1=1;
! ERROR:  update or delete on "pktable" violates foreign key constraint "pktable_base2_fkey" on "pktable"
! DETAIL:  Key (base1,ptest1)=(1,1) is still referenced from table "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;
--- 911,918 ----
  DETAIL:  Key (base1,ptest1)=(2,2) is still referenced from table "pktable".
  -- fails (1,1) is being referenced (twice)
  update pktable set base1=3 where base1=1;
! ERROR:  insert or update on table "pktable" violates foreign key constraint "pktable_base2_fkey"
! DETAIL:  Key (base2,ptest2)=(1,1) is not present in table "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;
***************
*** 1061,1066 ****
--- 1061,1068 ----
  COMMIT;
  ERROR:  insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
  DETAIL:  Key (fk)=(200) is not present in table "pktable".
+ DROP TABLE pktable, fktable CASCADE;
+ NOTICE:  drop cascades to constraint fktable_fk_fkey on table fktable
  -- test notice about expensive referential integrity checks,
  -- where the index cannot be used because of type incompatibilities.
  CREATE TEMP TABLE pktable (
***************
*** 1128,1130 ****
--- 1130,1174 ----
  DETAIL:  Key columns "x2" and "id1" are of different types: character varying and integer.
  WARNING:  foreign key constraint "fk_241_132" will require costly sequential scans
  DETAIL:  Key columns "x4" and "id3" are of different types: text and real.
+ DROP TABLE pktable, fktable CASCADE;
+ NOTICE:  drop cascades to constraint fk_241_132 on table fktable
+ NOTICE:  drop cascades to constraint fk_123_231 on table fktable
+ NOTICE:  drop cascades to constraint fk_253_213 on table fktable
+ NOTICE:  drop cascades to constraint fk_213_213 on table fktable
+ NOTICE:  drop cascades to constraint fk_123_123 on table fktable
+ NOTICE:  drop cascades to constraint fk_5_1 on table fktable
+ NOTICE:  drop cascades to constraint fk_3_1 on table fktable
+ NOTICE:  drop cascades to constraint fk_2_1 on table fktable
+ NOTICE:  drop cascades to constraint fktable_x1_fkey on table fktable
+ NOTICE:  drop cascades to constraint fk_4_2 on table fktable
+ NOTICE:  drop cascades to constraint fk_1_2 on table fktable
+ NOTICE:  drop cascades to constraint fktable_x2_fkey on table fktable
+ NOTICE:  drop cascades to constraint fk_1_3 on table fktable
+ NOTICE:  drop cascades to constraint fk_2_3 on table fktable
+ NOTICE:  drop cascades to constraint fktable_x3_fkey on table fktable
+ -- test a tricky case: we can elide firing the FK check trigger during
+ -- an UPDATE if the UPDATE did not change the foreign key
+ -- field. However, we can't do this if our transaction was the one that
+ -- created the updated row and the trigger is deferred, since our UPDATE
+ -- will have invalidated the original newly-inserted tuple, and therefore
+ -- cause the on-INSERT RI trigger not to be fired.
+ CREATE TEMP TABLE pktable (
+     id int primary key,
+     other int
+ );
+ NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
+ CREATE TEMP TABLE fktable (
+     id int primary key,
+     fk int references pktable deferrable initially deferred
+ );
+ NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable"
+ INSERT INTO pktable VALUES (5, 10);
+ BEGIN;
+ -- doesn't match PK, but no error yet
+ INSERT INTO fktable VALUES (0, 20);
+ -- don't change FK
+ UPDATE fktable SET id = id + 1;
+ -- should catch error from initial INSERT
+ COMMIT;
+ ERROR:  insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
+ DETAIL:  Key (fk)=(20) is not present in table "pktable".
Index: src/test/regress/sql/foreign_key.sql
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/test/regress/sql/foreign_key.sql,v
retrieving revision 1.15
diff -c -r1.15 foreign_key.sql
*** src/test/regress/sql/foreign_key.sql	4 Aug 2004 21:34:34 -0000	1.15
--- src/test/regress/sql/foreign_key.sql	27 May 2005 13:58:54 -0000
***************
*** 705,710 ****
--- 705,712 ----
  -- error here on commit
  COMMIT;
  
+ DROP TABLE pktable, fktable CASCADE;
+ 
  -- test notice about expensive referential integrity checks,
  -- where the index cannot be used because of type incompatibilities.
  
***************
*** 774,776 ****
--- 776,810 ----
  
  ALTER TABLE fktable ADD CONSTRAINT fk_241_132
  FOREIGN KEY (x2,x4,x1) REFERENCES pktable(id1,id3,id2);
+ 
+ DROP TABLE pktable, fktable CASCADE;
+ 
+ -- test a tricky case: we can elide firing the FK check trigger during
+ -- an UPDATE if the UPDATE did not change the foreign key
+ -- field. However, we can't do this if our transaction was the one that
+ -- created the updated row and the trigger is deferred, since our UPDATE
+ -- will have invalidated the original newly-inserted tuple, and therefore
+ -- cause the on-INSERT RI trigger not to be fired.
+ 
+ CREATE TEMP TABLE pktable (
+     id int primary key,
+     other int
+ );
+ 
+ CREATE TEMP TABLE fktable (
+     id int primary key,
+     fk int references pktable deferrable initially deferred
+ );
+ 
+ INSERT INTO pktable VALUES (5, 10);
+ 
+ BEGIN;
+ 
+ -- doesn't match PK, but no error yet
+ INSERT INTO fktable VALUES (0, 20);
+ 
+ -- don't change FK
+ UPDATE fktable SET id = id + 1;
+ 
+ -- should catch error from initial INSERT
+ COMMIT;
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to