ri_triggers.c

来自「postgresql8.3.4源码,开源数据库」· C语言 代码 · 共 2,476 行 · 第 1/5 页

			/*
			 * We have a plan now. Run it to check for existing references.
			 */
			ri_PerformCheck(&qkey, qplan,
							fk_rel, pk_rel,
							old_row, NULL,
							true,		/* must detect new rows */
							SPI_OK_SELECT,
							NameStr(riinfo.conname));

			if (SPI_finish() != SPI_OK_FINISH)
				elog(ERROR, "SPI_finish failed");

			heap_close(fk_rel, RowShareLock);

			return PointerGetDatum(NULL);

			/*
			 * Handle MATCH PARTIAL noaction update.
			 */
		case FKCONSTR_MATCH_PARTIAL:
			ereport(ERROR,
					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
					 errmsg("MATCH PARTIAL not yet implemented")));
			return PointerGetDatum(NULL);
	}

	/*
	 * Never reached
	 */
	elog(ERROR, "invalid confmatchtype");
	return PointerGetDatum(NULL);
}


/* ----------
 * RI_FKey_cascade_del -
 *
 *	Cascaded delete foreign key references at delete event on PK table.
 * ----------
 */
Datum
RI_FKey_cascade_del(PG_FUNCTION_ARGS)
{
	TriggerData *trigdata = (TriggerData *) fcinfo->context;
	RI_ConstraintInfo riinfo;
	Relation	fk_rel;
	Relation	pk_rel;
	HeapTuple	old_row;
	RI_QueryKey qkey;
	SPIPlanPtr	qplan;
	int			i;

	/*
	 * Check that this is a valid trigger call on the right time and event.
	 */
	ri_CheckTrigger(fcinfo, "RI_FKey_cascade_del", RI_TRIGTYPE_DELETE);

	/*
	 * Get arguments.
	 */
	ri_FetchConstraintInfo(&riinfo,
						   trigdata->tg_trigger, trigdata->tg_relation, true);

	/*
	 * Nothing to do if no column names to compare given
	 */
	if (riinfo.nkeys == 0)
		return PointerGetDatum(NULL);

	/*
	 * Get the relation descriptors of the FK and PK tables and the old tuple.
	 *
	 * fk_rel is opened in RowExclusiveLock mode since that's what our
	 * eventual DELETE will get on it.
	 */
	fk_rel = heap_open(riinfo.fk_relid, RowExclusiveLock);
	pk_rel = trigdata->tg_relation;
	old_row = trigdata->tg_trigtuple;

	switch (riinfo.confmatchtype)
	{
			/* ----------
			 * SQL3 11.9 <referential constraint definition>
			 *	Gereral rules 6) a) i):
			 *		MATCH <unspecified> or MATCH FULL
			 *			... ON DELETE CASCADE
			 * ----------
			 */
		case FKCONSTR_MATCH_UNSPECIFIED:
		case FKCONSTR_MATCH_FULL:
			ri_BuildQueryKeyFull(&qkey, &riinfo,
								 RI_PLAN_CASCADE_DEL_DODELETE);

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

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

				case RI_KEYS_NONE_NULL:

					/*
					 * Have a full qualified key - continue below
					 */
					break;
			}

			if (SPI_connect() != SPI_OK_CONNECT)
				elog(ERROR, "SPI_connect failed");

			/*
			 * Fetch or prepare a saved plan for the cascaded delete
			 */
			if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
			{
				StringInfoData querybuf;
				char		fkrelname[MAX_QUOTED_REL_NAME_LEN];
				char		attname[MAX_QUOTED_NAME_LEN];
				char		paramname[16];
				const char *querysep;
				Oid			queryoids[RI_MAX_NUMKEYS];

				/* ----------
				 * The query string built is
				 *	DELETE FROM ONLY <fktable> WHERE $1 = fkatt1 [AND ...]
				 * The type id's for the $ parameters are those of the
				 * corresponding PK attributes.
				 * ----------
				 */
				initStringInfo(&querybuf);
				quoteRelationName(fkrelname, fk_rel);
				appendStringInfo(&querybuf, "DELETE FROM ONLY %s", fkrelname);
				querysep = "WHERE";
				for (i = 0; i < riinfo.nkeys; i++)
				{
					Oid			pk_type = RIAttType(pk_rel, riinfo.pk_attnums[i]);
					Oid			fk_type = RIAttType(fk_rel, riinfo.fk_attnums[i]);

					quoteOneName(attname,
								 RIAttName(fk_rel, riinfo.fk_attnums[i]));
					sprintf(paramname, "$%d", i + 1);
					ri_GenerateQual(&querybuf, querysep,
									paramname, pk_type,
									riinfo.pf_eq_oprs[i],
									attname, fk_type);
					querysep = "AND";
					queryoids[i] = pk_type;
				}

				/* Prepare and save the plan */
				qplan = ri_PlanCheck(querybuf.data, riinfo.nkeys, queryoids,
									 &qkey, fk_rel, pk_rel, true);
			}

			/*
			 * We have a plan now. Build up the arguments from the key values
			 * in the deleted PK tuple and delete the referencing rows
			 */
			ri_PerformCheck(&qkey, qplan,
							fk_rel, pk_rel,
							old_row, NULL,
							true,		/* must detect new rows */
							SPI_OK_DELETE,
							NameStr(riinfo.conname));

			if (SPI_finish() != SPI_OK_FINISH)
				elog(ERROR, "SPI_finish failed");

			heap_close(fk_rel, RowExclusiveLock);

			return PointerGetDatum(NULL);

			/*
			 * Handle MATCH PARTIAL cascaded delete.
			 */
		case FKCONSTR_MATCH_PARTIAL:
			ereport(ERROR,
					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
					 errmsg("MATCH PARTIAL not yet implemented")));
			return PointerGetDatum(NULL);
	}

	/*
	 * Never reached
	 */
	elog(ERROR, "invalid confmatchtype");
	return PointerGetDatum(NULL);
}


