ri_triggers.c 99.6 KB
Newer Older
1 2 3 4 5 6
/* ----------
 * ri_triggers.c
 *
 *	Generic trigger procedures for referential integrity constraint
 *	checks.
 *
7 8 9 10 11
 *	Note about memory management: the private hashtables kept here live
 *	across query and transaction boundaries, in fact they live as long as
 *	the backend does.  This works because the hashtable structures
 *	themselves are allocated by dynahash.c in its permanent DynaHashCxt,
 *	and the parse/plan node trees they point to are copied into
12
 *	TopMemoryContext using SPI_saveplan().	This is pretty ugly, since there
13 14 15
 *	is no way to free a no-longer-needed plan tree, but then again we don't
 *	yet have any bookkeeping that would allow us to detect that a plan isn't
 *	needed anymore.  Improve it someday.
16
 *
17
 *
Bruce Momjian's avatar
Bruce Momjian committed
18
 * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
19
 *
20
 * $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.46 2002/12/12 15:49:40 tgl Exp $
21 22 23 24
 *
 * ----------
 */

Jan Wieck's avatar
Jan Wieck committed
25 26 27 28

/* ----------
 * Internal TODO:
 *
Jan Wieck's avatar
Jan Wieck committed
29
 *		Add MATCH PARTIAL logic.
Jan Wieck's avatar
Jan Wieck committed
30 31 32
 * ----------
 */

33 34
#include "postgres.h"

35
#include "catalog/pg_operator.h"
36
#include "commands/trigger.h"
37
#include "executor/spi_priv.h"
38
#include "optimizer/planmain.h"
39
#include "parser/parse_oper.h"
40
#include "utils/lsyscache.h"
41
#include "miscadmin.h"
42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60


/* ----------
 * Local definitions
 * ----------
 */

#define RI_INIT_QUERYHASHSIZE			128
#define RI_INIT_OPREQHASHSIZE			128

#define RI_MATCH_TYPE_UNSPECIFIED		0
#define RI_MATCH_TYPE_FULL				1
#define RI_MATCH_TYPE_PARTIAL			2

#define RI_KEYS_ALL_NULL				0
#define RI_KEYS_SOME_NULL				1
#define RI_KEYS_NONE_NULL				2


Jan Wieck's avatar
Jan Wieck committed
61 62 63 64
#define RI_PLAN_CHECK_LOOKUPPK_NOCOLS	1
#define RI_PLAN_CHECK_LOOKUPPK			2
#define RI_PLAN_CASCADE_DEL_DODELETE	1
#define RI_PLAN_CASCADE_UPD_DOUPDATE	1
65 66
#define RI_PLAN_NOACTION_DEL_CHECKREF	1
#define RI_PLAN_NOACTION_UPD_CHECKREF	1
Jan Wieck's avatar
Jan Wieck committed
67 68
#define RI_PLAN_RESTRICT_DEL_CHECKREF	1
#define RI_PLAN_RESTRICT_UPD_CHECKREF	1
Jan Wieck's avatar
Jan Wieck committed
69 70
#define RI_PLAN_SETNULL_DEL_DOUPDATE	1
#define RI_PLAN_SETNULL_UPD_DOUPDATE	1
71

72 73 74
#define MAX_QUOTED_NAME_LEN  (NAMEDATALEN*2+3)
#define MAX_QUOTED_REL_NAME_LEN  (MAX_QUOTED_NAME_LEN*2)

75 76 77 78 79 80 81

/* ----------
 * RI_QueryKey
 *
 *	The key identifying a prepared SPI plan in our private hashtable
 * ----------
 */
82 83 84 85 86 87 88 89 90
typedef struct RI_QueryKey
{
	int32		constr_type;
	Oid			constr_id;
	int32		constr_queryno;
	Oid			fk_relid;
	Oid			pk_relid;
	int32		nkeypairs;
	int16		keypair[RI_MAX_NUMKEYS][2];
91 92 93 94 95 96 97
} RI_QueryKey;


/* ----------
 * RI_QueryHashEntry
 * ----------
 */
98 99 100 101
typedef struct RI_QueryHashEntry
{
	RI_QueryKey key;
	void	   *plan;
102 103 104
} RI_QueryHashEntry;


105 106 107 108
typedef struct RI_OpreqHashEntry
{
	Oid			typeid;
	FmgrInfo	oprfmgrinfo;
109 110 111 112 113 114 115 116
} RI_OpreqHashEntry;



/* ----------
 * Local data
 * ----------
 */
117 118
static HTAB *ri_query_cache = (HTAB *) NULL;
static HTAB *ri_opreq_cache = (HTAB *) NULL;
119 120 121 122 123 124


/* ----------
 * Local function prototypes
 * ----------
 */
125 126
static void quoteOneName(char *buffer, const char *name);
static void quoteRelationName(char *buffer, Relation rel);
127 128 129
static int	ri_DetermineMatchType(char *str);
static int ri_NullCheck(Relation rel, HeapTuple tup,
			 RI_QueryKey *key, int pairidx);
130
static void ri_BuildQueryKeyFull(RI_QueryKey *key, Oid constr_id,
131 132 133
					 int32 constr_queryno,
					 Relation fk_rel, Relation pk_rel,
					 int argc, char **argv);
134
static void ri_BuildQueryKeyPkCheck(RI_QueryKey *key, Oid constr_id,
Bruce Momjian's avatar
Bruce Momjian committed
135 136 137
						int32 constr_queryno,
						Relation pk_rel,
						int argc, char **argv);
138 139 140 141
static bool ri_KeysEqual(Relation rel, HeapTuple oldtup, HeapTuple newtup,
			 RI_QueryKey *key, int pairidx);
static bool ri_AllKeysUnequal(Relation rel, HeapTuple oldtup, HeapTuple newtup,
				  RI_QueryKey *key, int pairidx);
142
static bool ri_OneKeyEqual(Relation rel, int column, HeapTuple oldtup,
143
			   HeapTuple newtup, RI_QueryKey *key, int pairidx);
144
static bool ri_AttributesEqual(Oid typeid, Datum oldvalue, Datum newvalue);
Bruce Momjian's avatar
Bruce Momjian committed
145 146
static bool ri_Check_Pk_Match(Relation pk_rel, HeapTuple old_row,
				  Oid tgoid, int match_type, int tgnargs, char **tgargs);
147 148 149 150 151 152 153 154

static void ri_InitHashTables(void);
static void *ri_FetchPreparedPlan(RI_QueryKey *key);
static void ri_HashPreparedPlan(RI_QueryKey *key, void *plan);

/* ----------
 * RI_FKey_check -
 *
155
 *	Check foreign key existence (combined for INSERT and UPDATE).
156 157
 * ----------
 */
158 159
static Datum
RI_FKey_check(PG_FUNCTION_ARGS)
160
{
161
	TriggerData *trigdata = (TriggerData *) fcinfo->context;
162 163 164 165 166 167 168 169 170 171 172 173 174
	int			tgnargs;
	char	  **tgargs;
	Relation	fk_rel;
	Relation	pk_rel;
	HeapTuple	new_row;
	HeapTuple	old_row;
	RI_QueryKey qkey;
	void	   *qplan;
	Datum		check_values[RI_MAX_NUMKEYS];
	char		check_nulls[RI_MAX_NUMKEYS + 1];
	bool		isnull;
	int			i;
	int			match_type;
175
	AclId		save_uid;
176 177

	save_uid = GetUserId();
178

179
	ReferentialIntegritySnapshotOverride = true;
180

181 182 183
	/*
	 * Check that this is a valid trigger call on the right time and
	 * event.
184
	 */
185
	if (!CALLED_AS_TRIGGER(fcinfo))
186
		elog(ERROR, "RI_FKey_check() not fired by trigger manager");
187 188
	if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) ||
		!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
189 190
		elog(ERROR, "RI_FKey_check() must be fired AFTER ROW");
	if (!TRIGGER_FIRED_BY_INSERT(trigdata->tg_event) &&
191
		!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
192 193
		elog(ERROR, "RI_FKey_check() must be fired for INSERT or UPDATE");

194
	/*
195
	 * Check for the correct # of call arguments
196 197
	 */
	tgnargs = trigdata->tg_trigger->tgnargs;
198
	tgargs = trigdata->tg_trigger->tgargs;
199 200 201 202
	if (tgnargs < 4 || (tgnargs % 2) != 0)
		elog(ERROR, "wrong # of arguments in call to RI_FKey_check()");
	if (tgnargs > RI_MAX_ARGUMENTS)
		elog(ERROR, "too many keys (%d max) in call to RI_FKey_check()",
203
			 RI_MAX_NUMKEYS);
204

205 206 207
	/*
	 * Get the relation descriptors of the FK and PK tables and the new
	 * tuple.
208
	 *
Bruce Momjian's avatar
Bruce Momjian committed
209 210
	 * pk_rel is opened in RowShareLock mode since that's what our eventual
	 * SELECT FOR UPDATE will get on it.
211 212 213
	 *
	 * Error check here is needed because of ancient pg_dump bug; see notes
	 * in CreateTrigger().
214
	 */
215 216 217 218 219 220
	if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid))
		elog(ERROR, "No target table given for trigger \"%s\" on \"%s\""
			 "\n\tRemove these RI triggers and do ALTER TABLE ADD CONSTRAINT",
			 trigdata->tg_trigger->tgname,
			 RelationGetRelationName(trigdata->tg_relation));

221
	pk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowShareLock);
222
	fk_rel = trigdata->tg_relation;
223 224 225 226
	if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
	{
		old_row = trigdata->tg_trigtuple;
		new_row = trigdata->tg_newtuple;
227 228 229
	}
	else
	{
230 231 232 233
		old_row = NULL;
		new_row = trigdata->tg_trigtuple;
	}

234 235
	/*
	 * We should not even consider checking the row if it is no longer
Bruce Momjian's avatar
Bruce Momjian committed
236 237
	 * valid since it was either deleted (doesn't matter) or updated (in
	 * which case it'll be checked with its final values).
238
	 */
Bruce Momjian's avatar
Bruce Momjian committed
239 240 241 242
	if (new_row)
	{
		if (!HeapTupleSatisfiesItself(new_row->t_data))
		{
243 244 245 246
			heap_close(pk_rel, RowShareLock);
			return PointerGetDatum(NULL);
		}
	}
Bruce Momjian's avatar
Bruce Momjian committed
247

248 249 250 251 252
	/* ----------
	 * SQL3 11.9 <referential constraint definition>
	 *	Gereral rules 2) a):
	 *		If Rf and Rt are empty (no columns to compare given)
	 *		constraint is true if 0 < (SELECT COUNT(*) FROM T)
Jan Wieck's avatar
Jan Wieck committed
253 254 255 256
	 *
	 *	Note: The special case that no columns are given cannot
	 *		occur up to now in Postgres, it's just there for
	 *		future enhancements.
257 258
	 * ----------
	 */
259 260
	if (tgnargs == 4)
	{
Jan Wieck's avatar
Jan Wieck committed
261
		ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
262 263 264
							 RI_PLAN_CHECK_LOOKUPPK_NOCOLS,
							 fk_rel, pk_rel,
							 tgnargs, tgargs);
265 266 267

		if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
		{
268 269
			char		querystr[MAX_QUOTED_REL_NAME_LEN + 100];
			char		pkrelname[MAX_QUOTED_REL_NAME_LEN];
270

271
			/* ---------
272
			 * The query string built is
273
			 *	SELECT 1 FROM ONLY <pktable>
274 275
			 * ----------
			 */
276
			quoteRelationName(pkrelname, pk_rel);
277
			snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x FOR UPDATE OF x",
Bruce Momjian's avatar
Bruce Momjian committed
278
					 pkrelname);
279

280
			/*
281 282 283 284 285 286 287
			 * Prepare, save and remember the new plan.
			 */
			qplan = SPI_prepare(querystr, 0, NULL);
			qplan = SPI_saveplan(qplan);
			ri_HashPreparedPlan(&qkey, qplan);
		}

288
		/*
289 290 291
		 * Execute the plan
		 */
		if (SPI_connect() != SPI_OK_CONNECT)
Bruce Momjian's avatar
Bruce Momjian committed
292
			elog(WARNING, "SPI_connect() failed in RI_FKey_check()");
293

294 295
		SetUserId(RelationGetForm(pk_rel)->relowner);

296 297
		if (SPI_execp(qplan, check_values, check_nulls, 1) != SPI_OK_SELECT)
			elog(ERROR, "SPI_execp() failed in RI_FKey_check()");
298

299 300
		SetUserId(save_uid);

301 302
		if (SPI_processed == 0)
			elog(ERROR, "%s referential integrity violation - "
303 304
				 "no rows found in %s",
				 tgargs[RI_CONSTRAINT_NAME_ARGNO],
305
				 RelationGetRelationName(pk_rel));
306 307

		if (SPI_finish() != SPI_OK_FINISH)
Bruce Momjian's avatar
Bruce Momjian committed
308
			elog(WARNING, "SPI_finish() failed in RI_FKey_check()");
309

310 311
		heap_close(pk_rel, RowShareLock);

312
		return PointerGetDatum(NULL);
313 314 315

	}

316 317 318 319 320
	match_type = ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]);

	if (match_type == RI_MATCH_TYPE_PARTIAL)
	{
		elog(ERROR, "MATCH PARTIAL not yet supported");
321
		return PointerGetDatum(NULL);
322 323 324
	}

	ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
325 326
						 RI_PLAN_CHECK_LOOKUPPK, fk_rel, pk_rel,
						 tgnargs, tgargs);
327 328 329 330

	switch (ri_NullCheck(fk_rel, new_row, &qkey, RI_KEYPAIR_FK_IDX))
	{
		case RI_KEYS_ALL_NULL:
331 332 333 334

			/*
			 * No check - if NULLs are allowed at all is already checked
			 * by NOT NULL constraint.
335
			 *
336 337
			 * This is true for MATCH FULL, MATCH PARTIAL, and MATCH
			 * <unspecified>
338
			 */
339
			heap_close(pk_rel, RowShareLock);
340
			return PointerGetDatum(NULL);
341

342
		case RI_KEYS_SOME_NULL:
343 344 345 346

			/*
			 * This is the only case that differs between the three kinds
			 * of MATCH.
347 348 349 350
			 */
			switch (match_type)
			{
				case RI_MATCH_TYPE_FULL:
351 352 353 354

					/*
					 * Not allowed - MATCH FULL says either all or none of
					 * the attributes can be NULLs
355 356
					 */
					elog(ERROR, "%s referential integrity violation - "
357 358 359
						 "MATCH FULL doesn't allow mixing of NULL "
						 "and NON-NULL key values",
						 tgargs[RI_CONSTRAINT_NAME_ARGNO]);
360
					heap_close(pk_rel, RowShareLock);
361
					return PointerGetDatum(NULL);
362 363

				case RI_MATCH_TYPE_UNSPECIFIED:
364 365

					/*
366 367 368
					 * MATCH <unspecified> - if ANY column is null, we
					 * have a match.
					 */
369
					heap_close(pk_rel, RowShareLock);
370
					return PointerGetDatum(NULL);
371 372

				case RI_MATCH_TYPE_PARTIAL:
373 374

					/*
375
					 * MATCH PARTIAL - all non-null columns must match.
376 377
					 * (not implemented, can be done by modifying the
					 * query below to only include non-null columns, or by
378
					 * writing a special version here)
379 380
					 */
					elog(ERROR, "MATCH PARTIAL not yet implemented");
381
					heap_close(pk_rel, RowShareLock);
382
					return PointerGetDatum(NULL);
383 384 385
			}

		case RI_KEYS_NONE_NULL:
386 387

			/*
388
			 * Have a full qualified key - continue below for all three
389
			 * kinds of MATCH.
390 391 392 393
			 */
			break;
	}

394 395 396 397 398 399 400
	/*
	 * 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).
401 402
	 */
	if (SPI_connect() != SPI_OK_CONNECT)
Bruce Momjian's avatar
Bruce Momjian committed
403
		elog(WARNING, "SPI_connect() failed in RI_FKey_check()");
404

405

406
	/*
407 408 409 410
	 * Fetch or prepare a saved plan for the real check
	 */
	if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
	{
411 412 413 414 415
		char		querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
							(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
		char		pkrelname[MAX_QUOTED_REL_NAME_LEN];
		char		attname[MAX_QUOTED_NAME_LEN];
		const char *querysep;
416 417 418 419
		Oid			queryoids[RI_MAX_NUMKEYS];

		/* ----------
		 * The query string built is
420
		 *	SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...]
421 422 423 424 425 426
		 * The type id's for the $ parameters are those of the
		 * corresponding FK attributes. Thus, SPI_prepare could
		 * eventually fail if the parser cannot identify some way
		 * how to compare these two types by '='.
		 * ----------
		 */
427
		quoteRelationName(pkrelname, pk_rel);
428
		snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x", pkrelname);
429 430 431
		querysep = "WHERE";
		for (i = 0; i < qkey.nkeypairs; i++)
		{
432
			quoteOneName(attname,
Bruce Momjian's avatar
Bruce Momjian committed
433
			 tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_PK_IDX]);
434
			snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), " %s %s = $%d",
Bruce Momjian's avatar
Bruce Momjian committed
435
					 querysep, attname, i + 1);
436 437
			querysep = "AND";
			queryoids[i] = SPI_gettypeid(fk_rel->rd_att,
438
									 qkey.keypair[i][RI_KEYPAIR_FK_IDX]);
439
		}
440
		strcat(querystr, " FOR UPDATE OF x");
441

442
		/*
443 444 445 446 447 448 449
		 * Prepare, save and remember the new plan.
		 */
		qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
		qplan = SPI_saveplan(qplan);
		ri_HashPreparedPlan(&qkey, qplan);
	}

450 451 452
	/*
	 * We have a plan now. Build up the arguments for SPI_execp() from the
	 * key values in the new FK tuple.
453 454 455
	 */
	for (i = 0; i < qkey.nkeypairs; i++)
	{
456
		/*
457
		 * We can implement MATCH PARTIAL by excluding this column from
458
		 * the query if it is null.  Simple!  Unfortunately, the
459 460
		 * referential actions aren't so I've not bothered to do so for
		 * the moment.
461
		 */
462

463
		check_values[i] = SPI_getbinval(new_row,
464 465 466 467
										fk_rel->rd_att,
									  qkey.keypair[i][RI_KEYPAIR_FK_IDX],
										&isnull);
		if (isnull)
468 469 470 471 472 473
			check_nulls[i] = 'n';
		else
			check_nulls[i] = ' ';
	}
	check_nulls[i] = '\0';

474
	/*
475 476
	 * Now check that foreign key exists in PK table
	 */
477 478 479

	SetUserId(RelationGetForm(pk_rel)->relowner);

480 481
	if (SPI_execp(qplan, check_values, check_nulls, 1) != SPI_OK_SELECT)
		elog(ERROR, "SPI_execp() failed in RI_FKey_check()");
482

483 484
	SetUserId(save_uid);

485 486
	if (SPI_processed == 0)
		elog(ERROR, "%s referential integrity violation - "
487 488
			 "key referenced from %s not found in %s",
			 tgargs[RI_CONSTRAINT_NAME_ARGNO],
489 490
			 RelationGetRelationName(fk_rel),
			 RelationGetRelationName(pk_rel));
491 492

	if (SPI_finish() != SPI_OK_FINISH)
