On Sun, 2005-05-29 at 21:06 -0400, Tom Lane wrote:
> Neil Conway <[EMAIL PROTECTED]> writes:
> > Hmm, I suppose -- if you prefer I can have check_ins called by the 
> > INSERT trigger and check_upd called by the UPDATE trigger, which 
> > probably makes more sense.
> 
> Yeah ... I thought it was doing that already.

Attached are two patches: one that changes ADD FOREIGN KEY to create
separate ON INSERT and ON UPDATE triggers that invoke different trigger
functions, and a revised version of the FK UPDATE enqueuing patch.

BTW, the regression test failure was just stupidity on my part: I had
updated the "expected" results using the regression test output from
some intermediate version of the patch without checking it carefully
enough. The attached patch doesn't FK enqueuing patch doesn't cause any
unexpected regression test changes.

Barring any objections I'll apply both of these to HEAD today or
tomorrow.

-Neil

Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /var/lib/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	30 May 2005 02:33:56 -0000
***************
*** 4380,4431 ****
  	pfree(trig.tgargs);
  }
  
- /*
-  * Create the triggers that implement an FK constraint.
-  */
  static void
! createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
! 						 Oid constrOid)
  {
- 	RangeVar   *myRel;
  	CreateTrigStmt *fk_trigger;
  	ListCell   *fk_attr;
  	ListCell   *pk_attr;
- 	ObjectAddress trigobj,
- 				constrobj;
- 
- 	/*
- 	 * Reconstruct a RangeVar for my relation (not passed in,
- 	 * unfortunately).
- 	 */
- 	myRel = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
- 						 pstrdup(RelationGetRelationName(rel)));
  
- 	/*
- 	 * Preset objectAddress fields
- 	 */
- 	constrobj.classId = ConstraintRelationId;
- 	constrobj.objectId = constrOid;
- 	constrobj.objectSubId = 0;
- 	trigobj.classId = TriggerRelationId;
- 	trigobj.objectSubId = 0;
- 
- 	/* Make changes-so-far visible */
- 	CommandCounterIncrement();
- 
- 	/*
- 	 * Build and execute a CREATE CONSTRAINT TRIGGER statement for the
- 	 * CHECK action.
- 	 */
  	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';
! 	fk_trigger->actions[1] = 'u';
! 	fk_trigger->actions[2] = '\0';
  
  	fk_trigger->isconstraint = true;
  	fk_trigger->deferrable = fkconstraint->deferrable;
--- 4380,4412 ----
  	pfree(trig.tgargs);
  }
  
  static void
! CreateFKCheckTrigger(RangeVar *myRel, FkConstraint *fkconstraint,
! 					 ObjectAddress *constrobj, ObjectAddress *trigobj,
! 					 bool on_insert)
  {
  	CreateTrigStmt *fk_trigger;
  	ListCell   *fk_attr;
  	ListCell   *pk_attr;
  
  	fk_trigger = makeNode(CreateTrigStmt);
  	fk_trigger->trigname = fkconstraint->constr_name;
  	fk_trigger->relation = myRel;
  	fk_trigger->before = false;
  	fk_trigger->row = true;
! 
! 	/* Either ON INSERT or ON UPDATE */
! 	if (on_insert)
! 	{
! 		fk_trigger->funcname = SystemFuncName("RI_FKey_check_ins");
! 		fk_trigger->actions[0] = 'i';
! 	}
! 	else
! 	{
! 		fk_trigger->funcname = SystemFuncName("RI_FKey_check_upd");
! 		fk_trigger->actions[0] = 'u';
! 	}
! 	fk_trigger->actions[1] = '\0';
  
  	fk_trigger->isconstraint = true;
  	fk_trigger->deferrable = fkconstraint->deferrable;
***************
*** 4453,4465 ****
  		fk_trigger->args = lappend(fk_trigger->args, lfirst(pk_attr));
  	}
  
! 	trigobj.objectId = CreateTrigger(fk_trigger, true);
  
  	/* Register dependency from trigger to constraint */
! 	recordDependencyOn(&trigobj, &constrobj, DEPENDENCY_INTERNAL);
  
  	/* Make changes-so-far visible */
  	CommandCounterIncrement();
  
  	/*
  	 * Build and execute a CREATE CONSTRAINT TRIGGER statement for the ON
--- 4434,4487 ----
  		fk_trigger->args = lappend(fk_trigger->args, lfirst(pk_attr));
  	}
  
! 	trigobj->objectId = CreateTrigger(fk_trigger, true);
  
  	/* Register dependency from trigger to constraint */