/* ----------
 * RI_FKey_cascade_upd -
 *
 *	Cascaded update/delete foreign key references at update event on PK table.
 * ----------
 */
Datum
RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
{
	TriggerData *trigdata = (TriggerData *) fcinfo->context;
	RI_ConstraintInfo riinfo;
	Relation	fk_rel;
	Relation	pk_rel;
	HeapTuple	new_row;
	HeapTuple	old_row;
	RI_QueryKey qkey;
	SPIPlanPtr	qplan;
	int			i;
	int			j;

	/*
	 * Check that this is a valid trigger call on the right time and event.
	 */
	ri_CheckTrigger(fcinfo, "RI_FKey_cascade_upd", RI_TRIGTYPE_UPDATE);

	/*
	 * Get arguments.
	 */
	ri_FetchConstraintInfo(&riinfo,
						   trigdata->tg_trigger, trigdata->tg_relation, true);

	/*
	 * Nothing to do if no column names to compare given
	 */
	if (riinfo.nkeys == 0)
		return PointerGetDatum(NULL);

	/*
	 * Get the relation descriptors of the FK and PK tables and the new and
	 * old tuple.
	 *
	 * fk_rel is opened in RowExclusiveLock mode since that's what our
	 * eventual UPDATE will get on it.
	 */
	fk_rel = heap_open(riinfo.fk_relid, RowExclusiveLock);
	pk_rel = trigdata->tg_relation;
	new_row = trigdata->tg_newtuple;
	old_row = trigdata->tg_trigtuple;

	switch (riinfo.confmatchtype)
	{
			/* ----------
			 * SQL3 11.9 <referential constraint definition>
			 *	Gereral rules 7) a) i):
			 *		MATCH <unspecified> or MATCH FULL
			 *			... ON UPDATE CASCADE
			 * ----------
			 */
		case FKCONSTR_MATCH_UNSPECIFIED:
		case FKCONSTR_MATCH_FULL:
			ri_BuildQueryKeyFull(&qkey, &riinfo,
								 RI_PLAN_CASCADE_UPD_DOUPDATE);

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

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

				case RI_KEYS_NONE_NULL:

					/*
					 * Have a full qualified key - continue below
					 */
					break;
			}

			/*
			 * No need to do anything if old and new keys are equal
			 */
			if (ri_KeysEqual(pk_rel, old_row, new_row, &riinfo, true))
			{
				heap_close(fk_rel, RowExclusiveLock);
				return PointerGetDatum(NULL);
			}

			if (SPI_connect() != SPI_OK_CONNECT)
				elog(ERROR, "SPI_connect failed");

			/*
			 * Fetch or prepare a saved plan for the cascaded update of
			 * foreign references
			 */
			if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
			{
				StringInfoData querybuf;
				StringInfoData qualbuf;
				char		fkrelname[MAX_QUOTED_REL_NAME_LEN];
				char		attname[MAX_QUOTED_NAME_LEN];
				char		paramname[16];
				const char *querysep;
				const char *qualsep;
				Oid			queryoids[RI_MAX_NUMKEYS * 2];

				/* ----------
				 * The query string built is
				 *	UPDATE ONLY <fktable> SET fkatt1 = $1 [, ...]
				 *			WHERE $n = fkatt1 [AND ...]
				 * The type id's for the $ parameters are those of the
				 * corresponding PK attributes.  Note that we are assuming
				 * there is an assignment cast from the PK to the FK type;
				 * else the parser will fail.
				 * ----------
				 */
				initStringInfo(&querybuf);
				initStringInfo(&qualbuf);
				quoteRelationName(fkrelname, fk_rel);
				appendStringInfo(&querybuf, "UPDATE ONLY %s SET", fkrelname);
				querysep = "";
				qualsep = "WHERE";
				for (i = 0, j = riinfo.nkeys; i < riinfo.nkeys; i++, j++)
				{
					Oid			pk_type = RIAttType(pk_rel, riinfo.pk_attnums[i]);
					Oid			fk_type = RIAttType(fk_rel, riinfo.fk_attnums[i]);

					quoteOneName(attname,
								 RIAttName(fk_rel, riinfo.fk_attnums[i]));
					appendStringInfo(&querybuf,
									 "%s %s = $%d",
									 querysep, attname, i + 1);
					sprintf(paramname, "$%d", j + 1);
					ri_GenerateQual(&qualbuf, qualsep,
									paramname, pk_type,
									riinfo.pf_eq_oprs[i],
									attname, fk_type);
					querysep = ",";
					qualsep = "AND";
					queryoids[i] = pk_type;
					queryoids[j] = pk_type;
				}
				appendStringInfoString(&querybuf, qualbuf.data);

				/* Prepare and save the plan */
				qplan = ri_PlanCheck(querybuf.data, riinfo.nkeys * 2, queryoids,
									 &qkey, fk_rel, pk_rel, true);
			}

			/*
			 * We have a plan now. Run it to update the existing references.
			 */
			ri_PerformCheck(&qkey, qplan,
							fk_rel, pk_rel,
							old_row, new_row,
							true,		/* must detect new rows */
							SPI_OK_UPDATE,
							NameStr(riinfo.conname));

			if (SPI_finish() != SPI_OK_FINISH)
				elog(ERROR, "SPI_finish failed");

			heap_close(fk_rel, RowExclusiveLock);

			return PointerGetDatum(NULL);

			/*
			 * Handle MATCH PARTIAL cascade update.
			 */
		case FKCONSTR_MATCH_PARTIAL:
			ereport(ERROR,
					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
					 errmsg("MATCH PARTIAL not yet implemented")));
			return PointerGetDatum(NULL);
	}

	/*
	 * Never reached
	 */
	elog(ERROR, "invalid confmatchtype");
	return PointerGetDatum(NULL);
}