Bruce Momjian's avatar
Bruce Momjian committed
493
		elog(WARNING, "SPI_finish() failed in RI_FKey_check()");
494

495 496
	heap_close(pk_rel, RowShareLock);

497
	return PointerGetDatum(NULL);
498

499
	/*
500 501 502
	 * Never reached
	 */
	elog(ERROR, "internal error #1 in ri_triggers.c");
503
	return PointerGetDatum(NULL);
504 505 506 507 508 509
}


/* ----------
 * RI_FKey_check_ins -
 *
510
 *	Check foreign key existence at insert event on FK table.
511 512
 * ----------
 */
513 514
Datum
RI_FKey_check_ins(PG_FUNCTION_ARGS)
515
{
516
	return RI_FKey_check(fcinfo);
517 518 519 520 521 522
}


/* ----------
 * RI_FKey_check_upd -
 *
523
 *	Check foreign key existence at update event on FK table.
524 525
 * ----------
 */
526 527
Datum
RI_FKey_check_upd(PG_FUNCTION_ARGS)
528
{
529
	return RI_FKey_check(fcinfo);
530 531 532
}


533 534 535
/* ----------
 * ri_Check_Pk_Match
 *
Bruce Momjian's avatar
Bruce Momjian committed
536
 *	Check for matching value of old pk row in current state for
537 538 539 540 541
 * noaction triggers. Returns false if no row was found and a fk row
 * could potentially be referencing this row, true otherwise.
 * ----------
 */
static bool
Bruce Momjian's avatar
Bruce Momjian committed
542 543 544
ri_Check_Pk_Match(Relation pk_rel, HeapTuple old_row, Oid tgoid, int match_type, int tgnargs, char **tgargs)
{
	void	   *qplan;
545
	RI_QueryKey qkey;
Bruce Momjian's avatar
Bruce Momjian committed
546
	bool		isnull;
547 548
	Datum		check_values[RI_MAX_NUMKEYS];
	char		check_nulls[RI_MAX_NUMKEYS + 1];
Bruce Momjian's avatar
Bruce Momjian committed
549
	int			i;
550
	AclId		save_uid;
Bruce Momjian's avatar
Bruce Momjian committed
551 552
	bool		result;

553 554 555
	save_uid = GetUserId();

	ri_BuildQueryKeyPkCheck(&qkey, tgoid,
Bruce Momjian's avatar
Bruce Momjian committed
556 557
							RI_PLAN_CHECK_LOOKUPPK, pk_rel,
							tgnargs, tgargs);
558 559 560 561

	switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
	{
		case RI_KEYS_ALL_NULL:
Bruce Momjian's avatar
Bruce Momjian committed
562

563
			/*
Bruce Momjian's avatar
Bruce Momjian committed
564 565
			 * No check - nothing could have been referencing this row
			 * anyway.
566 567 568 569 570 571 572 573 574 575 576 577 578
			 */
			return true;

		case RI_KEYS_SOME_NULL:

			/*
			 * This is the only case that differs between the three kinds
			 * of MATCH.
			 */
			switch (match_type)
			{
				case RI_MATCH_TYPE_FULL:
				case RI_MATCH_TYPE_UNSPECIFIED:
Bruce Momjian's avatar
Bruce Momjian committed
579

580
					/*
Bruce Momjian's avatar
Bruce Momjian committed
581 582
					 * MATCH <unspecified>/FULL  - if ANY column is null,
					 * we can't be matching to this row already.
583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632
					 */
					return true;

				case RI_MATCH_TYPE_PARTIAL:

					/*
					 * MATCH PARTIAL - all non-null columns must match.
					 * (not implemented, can be done by modifying the
					 * query below to only include non-null columns, or by
					 * writing a special version here)
					 */
					elog(ERROR, "MATCH PARTIAL not yet implemented");
					break;
			}

		case RI_KEYS_NONE_NULL:

			/*
			 * Have a full qualified key - continue below for all three
			 * kinds of MATCH.
			 */
			break;
	}

	if (SPI_connect() != SPI_OK_CONNECT)
		elog(WARNING, "SPI_connect() failed in RI_FKey_check()");


	/*
	 * Fetch or prepare a saved plan for the real check
	 */
	if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
	{
		char		querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
							(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
		char		pkrelname[MAX_QUOTED_REL_NAME_LEN];
		char		attname[MAX_QUOTED_NAME_LEN];
		const char *querysep;
		Oid			queryoids[RI_MAX_NUMKEYS];

		/* ----------
		 * The query string built is
		 *	SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...]
		 * The type id's for the $ parameters are those of the
		 * corresponding FK attributes. Thus, SPI_prepare could
		 * eventually fail if the parser cannot identify some way
		 * how to compare these two types by '='.
		 * ----------
		 */
		quoteRelationName(pkrelname, pk_rel);
633
		snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x", pkrelname);
634 635 636 637
		querysep = "WHERE";
		for (i = 0; i < qkey.nkeypairs; i++)
		{
			quoteOneName(attname,
Bruce Momjian's avatar
Bruce Momjian committed
638
			 tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_PK_IDX]);
639
			snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), " %s %s = $%d",
Bruce Momjian's avatar
Bruce Momjian committed
640
					 querysep, attname, i + 1);
641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662
			querysep = "AND";
			queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
									 qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
		}
		strcat(querystr, " FOR UPDATE OF x");

		/*
		 * Prepare, save and remember the new plan.
		 */
		qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
		qplan = SPI_saveplan(qplan);
		ri_HashPreparedPlan(&qkey, qplan);
	}

	/*
	 * We have a plan now. Build up the arguments for SPI_execp() from the
	 * key values in the new FK tuple.
	 */
	for (i = 0; i < qkey.nkeypairs; i++)
	{
		check_values[i] = SPI_getbinval(old_row,
										pk_rel->rd_att,
Bruce Momjian's avatar
Bruce Momjian committed
663
									  qkey.keypair[i][RI_KEYPAIR_PK_IDX],
664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682
										&isnull);
		if (isnull)
			check_nulls[i] = 'n';
		else
			check_nulls[i] = ' ';
	}
	check_nulls[i] = '\0';

	/*
	 * Now check that foreign key exists in PK table
	 */

	SetUserId(RelationGetForm(pk_rel)->relowner);

	if (SPI_execp(qplan, check_values, check_nulls, 1) != SPI_OK_SELECT)
		elog(ERROR, "SPI_execp() failed in ri_Check_Pk_Match()");

	SetUserId(save_uid);

Bruce Momjian's avatar
Bruce Momjian committed
683
	result = (SPI_processed != 0);
684 685 686 687 688 689 690 691

	if (SPI_finish() != SPI_OK_FINISH)
		elog(WARNING, "SPI_finish() failed in ri_Check_Pk_Match()");

	return result;
}


692 693 694 695
/* ----------
 * RI_FKey_noaction_del -
 *
 *	Give an error and roll back the current transaction if the
696 697
 *	delete has resulted in a violation of the given referential
 *	integrity constraint.
698 699
 * ----------
 */
700 701
Datum
RI_FKey_noaction_del(PG_FUNCTION_ARGS)
702
{
703
	TriggerData *trigdata = (TriggerData *) fcinfo->context;
704 705 706 707 708 709 710 711 712 713 714
	int			tgnargs;
	char	  **tgargs;
	Relation	fk_rel;
	Relation	pk_rel;
	HeapTuple	old_row;
	RI_QueryKey qkey;
	void	   *qplan;
	Datum		del_values[RI_MAX_NUMKEYS];
	char		del_nulls[RI_MAX_NUMKEYS + 1];
	bool		isnull;
	int			i;
715
	int			match_type;
716
	AclId		save_uid;
717 718

	save_uid = GetUserId();
719 720 721

	ReferentialIntegritySnapshotOverride = true;

722 723 724
	/*
	 * Check that this is a valid trigger call on the right time and
	 * event.
725
	 */
726
	if (!CALLED_AS_TRIGGER(fcinfo))
727
		elog(ERROR, "RI_FKey_noaction_del() not fired by trigger manager");
728 729
	if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) ||
		!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
730 731 732 733
		elog(ERROR, "RI_FKey_noaction_del() must be fired AFTER ROW");
	if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
		elog(ERROR, "RI_FKey_noaction_del() must be fired for DELETE");

734
	/*
735
	 * Check for the correct # of call arguments
736 737
	 */
	tgnargs = trigdata->tg_trigger->tgnargs;
738
	tgargs = trigdata->tg_trigger->tgargs;
739 740 741 742
	if (tgnargs < 4 || (tgnargs % 2) != 0)
		elog(ERROR, "wrong # of arguments in call to RI_FKey_noaction_del()");
	if (tgnargs > RI_MAX_ARGUMENTS)
		elog(ERROR, "too many keys (%d max) in call to RI_FKey_noaction_del()",
743
			 RI_MAX_NUMKEYS);
744

745
	/*
746 747 748
	 * Nothing to do if no column names to compare given
	 */
	if (tgnargs == 4)
749
		return PointerGetDatum(NULL);
750

751 752 753
	/*
	 * Get the relation descriptors of the FK and PK tables and the old
	 * tuple.
754
	 *
Bruce Momjian's avatar
Bruce Momjian committed
755 756
	 * fk_rel is opened in RowShareLock mode since that's what our eventual
	 * SELECT FOR UPDATE will get on it.
757
	 */
758 759 760 761 762 763
	if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid))
		elog(ERROR, "No target table given for trigger \"%s\" on \"%s\""
			 "\n\tRemove these RI triggers and do ALTER TABLE ADD CONSTRAINT",
			 trigdata->tg_trigger->tgname,
			 RelationGetRelationName(trigdata->tg_relation));

764
	fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowShareLock);
765
	pk_rel = trigdata->tg_relation;
766 767
	old_row = trigdata->tg_trigtuple;

768 769
	match_type = ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]);
	if (ri_Check_Pk_Match(pk_rel, old_row, trigdata->tg_trigger->tgoid,
Bruce Momjian's avatar
Bruce Momjian committed
770 771 772 773 774
						  match_type, tgnargs, tgargs))
	{
		/*
		 * There's either another row, or no row could match this one.  In
		 * either case, we don't need to do the check.
775 776 777 778 779 780
		 */
		heap_close(fk_rel, RowShareLock);
		return PointerGetDatum(NULL);
	}

	switch (match_type)
781
	{
782 783 784 785 786 787 788
			/* ----------
			 * SQL3 11.9 <referential constraint definition>
			 *	Gereral rules 6) a) iv):
			 *		MATCH <unspecified> or MATCH FULL
			 *			... ON DELETE CASCADE
			 * ----------
			 */
789
		case RI_MATCH_TYPE_UNSPECIFIED:
790 791
		case RI_MATCH_TYPE_FULL:
			ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
792 793 794
								 RI_PLAN_NOACTION_DEL_CHECKREF,
								 fk_rel, pk_rel,
								 tgnargs, tgargs);
795 796 797 798 799

			switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
			{
				case RI_KEYS_ALL_NULL:
				case RI_KEYS_SOME_NULL:
800 801

					/*
802 803 804
					 * No check - MATCH FULL means there cannot be any
					 * reference to old key if it contains NULL
					 */
805
					heap_close(fk_rel, RowShareLock);
806
					return PointerGetDatum(NULL);
807

808
				case RI_KEYS_NONE_NULL:
809 810

					/*
811 812 813 814 815 816
					 * Have a full qualified key - continue below
					 */
					break;
			}

			if (SPI_connect() != SPI_OK_CONNECT)
Bruce Momjian's avatar
Bruce Momjian committed
817
				elog(WARNING, "SPI_connect() failed in RI_FKey_noaction_del()");
818

819
			/*
820 821 822 823 824
			 * Fetch or prepare a saved plan for the restrict delete
			 * lookup if foreign references exist
			 */
			if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
			{
825
				char		querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
Bruce Momjian's avatar
Bruce Momjian committed
826
							(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
827 828 829
				char		fkrelname[MAX_QUOTED_REL_NAME_LEN];
				char		attname[MAX_QUOTED_NAME_LEN];
				const char *querysep;
830 831 832 833
				Oid			queryoids[RI_MAX_NUMKEYS];

				/* ----------
				 * The query string built is
834
				 *	SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
835 836 837 838 839 840
				 * The type id's for the $ parameters are those of the
				 * corresponding PK attributes. Thus, SPI_prepare could
				 * eventually fail if the parser cannot identify some way
				 * how to compare these two types by '='.
				 * ----------
				 */
841
				quoteRelationName(fkrelname, fk_rel);
842
				snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x", fkrelname);
843 844 845
				querysep = "WHERE";
				for (i = 0; i < qkey.nkeypairs; i++)
				{
846 847
					quoteOneName(attname,
								 tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
848
					snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), " %s %s = $%d",
Bruce Momjian's avatar
Bruce Momjian committed
849
							 querysep, attname, i + 1);
850 851
					querysep = "AND";
					queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
852
									 qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
853
				}
854
				strcat(querystr, " FOR UPDATE OF x");
855

856
				/*
857 858 859 860 861 862 863
				 * Prepare, save and remember the new plan.
				 */
				qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
				qplan = SPI_saveplan(qplan);
				ri_HashPreparedPlan(&qkey, qplan);
			}

864
			/*
865 866 867 868 869 870
			 * We have a plan now. Build up the arguments for SPI_execp()
			 * from the key values in the deleted PK tuple.
			 */
			for (i = 0; i < qkey.nkeypairs; i++)
			{
				del_values[i] = SPI_getbinval(old_row,
871 872 873 874
											  pk_rel->rd_att,
									  qkey.keypair[i][RI_KEYPAIR_PK_IDX],
											  &isnull);
				if (isnull)
875 876 877 878 879 880
					del_nulls[i] = 'n';
				else
					del_nulls[i] = ' ';
			}
			del_nulls[i] = '\0';

881
			/*
882 883
			 * Now check for existing references
			 */
884 885
			SetUserId(RelationGetForm(pk_rel)->relowner);

886 887
			if (SPI_execp(qplan, del_values, del_nulls, 1) != SPI_OK_SELECT)
				elog(ERROR, "SPI_execp() failed in RI_FKey_noaction_del()");
888

889 890
			SetUserId(save_uid);

891 892
			if (SPI_processed > 0)
				elog(ERROR, "%s referential integrity violation - "
893 894
					 "key in %s still referenced from %s",
					 tgargs[RI_CONSTRAINT_NAME_ARGNO],
895 896
					 RelationGetRelationName(pk_rel),
					 RelationGetRelationName(fk_rel));
897 898

			if (SPI_finish() != SPI_OK_FINISH)
Bruce Momjian's avatar
Bruce Momjian committed
899
				elog(WARNING, "SPI_finish() failed in RI_FKey_noaction_del()");
900

901 902
			heap_close(fk_rel, RowShareLock);

903
			return PointerGetDatum(NULL);
904

905
			/*
906 907
			 * Handle MATCH PARTIAL restrict delete.
			 */
908 909
		case RI_MATCH_TYPE_PARTIAL:
			elog(ERROR, "MATCH PARTIAL not yet supported");
910
			return PointerGetDatum(NULL);
911 912
	}

913
	/*
914 915 916
	 * Never reached
	 */
	elog(ERROR, "internal error #2 in ri_triggers.c");
917
	return PointerGetDatum(NULL);
918 919 920 921 922 923 924
}


/* ----------
 * RI_FKey_noaction_upd -
 *
 *	Give an error and roll back the current transaction if the
925 926
 *	update has resulted in a violation of the given referential
 *	integrity constraint.
927 928
 * ----------
 */
929 930
Datum
RI_FKey_noaction_upd(PG_FUNCTION_ARGS)
931
{
932
	TriggerData *trigdata = (TriggerData *) fcinfo->context;
933 934 935 936 937 938 939 940 941 942 943 944
	int			tgnargs;
	char	  **tgargs;
	Relation	fk_rel;
	Relation	pk_rel;
	HeapTuple	new_row;
	HeapTuple	old_row;
	RI_QueryKey qkey;
	void	   *qplan;
	Datum		upd_values[RI_MAX_NUMKEYS];
	char		upd_nulls[RI_MAX_NUMKEYS + 1];
	bool		isnull;
	int			i;
945
	int			match_type;
946
	AclId		save_uid;
947 948

	save_uid = GetUserId();
949 950 951

	ReferentialIntegritySnapshotOverride = true;

952 953 954
	/*
	 * Check that this is a valid trigger call on the right time and
	 * event.
955
	 */
956
	if (!CALLED_AS_TRIGGER(fcinfo))
957
		elog(ERROR, "RI_FKey_noaction_upd() not fired by trigger manager");
958 959
	if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) ||
		!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
960 961 962 963
		elog(ERROR, "RI_FKey_noaction_upd() must be fired AFTER ROW");
	if (!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
		elog(ERROR, "RI_FKey_noaction_upd() must be fired for UPDATE");

964
	/*
965
	 * Check for the correct # of call arguments
966 967
	 */
	tgnargs = trigdata->tg_trigger->tgnargs;
968
	tgargs = trigdata->tg_trigger->tgargs;
969 970 971 972
	if (tgnargs < 4 || (tgnargs % 2) != 0)
		elog(ERROR, "wrong # of arguments in call to RI_FKey_noaction_upd()");
	if (tgnargs > RI_MAX_ARGUMENTS)
		elog(ERROR, "too many keys (%d max) in call to RI_FKey_noaction_upd()",
973
			 RI_MAX_NUMKEYS);
974

975
	/*
976 977 978
	 * Nothing to do if no column names to compare given
	 */
	if (tgnargs == 4)
979
		return PointerGetDatum(NULL);
980

981 982 983
	/*
	 * Get the relation descriptors of the FK and PK tables and the new
	 * and old tuple.
984
	 *
Bruce Momjian's avatar
Bruce Momjian committed
985 986
	 * fk_rel is opened in RowShareLock mode since that's what our eventual
	 * SELECT FOR UPDATE will get on it.
987
	 */
988 989 990 991 992 993
	if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid))
		elog(ERROR, "No target table given for trigger \"%s\" on \"%s\""
			 "\n\tRemove these RI triggers and do ALTER TABLE ADD CONSTRAINT",
			 trigdata->tg_trigger->tgname,
			 RelationGetRelationName(trigdata->tg_relation));

994
	fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowShareLock);
995
	pk_rel = trigdata->tg_relation;
996 997
	new_row = trigdata->tg_newtuple;
	old_row = trigdata->tg_trigtuple;
998

999 1000
	match_type = ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]);
	if (ri_Check_Pk_Match(pk_rel, old_row, trigdata->tg_trigger->tgoid,
Bruce Momjian's avatar
Bruce Momjian committed
1001 1002 1003 1004 1005
						  match_type, tgnargs, tgargs))
	{
		/*
		 * There's either another row, or no row could match this one.  In
		 * either case, we don't need to do the check.
1006 1007 1008 1009 1010 1011
		 */
		heap_close(fk_rel, RowShareLock);
		return PointerGetDatum(NULL);
	}

	switch (match_type)
1012
	{
1013 1014 1015 1016 1017 1018 1019
			/* ----------
			 * SQL3 11.9 <referential constraint definition>
			 *	Gereral rules 6) a) iv):
			 *		MATCH <unspecified> or MATCH FULL
			 *			... ON DELETE CASCADE
			 * ----------
			 */
1020
		case RI_MATCH_TYPE_UNSPECIFIED:
1021
		case RI_MATCH_TYPE_FULL:
Jan Wieck's avatar
Jan Wieck committed
1022
			ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
1023 1024 1025
								 RI_PLAN_NOACTION_UPD_CHECKREF,
								 fk_rel, pk_rel,
								 tgnargs, tgargs);