! 	recordDependencyOn(trigobj, constrobj, DEPENDENCY_INTERNAL);
  
  	/* Make changes-so-far visible */
  	CommandCounterIncrement();
+ }
+ 
+ /*
+  * Create the triggers that implement an FK constraint.
+  */
+ static void
+ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
+ 						 Oid constrOid)
+ {
+ 	RangeVar   *myRel;
+ 	CreateTrigStmt *fk_trigger;
+ 	ListCell   *fk_attr;
+ 	ListCell   *pk_attr;
+ 	ObjectAddress trigobj,
+ 				constrobj;
+ 
+ 	/*
+ 	 * Reconstruct a RangeVar for my relation (not passed in,
+ 	 * unfortunately).
+ 	 */
+ 	myRel = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
+ 						 pstrdup(RelationGetRelationName(rel)));
+ 
+ 	/*
+ 	 * Preset objectAddress fields
+ 	 */
+ 	constrobj.classId = ConstraintRelationId;
+ 	constrobj.objectId = constrOid;
+ 	constrobj.objectSubId = 0;
+ 	trigobj.classId = TriggerRelationId;
+ 	trigobj.objectSubId = 0;
+ 
+ 	/* Make changes-so-far visible */
+ 	CommandCounterIncrement();
+ 
+ 	/*
+ 	 * Build and execute a CREATE CONSTRAINT TRIGGER statement for the
+ 	 * CHECK action for both INSERTs and UPDATEs on the referencing table.
+ 	 */
+ 	CreateFKCheckTrigger(myRel, fkconstraint, &constrobj, &trigobj, true);
+ 	CreateFKCheckTrigger(myRel, fkconstraint, &constrobj, &trigobj, false);
  
  	/*
  	 * Build and execute a CREATE CONSTRAINT TRIGGER statement for the ON
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /var/lib/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	30 May 2005 02:50:45 -0000
***************
*** 156,167 ****
  } NewColumnValue;
  
  
- /* Used by attribute and relation renaming routines: */
- #define RI_TRIGGER_PK	1		/* is a trigger on the PK relation */
- #define RI_TRIGGER_FK	2		/* is a trigger on the FK relation */
- #define RI_TRIGGER_NONE 0		/* is not an RI trigger function */
- 
- 
  static List *MergeAttributes(List *schema, List *supers, bool istemp,
  				List **supOids, List **supconstr, int *supOidCount);
  static bool change_varattnos_of_a_node(Node *node, const AttrNumber *newattno);
--- 156,161 ----
***************
*** 246,252 ****
  					char *tablespacename);
  static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace);
  static void copy_relation_data(Relation rel, SMgrRelation dst);
- static int	ri_trigger_type(Oid tgfoid);
  static void update_ri_trigger_args(Oid relid,
  					   const char *oldname,
  					   const char *newname,
--- 240,245 ----
***************
*** 1571,1609 ****
  	relation_close(targetrelation, NoLock);
  }
  
- 
- /*
-  * Given a trigger function OID, determine whether it is an RI trigger,
-  * and if so whether it is attached to PK or FK relation.
-  *
-  * XXX this probably doesn't belong here; should be exported by
-  * ri_triggers.c
-  */
- static int
- ri_trigger_type(Oid tgfoid)
- {
- 	switch (tgfoid)
- 	{
- 		case F_RI_FKEY_CASCADE_DEL:
- 		case F_RI_FKEY_CASCADE_UPD:
- 		case F_RI_FKEY_RESTRICT_DEL:
- 		case F_RI_FKEY_RESTRICT_UPD:
- 		case F_RI_FKEY_SETNULL_DEL:
- 		case F_RI_FKEY_SETNULL_UPD:
- 		case F_RI_FKEY_SETDEFAULT_DEL:
- 		case F_RI_FKEY_SETDEFAULT_UPD:
- 		case F_RI_FKEY_NOACTION_DEL:
- 		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;
- 	}
- 
- 	return RI_TRIGGER_NONE;
- }
- 
  /*
   * Scan pg_trigger for RI triggers that are on the specified relation
   * (if fk_scan is false) or have it as the tgconstrrel (if fk_scan
--- 1564,1569 ----
***************
*** 1663,1669 ****
  		const char *arga[RI_MAX_ARGUMENTS];
  		const char *argp;
  
! 		tg_type = ri_trigger_type(pg_trigger->tgfoid);
  		if (tg_type == RI_TRIGGER_NONE)
  		{
  			/* Not an RI trigger, forget it */
--- 1623,1629 ----
  		const char *arga[RI_MAX_ARGUMENTS];
  		const char *argp;
  