/* ----------
 * RI_FKey_restrict_del -
 *
 *	Restrict delete from PK table to rows unreferenced by foreign key.
 *
 *	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".
 *
 *	For now, however, we treat "RESTRICT" and "NO ACTION" as
 *	equivalent.
 * ----------
 */
Datum
RI_FKey_restrict_del(PG_FUNCTION_ARGS)
{
	TriggerData *trigdata = (TriggerData *) fcinfo->context;
	RI_ConstraintInfo riinfo;
	Relation	fk_rel;
	Relation	pk_rel;
	HeapTuple	old_row;
	RI_QueryKey qkey;
	SPIPlanPtr	qplan;
	int			i;

	/*
	 * Check that this is a valid trigger call on the right time and event.
	 */
	ri_CheckTrigger(fcinfo, "RI_FKey_restrict_del", RI_TRIGTYPE_DELETE);

	/*
	 * Get arguments.
	 */
	ri_FetchConstraintInfo(&riinfo,
						   trigdata->tg_trigger, trigdata->tg_relation, true);

	/*
	 * Nothing to do if no column names to compare given
	 */
	if (riinfo.nkeys == 0)
		return PointerGetDatum(NULL);

	/*
	 * Get the relation descriptors of the FK and PK tables and the old tuple.
	 *
	 * fk_rel is opened in RowShareLock mode since that's what our eventual
	 * SELECT FOR SHARE will get on it.
	 */
	fk_rel = heap_open(riinfo.fk_relid, RowShareLock);
	pk_rel = trigdata->tg_relation;
	old_row = trigdata->tg_trigtuple;

	switch (riinfo.confmatchtype)
	{
			/* ----------
			 * SQL3 11.9 <referential constraint definition>
			 *	Gereral rules 6) a) iv):
			 *		MATCH <unspecified> or MATCH FULL
			 *			... ON DELETE CASCADE
			 * ----------
			 */
		case FKCONSTR_MATCH_UNSPECIFIED:
		case FKCONSTR_MATCH_FULL:
			ri_BuildQueryKeyFull(&qkey, &riinfo,
								 RI_PLAN_RESTRICT_DEL_CHECKREF);

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

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

				case RI_KEYS_NONE_NULL:

					/*
					 * Have a full qualified key - continue below
					 */
					break;
			}

			if (SPI_connect() != SPI_OK_CONNECT)
				elog(ERROR, "SPI_connect failed");

			/*
			 * Fetch or prepare a saved plan for the restrict delete lookup if
			 * foreign references exist
			 */
			if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
			{
				StringInfoData querybuf;
				char		fkrelname[MAX_QUOTED_REL_NAME_LEN];
				char		attname[MAX_QUOTED_NAME_LEN];
				char		paramname[16];
				const char *querysep;
				Oid			queryoids[RI_MAX_NUMKEYS];

				/* ----------
				 * The query string built is
				 *	SELECT 1 FROM ONLY <fktable> WHERE $1 = fkatt1 [AND ...]
				 * The type id's for the $ parameters are those of the
				 * corresponding PK attributes.
				 * ----------
				 */
				initStringInfo(&querybuf);