1026

1027
			switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
1028 1029
			{
				case RI_KEYS_ALL_NULL:
1030
				case RI_KEYS_SOME_NULL:
1031 1032

					/*
1033 1034
					 * No check - MATCH FULL means there cannot be any
					 * reference to old key if it contains NULL
1035
					 */
1036
					heap_close(fk_rel, RowShareLock);
1037
					return PointerGetDatum(NULL);
1038

1039
				case RI_KEYS_NONE_NULL:
1040 1041

					/*
1042 1043 1044 1045 1046
					 * Have a full qualified key - continue below
					 */
					break;
			}

1047
			/*
1048
			 * No need to check anything if old and new keys are equal
1049
			 */
1050
			if (ri_KeysEqual(pk_rel, old_row, new_row, &qkey,
1051
							 RI_KEYPAIR_PK_IDX))
1052 1053
			{
				heap_close(fk_rel, RowShareLock);
1054
				return PointerGetDatum(NULL);
1055
			}
1056

1057
			if (SPI_connect() != SPI_OK_CONNECT)
Bruce Momjian's avatar
Bruce Momjian committed
1058
				elog(WARNING, "SPI_connect() failed in RI_FKey_noaction_upd()");
1059

1060
			/*
1061 1062
			 * Fetch or prepare a saved plan for the noaction update
			 * lookup if foreign references exist
1063 1064 1065
			 */
			if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
			{
1066
				char		querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
Bruce Momjian's avatar
Bruce Momjian committed
1067
							(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
1068 1069 1070
				char		fkrelname[MAX_QUOTED_REL_NAME_LEN];
				char		attname[MAX_QUOTED_NAME_LEN];
				const char *querysep;
1071 1072 1073 1074
				Oid			queryoids[RI_MAX_NUMKEYS];

				/* ----------
				 * The query string built is
1075
				 *	SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
1076
				 * The type id's for the $ parameters are those of the
1077
				 * corresponding PK attributes. Thus, SPI_prepare could
1078 1079 1080 1081
				 * eventually fail if the parser cannot identify some way
				 * how to compare these two types by '='.
				 * ----------
				 */
1082
				quoteRelationName(fkrelname, fk_rel);
1083
				snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x", fkrelname);
1084 1085 1086
				querysep = "WHERE";
				for (i = 0; i < qkey.nkeypairs; i++)
				{
1087 1088
					quoteOneName(attname,
								 tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
1089
					snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), " %s %s = $%d",
Bruce Momjian's avatar
Bruce Momjian committed
1090
							 querysep, attname, i + 1);
1091
					querysep = "AND";
1092
					queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
1093
									 qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
1094
				}
1095
				strcat(querystr, " FOR UPDATE OF x");
1096

1097
				/*
1098 1099 1100 1101 1102 1103 1104
				 * Prepare, save and remember the new plan.
				 */
				qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
				qplan = SPI_saveplan(qplan);
				ri_HashPreparedPlan(&qkey, qplan);
			}

1105
			/*
1106
			 * We have a plan now. Build up the arguments for SPI_execp()
1107
			 * from the key values in the updated PK tuple.
1108 1109 1110
			 */
			for (i = 0; i < qkey.nkeypairs; i++)
			{
1111
				upd_values[i] = SPI_getbinval(old_row,
1112 1113 1114 1115
											  pk_rel->rd_att,
									  qkey.keypair[i][RI_KEYPAIR_PK_IDX],
											  &isnull);
				if (isnull)
1116
					upd_nulls[i] = 'n';
1117
				else
1118
					upd_nulls[i] = ' ';
1119
			}
1120
			upd_nulls[i] = '\0';
1121

1122
			/*
1123
			 * Now check for existing references
1124
			 */
1125 1126
			SetUserId(RelationGetForm(pk_rel)->relowner);

1127 1128
			if (SPI_execp(qplan, upd_values, upd_nulls, 1) != SPI_OK_SELECT)
				elog(ERROR, "SPI_execp() failed in RI_FKey_noaction_upd()");
1129

1130 1131
			SetUserId(save_uid);

1132
			if (SPI_processed > 0)
1133
				elog(ERROR, "%s referential integrity violation - "
1134 1135
					 "key in %s still referenced from %s",
					 tgargs[RI_CONSTRAINT_NAME_ARGNO],
1136 1137
					 RelationGetRelationName(pk_rel),
					 RelationGetRelationName(fk_rel));
1138 1139

			if (SPI_finish() != SPI_OK_FINISH)
Bruce Momjian's avatar
Bruce Momjian committed
1140
				elog(WARNING, "SPI_finish() failed in RI_FKey_noaction_upd()");
1141

1142 1143
			heap_close(fk_rel, RowShareLock);

1144
			return PointerGetDatum(NULL);
1145

1146
			/*
1147 1148
			 * Handle MATCH PARTIAL noaction update.
			 */
1149 1150
		case RI_MATCH_TYPE_PARTIAL:
			elog(ERROR, "MATCH PARTIAL not yet supported");
1151
			return PointerGetDatum(NULL);
1152 1153
	}

1154
	/*
1155 1156
	 * Never reached
	 */
1157
	elog(ERROR, "internal error #3 in ri_triggers.c");
1158
	return PointerGetDatum(NULL);
1159 1160 1161
}


1162 1163 1164 1165 1166 1167
/* ----------
 * RI_FKey_cascade_del -
 *
 *	Cascaded delete foreign key references at delete event on PK table.
 * ----------
 */
1168 1169
Datum
RI_FKey_cascade_del(PG_FUNCTION_ARGS)
1170
{
1171
	TriggerData *trigdata = (TriggerData *) fcinfo->context;
1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182
	int			tgnargs;
	char	  **tgargs;
	Relation	fk_rel;
	Relation	pk_rel;
	HeapTuple	old_row;
	RI_QueryKey qkey;
	void	   *qplan;
	Datum		del_values[RI_MAX_NUMKEYS];
	char		del_nulls[RI_MAX_NUMKEYS + 1];
	bool		isnull;
	int			i;
1183 1184
	AclId		save_uid;
	AclId		fk_owner;
1185

1186
	ReferentialIntegritySnapshotOverride = true;
1187

1188 1189 1190
	/*
	 * Check that this is a valid trigger call on the right time and
	 * event.
1191
	 */
1192
	if (!CALLED_AS_TRIGGER(fcinfo))
1193
		elog(ERROR, "RI_FKey_cascade_del() not fired by trigger manager");
1194 1195
	if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) ||
		!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
1196 1197 1198 1199
		elog(ERROR, "RI_FKey_cascade_del() must be fired AFTER ROW");
	if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
		elog(ERROR, "RI_FKey_cascade_del() must be fired for DELETE");

1200
	/*
1201
	 * Check for the correct # of call arguments
1202 1203
	 */
	tgnargs = trigdata->tg_trigger->tgnargs;
1204
	tgargs = trigdata->tg_trigger->tgargs;
1205 1206 1207 1208
	if (tgnargs < 4 || (tgnargs % 2) != 0)
		elog(ERROR, "wrong # of arguments in call to RI_FKey_cascade_del()");
	if (tgnargs > RI_MAX_ARGUMENTS)
		elog(ERROR, "too many keys (%d max) in call to RI_FKey_cascade_del()",
1209
			 RI_MAX_NUMKEYS);
1210

1211
	/*
1212 1213 1214
	 * Nothing to do if no column names to compare given
	 */
	if (tgnargs == 4)
1215
		return PointerGetDatum(NULL);
1216

1217 1218 1219
	/*
	 * Get the relation descriptors of the FK and PK tables and the old
	 * tuple.
1220 1221 1222
	 *
	 * fk_rel is opened in RowExclusiveLock mode since that's what our
	 * eventual DELETE will get on it.
1223
	 */
1224 1225 1226 1227 1228 1229
	if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid))
		elog(ERROR, "No target table given for trigger \"%s\" on \"%s\""
			 "\n\tRemove these RI triggers and do ALTER TABLE ADD CONSTRAINT",
			 trigdata->tg_trigger->tgname,
			 RelationGetRelationName(trigdata->tg_relation));

1230
	fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowExclusiveLock);
1231
	pk_rel = trigdata->tg_relation;
1232
	old_row = trigdata->tg_trigtuple;
1233
	fk_owner = RelationGetForm(fk_rel)->relowner;
1234 1235 1236

	switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
	{
1237 1238 1239 1240 1241 1242 1243
			/* ----------
			 * SQL3 11.9 <referential constraint definition>
			 *	Gereral rules 6) a) i):
			 *		MATCH <unspecified> or MATCH FULL
			 *			... ON DELETE CASCADE
			 * ----------
			 */
1244 1245
		case RI_MATCH_TYPE_UNSPECIFIED:
		case RI_MATCH_TYPE_FULL:
Jan Wieck's avatar
Jan Wieck committed
1246
			ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
1247 1248 1249
								 RI_PLAN_CASCADE_DEL_DODELETE,
								 fk_rel, pk_rel,
								 tgnargs, tgargs);
1250 1251 1252 1253 1254

			switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
			{
				case RI_KEYS_ALL_NULL:
				case RI_KEYS_SOME_NULL:
1255 1256

					/*
1257 1258 1259
					 * No check - MATCH FULL means there cannot be any
					 * reference to old key if it contains NULL
					 */
1260
					heap_close(fk_rel, RowExclusiveLock);
1261
					return PointerGetDatum(NULL);
1262

1263
				case RI_KEYS_NONE_NULL:
1264 1265

					/*
1266 1267 1268 1269 1270 1271
					 * Have a full qualified key - continue below
					 */
					break;
			}

			if (SPI_connect() != SPI_OK_CONNECT)
Bruce Momjian's avatar
Bruce Momjian committed
1272
				elog(WARNING, "SPI_connect() failed in RI_FKey_cascade_del()");
1273

1274
			/*
1275 1276 1277 1278
			 * Fetch or prepare a saved plan for the cascaded delete
			 */
			if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
			{
1279
				char		querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
Bruce Momjian's avatar
Bruce Momjian committed
1280
							(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
1281 1282 1283
				char		fkrelname[MAX_QUOTED_REL_NAME_LEN];
				char		attname[MAX_QUOTED_NAME_LEN];
				const char *querysep;
1284 1285 1286 1287
				Oid			queryoids[RI_MAX_NUMKEYS];

				/* ----------
				 * The query string built is
1288
				 *	DELETE FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
1289 1290 1291 1292 1293 1294
				 * The type id's for the $ parameters are those of the
				 * corresponding PK attributes. Thus, SPI_prepare could
				 * eventually fail if the parser cannot identify some way
				 * how to compare these two types by '='.
				 * ----------
				 */
1295
				quoteRelationName(fkrelname, fk_rel);
1296
				snprintf(querystr, sizeof(querystr), "DELETE FROM ONLY %s", fkrelname);
1297 1298 1299
				querysep = "WHERE";
				for (i = 0; i < qkey.nkeypairs; i++)
				{
1300 1301
					quoteOneName(attname,
								 tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
1302
					snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), " %s %s = $%d",
Bruce Momjian's avatar
Bruce Momjian committed
1303
							 querysep, attname, i + 1);
1304 1305
					querysep = "AND";
					queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
1306
									 qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
1307 1308
				}

1309
				/*
1310 1311 1312 1313 1314 1315 1316
				 * Prepare, save and remember the new plan.
				 */
				qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
				qplan = SPI_saveplan(qplan);
				ri_HashPreparedPlan(&qkey, qplan);
			}

1317
			/*
1318 1319 1320 1321 1322 1323
			 * We have a plan now. Build up the arguments for SPI_execp()
			 * from the key values in the deleted PK tuple.
			 */
			for (i = 0; i < qkey.nkeypairs; i++)
			{
				del_values[i] = SPI_getbinval(old_row,
1324 1325 1326 1327
											  pk_rel->rd_att,
									  qkey.keypair[i][RI_KEYPAIR_PK_IDX],
											  &isnull);
				if (isnull)
1328 1329 1330 1331
					del_nulls[i] = 'n';
				else
					del_nulls[i] = ' ';
			}
Jan Wieck's avatar
Jan Wieck committed
1332
			del_nulls[i] = '\0';
1333

1334
			/*
1335 1336
			 * Now delete constraint
			 */
1337 1338 1339
			save_uid = GetUserId();
			SetUserId(fk_owner);

Jan Wieck's avatar
Jan Wieck committed
1340
			if (SPI_execp(qplan, del_values, del_nulls, 0) != SPI_OK_DELETE)
1341
				elog(ERROR, "SPI_execp() failed in RI_FKey_cascade_del()");
1342

1343 1344
			SetUserId(save_uid);

1345
			if (SPI_finish() != SPI_OK_FINISH)
Bruce Momjian's avatar
Bruce Momjian committed
1346
				elog(WARNING, "SPI_finish() failed in RI_FKey_cascade_del()");
1347

1348 1349
			heap_close(fk_rel, RowExclusiveLock);

1350
			return PointerGetDatum(NULL);
1351

1352
			/*
1353 1354
			 * Handle MATCH PARTIAL cascaded delete.
			 */
1355 1356
		case RI_MATCH_TYPE_PARTIAL:
			elog(ERROR, "MATCH PARTIAL not yet supported");
1357
			return PointerGetDatum(NULL);
1358 1359
	}

1360
	/*
1361 1362
	 * Never reached
	 */
1363
	elog(ERROR, "internal error #4 in ri_triggers.c");
1364
	return PointerGetDatum(NULL);
1365 1366 1367 1368 1369 1370 1371 1372 1373
}


/* ----------
 * RI_FKey_cascade_upd -
 *
 *	Cascaded update/delete foreign key references at update event on PK table.
 * ----------
 */
1374 1375
Datum
RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
1376
{
1377
	TriggerData *trigdata = (TriggerData *) fcinfo->context;
1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390
	int			tgnargs;
	char	  **tgargs;
	Relation	fk_rel;
	Relation	pk_rel;
	HeapTuple	new_row;
	HeapTuple	old_row;
	RI_QueryKey qkey;
	void	   *qplan;
	Datum		upd_values[RI_MAX_NUMKEYS * 2];
	char		upd_nulls[RI_MAX_NUMKEYS * 2 + 1];
	bool		isnull;
	int			i;
	int			j;
1391 1392
	AclId		save_uid;
	AclId		fk_owner;
1393

1394
	ReferentialIntegritySnapshotOverride = true;
1395

1396 1397 1398
	/*
	 * Check that this is a valid trigger call on the right time and
	 * event.
Jan Wieck's avatar
Jan Wieck committed
1399
	 */
1400
	if (!CALLED_AS_TRIGGER(fcinfo))
Jan Wieck's avatar
Jan Wieck committed
1401
		elog(ERROR, "RI_FKey_cascade_upd() not fired by trigger manager");
1402 1403
	if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) ||
		!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
Jan Wieck's avatar
Jan Wieck committed
1404 1405 1406 1407
		elog(ERROR, "RI_FKey_cascade_upd() must be fired AFTER ROW");
	if (!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
		elog(ERROR, "RI_FKey_cascade_upd() must be fired for UPDATE");

1408
	/*
1409
	 * Check for the correct # of call arguments
Jan Wieck's avatar
Jan Wieck committed
1410 1411
	 */
	tgnargs = trigdata->tg_trigger->tgnargs;
1412
	tgargs = trigdata->tg_trigger->tgargs;
Jan Wieck's avatar
Jan Wieck committed
1413 1414 1415 1416
	if (tgnargs < 4 || (tgnargs % 2) != 0)
		elog(ERROR, "wrong # of arguments in call to RI_FKey_cascade_upd()");
	if (tgnargs > RI_MAX_ARGUMENTS)
		elog(ERROR, "too many keys (%d max) in call to RI_FKey_cascade_upd()",
1417
			 RI_MAX_NUMKEYS);
Jan Wieck's avatar
Jan Wieck committed
1418

1419
	/*
Jan Wieck's avatar
Jan Wieck committed
1420 1421 1422
	 * Nothing to do if no column names to compare given
	 */
	if (tgnargs == 4)
1423
		return PointerGetDatum(NULL);
Jan Wieck's avatar
Jan Wieck committed
1424

1425 1426 1427
	/*
	 * Get the relation descriptors of the FK and PK tables and the new
	 * and old tuple.
1428 1429 1430
	 *
	 * fk_rel is opened in RowExclusiveLock mode since that's what our
	 * eventual UPDATE will get on it.
Jan Wieck's avatar
Jan Wieck committed
1431
	 */
1432 1433 1434 1435 1436 1437
	if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid))
		elog(ERROR, "No target table given for trigger \"%s\" on \"%s\""
			 "\n\tRemove these RI triggers and do ALTER TABLE ADD CONSTRAINT",
			 trigdata->tg_trigger->tgname,
			 RelationGetRelationName(trigdata->tg_relation));

1438
	fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowExclusiveLock);
1439
	pk_rel = trigdata->tg_relation;
Jan Wieck's avatar
Jan Wieck committed
1440 1441
	new_row = trigdata->tg_newtuple;
	old_row = trigdata->tg_trigtuple;
1442
	fk_owner = RelationGetForm(fk_rel)->relowner;
Jan Wieck's avatar
Jan Wieck committed
1443 1444 1445

	switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
	{
1446 1447 1448 1449 1450 1451 1452
			/* ----------
			 * SQL3 11.9 <referential constraint definition>
			 *	Gereral rules 7) a) i):
			 *		MATCH <unspecified> or MATCH FULL
			 *			... ON UPDATE CASCADE
			 * ----------
			 */
Jan Wieck's avatar
Jan Wieck committed
1453 1454 1455
		case RI_MATCH_TYPE_UNSPECIFIED:
		case RI_MATCH_TYPE_FULL:
			ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
1456 1457 1458
								 RI_PLAN_CASCADE_UPD_DOUPDATE,
								 fk_rel, pk_rel,
								 tgnargs, tgargs);
Jan Wieck's avatar
Jan Wieck committed
1459 1460 1461 1462 1463

			switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
			{
				case RI_KEYS_ALL_NULL:
				case RI_KEYS_SOME_NULL:
1464 1465

					/*
Jan Wieck's avatar
Jan Wieck committed
1466 1467 1468
					 * No update - MATCH FULL means there cannot be any
					 * reference to old key if it contains NULL
					 */
1469
					heap_close(fk_rel, RowExclusiveLock);
1470
					return PointerGetDatum(NULL);
1471

Jan Wieck's avatar
Jan Wieck committed
1472
				case RI_KEYS_NONE_NULL:
1473 1474

					/*
Jan Wieck's avatar
Jan Wieck committed
1475 1476 1477 1478 1479
					 * Have a full qualified key - continue below
					 */
					break;
			}

1480
			/*
Jan Wieck's avatar
Jan Wieck committed
1481 1482 1483
			 * No need to do anything if old and new keys are equal
			 */
			if (ri_KeysEqual(pk_rel, old_row, new_row, &qkey,
1484
							 RI_KEYPAIR_PK_IDX))
1485 1486
			{
				heap_close(fk_rel, RowExclusiveLock);
1487
				return PointerGetDatum(NULL);
1488
			}
Jan Wieck's avatar
Jan Wieck committed
1489 1490

			if (SPI_connect() != SPI_OK_CONNECT)