! 		tg_type = RI_FKey_trigger_type(pg_trigger->tgfoid);
  		if (tg_type == RI_TRIGGER_NONE)
  		{
  			/* Not an RI trigger, forget it */
Index: src/backend/commands/trigger.c
===================================================================
RCS file: /var/lib/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	30 May 2005 02:47:03 -0000
***************
*** 2994,3047 ****
  			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:
! 				case F_RI_FKEY_SETNULL_UPD:
! 				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;
! 				}
  			}
  		}
  
--- 2994,3040 ----
  			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)
  		{
! 			switch (RI_FKey_trigger_type(trigger->tgfoid))
  			{
! 				case RI_TRIGGER_PK:
! 					/* Update on PK table */
! 					if (RI_FKey_keyequal_upd_pk(trigger, rel, oldtup, newtup))
! 					{
! 						/* key unchanged, so skip queuing this event */
! 						continue;
! 					}
  					break;
  
! 				case RI_TRIGGER_FK:
! 					/*
! 					 * Update on FK table
! 					 *
! 					 * 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 (HeapTupleHeaderGetXmin(oldtup->t_data) !=
! 						GetCurrentTransactionId() &&
! 						RI_FKey_keyequal_upd_fk(trigger, rel, oldtup, newtup))
! 					{
! 						continue;
! 					}
  					break;
  
! 				case RI_TRIGGER_NONE:
! 					/* Not an FK trigger */
! 					break;
  			}
  		}
  
Index: src/backend/utils/adt/ri_triggers.c
===================================================================
RCS file: /var/lib/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	30 May 2005 02:43:26 -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"
  
  
***************
*** 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 ----
***************
*** 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);
--- 1990,1997 ----
  					 * 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);
  
--- 2001,2007 ----
  							 qualsep, attname, i + 1);
  					qualsep = "AND";
  					queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
! 												 qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
  				}
  				strcat(querystr, qualstr);
  
***************
*** 2451,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)
--- 2436,2461 ----
  
  
  /* ----------
!  * RI_FKey_keyequal_upd_pk -
   *
!  *	Check if we have a key change on an update to a PK relation. This is
!  *	used by the AFTER trigger queue manager to detect "triggered data
!  *	change violation".
   * ----------
   */
  bool
! RI_FKey_keyequal_upd_pk(Trigger *trigger, Relation pk_rel, HeapTuple old_row, HeapTuple new_row)
  {
  	int			tgnargs;
  	char	  **tgargs;
  	Relation	fk_rel;
  	RI_QueryKey qkey;
  
  	/*
  	 * Check for the correct # of call arguments
  	 */
! 	tgnargs = trigger->tgnargs;
! 	tgargs = trigger->tgargs;
  	if (tgnargs < 4 ||
  		tgnargs > RI_MAX_ARGUMENTS ||
  		(tgnargs % 2) != 0)
***************
*** 2489,2536 ****
  	if (tgnargs == 4)
  		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),
--- 2470,2501 ----
  	if (tgnargs == 4)
  		return true;
  
! 	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(pk_rel)),
! 				 errhint("Remove this referential integrity trigger and its mates, "
! 						 "then do ALTER TABLE ADD CONSTRAINT.")));
  
! 	fk_rel = heap_open(trigger->tgconstrrelid, AccessShareLock);
  
  	switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
  	{
  		case RI_MATCH_TYPE_UNSPECIFIED:
  		case RI_MATCH_TYPE_FULL:
! 			ri_BuildQueryKeyFull(&qkey, 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),
***************
*** 2538,2551 ****
  			break;
  	}
  
  	/*
! 	 * Never reached
  	 */
  	elog(ERROR, "invalid match_type");
  	return false;
  }
  