Bruce Momjian's avatar
Bruce Momjian committed
1491
				elog(WARNING, "SPI_connect() failed in RI_FKey_cascade_upd()");
Jan Wieck's avatar
Jan Wieck committed
1492

1493 1494 1495
			/*
			 * Fetch or prepare a saved plan for the cascaded update of
			 * foreign references
Jan Wieck's avatar
Jan Wieck committed
1496 1497 1498
			 */
			if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
			{
1499
				char		querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
Bruce Momjian's avatar
Bruce Momjian committed
1500
												 (MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS * 2];
1501 1502 1503 1504 1505
				char		qualstr[(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
				char		fkrelname[MAX_QUOTED_REL_NAME_LEN];
				char		attname[MAX_QUOTED_NAME_LEN];
				const char *querysep;
				const char *qualsep;
Jan Wieck's avatar
Jan Wieck committed
1506 1507 1508 1509
				Oid			queryoids[RI_MAX_NUMKEYS * 2];

				/* ----------
				 * The query string built is
1510
				 *	UPDATE ONLY <fktable> SET fkatt1 = $1 [, ...]
Jan Wieck's avatar
Jan Wieck committed
1511 1512 1513 1514 1515 1516 1517
				 *			WHERE fkatt1 = $n [AND ...]
				 * The type id's for the $ parameters are those of the
				 * corresponding PK attributes. Thus, SPI_prepare could
				 * eventually fail if the parser cannot identify some way
				 * how to compare these two types by '='.
				 * ----------
				 */
1518
				quoteRelationName(fkrelname, fk_rel);
1519
				snprintf(querystr, sizeof(querystr), "UPDATE ONLY %s SET", fkrelname);
Jan Wieck's avatar
Jan Wieck committed
1520 1521 1522 1523 1524
				qualstr[0] = '\0';
				querysep = "";
				qualsep = "WHERE";
				for (i = 0, j = qkey.nkeypairs; i < qkey.nkeypairs; i++, j++)
				{
1525 1526
					quoteOneName(attname,
								 tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
1527
					snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), "%s %s = $%d",
Bruce Momjian's avatar
Bruce Momjian committed
1528
							 querysep, attname, i + 1);
1529
					snprintf(qualstr + strlen(qualstr), sizeof(qualstr) - strlen(qualstr), " %s %s = $%d",
Bruce Momjian's avatar
Bruce Momjian committed
1530
							 qualsep, attname, j + 1);
Jan Wieck's avatar
Jan Wieck committed
1531 1532 1533
					querysep = ",";
					qualsep = "AND";
					queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
1534
									 qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
Jan Wieck's avatar
Jan Wieck committed
1535 1536 1537 1538
					queryoids[j] = queryoids[i];
				}
				strcat(querystr, qualstr);

1539
				/*
Jan Wieck's avatar
Jan Wieck committed
1540 1541 1542 1543 1544 1545 1546
				 * Prepare, save and remember the new plan.
				 */
				qplan = SPI_prepare(querystr, qkey.nkeypairs * 2, queryoids);
				qplan = SPI_saveplan(qplan);
				ri_HashPreparedPlan(&qkey, qplan);
			}

1547
			/*
Jan Wieck's avatar
Jan Wieck committed
1548 1549 1550 1551 1552 1553
			 * We have a plan now. Build up the arguments for SPI_execp()
			 * from the key values in the updated PK tuple.
			 */
			for (i = 0, j = qkey.nkeypairs; i < qkey.nkeypairs; i++, j++)
			{
				upd_values[i] = SPI_getbinval(new_row,
1554 1555 1556 1557
											  pk_rel->rd_att,
									  qkey.keypair[i][RI_KEYPAIR_PK_IDX],
											  &isnull);
				if (isnull)
Jan Wieck's avatar
Jan Wieck committed
1558 1559 1560 1561 1562
					upd_nulls[i] = 'n';
				else
					upd_nulls[i] = ' ';

				upd_values[j] = SPI_getbinval(old_row,
1563 1564 1565 1566
											  pk_rel->rd_att,
									  qkey.keypair[i][RI_KEYPAIR_PK_IDX],
											  &isnull);
				if (isnull)
Jan Wieck's avatar
Jan Wieck committed
1567 1568 1569 1570 1571 1572
					upd_nulls[j] = 'n';
				else
					upd_nulls[j] = ' ';
			}
			upd_nulls[j] = '\0';

1573
			/*
Jan Wieck's avatar
Jan Wieck committed
1574 1575
			 * Now update the existing references
			 */
1576 1577 1578
			save_uid = GetUserId();
			SetUserId(fk_owner);

Jan Wieck's avatar
Jan Wieck committed
1579 1580
			if (SPI_execp(qplan, upd_values, upd_nulls, 0) != SPI_OK_UPDATE)
				elog(ERROR, "SPI_execp() failed in RI_FKey_cascade_upd()");
1581

1582 1583
			SetUserId(save_uid);

Jan Wieck's avatar
Jan Wieck committed
1584
			if (SPI_finish() != SPI_OK_FINISH)
Bruce Momjian's avatar
Bruce Momjian committed
1585
				elog(WARNING, "SPI_finish() failed in RI_FKey_cascade_upd()");
Jan Wieck's avatar
Jan Wieck committed
1586

1587 1588
			heap_close(fk_rel, RowExclusiveLock);

1589
			return PointerGetDatum(NULL);
Jan Wieck's avatar
Jan Wieck committed
1590

1591
			/*
1592 1593
			 * Handle MATCH PARTIAL cascade update.
			 */
Jan Wieck's avatar
Jan Wieck committed
1594 1595
		case RI_MATCH_TYPE_PARTIAL:
			elog(ERROR, "MATCH PARTIAL not yet supported");
1596
			return PointerGetDatum(NULL);
Jan Wieck's avatar
Jan Wieck committed
1597 1598
	}

1599
	/*
Jan Wieck's avatar
Jan Wieck committed
1600 1601
	 * Never reached
	 */
1602
	elog(ERROR, "internal error #5 in ri_triggers.c");
1603
	return PointerGetDatum(NULL);
1604 1605 1606 1607 1608 1609 1610
}


/* ----------
 * RI_FKey_restrict_del -
 *
 *	Restrict delete from PK table to rows unreferenced by foreign key.
1611
 *
1612 1613 1614
 *	SQL3 intends that this referential action occur BEFORE the
 *	update is performed, rather than after.  This appears to be
 *	the only difference between "NO ACTION" and "RESTRICT".
1615
 *
1616 1617
 *	For now, however, we treat "RESTRICT" and "NO ACTION" as
 *	equivalent.
1618 1619
 * ----------
 */
1620 1621
Datum
RI_FKey_restrict_del(PG_FUNCTION_ARGS)
1622
{
1623
	TriggerData *trigdata = (TriggerData *) fcinfo->context;
1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634
	int			tgnargs;
	char	  **tgargs;
	Relation	fk_rel;
	Relation	pk_rel;
	HeapTuple	old_row;
	RI_QueryKey qkey;
	void	   *qplan;
	Datum		del_values[RI_MAX_NUMKEYS];
	char		del_nulls[RI_MAX_NUMKEYS + 1];
	bool		isnull;
	int			i;
1635 1636
	AclId		save_uid;
	AclId		fk_owner;
1637

1638
	ReferentialIntegritySnapshotOverride = true;
1639

1640 1641 1642
	/*
	 * Check that this is a valid trigger call on the right time and
	 * event.
Jan Wieck's avatar
Jan Wieck committed
1643
	 */
1644
	if (!CALLED_AS_TRIGGER(fcinfo))
Jan Wieck's avatar
Jan Wieck committed
1645
		elog(ERROR, "RI_FKey_restrict_del() not fired by trigger manager");
1646 1647
	if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) ||
		!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
Jan Wieck's avatar
Jan Wieck committed
1648 1649 1650 1651
		elog(ERROR, "RI_FKey_restrict_del() must be fired AFTER ROW");
	if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
		elog(ERROR, "RI_FKey_restrict_del() must be fired for DELETE");

1652
	/*
1653
	 * Check for the correct # of call arguments
Jan Wieck's avatar
Jan Wieck committed
1654 1655
	 */
	tgnargs = trigdata->tg_trigger->tgnargs;
1656
	tgargs = trigdata->tg_trigger->tgargs;
Jan Wieck's avatar
Jan Wieck committed
1657 1658 1659 1660
	if (tgnargs < 4 || (tgnargs % 2) != 0)
		elog(ERROR, "wrong # of arguments in call to RI_FKey_restrict_del()");
	if (tgnargs > RI_MAX_ARGUMENTS)
		elog(ERROR, "too many keys (%d max) in call to RI_FKey_restrict_del()",
1661
			 RI_MAX_NUMKEYS);
Jan Wieck's avatar
Jan Wieck committed
1662

1663
	/*
Jan Wieck's avatar
Jan Wieck committed
1664 1665 1666
	 * Nothing to do if no column names to compare given
	 */
	if (tgnargs == 4)
1667
		return PointerGetDatum(NULL);
Jan Wieck's avatar
Jan Wieck committed
1668

1669 1670 1671
	/*
	 * Get the relation descriptors of the FK and PK tables and the old
	 * tuple.
1672
	 *
Bruce Momjian's avatar
Bruce Momjian committed
1673 1674
	 * fk_rel is opened in RowShareLock mode since that's what our eventual
	 * SELECT FOR UPDATE will get on it.
Jan Wieck's avatar
Jan Wieck committed
1675
	 */
1676 1677 1678 1679 1680 1681
	if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid))
		elog(ERROR, "No target table given for trigger \"%s\" on \"%s\""
			 "\n\tRemove these RI triggers and do ALTER TABLE ADD CONSTRAINT",
			 trigdata->tg_trigger->tgname,
			 RelationGetRelationName(trigdata->tg_relation));

1682
	fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowShareLock);
1683
	pk_rel = trigdata->tg_relation;
Jan Wieck's avatar
Jan Wieck committed
1684
	old_row = trigdata->tg_trigtuple;
1685
	fk_owner = RelationGetForm(fk_rel)->relowner;
Jan Wieck's avatar
Jan Wieck committed
1686 1687 1688

	switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
	{
1689 1690 1691 1692 1693 1694 1695
			/* ----------
			 * SQL3 11.9 <referential constraint definition>
			 *	Gereral rules 6) a) iv):
			 *		MATCH <unspecified> or MATCH FULL
			 *			... ON DELETE CASCADE
			 * ----------
			 */
Jan Wieck's avatar
Jan Wieck committed
1696 1697 1698
		case RI_MATCH_TYPE_UNSPECIFIED:
		case RI_MATCH_TYPE_FULL:
			ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
1699 1700 1701
								 RI_PLAN_RESTRICT_DEL_CHECKREF,
								 fk_rel, pk_rel,
								 tgnargs, tgargs);
Jan Wieck's avatar
Jan Wieck committed
1702 1703 1704 1705 1706

			switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
			{
				case RI_KEYS_ALL_NULL:
				case RI_KEYS_SOME_NULL:
1707 1708

					/*
Jan Wieck's avatar
Jan Wieck committed
1709 1710 1711
					 * No check - MATCH FULL means there cannot be any
					 * reference to old key if it contains NULL
					 */
1712
					heap_close(fk_rel, RowShareLock);
1713
					return PointerGetDatum(NULL);
1714

Jan Wieck's avatar
Jan Wieck committed
1715
				case RI_KEYS_NONE_NULL:
1716 1717

					/*
Jan Wieck's avatar
Jan Wieck committed
1718 1719 1720 1721 1722 1723
					 * Have a full qualified key - continue below
					 */
					break;
			}

			if (SPI_connect() != SPI_OK_CONNECT)
Bruce Momjian's avatar
Bruce Momjian committed
1724
				elog(WARNING, "SPI_connect() failed in RI_FKey_restrict_del()");
Jan Wieck's avatar
Jan Wieck committed
1725

1726
			/*
Jan Wieck's avatar
Jan Wieck committed
1727
			 * Fetch or prepare a saved plan for the restrict delete
Jan Wieck's avatar
Jan Wieck committed
1728
			 * lookup if foreign references exist
Jan Wieck's avatar
Jan Wieck committed
1729 1730 1731
			 */
			if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
			{
1732
				char		querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
Bruce Momjian's avatar
Bruce Momjian committed
1733
							(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
1734 1735 1736
				char		fkrelname[MAX_QUOTED_REL_NAME_LEN];
				char		attname[MAX_QUOTED_NAME_LEN];
				const char *querysep;
Jan Wieck's avatar
Jan Wieck committed
1737 1738 1739 1740
				Oid			queryoids[RI_MAX_NUMKEYS];

				/* ----------
				 * The query string built is
1741
				 *	SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
Jan Wieck's avatar
Jan Wieck committed
1742 1743 1744 1745 1746 1747
				 * The type id's for the $ parameters are those of the
				 * corresponding PK attributes. Thus, SPI_prepare could
				 * eventually fail if the parser cannot identify some way
				 * how to compare these two types by '='.
				 * ----------
				 */
1748
				quoteRelationName(fkrelname, fk_rel);
1749
				snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x", fkrelname);
Jan Wieck's avatar
Jan Wieck committed
1750 1751 1752
				querysep = "WHERE";
				for (i = 0; i < qkey.nkeypairs; i++)
				{
1753 1754
					quoteOneName(attname,
								 tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
1755
					snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), " %s %s = $%d",
Bruce Momjian's avatar
Bruce Momjian committed
1756
							 querysep, attname, i + 1);
Jan Wieck's avatar
Jan Wieck committed
1757 1758
					querysep = "AND";
					queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
1759
									 qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
Jan Wieck's avatar
Jan Wieck committed
1760
				}
1761
				strcat(querystr, " FOR UPDATE OF x");
Jan Wieck's avatar
Jan Wieck committed
1762

1763
				/*
Jan Wieck's avatar
Jan Wieck committed
1764 1765 1766 1767 1768 1769 1770
				 * Prepare, save and remember the new plan.
				 */
				qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
				qplan = SPI_saveplan(qplan);
				ri_HashPreparedPlan(&qkey, qplan);
			}

1771
			/*
Jan Wieck's avatar
Jan Wieck committed
1772 1773 1774 1775 1776 1777
			 * We have a plan now. Build up the arguments for SPI_execp()
			 * from the key values in the deleted PK tuple.
			 */
			for (i = 0; i < qkey.nkeypairs; i++)
			{
				del_values[i] = SPI_getbinval(old_row,
1778 1779 1780 1781
											  pk_rel->rd_att,
									  qkey.keypair[i][RI_KEYPAIR_PK_IDX],
											  &isnull);
				if (isnull)
Jan Wieck's avatar
Jan Wieck committed
1782 1783 1784 1785 1786 1787
					del_nulls[i] = 'n';
				else
					del_nulls[i] = ' ';
			}
			del_nulls[i] = '\0';

1788
			/*
Jan Wieck's avatar
Jan Wieck committed
1789 1790
			 * Now check for existing references
			 */
1791 1792 1793
			save_uid = GetUserId();
			SetUserId(fk_owner);

Jan Wieck's avatar
Jan Wieck committed
1794 1795
			if (SPI_execp(qplan, del_values, del_nulls, 1) != SPI_OK_SELECT)
				elog(ERROR, "SPI_execp() failed in RI_FKey_restrict_del()");
1796

1797 1798
			SetUserId(save_uid);

Jan Wieck's avatar
Jan Wieck committed
1799 1800
			if (SPI_processed > 0)
				elog(ERROR, "%s referential integrity violation - "
1801 1802
					 "key in %s still referenced from %s",
					 tgargs[RI_CONSTRAINT_NAME_ARGNO],
1803 1804
					 RelationGetRelationName(pk_rel),
					 RelationGetRelationName(fk_rel));
Jan Wieck's avatar
Jan Wieck committed
1805 1806

			if (SPI_finish() != SPI_OK_FINISH)
Bruce Momjian's avatar
Bruce Momjian committed
1807
				elog(WARNING, "SPI_finish() failed in RI_FKey_restrict_del()");
Jan Wieck's avatar
Jan Wieck committed
1808

1809 1810
			heap_close(fk_rel, RowShareLock);

1811
			return PointerGetDatum(NULL);
Jan Wieck's avatar
Jan Wieck committed
1812

1813
			/*
1814 1815
			 * Handle MATCH PARTIAL restrict delete.
			 */
Jan Wieck's avatar
Jan Wieck committed
1816 1817
		case RI_MATCH_TYPE_PARTIAL:
			elog(ERROR, "MATCH PARTIAL not yet supported");
1818
			return PointerGetDatum(NULL);
Jan Wieck's avatar
Jan Wieck committed
1819 1820
	}

1821
	/*
Jan Wieck's avatar
Jan Wieck committed
1822 1823
	 * Never reached
	 */
1824
	elog(ERROR, "internal error #6 in ri_triggers.c");
1825
	return PointerGetDatum(NULL);
1826 1827 1828 1829 1830 1831 1832
}


/* ----------
 * RI_FKey_restrict_upd -
 *
 *	Restrict update of PK to rows unreferenced by foreign key.
1833
 *
1834 1835 1836
 *	SQL3 intends that this referential action occur BEFORE the
 *	update is performed, rather than after.  This appears to be
 *	the only difference between "NO ACTION" and "RESTRICT".
1837
 *
1838 1839
 *	For now, however, we treat "RESTRICT" and "NO ACTION" as
 *	equivalent.
1840 1841
 * ----------
 */
1842 1843
Datum
RI_FKey_restrict_upd(PG_FUNCTION_ARGS)
1844
{
1845
	TriggerData *trigdata = (TriggerData *) fcinfo->context;
1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857
	int			tgnargs;
	char	  **tgargs;
	Relation	fk_rel;
	Relation	pk_rel;
	HeapTuple	new_row;
	HeapTuple	old_row;
	RI_QueryKey qkey;
	void	   *qplan;
	Datum		upd_values[RI_MAX_NUMKEYS];
	char		upd_nulls[RI_MAX_NUMKEYS + 1];
	bool		isnull;
	int			i;
1858 1859
	AclId		save_uid;
	AclId		fk_owner;
1860

1861
	ReferentialIntegritySnapshotOverride = true;
1862

1863 1864 1865
	/*
	 * Check that this is a valid trigger call on the right time and
	 * event.
Jan Wieck's avatar
Jan Wieck committed
1866
	 */
1867
	if (!CALLED_AS_TRIGGER(fcinfo))
Jan Wieck's avatar
Jan Wieck committed
1868
		elog(ERROR, "RI_FKey_restrict_upd() not fired by trigger manager");
1869 1870
	if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) ||
		!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
Jan Wieck's avatar
Jan Wieck committed
1871 1872 1873 1874
		elog(ERROR, "RI_FKey_restrict_upd() must be fired AFTER ROW");
	if (!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
		elog(ERROR, "RI_FKey_restrict_upd() must be fired for UPDATE");

1875
	/*
1876
	 * Check for the correct # of call arguments
Jan Wieck's avatar
Jan Wieck committed
1877 1878
	 */
	tgnargs = trigdata->tg_trigger->tgnargs;
1879
	tgargs = trigdata->tg_trigger->tgargs;
Jan Wieck's avatar
Jan Wieck committed
1880 1881 1882 1883
	if (tgnargs < 4 || (tgnargs % 2) != 0)
		elog(ERROR, "wrong # of arguments in call to RI_FKey_restrict_upd()");
	if (tgnargs > RI_MAX_ARGUMENTS)
		elog(ERROR, "too many keys (%d max) in call to RI_FKey_restrict_upd()",
1884
			 RI_MAX_NUMKEYS);
Jan Wieck's avatar
Jan Wieck committed
1885

1886
	/*
Jan Wieck's avatar
Jan Wieck committed
1887 1888 1889
	 * Nothing to do if no column names to compare given
	 */
	if (tgnargs == 4)
1890
		return PointerGetDatum(NULL);
Jan Wieck's avatar
Jan Wieck committed
1891

1892 1893 1894
	/*
	 * Get the relation descriptors of the FK and PK tables and the new
	 * and old tuple.
1895
	 *
Bruce Momjian's avatar
Bruce Momjian committed
1896 1897
	 * fk_rel is opened in RowShareLock mode since that's what our eventual
	 * SELECT FOR UPDATE will get on it.
Jan Wieck's avatar
Jan Wieck committed
1898
	 */
1899 1900 1901 1902 1903 1904
	if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid))
		elog(ERROR, "No target table given for trigger \"%s\" on \"%s\""
			 "\n\tRemove these RI triggers and do ALTER TABLE ADD CONSTRAINT",
			 trigdata->tg_trigger->tgname,
			 RelationGetRelationName(trigdata->tg_relation));

1905
	fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowShareLock);
1906
	pk_rel = trigdata->tg_relation;
Jan Wieck's avatar
Jan Wieck committed
1907 1908
	new_row = trigdata->tg_newtuple;
	old_row = trigdata->tg_trigtuple;
1909
	fk_owner = RelationGetForm(fk_rel)->relowner;
Jan Wieck's avatar
Jan Wieck committed
1910 1911 1912

	switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
	{
1913 1914 1915 1916 1917 1918 1919
			/* ----------
			 * SQL3 11.9 <referential constraint definition>
			 *	Gereral rules 6) a) iv):
			 *		MATCH <unspecified> or MATCH FULL
			 *			... ON DELETE CASCADE
			 * ----------
			 */
Jan Wieck's avatar
Jan Wieck committed
1920 1921 1922
		case RI_MATCH_TYPE_UNSPECIFIED:
		case RI_MATCH_TYPE_FULL:
			ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
1923 1924 1925
								 RI_PLAN_RESTRICT_UPD_CHECKREF,
								 fk_rel, pk_rel,
								 tgnargs, tgargs);
Jan Wieck's avatar
Jan Wieck committed
1926 1927 1928 1929 1930

			switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
			{
				case RI_KEYS_ALL_NULL:
				case RI_KEYS_SOME_NULL:
1931 1932

					/*
Jan Wieck's avatar
Jan Wieck committed
1933 1934 1935
					 * No check - MATCH FULL means there cannot be any
					 * reference to old key if it contains NULL
					 */
1936
					heap_close(fk_rel, RowShareLock);
1937
					return PointerGetDatum(NULL);
1938

Jan Wieck's avatar
Jan Wieck committed
1939
				case RI_KEYS_NONE_NULL:
1940 1941

					/*
Jan Wieck's avatar
Jan Wieck committed
1942 1943 1944 1945 1946
					 * Have a full qualified key - continue below
					 */
					break;
			}

1947
			/*
Jan Wieck's avatar
Jan Wieck committed
1948 1949 1950
			 * No need to check anything if old and new keys are equal
			 */
			if (ri_KeysEqual(pk_rel, old_row, new_row, &qkey,
1951
							 RI_KEYPAIR_PK_IDX))
1952 1953
			{
				heap_close(fk_rel, RowShareLock);
1954
				return PointerGetDatum(NULL);
1955
			}
Jan Wieck's avatar
Jan Wieck committed
1956 1957

			if (SPI_connect() != SPI_OK_CONNECT)
Bruce Momjian's avatar
Bruce Momjian committed
1958
				elog(WARNING, "SPI_connect() failed in RI_FKey_restrict_upd()");
Jan Wieck's avatar
Jan Wieck committed
1959

1960
			/*
Jan Wieck's avatar
Jan Wieck committed
1961 1962
			 * Fetch or prepare a saved plan for the restrict update
			 * lookup if foreign references exist
Jan Wieck's avatar
Jan Wieck committed
1963 1964 1965
			 */
			if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
			{
1966
				char		querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
Bruce Momjian's avatar
Bruce Momjian committed
1967
							(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
1968 1969 1970
				char		fkrelname[MAX_QUOTED_REL_NAME_LEN];
				char		attname[MAX_QUOTED_NAME_LEN];
				const char *querysep;
Jan Wieck's avatar
Jan Wieck committed
1971 1972 1973 1974
				Oid			queryoids[RI_MAX_NUMKEYS];

				/* ----------
				 * The query string built is
1975
				 *	SELECT 1 FROM ONLY <fktable> WHERE fkatt1 = $1 [AND ...]
Jan Wieck's avatar
Jan Wieck committed
1976 1977 1978 1979 1980 1981
				 * The type id's for the $ parameters are those of the
				 * corresponding PK attributes. Thus, SPI_prepare could
				 * eventually fail if the parser cannot identify some way
				 * how to compare these two types by '='.
				 * ----------
				 */
1982
				quoteRelationName(fkrelname, fk_rel);
1983
				snprintf(querystr, sizeof(querystr), "SELECT 1 FROM ONLY %s x", fkrelname);
Jan Wieck's avatar
Jan Wieck committed
1984 1985 1986
				querysep = "WHERE";
				for (i = 0; i < qkey.nkeypairs; i++)
				{
1987 1988
					quoteOneName(attname,
								 tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
1989
					snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), " %s %s = $%d",
Bruce Momjian's avatar
Bruce Momjian committed
1990
							 querysep, attname, i + 1);
Jan Wieck's avatar
Jan Wieck committed
1991 1992
					querysep = "AND";
					queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
1993
									 qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
Jan Wieck's avatar
Jan Wieck committed
1994
				}
1995
				strcat(querystr, " FOR UPDATE OF x");
Jan Wieck's avatar
Jan Wieck committed
1996

1997
				/*
Jan Wieck's avatar
Jan Wieck committed
1998 1999 2000 2001 2002 2003 2004
				 * Prepare, save and remember the new plan.
				 */
				qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
				qplan = SPI_saveplan(qplan);
				ri_HashPreparedPlan(&qkey, qplan);
			}

2005
			/*
Jan Wieck's avatar
Jan Wieck committed
2006 2007 2008 2009 2010 2011
			 * We have a plan now. Build up the arguments for SPI_execp()
			 * from the key values in the updated PK tuple.
			 */
			for (i = 0; i < qkey.nkeypairs; i++)
			{
				upd_values[i] = SPI_getbinval(old_row,
2012 2013 2014 2015
											  pk_rel->rd_att,
									  qkey.keypair[i][RI_KEYPAIR_PK_IDX],
											  &isnull);
				if (isnull)
Jan Wieck's avatar
Jan Wieck committed
2016 2017 2018 2019 2020 2021
					upd_nulls[i] = 'n';
				else
					upd_nulls[i] = ' ';
			}
			upd_nulls[i] = '\0';

2022
			/*
Jan Wieck's avatar
Jan Wieck committed
2023 2024
			 * Now check for existing references
			 */
2025 2026 2027
			save_uid = GetUserId();
			SetUserId(fk_owner);

2028 2029
			SetUserId(RelationGetForm(pk_rel)->relowner);

Jan Wieck's avatar
Jan Wieck committed
2030 2031
			if (SPI_execp(qplan, upd_values, upd_nulls, 1) != SPI_OK_SELECT)
				elog(ERROR, "SPI_execp() failed in RI_FKey_restrict_upd()");
2032

2033 2034
			SetUserId(save_uid);

Jan Wieck's avatar
Jan Wieck committed
2035 2036
			if (SPI_processed > 0)
				elog(ERROR, "%s referential integrity violation - "
2037 2038
					 "key in %s still referenced from %s",
					 tgargs[RI_CONSTRAINT_NAME_ARGNO],
2039 2040
					 RelationGetRelationName(pk_rel),
					 RelationGetRelationName(fk_rel));
Jan Wieck's avatar
Jan Wieck committed
2041 2042

			if (SPI_finish() != SPI_OK_FINISH)
Bruce Momjian's avatar
Bruce Momjian committed
2043
				elog(WARNING, "SPI_finish() failed in RI_FKey_restrict_upd()");
Jan Wieck's avatar
Jan Wieck committed
2044

2045 2046
			heap_close(fk_rel, RowShareLock);

2047
			return PointerGetDatum(NULL);
Jan Wieck's avatar
Jan Wieck committed
2048

2049
			/*
2050 2051
			 * Handle MATCH PARTIAL restrict update.
			 */
Jan Wieck's avatar
Jan Wieck committed
2052 2053
		case RI_MATCH_TYPE_PARTIAL:
			elog(ERROR, "MATCH PARTIAL not yet supported");
2054
			return PointerGetDatum(NULL);
Jan Wieck's avatar
Jan Wieck committed
2055 2056
	}

2057
	/*
Jan Wieck's avatar
Jan Wieck committed
2058 2059
	 * Never reached
	 */
2060
	elog(ERROR, "internal error #7 in ri_triggers.c");
2061
	return PointerGetDatum(NULL);
2062 2063 2064 2065 2066 2067 2068 2069 2070
}


/* ----------
 * RI_FKey_setnull_del -
 *
 *	Set foreign key references to NULL values at delete event on PK table.
 * ----------
 */
2071 2072
Datum
RI_FKey_setnull_del(PG_FUNCTION_ARGS)
2073
{
2074
	TriggerData *trigdata = (TriggerData *) fcinfo->context;
2075 2076 2077 2078 2079 2080 2081 2082 2083 2084 2085
	int			tgnargs;
	char	  **tgargs;
	Relation	fk_rel;
	Relation	pk_rel;
	HeapTuple	old_row;
	RI_QueryKey qkey;
	void	   *qplan;
	Datum		upd_values[RI_MAX_NUMKEYS];
	char		upd_nulls[RI_MAX_NUMKEYS + 1];
	bool		isnull;
	int			i;
2086 2087
	AclId		save_uid;
	AclId		fk_owner;
2088

2089
	ReferentialIntegritySnapshotOverride = true;
2090

2091 2092 2093
	/*
	 * Check that this is a valid trigger call on the right time and
	 * event.
Jan Wieck's avatar
Jan Wieck committed
2094
	 */
2095
	if (!CALLED_AS_TRIGGER(fcinfo))
Jan Wieck's avatar
Jan Wieck committed
2096
		elog(ERROR, "RI_FKey_setnull_del() not fired by trigger manager");
2097 2098
	if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) ||
		!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
Jan Wieck's avatar
Jan Wieck committed
2099 2100 2101 2102
		elog(ERROR, "RI_FKey_setnull_del() must be fired AFTER ROW");
	if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
		elog(ERROR, "RI_FKey_setnull_del() must be fired for DELETE");

2103
	/*
2104
	 * Check for the correct # of call arguments
Jan Wieck's avatar
Jan Wieck committed
2105 2106
	 */
	tgnargs = trigdata->tg_trigger->tgnargs;
2107
	tgargs = trigdata->tg_trigger->tgargs;
Jan Wieck's avatar
Jan Wieck committed
2108 2109 2110 2111
	if (tgnargs < 4 || (tgnargs % 2) != 0)
		elog(ERROR, "wrong # of arguments in call to RI_FKey_setnull_del()");
	if (tgnargs > RI_MAX_ARGUMENTS)
		elog(ERROR, "too many keys (%d max) in call to RI_FKey_setnull_del()",
2112
			 RI_MAX_NUMKEYS);
Jan Wieck's avatar
Jan Wieck committed
2113

2114
	/*
Jan Wieck's avatar
Jan Wieck committed
2115 2116 2117
	 * Nothing to do if no column names to compare given
	 */
	if (tgnargs == 4)
2118
		return PointerGetDatum(NULL);
Jan Wieck's avatar
Jan Wieck committed
2119

2120 2121 2122
	/*
	 * Get the relation descriptors of the FK and PK tables and the old
	 * tuple.
2123 2124 2125
	 *
	 * fk_rel is opened in RowExclusiveLock mode since that's what our
	 * eventual UPDATE will get on it.
Jan Wieck's avatar
Jan Wieck committed
2126
	 */
2127 2128 2129 2130 2131 2132
	if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid))
		elog(ERROR, "No target table given for trigger \"%s\" on \"%s\""
			 "\n\tRemove these RI triggers and do ALTER TABLE ADD CONSTRAINT",
			 trigdata->tg_trigger->tgname,
			 RelationGetRelationName(trigdata->tg_relation));

2133
	fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowExclusiveLock);
2134
	pk_rel = trigdata->tg_relation;
Jan Wieck's avatar
Jan Wieck committed
2135
	old_row = trigdata->tg_trigtuple;
2136
	fk_owner = RelationGetForm(fk_rel)->relowner;
Jan Wieck's avatar
Jan Wieck committed
2137 2138 2139

	switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
	{
2140 2141 2142 2143 2144 2145 2146
			/* ----------
			 * SQL3 11.9 <referential constraint definition>
			 *	Gereral rules 6) a) ii):
			 *		MATCH <UNSPECIFIED> or MATCH FULL
			 *			... ON DELETE SET NULL
			 * ----------
			 */
Jan Wieck's avatar
Jan Wieck committed
2147 2148 2149
		case RI_MATCH_TYPE_UNSPECIFIED:
		case RI_MATCH_TYPE_FULL:
			ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
2150 2151 2152
								 RI_PLAN_SETNULL_DEL_DOUPDATE,
								 fk_rel, pk_rel,
								 tgnargs, tgargs);
Jan Wieck's avatar
Jan Wieck committed
2153 2154 2155 2156 2157

			switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
			{
				case RI_KEYS_ALL_NULL:
				case RI_KEYS_SOME_NULL:
2158 2159

					/*
Jan Wieck's avatar
Jan Wieck committed
2160 2161 2162
					 * No update - MATCH FULL means there cannot be any
					 * reference to old key if it contains NULL
					 */
2163
					heap_close(fk_rel, RowExclusiveLock);
2164
					return PointerGetDatum(NULL);
2165

Jan Wieck's avatar
Jan Wieck committed
2166
				case RI_KEYS_NONE_NULL:
2167 2168

					/*
Jan Wieck's avatar
Jan Wieck committed
2169 2170 2171 2172 2173 2174
					 * Have a full qualified key - continue below
					 */
					break;
			}

			if (SPI_connect() != SPI_OK_CONNECT)
Bruce Momjian's avatar
Bruce Momjian committed
2175
				elog(WARNING, "SPI_connect() failed in RI_FKey_setnull_del()");
Jan Wieck's avatar
Jan Wieck committed
2176

2177
			/*
Jan Wieck's avatar
Jan Wieck committed
2178 2179 2180 2181 2182
			 * Fetch or prepare a saved plan for the set null delete
			 * operation
			 */
			if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
			{
2183
				char		querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
Bruce Momjian's avatar
Bruce Momjian committed
2184
												 (MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS * 2];
2185 2186 2187 2188 2189
				char		qualstr[(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
				char		fkrelname[MAX_QUOTED_REL_NAME_LEN];
				char		attname[MAX_QUOTED_NAME_LEN];
				const char *querysep;
				const char *qualsep;
Jan Wieck's avatar
Jan Wieck committed
2190 2191 2192 2193
				Oid			queryoids[RI_MAX_NUMKEYS];

				/* ----------
				 * The query string built is
2194
				 *	UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
Jan Wieck's avatar
Jan Wieck committed
2195 2196 2197 2198 2199 2200 2201
				 *			WHERE fkatt1 = $1 [AND ...]
				 * The type id's for the $ parameters are those of the
				 * corresponding PK attributes. Thus, SPI_prepare could
				 * eventually fail if the parser cannot identify some way
				 * how to compare these two types by '='.
				 * ----------
				 */
2202
				quoteRelationName(fkrelname, fk_rel);
2203
				snprintf(querystr, sizeof(querystr), "UPDATE ONLY %s SET", fkrelname);
Jan Wieck's avatar
Jan Wieck committed
2204 2205 2206 2207 2208
				qualstr[0] = '\0';
				querysep = "";
				qualsep = "WHERE";
				for (i = 0; i < qkey.nkeypairs; i++)
				{
2209 2210
					quoteOneName(attname,
								 tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
2211
					snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), "%s %s = NULL",
Bruce Momjian's avatar
Bruce Momjian committed
2212
							 querysep, attname);
2213
					snprintf(qualstr + strlen(qualstr), sizeof(qualstr) - strlen(qualstr), " %s %s = $%d",
Bruce Momjian's avatar
Bruce Momjian committed
2214
							 qualsep, attname, i + 1);
Jan Wieck's avatar
Jan Wieck committed
2215 2216 2217
					querysep = ",";
					qualsep = "AND";
					queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
2218
									 qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
Jan Wieck's avatar
Jan Wieck committed
2219 2220 2221
				}
				strcat(querystr, qualstr);

2222
				/*
Jan Wieck's avatar
Jan Wieck committed
2223 2224
				 * Prepare, save and remember the new plan.
				 */
2225
				qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
Jan Wieck's avatar
Jan Wieck committed
2226 2227 2228 2229
				qplan = SPI_saveplan(qplan);
				ri_HashPreparedPlan(&qkey, qplan);
			}

2230
			/*
Jan Wieck's avatar
Jan Wieck committed
2231 2232 2233 2234 2235 2236
			 * We have a plan now. Build up the arguments for SPI_execp()
			 * from the key values in the updated PK tuple.
			 */
			for (i = 0; i < qkey.nkeypairs; i++)
			{
				upd_values[i] = SPI_getbinval(old_row,
2237 2238 2239 2240
											  pk_rel->rd_att,
									  qkey.keypair[i][RI_KEYPAIR_PK_IDX],
											  &isnull);
				if (isnull)
Jan Wieck's avatar
Jan Wieck committed
2241 2242 2243 2244 2245 2246
					upd_nulls[i] = 'n';
				else
					upd_nulls[i] = ' ';
			}
			upd_nulls[i] = '\0';

2247
			/*
Jan Wieck's avatar
Jan Wieck committed
2248 2249
			 * Now update the existing references
			 */
2250 2251 2252
			save_uid = GetUserId();
			SetUserId(fk_owner);

Jan Wieck's avatar
Jan Wieck committed
2253 2254
			if (SPI_execp(qplan, upd_values, upd_nulls, 0) != SPI_OK_UPDATE)
				elog(ERROR, "SPI_execp() failed in RI_FKey_setnull_del()");
2255

2256 2257
			SetUserId(save_uid);

Jan Wieck's avatar
Jan Wieck committed
2258
			if (SPI_finish() != SPI_OK_FINISH)
Bruce Momjian's avatar
Bruce Momjian committed
2259
				elog(WARNING, "SPI_finish() failed in RI_FKey_setnull_del()");
Jan Wieck's avatar
Jan Wieck committed
2260

2261 2262
			heap_close(fk_rel, RowExclusiveLock);

2263
			return PointerGetDatum(NULL);
Jan Wieck's avatar
Jan Wieck committed
2264

2265
			/*
2266 2267
			 * Handle MATCH PARTIAL set null delete.
			 */
Jan Wieck's avatar
Jan Wieck committed
2268 2269
		case RI_MATCH_TYPE_PARTIAL:
			elog(ERROR, "MATCH PARTIAL not yet supported");
2270
			return PointerGetDatum(NULL);
Jan Wieck's avatar
Jan Wieck committed
2271 2272
	}