- 
  /* ----------
   * RI_Initial_Check -
   *
--- 2503,2586 ----
  			break;
  	}
  
+ 	/* Never reached */
+ 	elog(ERROR, "invalid match_type");
+ 	return false;
+ }
+ 
+ /* ----------
+  * RI_FKey_keyequal_upd_fk -
+  *
+  *	Check if we have a key change on an update to an FK relation. This is
+  *	used by the AFTER trigger queue manager to detect "triggered data
+  *	change violation".
+  * ----------
+  */
+ bool
+ RI_FKey_keyequal_upd_fk(Trigger *trigger, Relation fk_rel, HeapTuple old_row, HeapTuple new_row)
+ {
+ 	int			tgnargs;
+ 	char	  **tgargs;
+ 	Relation	pk_rel;
+ 	RI_QueryKey qkey;
+ 
  	/*
! 	 * Check for the correct # of call arguments
  	 */
+ 	tgnargs = trigger->tgnargs;
+ 	tgargs = trigger->tgargs;
+ 	if (tgnargs < 4 ||
+ 		tgnargs > RI_MAX_ARGUMENTS ||
+ 		(tgnargs % 2) != 0)
+ 		ereport(ERROR,
+ 				(errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ 				 errmsg("function \"%s\" called with wrong number of trigger arguments",
+ 						"RI_FKey_keyequal_upd")));
+ 
+ 	/*
+ 	 * Nothing to do if no column names to compare given
+ 	 */
+ 	if (tgnargs == 4)
+ 		return true;
+ 
+ 	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(fk_rel)),
+ 				 errhint("Remove this referential integrity trigger and its mates, "
+ 						 "then do ALTER TABLE ADD CONSTRAINT.")));
+ 
+ 	pk_rel = heap_open(trigger->tgconstrrelid, AccessShareLock);
+ 
+ 	switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
+ 	{
+ 		case RI_MATCH_TYPE_UNSPECIFIED:
+ 		case RI_MATCH_TYPE_FULL:
+ 			ri_BuildQueryKeyFull(&qkey, trigger->tgoid,
+ 								 RI_PLAN_KEYEQUAL_UPD,
+ 								 fk_rel, pk_rel,
+ 								 tgnargs, tgargs);
+ 			heap_close(pk_rel, AccessShareLock);
+ 
+ 			/* Return if key's are equal */
+ 			return ri_KeysEqual(fk_rel, old_row, new_row, &qkey,
+ 								RI_KEYPAIR_FK_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;
  }
  
  /* ----------
   * RI_Initial_Check -
   *
***************
*** 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;
--- 2906,2912 ----
  	/*
  	 * 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);
--- 3524,3530 ----
  	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);
--- 3532,3538 ----
  			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]);
--- 3540,3546 ----
  			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]);
***************
*** 3644,3646 ****
--- 3679,3710 ----
  	return DatumGetBool(FunctionCall2(&(typentry->eq_opr_finfo),
  									  oldvalue, newvalue));
  }
+ 
+ /*
+  * Given a trigger function OID, determine whether it is an RI trigger,
+  * and if so whether it is attached to PK or FK relation.
+  */
+ int
+ RI_FKey_trigger_type(Oid tgfoid)
+ {
+ 	switch (tgfoid)
+ 	{
+ 		case F_RI_FKEY_CASCADE_DEL:
+ 		case F_RI_FKEY_CASCADE_UPD:
+ 		case F_RI_FKEY_RESTRICT_DEL:
+ 		case F_RI_FKEY_RESTRICT_UPD:
+ 		case F_RI_FKEY_SETNULL_DEL:
+ 		case F_RI_FKEY_SETNULL_UPD:
+ 		case F_RI_FKEY_SETDEFAULT_DEL:
+ 		case F_RI_FKEY_SETDEFAULT_UPD:
+ 		case F_RI_FKEY_NOACTION_DEL:
+ 		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;
+ 	}
+ 
+ 	return RI_TRIGGER_NONE;
+ }
Index: src/include/commands/trigger.h
===================================================================
RCS file: /var/lib/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	30 May 2005 02:48:37 -0000
***************
*** 168,176 ****
  /*
   * 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);
  
  #endif   /* TRIGGER_H */
--- 168,185 ----
  /*
   * in utils/adt/ri_triggers.c
   */
! extern bool RI_FKey_keyequal_upd_pk(Trigger *trigger, Relation pk_rel,
! 									HeapTuple old_row, HeapTuple new_row);
! extern bool RI_FKey_keyequal_upd_fk(Trigger *trigger, Relation fk_rel,
! 									HeapTuple old_row, HeapTuple new_row);
  extern bool RI_Initial_Check(FkConstraint *fkconstraint,
  				 Relation rel,
  				 Relation pkrel);
  
+ #define RI_TRIGGER_PK	1		/* is a trigger on the PK relation */
+ #define RI_TRIGGER_FK	2		/* is a trigger on the FK relation */
+ #define RI_TRIGGER_NONE 0		/* is not an RI trigger function */
+ 
+ extern int RI_FKey_trigger_type(Oid tgfoid);
+ 
  #endif   /* TRIGGER_H */
Index: src/test/regress/expected/foreign_key.out
===================================================================
RCS file: /var/lib/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	30 May 2005 02:38:06 -0000
***************
*** 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: /var/lib/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	30 May 2005 02:38:06 -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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to