2273
	/*
Jan Wieck's avatar
Jan Wieck committed
2274 2275
	 * Never reached
	 */
2276
	elog(ERROR, "internal error #8 in ri_triggers.c");
2277
	return PointerGetDatum(NULL);
2278 2279 2280 2281 2282 2283 2284 2285 2286
}


/* ----------
 * RI_FKey_setnull_upd -
 *
 *	Set foreign key references to NULL at update event on PK table.
 * ----------
 */
2287 2288
Datum
RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
2289
{
2290
	TriggerData *trigdata = (TriggerData *) fcinfo->context;
2291 2292 2293 2294 2295 2296 2297 2298 2299 2300 2301 2302 2303 2304
	int			tgnargs;
	char	  **tgargs;
	Relation	fk_rel;
	Relation	pk_rel;
	HeapTuple	new_row;
	HeapTuple	old_row;
	RI_QueryKey qkey;
	void	   *qplan;
	Datum		upd_values[RI_MAX_NUMKEYS];
	char		upd_nulls[RI_MAX_NUMKEYS + 1];
	bool		isnull;
	int			i;
	int			match_type;
	bool		use_cached_query;
2305 2306
	AclId		save_uid;
	AclId		fk_owner;
2307

2308
	ReferentialIntegritySnapshotOverride = true;
2309

2310 2311 2312
	/*
	 * Check that this is a valid trigger call on the right time and
	 * event.
Jan Wieck's avatar
Jan Wieck committed
2313
	 */
2314
	if (!CALLED_AS_TRIGGER(fcinfo))
Jan Wieck's avatar
Jan Wieck committed
2315
		elog(ERROR, "RI_FKey_setnull_upd() not fired by trigger manager");
2316 2317
	if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) ||
		!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
Jan Wieck's avatar
Jan Wieck committed
2318 2319 2320 2321
		elog(ERROR, "RI_FKey_setnull_upd() must be fired AFTER ROW");
	if (!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
		elog(ERROR, "RI_FKey_setnull_upd() must be fired for UPDATE");

2322
	/*
2323
	 * Check for the correct # of call arguments
Jan Wieck's avatar
Jan Wieck committed
2324 2325
	 */
	tgnargs = trigdata->tg_trigger->tgnargs;
2326
	tgargs = trigdata->tg_trigger->tgargs;
Jan Wieck's avatar
Jan Wieck committed
2327 2328 2329 2330
	if (tgnargs < 4 || (tgnargs % 2) != 0)
		elog(ERROR, "wrong # of arguments in call to RI_FKey_setnull_upd()");
	if (tgnargs > RI_MAX_ARGUMENTS)
		elog(ERROR, "too many keys (%d max) in call to RI_FKey_setnull_upd()",
2331
			 RI_MAX_NUMKEYS);
Jan Wieck's avatar
Jan Wieck committed
2332

2333
	/*
Jan Wieck's avatar
Jan Wieck committed
2334 2335 2336
	 * Nothing to do if no column names to compare given
	 */
	if (tgnargs == 4)
2337
		return PointerGetDatum(NULL);
Jan Wieck's avatar
Jan Wieck committed
2338

2339 2340 2341
	/*
	 * Get the relation descriptors of the FK and PK tables and the old
	 * tuple.
2342 2343 2344
	 *
	 * fk_rel is opened in RowExclusiveLock mode since that's what our
	 * eventual UPDATE will get on it.
Jan Wieck's avatar
Jan Wieck committed
2345
	 */
2346 2347 2348 2349 2350 2351
	if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid))
		elog(ERROR, "No target table given for trigger \"%s\" on \"%s\""
			 "\n\tRemove these RI triggers and do ALTER TABLE ADD CONSTRAINT",
			 trigdata->tg_trigger->tgname,
			 RelationGetRelationName(trigdata->tg_relation));

2352
	fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowExclusiveLock);
2353
	pk_rel = trigdata->tg_relation;
Jan Wieck's avatar
Jan Wieck committed
2354 2355
	new_row = trigdata->tg_newtuple;
	old_row = trigdata->tg_trigtuple;
2356
	match_type = ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]);
2357
	fk_owner = RelationGetForm(fk_rel)->relowner;
Jan Wieck's avatar
Jan Wieck committed
2358

2359
	switch (match_type)
Jan Wieck's avatar
Jan Wieck committed
2360
	{
2361 2362 2363 2364 2365 2366 2367
			/* ----------
			 * SQL3 11.9 <referential constraint definition>
			 *	Gereral rules 7) a) ii) 2):
			 *		MATCH FULL
			 *			... ON UPDATE SET NULL
			 * ----------
			 */
Jan Wieck's avatar
Jan Wieck committed
2368 2369 2370
		case RI_MATCH_TYPE_UNSPECIFIED:
		case RI_MATCH_TYPE_FULL:
			ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
2371 2372 2373
								 RI_PLAN_SETNULL_UPD_DOUPDATE,
								 fk_rel, pk_rel,
								 tgnargs, tgargs);
Jan Wieck's avatar
Jan Wieck committed
2374 2375 2376 2377 2378

			switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
			{
				case RI_KEYS_ALL_NULL:
				case RI_KEYS_SOME_NULL:
2379 2380

					/*
Jan Wieck's avatar
Jan Wieck committed
2381 2382 2383
					 * No update - MATCH FULL means there cannot be any
					 * reference to old key if it contains NULL
					 */
2384
					heap_close(fk_rel, RowExclusiveLock);
2385
					return PointerGetDatum(NULL);
2386

Jan Wieck's avatar
Jan Wieck committed
2387
				case RI_KEYS_NONE_NULL:
2388 2389

					/*
Jan Wieck's avatar
Jan Wieck committed
2390 2391 2392 2393
					 * Have a full qualified key - continue below
					 */
					break;
			}
2394

2395
			/*
Jan Wieck's avatar
Jan Wieck committed
2396 2397 2398
			 * No need to do anything if old and new keys are equal
			 */
			if (ri_KeysEqual(pk_rel, old_row, new_row, &qkey,
2399
							 RI_KEYPAIR_PK_IDX))
2400 2401
			{
				heap_close(fk_rel, RowExclusiveLock);
2402
				return PointerGetDatum(NULL);
2403
			}
Jan Wieck's avatar
Jan Wieck committed
2404 2405

			if (SPI_connect() != SPI_OK_CONNECT)
Bruce Momjian's avatar
Bruce Momjian committed
2406
				elog(WARNING, "SPI_connect() failed in RI_FKey_setnull_upd()");
Jan Wieck's avatar
Jan Wieck committed
2407

2408 2409 2410 2411 2412 2413 2414 2415 2416
			/*
			 * "MATCH <unspecified>" 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.
2417 2418 2419 2420 2421 2422
			 *
			 * 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 = match_type == RI_MATCH_TYPE_FULL ||
2423 2424
				ri_AllKeysUnequal(pk_rel, old_row, new_row,
								  &qkey, RI_KEYPAIR_PK_IDX);
2425

2426
			/*
Jan Wieck's avatar
Jan Wieck committed
2427
			 * Fetch or prepare a saved plan for the set null update
2428
			 * operation if possible, or build a temporary plan if not.
Jan Wieck's avatar
Jan Wieck committed
2429
			 */
2430 2431
			if (!use_cached_query ||
				(qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
Jan Wieck's avatar
Jan Wieck committed
2432
			{
2433
				char		querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
Bruce Momjian's avatar
Bruce Momjian committed
2434
												 (MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS * 2];
2435 2436 2437 2438 2439
				char		qualstr[(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
				char		fkrelname[MAX_QUOTED_REL_NAME_LEN];
				char		attname[MAX_QUOTED_NAME_LEN];
				const char *querysep;
				const char *qualsep;
Jan Wieck's avatar
Jan Wieck committed
2440 2441 2442 2443
				Oid			queryoids[RI_MAX_NUMKEYS];

				/* ----------
				 * The query string built is
2444
				 *	UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
Jan Wieck's avatar
Jan Wieck committed
2445 2446 2447 2448 2449 2450 2451
				 *			WHERE fkatt1 = $1 [AND ...]
				 * The type id's for the $ parameters are those of the
				 * corresponding PK attributes. Thus, SPI_prepare could
				 * eventually fail if the parser cannot identify some way
				 * how to compare these two types by '='.
				 * ----------
				 */
2452
				quoteRelationName(fkrelname, fk_rel);
2453
				snprintf(querystr, sizeof(querystr), "UPDATE ONLY %s SET", fkrelname);
Jan Wieck's avatar
Jan Wieck committed
2454 2455 2456 2457 2458
				qualstr[0] = '\0';
				querysep = "";
				qualsep = "WHERE";
				for (i = 0; i < qkey.nkeypairs; i++)
				{
2459 2460
					quoteOneName(attname,
								 tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
Bruce Momjian's avatar
Bruce Momjian committed
2461

2462 2463 2464
					/*
					 * MATCH <unspecified> - only change columns
					 * corresponding to changed columns in pk_rel's key
2465 2466
					 */
					if (match_type == RI_MATCH_TYPE_FULL ||
2467 2468
					  !ri_OneKeyEqual(pk_rel, i, old_row, new_row, &qkey,
									  RI_KEYPAIR_PK_IDX))
2469
					{
2470
						snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), "%s %s = NULL",
Bruce Momjian's avatar
Bruce Momjian committed
2471
								 querysep, attname);
2472 2473
						querysep = ",";
					}
2474
					snprintf(qualstr + strlen(qualstr), sizeof(qualstr) - strlen(qualstr), " %s %s = $%d",
Bruce Momjian's avatar
Bruce Momjian committed
2475
							 qualsep, attname, i + 1);
Jan Wieck's avatar
Jan Wieck committed
2476 2477
					qualsep = "AND";
					queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
2478
									 qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
Jan Wieck's avatar
Jan Wieck committed
2479 2480 2481
				}
				strcat(querystr, qualstr);

2482
				/*
2483
				 * Prepare the new plan.
Jan Wieck's avatar
Jan Wieck committed
2484
				 */
2485
				qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);
2486

2487 2488 2489
				/*
				 * Save and remember the plan if we're building the
				 * "standard" plan.
2490 2491 2492 2493 2494 2495
				 */
				if (use_cached_query)
				{
					qplan = SPI_saveplan(qplan);
					ri_HashPreparedPlan(&qkey, qplan);
				}
Jan Wieck's avatar
Jan Wieck committed
2496 2497
			}

2498
			/*
Jan Wieck's avatar
Jan Wieck committed
2499 2500 2501 2502 2503 2504
			 * We have a plan now. Build up the arguments for SPI_execp()
			 * from the key values in the updated PK tuple.
			 */
			for (i = 0; i < qkey.nkeypairs; i++)
			{
				upd_values[i] = SPI_getbinval(old_row,
2505 2506 2507 2508
											  pk_rel->rd_att,
									  qkey.keypair[i][RI_KEYPAIR_PK_IDX],
											  &isnull);
				if (isnull)
Jan Wieck's avatar
Jan Wieck committed
2509 2510 2511 2512 2513 2514
					upd_nulls[i] = 'n';
				else
					upd_nulls[i] = ' ';
			}
			upd_nulls[i] = '\0';

2515
			/*
Jan Wieck's avatar
Jan Wieck committed
2516 2517
			 * Now update the existing references
			 */
2518 2519 2520
			save_uid = GetUserId();
			SetUserId(fk_owner);

Jan Wieck's avatar
Jan Wieck committed
2521 2522
			if (SPI_execp(qplan, upd_values, upd_nulls, 0) != SPI_OK_UPDATE)
				elog(ERROR, "SPI_execp() failed in RI_FKey_setnull_upd()");
2523

2524 2525
			SetUserId(save_uid);

Jan Wieck's avatar
Jan Wieck committed
2526
			if (SPI_finish() != SPI_OK_FINISH)
Bruce Momjian's avatar
Bruce Momjian committed
2527
				elog(WARNING, "SPI_finish() failed in RI_FKey_setnull_upd()");
Jan Wieck's avatar
Jan Wieck committed
2528

2529 2530
			heap_close(fk_rel, RowExclusiveLock);

2531
			return PointerGetDatum(NULL);
Jan Wieck's avatar
Jan Wieck committed
2532

2533
			/*
2534 2535
			 * Handle MATCH PARTIAL set null update.
			 */
Jan Wieck's avatar
Jan Wieck committed
2536 2537
		case RI_MATCH_TYPE_PARTIAL:
			elog(ERROR, "MATCH PARTIAL not yet supported");
2538
			return PointerGetDatum(NULL);
Jan Wieck's avatar
Jan Wieck committed
2539 2540
	}

2541
	/*
Jan Wieck's avatar
Jan Wieck committed
2542 2543
	 * Never reached
	 */
2544
	elog(ERROR, "internal error #9 in ri_triggers.c");
2545
	return PointerGetDatum(NULL);
2546 2547 2548 2549 2550 2551 2552 2553 2554
}


/* ----------
 * RI_FKey_setdefault_del -
 *
 *	Set foreign key references to defaults at delete event on PK table.
 * ----------
 */
2555 2556
Datum
RI_FKey_setdefault_del(PG_FUNCTION_ARGS)
2557
{
2558
	TriggerData *trigdata = (TriggerData *) fcinfo->context;
2559 2560 2561 2562 2563 2564 2565 2566 2567 2568 2569
	int			tgnargs;
	char	  **tgargs;
	Relation	fk_rel;
	Relation	pk_rel;
	HeapTuple	old_row;
	RI_QueryKey qkey;
	void	   *qplan;
	Datum		upd_values[RI_MAX_NUMKEYS];
	char		upd_nulls[RI_MAX_NUMKEYS + 1];
	bool		isnull;
	int			i;
2570 2571
	AclId		save_uid;
	AclId			fk_owner;
2572

2573
	ReferentialIntegritySnapshotOverride = true;
2574

2575 2576 2577
	/*
	 * Check that this is a valid trigger call on the right time and
	 * event.
2578
	 */
2579
	if (!CALLED_AS_TRIGGER(fcinfo))
2580
		elog(ERROR, "RI_FKey_setdefault_del() not fired by trigger manager");
2581 2582
	if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) ||
		!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
2583 2584 2585 2586
		elog(ERROR, "RI_FKey_setdefault_del() must be fired AFTER ROW");
	if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
		elog(ERROR, "RI_FKey_setdefault_del() must be fired for DELETE");

2587
	/*
2588
	 * Check for the correct # of call arguments
2589 2590
	 */
	tgnargs = trigdata->tg_trigger->tgnargs;
2591
	tgargs = trigdata->tg_trigger->tgargs;
2592 2593 2594 2595
	if (tgnargs < 4 || (tgnargs % 2) != 0)
		elog(ERROR, "wrong # of arguments in call to RI_FKey_setdefault_del()");
	if (tgnargs > RI_MAX_ARGUMENTS)
		elog(ERROR, "too many keys (%d max) in call to RI_FKey_setdefault_del()",
2596
			 RI_MAX_NUMKEYS);
2597

2598
	/*
2599 2600 2601
	 * Nothing to do if no column names to compare given
	 */
	if (tgnargs == 4)
2602
		return PointerGetDatum(NULL);
2603

2604 2605 2606
	/*
	 * Get the relation descriptors of the FK and PK tables and the old
	 * tuple.
2607 2608 2609
	 *
	 * fk_rel is opened in RowExclusiveLock mode since that's what our
	 * eventual UPDATE will get on it.
2610
	 */
2611 2612 2613 2614 2615 2616
	if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid))
		elog(ERROR, "No target table given for trigger \"%s\" on \"%s\""
			 "\n\tRemove these RI triggers and do ALTER TABLE ADD CONSTRAINT",
			 trigdata->tg_trigger->tgname,
			 RelationGetRelationName(trigdata->tg_relation));

2617
	fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowExclusiveLock);
2618
	pk_rel = trigdata->tg_relation;
2619
	old_row = trigdata->tg_trigtuple;
2620
	fk_owner = RelationGetForm(fk_rel)->relowner;
2621 2622 2623

	switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
	{
2624 2625 2626 2627 2628 2629 2630
			/* ----------
			 * SQL3 11.9 <referential constraint definition>
			 *	Gereral rules 6) a) iii):
			 *		MATCH <UNSPECIFIED> or MATCH FULL
			 *			... ON DELETE SET DEFAULT
			 * ----------
			 */
2631 2632 2633
		case RI_MATCH_TYPE_UNSPECIFIED:
		case RI_MATCH_TYPE_FULL:
			ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
2634 2635 2636
								 RI_PLAN_SETNULL_DEL_DOUPDATE,
								 fk_rel, pk_rel,
								 tgnargs, tgargs);
2637 2638 2639 2640 2641

			switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
			{
				case RI_KEYS_ALL_NULL:
				case RI_KEYS_SOME_NULL:
2642 2643

					/*
2644 2645 2646
					 * No update - MATCH FULL means there cannot be any
					 * reference to old key if it contains NULL
					 */
2647
					heap_close(fk_rel, RowExclusiveLock);
2648
					return PointerGetDatum(NULL);
2649

2650
				case RI_KEYS_NONE_NULL:
2651 2652

					/*
2653 2654 2655 2656 2657 2658
					 * Have a full qualified key - continue below
					 */
					break;
			}

			if (SPI_connect() != SPI_OK_CONNECT)
Bruce Momjian's avatar
Bruce Momjian committed
2659
				elog(WARNING, "SPI_connect() failed in RI_FKey_setdefault_del()");
2660

2661
			/*
2662
			 * Prepare a plan for the set default delete operation.
2663 2664
			 * Unfortunately we need to do it on every invocation because
			 * the default value could potentially change between calls.
2665 2666
			 */
			{
2667
				char		querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
Bruce Momjian's avatar
Bruce Momjian committed
2668
												 (MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS * 2];
2669 2670 2671 2672 2673
				char		qualstr[(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
				char		fkrelname[MAX_QUOTED_REL_NAME_LEN];
				char		attname[MAX_QUOTED_NAME_LEN];
				const char *querysep;
				const char *qualsep;
2674
				Oid			queryoids[RI_MAX_NUMKEYS];
2675 2676 2677 2678 2679
				Plan	   *spi_plan;
				AttrDefault *defval;
				TargetEntry *spi_qptle;
				int			i,
							j;
2680 2681 2682

				/* ----------
				 * The query string built is
2683
				 *	UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
2684 2685 2686 2687 2688 2689 2690
				 *			WHERE fkatt1 = $1 [AND ...]
				 * The type id's for the $ parameters are those of the
				 * corresponding PK attributes. Thus, SPI_prepare could
				 * eventually fail if the parser cannot identify some way
				 * how to compare these two types by '='.
				 * ----------
				 */
2691
				quoteRelationName(fkrelname, fk_rel);
2692
				snprintf(querystr, sizeof(querystr), "UPDATE ONLY %s SET", fkrelname);
2693 2694 2695 2696 2697
				qualstr[0] = '\0';
				querysep = "";
				qualsep = "WHERE";
				for (i = 0; i < qkey.nkeypairs; i++)
				{
2698 2699
					quoteOneName(attname,
								 tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
2700
					snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), "%s %s = NULL",
Bruce Momjian's avatar
Bruce Momjian committed
2701
							 querysep, attname);
2702
					snprintf(qualstr + strlen(qualstr), sizeof(qualstr) - strlen(qualstr), " %s %s = $%d",
Bruce Momjian's avatar
Bruce Momjian committed
2703
							 qualsep, attname, i + 1);
2704 2705 2706
					querysep = ",";
					qualsep = "AND";
					queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
2707
									 qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
2708 2709 2710
				}
				strcat(querystr, qualstr);

2711
				/*
2712 2713 2714 2715 2716
				 * Prepare the plan
				 */
				qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);

				/* ----------
2717 2718 2719 2720 2721 2722
				 * Here now follows very ugly code depending on internals
				 * of the SPI manager.
				 *
				 * EVIL EVIL EVIL (but must be - Jan)
				 *
				 * We replace the CONST NULL targetlist expressions
2723 2724 2725 2726
				 * in the generated plan by (any) default values found
				 * in the tuple constructor.
				 * ----------
				 */
2727
				spi_plan = (Plan *) lfirst(((_SPI_plan *) qplan)->ptlist);
2728 2729 2730 2731 2732 2733
				if (fk_rel->rd_att->constr != NULL)
					defval = fk_rel->rd_att->constr->defval;
				else
					defval = NULL;
				for (i = 0; i < qkey.nkeypairs && defval != NULL; i++)
				{
2734
					/*
2735 2736 2737 2738 2739
					 * For each key attribute lookup the tuple constructor
					 * for a corresponding default value
					 */
					for (j = 0; j < fk_rel->rd_att->constr->num_defval; j++)
					{
2740 2741
						if (defval[j].adnum ==
							qkey.keypair[i][RI_KEYPAIR_FK_IDX])
2742
						{
2743 2744 2745
							/*
							 * That's the one - push the expression from
							 * defval.adbin into the plan's targetlist
2746 2747
							 */
							spi_qptle = (TargetEntry *)
2748 2749
								nth(defval[j].adnum - 1,
									spi_plan->targetlist);
2750
							spi_qptle->expr = stringToNode(defval[j].adbin);
2751
							fix_opfuncids((Node *) spi_qptle->expr);
2752 2753 2754 2755 2756 2757 2758

							break;
						}
					}
				}
			}

2759
			/*
2760 2761 2762 2763 2764 2765
			 * We have a plan now. Build up the arguments for SPI_execp()
			 * from the key values in the deleted PK tuple.
			 */
			for (i = 0; i < qkey.nkeypairs; i++)
			{
				upd_values[i] = SPI_getbinval(old_row,
2766 2767 2768 2769
											  pk_rel->rd_att,
									  qkey.keypair[i][RI_KEYPAIR_PK_IDX],
											  &isnull);
				if (isnull)
2770 2771 2772 2773 2774 2775
					upd_nulls[i] = 'n';
				else
					upd_nulls[i] = ' ';
			}
			upd_nulls[i] = '\0';

2776
			/*
2777 2778
			 * Now update the existing references
			 */
2779 2780 2781
			save_uid = GetUserId();
			SetUserId(fk_owner);

2782 2783
			if (SPI_execp(qplan, upd_values, upd_nulls, 0) != SPI_OK_UPDATE)
				elog(ERROR, "SPI_execp() failed in RI_FKey_setdefault_del()");
2784

2785 2786
			SetUserId(save_uid);

2787
			if (SPI_finish() != SPI_OK_FINISH)
Bruce Momjian's avatar
Bruce Momjian committed
2788
				elog(WARNING, "SPI_finish() failed in RI_FKey_setdefault_del()");
2789

2790 2791
			heap_close(fk_rel, RowExclusiveLock);

2792
			return PointerGetDatum(NULL);
2793

2794
			/*
2795 2796
			 * Handle MATCH PARTIAL set null delete.
			 */
2797 2798
		case RI_MATCH_TYPE_PARTIAL:
			elog(ERROR, "MATCH PARTIAL not yet supported");
2799
			return PointerGetDatum(NULL);
2800 2801
	}

2802
	/*
2803 2804
	 * Never reached
	 */
2805
	elog(ERROR, "internal error #10 in ri_triggers.c");
2806
	return PointerGetDatum(NULL);
2807 2808 2809 2810 2811 2812 2813 2814 2815
}


/* ----------
 * RI_FKey_setdefault_upd -
 *
 *	Set foreign key references to defaults at update event on PK table.
 * ----------
 */
2816 2817
Datum
RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
2818
{
2819
	TriggerData *trigdata = (TriggerData *) fcinfo->context;
2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 2831 2832
	int			tgnargs;
	char	  **tgargs;
	Relation	fk_rel;
	Relation	pk_rel;
	HeapTuple	new_row;
	HeapTuple	old_row;
	RI_QueryKey qkey;
	void	   *qplan;
	Datum		upd_values[RI_MAX_NUMKEYS];
	char		upd_nulls[RI_MAX_NUMKEYS + 1];
	bool		isnull;
	int			i;
	int			match_type;
2833 2834
	AclId		save_uid;
	AclId		fk_owner;
2835

2836
	ReferentialIntegritySnapshotOverride = true;
2837

2838 2839 2840
	/*
	 * Check that this is a valid trigger call on the right time and
	 * event.
2841
	 */
2842
	if (!CALLED_AS_TRIGGER(fcinfo))
2843
		elog(ERROR, "RI_FKey_setdefault_upd() not fired by trigger manager");
2844 2845
	if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) ||
		!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
2846 2847 2848 2849
		elog(ERROR, "RI_FKey_setdefault_upd() must be fired AFTER ROW");
	if (!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
		elog(ERROR, "RI_FKey_setdefault_upd() must be fired for UPDATE");

2850
	/*
2851
	 * Check for the correct # of call arguments
2852 2853
	 */
	tgnargs = trigdata->tg_trigger->tgnargs;
2854
	tgargs = trigdata->tg_trigger->tgargs;
2855 2856 2857 2858
	if (tgnargs < 4 || (tgnargs % 2) != 0)
		elog(ERROR, "wrong # of arguments in call to RI_FKey_setdefault_upd()");
	if (tgnargs > RI_MAX_ARGUMENTS)
		elog(ERROR, "too many keys (%d max) in call to RI_FKey_setdefault_upd()",
2859
			 RI_MAX_NUMKEYS);
2860

2861
	/*
2862 2863 2864
	 * Nothing to do if no column names to compare given
	 */
	if (tgnargs == 4)
2865
		return PointerGetDatum(NULL);
2866

2867 2868 2869
	/*
	 * Get the relation descriptors of the FK and PK tables and the old
	 * tuple.
2870 2871 2872
	 *
	 * fk_rel is opened in RowExclusiveLock mode since that's what our
	 * eventual UPDATE will get on it.
2873
	 */
2874 2875 2876 2877 2878 2879
	if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid))
		elog(ERROR, "No target table given for trigger \"%s\" on \"%s\""
			 "\n\tRemove these RI triggers and do ALTER TABLE ADD CONSTRAINT",
			 trigdata->tg_trigger->tgname,
			 RelationGetRelationName(trigdata->tg_relation));

2880
	fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, RowExclusiveLock);
2881
	pk_rel = trigdata->tg_relation;
2882 2883
	new_row = trigdata->tg_newtuple;
	old_row = trigdata->tg_trigtuple;
2884
	fk_owner = RelationGetForm(fk_rel)->relowner;
2885

2886 2887 2888
	match_type = ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]);

	switch (match_type)
2889
	{
2890 2891 2892 2893 2894 2895 2896
			/* ----------
			 * SQL3 11.9 <referential constraint definition>
			 *	Gereral rules 7) a) iii):
			 *		MATCH <UNSPECIFIED> or MATCH FULL
			 *			... ON UPDATE SET DEFAULT
			 * ----------
			 */
2897 2898 2899
		case RI_MATCH_TYPE_UNSPECIFIED:
		case RI_MATCH_TYPE_FULL:
			ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
2900 2901 2902
								 RI_PLAN_SETNULL_DEL_DOUPDATE,
								 fk_rel, pk_rel,
								 tgnargs, tgargs);
2903 2904 2905 2906 2907

			switch (ri_NullCheck(pk_rel, old_row, &qkey, RI_KEYPAIR_PK_IDX))
			{
				case RI_KEYS_ALL_NULL:
				case RI_KEYS_SOME_NULL:
2908 2909

					/*
2910 2911 2912
					 * No update - MATCH FULL means there cannot be any
					 * reference to old key if it contains NULL
					 */
2913
					heap_close(fk_rel, RowExclusiveLock);
2914
					return PointerGetDatum(NULL);
2915

2916
				case RI_KEYS_NONE_NULL:
2917 2918

					/*
2919 2920 2921 2922 2923
					 * Have a full qualified key - continue below
					 */
					break;
			}

2924
			/*
2925 2926 2927
			 * No need to do anything if old and new keys are equal
			 */
			if (ri_KeysEqual(pk_rel, old_row, new_row, &qkey,
2928
							 RI_KEYPAIR_PK_IDX))
2929 2930
			{
				heap_close(fk_rel, RowExclusiveLock);
2931
				return PointerGetDatum(NULL);
2932
			}
2933 2934

			if (SPI_connect() != SPI_OK_CONNECT)
Bruce Momjian's avatar
Bruce Momjian committed
2935
				elog(WARNING, "SPI_connect() failed in RI_FKey_setdefault_upd()");
2936

2937
			/*
2938
			 * Prepare a plan for the set default delete operation.
2939 2940
			 * Unfortunately we need to do it on every invocation because
			 * the default value could potentially change between calls.
2941 2942
			 */
			{
2943
				char		querystr[MAX_QUOTED_REL_NAME_LEN + 100 +
Bruce Momjian's avatar
Bruce Momjian committed
2944
												 (MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS * 2];
2945 2946 2947 2948 2949
				char		qualstr[(MAX_QUOTED_NAME_LEN + 32) * RI_MAX_NUMKEYS];
				char		fkrelname[MAX_QUOTED_REL_NAME_LEN];
				char		attname[MAX_QUOTED_NAME_LEN];
				const char *querysep;
				const char *qualsep;
2950
				Oid			queryoids[RI_MAX_NUMKEYS];
2951 2952 2953 2954 2955
				Plan	   *spi_plan;
				AttrDefault *defval;
				TargetEntry *spi_qptle;
				int			i,
							j;
2956 2957 2958

				/* ----------
				 * The query string built is
2959
				 *	UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
2960 2961 2962 2963 2964 2965 2966
				 *			WHERE fkatt1 = $1 [AND ...]
				 * The type id's for the $ parameters are those of the
				 * corresponding PK attributes. Thus, SPI_prepare could
				 * eventually fail if the parser cannot identify some way
				 * how to compare these two types by '='.
				 * ----------
				 */
2967
				quoteRelationName(fkrelname, fk_rel);
2968
				snprintf(querystr, sizeof(querystr), "UPDATE ONLY %s SET", fkrelname);
2969 2970 2971 2972 2973
				qualstr[0] = '\0';
				querysep = "";
				qualsep = "WHERE";
				for (i = 0; i < qkey.nkeypairs; i++)
				{
2974 2975
					quoteOneName(attname,
								 tgargs[RI_FIRST_ATTNAME_ARGNO + i * 2 + RI_KEYPAIR_FK_IDX]);
Bruce Momjian's avatar
Bruce Momjian committed
2976

2977 2978 2979
					/*
					 * MATCH <unspecified> - only change columns
					 * corresponding to changed columns in pk_rel's key
2980 2981 2982
					 */
					if (match_type == RI_MATCH_TYPE_FULL ||
						!ri_OneKeyEqual(pk_rel, i, old_row,
2983
									  new_row, &qkey, RI_KEYPAIR_PK_IDX))
2984
					{
2985
						snprintf(querystr + strlen(querystr), sizeof(querystr) - strlen(querystr), "%s %s = NULL",
Bruce Momjian's avatar
Bruce Momjian committed
2986
								 querysep, attname);
2987 2988
						querysep = ",";
					}
2989
					snprintf(qualstr + strlen(qualstr), sizeof(qualstr) - strlen(qualstr), " %s %s = $%d",
Bruce Momjian's avatar
Bruce Momjian committed
2990
							 qualsep, attname, i + 1);
2991 2992
					qualsep = "AND";
					queryoids[i] = SPI_gettypeid(pk_rel->rd_att,
2993
									 qkey.keypair[i][RI_KEYPAIR_PK_IDX]);
2994 2995 2996
				}
				strcat(querystr, qualstr);

2997
				/*
2998 2999 3000 3001
				 * Prepare the plan
				 */
				qplan = SPI_prepare(querystr, qkey.nkeypairs, queryoids);

3002 3003 3004 3005
				/*
				 * Now replace the CONST NULL targetlist expressions in
				 * the generated plan by (any) default values found in the
				 * tuple constructor.
3006
				 */
3007
				spi_plan = (Plan *) lfirst(((_SPI_plan *) qplan)->ptlist);
3008 3009 3010 3011 3012 3013
				if (fk_rel->rd_att->constr != NULL)
					defval = fk_rel->rd_att->constr->defval;
				else
					defval = NULL;
				for (i = 0; i < qkey.nkeypairs && defval != NULL; i++)
				{
3014 3015 3016 3017 3018
					/*
					 * MATCH <unspecified> - only change columns
					 * corresponding to changed columns in pk_rel's key.
					 * This conditional must match the one in the loop
					 * above that built the SET attrn=NULL list.
3019
					 */
3020 3021
					if (match_type == RI_MATCH_TYPE_FULL ||
						!ri_OneKeyEqual(pk_rel, i, old_row,
3022
									  new_row, &qkey, RI_KEYPAIR_PK_IDX))
3023
					{
3024 3025 3026
						/*
						 * For each key attribute lookup the tuple
						 * constructor for a corresponding default value
3027 3028
						 */
						for (j = 0; j < fk_rel->rd_att->constr->num_defval; j++)
3029
						{
3030 3031
							if (defval[j].adnum ==
								qkey.keypair[i][RI_KEYPAIR_FK_IDX])
3032
							{
3033
								/*
3034
								 * That's the one - push the expression
3035 3036
								 * from defval.adbin into the plan's
								 * targetlist
3037 3038
								 */
								spi_qptle = (TargetEntry *)
3039
									nth(defval[j].adnum - 1,
3040
										spi_plan->targetlist);
3041
								spi_qptle->expr = stringToNode(defval[j].adbin);
3042
								fix_opfuncids((Node *) spi_qptle->expr);
3043

3044
								break;
3045
							}
3046 3047 3048 3049 3050
						}
					}
				}
			}

3051
			/*
3052 3053 3054 3055 3056 3057
			 * We have a plan now. Build up the arguments for SPI_execp()
			 * from the key values in the deleted PK tuple.
			 */
			for (i = 0; i < qkey.nkeypairs; i++)
			{
				upd_values[i] = SPI_getbinval(old_row,
3058 3059 3060 3061
											  pk_rel->rd_att,
									  qkey.keypair[i][RI_KEYPAIR_PK_IDX],
											  &isnull);
				if (isnull)
3062 3063 3064 3065 3066 3067
					upd_nulls[i] = 'n';
				else
					upd_nulls[i] = ' ';
			}
			upd_nulls[i] = '\0';

3068
			/*
3069 3070
			 * Now update the existing references
			 */
3071 3072 3073
			save_uid = GetUserId();
			SetUserId(fk_owner);

3074 3075
			if (SPI_execp(qplan, upd_values, upd_nulls, 0) != SPI_OK_UPDATE)
				elog(ERROR, "SPI_execp() failed in RI_FKey_setdefault_upd()");
3076

3077 3078
			SetUserId(save_uid);

3079
			if (SPI_finish() != SPI_OK_FINISH)
Bruce Momjian's avatar
Bruce Momjian committed
3080
				elog(WARNING, "SPI_finish() failed in RI_FKey_setdefault_upd()");
3081

3082 3083
			heap_close(fk_rel, RowExclusiveLock);

3084
			return PointerGetDatum(NULL);
3085

3086
			/*
3087 3088
			 * Handle MATCH PARTIAL set null delete.
			 */
3089 3090
		case RI_MATCH_TYPE_PARTIAL:
			elog(ERROR, "MATCH PARTIAL not yet supported");
3091
			return PointerGetDatum(NULL);
3092 3093
	}

3094
	/*
3095 3096
	 * Never reached
	 */
3097
	elog(ERROR, "internal error #11 in ri_triggers.c");
3098
	return PointerGetDatum(NULL);
3099 3100 3101
}


3102 3103 3104 3105 3106
/* ----------
 * RI_FKey_keyequal_upd -
 *
 *	Check if we have a key change on update.
 *
3107
 *	This is not a real trigger procedure. It is used by the deferred
3108 3109 3110 3111
 *	trigger queue manager to detect "triggered data change violation".
 * ----------
 */
bool
3112
RI_FKey_keyequal_upd(TriggerData *trigdata)
3113
{
3114 3115 3116 3117 3118 3119 3120
	int			tgnargs;
	char	  **tgargs;
	Relation	fk_rel;
	Relation	pk_rel;
	HeapTuple	new_row;
	HeapTuple	old_row;
	RI_QueryKey qkey;
3121

3122
	/*
3123
	 * Check for the correct # of call arguments
3124 3125
	 */
	tgnargs = trigdata->tg_trigger->tgnargs;
3126
	tgargs = trigdata->tg_trigger->tgargs;
3127 3128 3129 3130
	if (tgnargs < 4 || (tgnargs % 2) != 0)
		elog(ERROR, "wrong # of arguments in call to RI_FKey_keyequal_upd()");
	if (tgnargs > RI_MAX_ARGUMENTS)
		elog(ERROR, "too many keys (%d max) in call to RI_FKey_keyequal_upd()",
3131
			 RI_MAX_NUMKEYS);
3132

3133
	/*
3134 3135 3136 3137 3138
	 * Nothing to do if no column names to compare given
	 */
	if (tgnargs == 4)
		return true;

3139 3140 3141
	/*
	 * Get the relation descriptors of the FK and PK tables and the new
	 * and old tuple.
3142 3143
	 *
	 * Use minimal locking for fk_rel here.
3144
	 */
3145 3146 3147 3148 3149 3150
	if (!OidIsValid(trigdata->tg_trigger->tgconstrrelid))
		elog(ERROR, "No target table given for trigger \"%s\" on \"%s\""
			 "\n\tRemove these RI triggers and do ALTER TABLE ADD CONSTRAINT",
			 trigdata->tg_trigger->tgname,
			 RelationGetRelationName(trigdata->tg_relation));

3151
	fk_rel = heap_open(trigdata->tg_trigger->tgconstrrelid, AccessShareLock);
3152
	pk_rel = trigdata->tg_relation;
3153 3154 3155 3156 3157
	new_row = trigdata->tg_newtuple;
	old_row = trigdata->tg_trigtuple;

	switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO]))
	{
3158
			/*
3159 3160
			 * MATCH <UNSPECIFIED>
			 */
3161 3162 3163
		case RI_MATCH_TYPE_UNSPECIFIED:
		case RI_MATCH_TYPE_FULL:
			ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid,
3164 3165 3166
								 0,
								 fk_rel, pk_rel,
								 tgnargs, tgargs);
3167 3168

			heap_close(fk_rel, AccessShareLock);
3169

3170
			/*
3171 3172 3173
			 * Return if key's are equal
			 */
			return ri_KeysEqual(pk_rel, old_row, new_row, &qkey,
3174
								RI_KEYPAIR_PK_IDX);
3175

3176
			/*
3177 3178
			 * Handle MATCH PARTIAL set null delete.
			 */
3179 3180 3181 3182 3183
		case RI_MATCH_TYPE_PARTIAL:
			elog(ERROR, "MATCH PARTIAL not yet supported");
			break;
	}

3184
	/*
3185 3186
	 * Never reached
	 */
3187
	elog(ERROR, "internal error #12 in ri_triggers.c");
3188 3189 3190 3191
	return false;
}


3192 3193 3194 3195 3196 3197 3198 3199 3200



/* ----------
 * Local functions below
 * ----------
 */


3201 3202 3203 3204 3205 3206 3207 3208 3209 3210 3211 3212 3213 3214 3215 3216 3217 3218 3219
/*
 * quoteOneName --- safely quote a single SQL name
 *
 * buffer must be MAX_QUOTED_NAME_LEN long (includes room for \0)
 */
static void
quoteOneName(char *buffer, const char *name)
{
	/* Rather than trying to be smart, just always quote it. */
	*buffer++ = '"';
	while (*name)
	{
		if (*name == '"')
			*buffer++ = '"';
		*buffer++ = *name++;
	}
	*buffer++ = '"';
	*buffer = '\0';
}
3220

3221 3222 3223 3224 3225 3226 3227 3228
/*
 * quoteRelationName --- safely quote a fully qualified relation name
 *
 * buffer must be MAX_QUOTED_REL_NAME_LEN long (includes room for \0)
 */
static void
quoteRelationName(char *buffer, Relation rel)
{
3229
	quoteOneName(buffer, get_namespace_name(RelationGetNamespace(rel)));
3230 3231 3232 3233
	buffer += strlen(buffer);
	*buffer++ = '.';
	quoteOneName(buffer, RelationGetRelationName(rel));
}
3234 3235 3236 3237 3238 3239 3240 3241 3242 3243 3244


/* ----------
 * ri_DetermineMatchType -
 *
 *	Convert the MATCH TYPE string into a switchable int
 * ----------
 */
static int
ri_DetermineMatchType(char *str)
{
3245
	if (strcmp(str, "UNSPECIFIED") == 0)
3246
		return RI_MATCH_TYPE_UNSPECIFIED;
3247
	if (strcmp(str, "FULL") == 0)
3248
		return RI_MATCH_TYPE_FULL;
3249
	if (strcmp(str, "PARTIAL") == 0)
3250 3251 3252 3253 3254 3255 3256 3257 3258 3259 3260 3261 3262 3263 3264 3265 3266 3267 3268 3269 3270 3271 3272 3273
		return RI_MATCH_TYPE_PARTIAL;

	elog(ERROR, "unrecognized referential integrity MATCH type '%s'", str);
	return 0;
}


/* ----------
 * ri_BuildQueryKeyFull -
 *
 *	Build up a new hashtable key for a prepared SPI plan of a
 *	constraint trigger of MATCH FULL. The key consists of:
 *
 *		constr_type is FULL
 *		constr_id is the OID of the pg_trigger row that invoked us
 *		constr_queryno is an internal number of the query inside the proc
 *		fk_relid is the OID of referencing relation
 *		pk_relid is the OID of referenced relation
 *		nkeypairs is the number of keypairs
 *		following are the attribute number keypairs of the trigger invocation
 *
 *	At least for MATCH FULL this builds a unique key per plan.
 * ----------
 */
3274
static void
3275
ri_BuildQueryKeyFull(RI_QueryKey *key, Oid constr_id, int32 constr_queryno,
3276 3277
					 Relation fk_rel, Relation pk_rel,
					 int argc, char **argv)
3278
{
3279 3280 3281
	int			i;
	int			j;
	int			fno;
3282

3283
	/*
3284 3285
	 * Initialize the key and fill in type, oid's and number of keypairs
	 */
3286 3287 3288 3289 3290 3291 3292
	memset((void *) key, 0, sizeof(RI_QueryKey));
	key->constr_type = RI_MATCH_TYPE_FULL;
	key->constr_id = constr_id;
	key->constr_queryno = constr_queryno;
	key->fk_relid = fk_rel->rd_id;
	key->pk_relid = pk_rel->rd_id;
	key->nkeypairs = (argc - RI_FIRST_ATTNAME_ARGNO) / 2;
3293

3294
	/*
3295 3296 3297 3298 3299 3300 3301 3302
	 * Lookup the attribute numbers of the arguments to the trigger call
	 * and fill in the keypairs.
	 */
	for (i = 0, j = RI_FIRST_ATTNAME_ARGNO; j < argc; i++, j += 2)
	{
		fno = SPI_fnumber(fk_rel->rd_att, argv[j]);
		if (fno == SPI_ERROR_NOATTRIBUTE)
			elog(ERROR, "constraint %s: table %s does not have an attribute %s",
3303
				 argv[RI_CONSTRAINT_NAME_ARGNO],
3304
				 RelationGetRelationName(fk_rel),
3305
				 argv[j]);
3306 3307 3308 3309 3310
		key->keypair[i][RI_KEYPAIR_FK_IDX] = fno;

		fno = SPI_fnumber(pk_rel->rd_att, argv[j + 1]);
		if (fno == SPI_ERROR_NOATTRIBUTE)
			elog(ERROR, "constraint %s: table %s does not have an attribute %s",
3311
				 argv[RI_CONSTRAINT_NAME_ARGNO],
3312
				 RelationGetRelationName(pk_rel),
3313
				 argv[j + 1]);
3314 3315 3316 3317
		key->keypair[i][RI_KEYPAIR_PK_IDX] = fno;
	}
}

3318 3319 3320 3321 3322 3323 3324 3325 3326 3327 3328 3329 3330 3331 3332 3333 3334 3335
/* ----------
 * ri_BuildQueryKeyPkCheck -
 *
 *	Build up a new hashtable key for a prepared SPI plan of a
 *	check for PK rows in noaction triggers.
 *
 *		constr_type is FULL
 *		constr_id is the OID of the pg_trigger row that invoked us
 *		constr_queryno is an internal number of the query inside the proc
 *		pk_relid is the OID of referenced relation
 *		nkeypairs is the number of keypairs
 *		following are the attribute number keypairs of the trigger invocation
 *
 *	At least for MATCH FULL this builds a unique key per plan.
 * ----------
 */
static void
ri_BuildQueryKeyPkCheck(RI_QueryKey *key, Oid constr_id, int32 constr_queryno,
Bruce Momjian's avatar
Bruce Momjian committed
3336 3337
						Relation pk_rel,
						int argc, char **argv)
3338 3339 3340 3341 3342 3343 3344 3345 3346 3347 3348 3349 3350 3351 3352 3353 3354 3355 3356 3357 3358 3359 3360 3361 3362 3363 3364 3365 3366 3367 3368 3369 3370
{
	int			i;
	int			j;
	int			fno;

	/*
	 * 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;
	key->fk_relid = 0;
	key->pk_relid = pk_rel->rd_id;
	key->nkeypairs = (argc - RI_FIRST_ATTNAME_ARGNO) / 2;

	/*
	 * Lookup the attribute numbers of the arguments to the trigger call
	 * and fill in the keypairs.
	 */
	for (i = 0, j = RI_FIRST_ATTNAME_ARGNO + RI_KEYPAIR_PK_IDX; j < argc; i++, j += 2)
	{
		fno = SPI_fnumber(pk_rel->rd_att, argv[j]);
		if (fno == SPI_ERROR_NOATTRIBUTE)
			elog(ERROR, "constraint %s: table %s does not have an attribute %s",
				 argv[RI_CONSTRAINT_NAME_ARGNO],
				 RelationGetRelationName(pk_rel),
				 argv[j + 1]);
		key->keypair[i][RI_KEYPAIR_PK_IDX] = fno;
		key->keypair[i][RI_KEYPAIR_FK_IDX] = 0;
	}
}

3371 3372 3373 3374 3375 3376 3377 3378 3379

/* ----------
 * ri_NullCheck -
 *
 *	Determine the NULL state of all key values in a tuple
 *
 *	Returns one of RI_KEYS_ALL_NULL, RI_KEYS_NONE_NULL or RI_KEYS_SOME_NULL.
 * ----------
 */
3380
static int
3381 3382
ri_NullCheck(Relation rel, HeapTuple tup, RI_QueryKey *key, int pairidx)
{
3383 3384 3385 3386
	int			i;
	bool		isnull;
	bool		allnull = true;
	bool		nonenull = true;
3387 3388 3389 3390 3391 3392 3393 3394 3395 3396 3397 3398 3399 3400 3401 3402 3403 3404 3405 3406 3407 3408 3409 3410 3411 3412 3413 3414 3415 3416 3417 3418 3419 3420

	for (i = 0; i < key->nkeypairs; i++)
	{
		isnull = false;
		SPI_getbinval(tup, rel->rd_att, key->keypair[i][pairidx], &isnull);
		if (isnull)
			nonenull = false;
		else
			allnull = false;
	}

	if (allnull)
		return RI_KEYS_ALL_NULL;

	if (nonenull)
		return RI_KEYS_NONE_NULL;

	return RI_KEYS_SOME_NULL;
}


/* ----------
 * ri_InitHashTables -
 *
 *	Initialize our internal hash tables for prepared
 *	query plans and equal operators.
 * ----------
 */
static void
ri_InitHashTables(void)
{
	HASHCTL		ctl;

	memset(&ctl, 0, sizeof(ctl));
3421
	ctl.keysize = sizeof(RI_QueryKey);
3422 3423
	ctl.entrysize = sizeof(RI_QueryHashEntry);
	ctl.hash = tag_hash;
3424 3425
	ri_query_cache = hash_create("RI query cache", RI_INIT_QUERYHASHSIZE,
								 &ctl, HASH_ELEM | HASH_FUNCTION);
3426

3427
	ctl.keysize = sizeof(Oid);
3428
	ctl.entrysize = sizeof(RI_OpreqHashEntry);
3429
	ctl.hash = tag_hash;
3430 3431
	ri_opreq_cache = hash_create("RI OpReq cache", RI_INIT_OPREQHASHSIZE,
								 &ctl, HASH_ELEM | HASH_FUNCTION);
3432 3433 3434 3435 3436 3437 3438 3439 3440 3441 3442 3443 3444
}


/* ----------
 * ri_FetchPreparedPlan -
 *
 *	Lookup for a query key in our private hash table of prepared
 *	and saved SPI execution plans. Return the plan if found or NULL.
 * ----------
 */
static void *
ri_FetchPreparedPlan(RI_QueryKey *key)
{
3445
	RI_QueryHashEntry *entry;
3446

3447
	/*
3448 3449 3450 3451 3452
	 * On the first call initialize the hashtable
	 */
	if (!ri_query_cache)
		ri_InitHashTables();

3453
	/*
3454 3455
	 * Lookup for the key
	 */
3456
	entry = (RI_QueryHashEntry *) hash_search(ri_query_cache,
3457
											  (void *) key,
3458
											  HASH_FIND, NULL);
3459 3460 3461 3462 3463 3464 3465 3466 3467 3468 3469 3470 3471 3472 3473
	if (entry == NULL)
		return NULL;
	return entry->plan;
}


/* ----------
 * ri_HashPreparedPlan -
 *
 *	Add another plan to our private SPI query plan hashtable.
 * ----------
 */
static void
ri_HashPreparedPlan(RI_QueryKey *key, void *plan)
{
3474 3475
	RI_QueryHashEntry *entry;
	bool		found;
3476

3477
	/*
3478 3479 3480 3481 3482
	 * On the first call initialize the hashtable
	 */
	if (!ri_query_cache)
		ri_InitHashTables();

3483
	/*
3484 3485
	 * Add the new plan.
	 */
3486
	entry = (RI_QueryHashEntry *) hash_search(ri_query_cache,
3487 3488
											  (void *) key,
											  HASH_ENTER, &found);
3489
	if (entry == NULL)
3490
		elog(ERROR, "out of memory for RI plan cache");
3491 3492 3493 3494 3495 3496 3497 3498 3499 3500 3501
	entry->plan = plan;
}


/* ----------
 * ri_KeysEqual -
 *
 *	Check if all key values in OLD and NEW are equal.
 * ----------
 */
static bool
3502 3503
ri_KeysEqual(Relation rel, HeapTuple oldtup, HeapTuple newtup,
			 RI_QueryKey *key, int pairidx)
3504
{
3505 3506 3507 3508 3509
	int			i;
	Oid			typeid;
	Datum		oldvalue;
	Datum		newvalue;
	bool		isnull;
3510 3511 3512

	for (i = 0; i < key->nkeypairs; i++)
	{
3513
		/*
3514 3515
		 * Get one attributes oldvalue. If it is NULL - they're not equal.
		 */
3516 3517
		oldvalue = SPI_getbinval(oldtup, rel->rd_att,
								 key->keypair[i][pairidx], &isnull);
3518 3519 3520
		if (isnull)
			return false;

3521
		/*
3522 3523
		 * Get one attributes oldvalue. If it is NULL - they're not equal.
		 */
3524 3525
		newvalue = SPI_getbinval(newtup, rel->rd_att,
								 key->keypair[i][pairidx], &isnull);
3526 3527 3528
		if (isnull)
			return false;

3529 3530 3531
		/*
		 * Get the attributes type OID and call the '=' operator to
		 * compare the values.
3532 3533 3534 3535 3536 3537 3538 3539 3540 3541
		 */
		typeid = SPI_gettypeid(rel->rd_att, key->keypair[i][pairidx]);
		if (!ri_AttributesEqual(typeid, oldvalue, newvalue))
			return false;
	}

	return true;
}


3542 3543 3544 3545 3546 3547 3548
/* ----------
 * ri_AllKeysUnequal -
 *
 *	Check if all key values in OLD and NEW are not equal.
 * ----------
 */
static bool
3549 3550
ri_AllKeysUnequal(Relation rel, HeapTuple oldtup, HeapTuple newtup,
				  RI_QueryKey *key, int pairidx)
3551
{
3552 3553 3554 3555 3556 3557
	int			i;
	Oid			typeid;
	Datum		oldvalue;
	Datum		newvalue;
	bool		isnull;
	bool		keys_unequal;
3558 3559 3560 3561

	keys_unequal = true;
	for (i = 0; keys_unequal && i < key->nkeypairs; i++)
	{
3562
		/*
3563 3564
		 * Get one attributes oldvalue. If it is NULL - they're not equal.
		 */
3565 3566
		oldvalue = SPI_getbinval(oldtup, rel->rd_att,
								 key->keypair[i][pairidx], &isnull);
3567 3568 3569
		if (isnull)
			continue;

3570
		/*
3571 3572
		 * Get one attributes oldvalue. If it is NULL - they're not equal.
		 */
3573 3574
		newvalue = SPI_getbinval(newtup, rel->rd_att,
								 key->keypair[i][pairidx], &isnull);
3575 3576 3577
		if (isnull)
			continue;

3578 3579 3580
		/*
		 * Get the attributes type OID and call the '=' operator to
		 * compare the values.
3581 3582 3583 3584 3585 3586 3587 3588 3589 3590 3591 3592 3593 3594 3595 3596
		 */
		typeid = SPI_gettypeid(rel->rd_att, key->keypair[i][pairidx]);
		if (!ri_AttributesEqual(typeid, oldvalue, newvalue))
			continue;
		keys_unequal = false;
	}

	return keys_unequal;
}


/* ----------
 * ri_OneKeyEqual -
 *
 *	Check if one key value in OLD and NEW is equal.
 *
3597 3598
 *	ri_KeysEqual could call this but would run a bit slower.  For
 *	now, let's duplicate the code.
3599 3600 3601
 * ----------
 */
static bool
3602 3603
ri_OneKeyEqual(Relation rel, int column, HeapTuple oldtup, HeapTuple newtup,
			   RI_QueryKey *key, int pairidx)
3604
{
3605 3606 3607 3608
	Oid			typeid;
	Datum		oldvalue;
	Datum		newvalue;
	bool		isnull;
3609

3610
	/*
3611 3612
	 * Get one attributes oldvalue. If it is NULL - they're not equal.
	 */
3613 3614
	oldvalue = SPI_getbinval(oldtup, rel->rd_att,
							 key->keypair[column][pairidx], &isnull);
3615 3616 3617
	if (isnull)
		return false;

3618
	/*
3619 3620
	 * Get one attributes oldvalue. If it is NULL - they're not equal.
	 */
3621 3622
	newvalue = SPI_getbinval(newtup, rel->rd_att,
							 key->keypair[column][pairidx], &isnull);
3623 3624 3625
	if (isnull)
		return false;

3626 3627 3628
	/*
	 * Get the attributes type OID and call the '=' operator to compare
	 * the values.
3629 3630 3631
	 */
	typeid = SPI_gettypeid(rel->rd_att, key->keypair[column][pairidx]);
	if (!ri_AttributesEqual(typeid, oldvalue, newvalue))
3632
		return false;
3633 3634 3635 3636 3637

	return true;
}


3638 3639 3640
/* ----------
 * ri_AttributesEqual -
 *
3641
 *	Call the type specific '=' operator comparison function
3642
 *	for two values.
3643 3644
 *
 *	NB: we have already checked that neither value is null.
3645 3646 3647 3648 3649
 * ----------
 */
static bool
ri_AttributesEqual(Oid typeid, Datum oldvalue, Datum newvalue)
{
3650 3651
	RI_OpreqHashEntry *entry;
	bool		found;
3652

3653
	/*
3654 3655
	 * On the first call initialize the hashtable
	 */
3656
	if (!ri_opreq_cache)
3657 3658
		ri_InitHashTables();

3659
	/*
3660 3661
	 * Try to find the '=' operator for this type in our cache
	 */
3662
	entry = (RI_OpreqHashEntry *) hash_search(ri_opreq_cache,
3663
											  (void *) &typeid,
3664
											  HASH_FIND, NULL);
3665

3666
	/*
3667 3668
	 * If not found, lookup the operator, then do the function manager
	 * lookup, and remember that info.
3669
	 */
3670
	if (!entry)
3671
	{
3672 3673
		Oid			opr_proc;
		FmgrInfo	finfo;
3674

3675
		opr_proc = equality_oper_funcid(typeid);
3676 3677 3678

		/*
		 * Since fmgr_info could fail, call it *before* creating the
3679 3680 3681 3682
		 * hashtable entry --- otherwise we could elog leaving an
		 * incomplete entry in the hashtable.  Also, because this will be
		 * a permanent table entry, we must make sure any subsidiary
		 * structures of the fmgr record are kept in TopMemoryContext.
3683
		 */
3684
		fmgr_info_cxt(opr_proc, &finfo, TopMemoryContext);
3685

3686
		entry = (RI_OpreqHashEntry *) hash_search(ri_opreq_cache,
3687 3688
												  (void *) &typeid,
												  HASH_ENTER, &found);
3689
		if (entry == NULL)
3690
			elog(ERROR, "out of memory for RI operator cache");
3691

3692
		entry->typeid = typeid;
3693
		memcpy(&(entry->oprfmgrinfo), &finfo, sizeof(FmgrInfo));
3694 3695
	}

3696
	/*
3697 3698
	 * Call the type specific '=' function
	 */
3699 3700
	return DatumGetBool(FunctionCall2(&(entry->oprfmgrinfo),
									  oldvalue, newvalue));
3701
}