Hi all,
I was looking through the RI triggers code recently and noticed a few
almost identical functions, e.g. ri_restrict_upd() and
ri_restrict_del(). The following patch is an attempt to reduce some of
repetitive code. Yet there is still room for improvement.
Thanks,
--
Ildar Musin
i.mu...@postgrespro.ru
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 37139f9..259c988 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -199,8 +199,9 @@ static int ri_constraint_cache_valid_count = 0;
static bool ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
HeapTuple old_row,
const RI_ConstraintInfo *riinfo);
-static Datum ri_restrict_del(TriggerData *trigdata, bool is_no_action);
-static Datum ri_restrict_upd(TriggerData *trigdata, bool is_no_action);
+static Datum ri_restrict(TriggerData *trigdata, bool is_no_action, bool is_update);
+static Datum ri_setnull(TriggerData *trigdata, bool is_update);
+static Datum ri_setdefault(FunctionCallInfo fcinfo, bool is_update);
static void quoteOneName(char *buffer, const char *name);
static void quoteRelationName(char *buffer, Relation rel);
static void ri_GenerateQual(StringInfo buf,
@@ -608,7 +609,7 @@ RI_FKey_noaction_del(PG_FUNCTION_ARGS)
/*
* Share code with RESTRICT case.
*/
- return ri_restrict_del((TriggerData *) fcinfo->context, true);
+ return ri_restrict((TriggerData *) fcinfo->context, true, false);
}
/* ----------
@@ -633,175 +634,10 @@ RI_FKey_restrict_del(PG_FUNCTION_ARGS)
/*
* Share code with NO ACTION case.
*/
- return ri_restrict_del((TriggerData *) fcinfo->context, false);
+ return ri_restrict((TriggerData *) fcinfo->context, false, false);
}
/* ----------
- * ri_restrict_del -
- *
- * Common code for ON DELETE RESTRICT and ON DELETE NO ACTION.
- * ----------
- */
-static Datum
-ri_restrict_del(TriggerData *trigdata, bool is_no_action)
-{
- const RI_ConstraintInfo *riinfo;
- Relation fk_rel;
- Relation pk_rel;
- HeapTuple old_row;
- RI_QueryKey qkey;
- SPIPlanPtr qplan;
- int i;
-
- /*
- * Get arguments.
- */
- riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
- trigdata->tg_relation, true);
-
- /*
- * 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 KEY 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)
- {
- /* ----------
- * SQL:2008 15.17 <Execution of referential actions>
- * General rules 9) a) iv):
- * MATCH SIMPLE/FULL
- * ... ON DELETE RESTRICT
- * ----------
- */
- case FKCONSTR_MATCH_SIMPLE:
- case FKCONSTR_MATCH_FULL:
- switch (ri_NullCheck(old_row, riinfo, true))
- {
- case RI_KEYS_ALL_NULL:
- case RI_KEYS_SOME_NULL:
-
- /*
- * No check needed - there cannot be any reference to old
- * key if it contains a NULL
- */
- heap_close(fk_rel, RowShareLock);
- return PointerGetDatum(NULL);
-
- case RI_KEYS_NONE_NULL:
-
- /*
- * Have a full qualified key - continue below
- */
- break;
- }
-
- /*
- * If another PK row now exists providing the old key values, we
- * should not do anything. However, this check should only be
- * made in the NO ACTION case; in RESTRICT cases we don't wish to
- * allow another row to be substituted.
- */
- if (is_no_action &&
- ri_Check_Pk_Match(pk_rel, fk_rel, old_row, riinfo))
- {
- heap_close(fk_rel, RowShareLock);
- return PointerGetDatum(NULL);
- }
-
- if (SPI_connect() != SPI_OK_CONNECT)
- elog(ERROR, "SPI_connect failed");
-
- /*
- * Fetch or prepare a saved plan for the restrict delete lookup
- */
- ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_RESTRICT_DEL_CHECKREF);
-
- 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> x WHERE $1 = fkatt1 [AND ...]
- * FOR KEY SHARE OF x
- * The type id's for the $ parameters are those of the
- * corresponding PK attributes.
- * ----------
- */
- initStringInfo(&querybuf);
- quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x",
- 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;
- }
- appendStringInfoString(&querybuf, " FOR KEY SHARE OF x");
-
- /* Prepare and save the plan */
- qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
- &qkey, fk_rel, pk_rel, true);
- }
-
- /*
- * We have a plan now. Run it to check for existing references.
- */
- ri_PerformCheck(riinfo, &qkey, qplan,
- fk_rel, pk_rel,
- old_row, NULL,
- true, /* must detect new rows */
- SPI_OK_SELECT);
-
- if (SPI_finish() != SPI_OK_FINISH)
- elog(ERROR, "SPI_finish failed");
-
- heap_close(fk_rel, RowShareLock);
-
- return PointerGetDatum(NULL);
-
- /*
- * Handle MATCH PARTIAL restrict delete.
- */
- case FKCONSTR_MATCH_PARTIAL:
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("MATCH PARTIAL not yet implemented")));
- return PointerGetDatum(NULL);
-
- default:
- elog(ERROR, "unrecognized confmatchtype: %d",
- riinfo->confmatchtype);
- break;
- }
-
- /* Never reached */
- return PointerGetDatum(NULL);
-}
-
-
-/* ----------
* RI_FKey_noaction_upd -
*
* Give an error and roll back the current transaction if the
@@ -820,7 +656,7 @@ RI_FKey_noaction_upd(PG_FUNCTION_ARGS)
/*
* Share code with RESTRICT case.
*/
- return ri_restrict_upd((TriggerData *) fcinfo->context, true);
+ return ri_restrict((TriggerData *) fcinfo->context, true, true);
}
/* ----------
@@ -845,26 +681,26 @@ RI_FKey_restrict_upd(PG_FUNCTION_ARGS)
/*
* Share code with NO ACTION case.
*/
- return ri_restrict_upd((TriggerData *) fcinfo->context, false);
+ return ri_restrict((TriggerData *) fcinfo->context, false, true);
}
/* ----------
- * ri_restrict_upd -
+ * ri_restrict -
*
- * Common code for ON UPDATE RESTRICT and ON UPDATE NO ACTION.
+ * Common code for ON DELETE RESTRICT, ON DELETE NO ACTION, ON UPDATE RESTRICT
+ * and ON UPDATE NO ACTION.
* ----------
*/
static Datum
-ri_restrict_upd(TriggerData *trigdata, bool is_no_action)
+ri_restrict(TriggerData *trigdata, bool is_no_action, bool is_update)
{
const RI_ConstraintInfo *riinfo;
Relation fk_rel;
Relation pk_rel;
- HeapTuple new_row;
HeapTuple old_row;
RI_QueryKey qkey;
SPIPlanPtr qplan;
- int i;
+ int32 qtype;
/*
* Get arguments.
@@ -873,21 +709,22 @@ ri_restrict_upd(TriggerData *trigdata, bool is_no_action)
trigdata->tg_relation, true);
/*
- * Get the relation descriptors of the FK and PK tables and the new and
- * old tuple.
+ * 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 KEY SHARE will get on it.
*/
fk_rel = heap_open(riinfo->fk_relid, RowShareLock);
pk_rel = trigdata->tg_relation;
- new_row = trigdata->tg_newtuple;
old_row = trigdata->tg_trigtuple;
switch (riinfo->confmatchtype)
{
/* ----------
* SQL:2008 15.17 <Execution of referential actions>
+ * General rules 9) a) iv):
+ * MATCH SIMPLE/FULL
+ * ... ON DELETE RESTRICT
* General rules 10) a) iv):
* MATCH SIMPLE/FULL
* ... ON UPDATE RESTRICT
@@ -915,14 +752,23 @@ ri_restrict_upd(TriggerData *trigdata, bool is_no_action)
break;
}
- /*
- * No need to check anything if old and new keys are equal
- */
- if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
+ if (is_update)
{
- heap_close(fk_rel, RowShareLock);
- return PointerGetDatum(NULL);
+ HeapTuple new_row = trigdata->tg_newtuple;
+
+ /*
+ * No need to check anything if old and new keys are equal
+ */
+ if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
+ {
+ heap_close(fk_rel, RowShareLock);
+ return PointerGetDatum(NULL);
+ }
+ qtype = RI_PLAN_RESTRICT_UPD_CHECKREF;
}
+ else
+ qtype = RI_PLAN_RESTRICT_DEL_CHECKREF;
+
/*
* If another PK row now exists providing the old key values, we
@@ -941,9 +787,10 @@ ri_restrict_upd(TriggerData *trigdata, bool is_no_action)
elog(ERROR, "SPI_connect failed");
/*
- * Fetch or prepare a saved plan for the restrict update lookup
+ * Fetch or prepare a saved plan for the restrict delete or update
+ * lookup
*/
- ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_RESTRICT_UPD_CHECKREF);
+ ri_BuildQueryKey(&qkey, riinfo, qtype);
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
{
@@ -953,10 +800,12 @@ ri_restrict_upd(TriggerData *trigdata, bool is_no_action)
char paramname[16];
const char *querysep;
Oid queryoids[RI_MAX_NUMKEYS];
+ int i;
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <fktable> WHERE $1 = fkatt1 [AND ...]
+ * SELECT 1 FROM ONLY <fktable> x WHERE $1 = fkatt1 [AND ...]
+ * FOR KEY SHARE OF x
* The type id's for the $ parameters are those of the
* corresponding PK attributes.
* ----------
@@ -1005,7 +854,7 @@ ri_restrict_upd(TriggerData *trigdata, bool is_no_action)
return PointerGetDatum(NULL);
/*
- * Handle MATCH PARTIAL restrict update.
+ * Handle MATCH PARTIAL restrict delete or update.
*/
case FKCONSTR_MATCH_PARTIAL:
ereport(ERROR,
@@ -1370,21 +1219,56 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
Datum
RI_FKey_setnull_del(PG_FUNCTION_ARGS)
{
- TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ /*
+ * Check that this is a valid trigger call on the right time and event.
+ */
+ ri_CheckTrigger(fcinfo, "RI_FKey_setnull_del", RI_TRIGTYPE_DELETE);
+
+ /*
+ * Share code with UPDATE case
+ */
+ return ri_setnull((TriggerData *) fcinfo->context, false);
+}
+
+/* ----------
+ * RI_FKey_setnull_upd -
+ *
+ * Set foreign key references to NULL at update event on PK table.
+ * ----------
+ */
+Datum
+RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
+{
+ /*
+ * Check that this is a valid trigger call on the right time and event.
+ */
+ ri_CheckTrigger(fcinfo, "RI_FKey_setnull_upd", RI_TRIGTYPE_UPDATE);
+
+ /*
+ * Share code with DELETE case
+ */
+ return ri_setnull((TriggerData *) fcinfo->context, true);
+}
+
+/* ----------
+ * ri_setnull -
+ *
+ * Common code for ON DELETE SET NULL and ON UPDATE SET NULL
+ * ----------
+ */
+static Datum
+ri_setnull(TriggerData *trigdata, bool is_update)
+{
const RI_ConstraintInfo *riinfo;
Relation fk_rel;
Relation pk_rel;
HeapTuple old_row;
RI_QueryKey qkey;
SPIPlanPtr qplan;
+ int32 qtype;
int i;
/*
- * Check that this is a valid trigger call on the right time and event.
- */
- ri_CheckTrigger(fcinfo, "RI_FKey_setnull_del", RI_TRIGTYPE_DELETE);
-
- /*
* Get arguments.
*/
riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
@@ -1407,6 +1291,9 @@ RI_FKey_setnull_del(PG_FUNCTION_ARGS)
* General rules 9) a) ii):
* MATCH SIMPLE/FULL
* ... ON DELETE SET NULL
+ * General rules 10) a) ii):
+ * MATCH SIMPLE/FULL
+ * ... ON UPDATE SET NULL
* ----------
*/
case FKCONSTR_MATCH_SIMPLE:
@@ -1431,13 +1318,30 @@ RI_FKey_setnull_del(PG_FUNCTION_ARGS)
break;
}
+ if (is_update)
+ {
+ HeapTuple new_row = trigdata->tg_newtuple;
+ /*
+ * 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);
+ }
+ qtype = RI_PLAN_SETNULL_UPD_DOUPDATE;
+ }
+ else
+ qtype = RI_PLAN_SETNULL_DEL_DOUPDATE;
+
if (SPI_connect() != SPI_OK_CONNECT)
elog(ERROR, "SPI_connect failed");
/*
- * Fetch or prepare a saved plan for the set null delete operation
+ * Fetch or prepare a saved plan for the set null delete or update
+ * operation
*/
- ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_SETNULL_DEL_DOUPDATE);
+ ri_BuildQueryKey(&qkey, riinfo, qtype);
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
{
@@ -1491,7 +1395,7 @@ RI_FKey_setnull_del(PG_FUNCTION_ARGS)
}
/*
- * We have a plan now. Run it to check for existing references.
+ * We have a plan now. Run it to update the existing references.
*/
ri_PerformCheck(riinfo, &qkey, qplan,
fk_rel, pk_rel,
@@ -1507,7 +1411,7 @@ RI_FKey_setnull_del(PG_FUNCTION_ARGS)
return PointerGetDatum(NULL);
/*
- * Handle MATCH PARTIAL set null delete.
+ * Handle MATCH PARTIAL set null delete or update.
*/
case FKCONSTR_MATCH_PARTIAL:
ereport(ERROR,
@@ -1527,383 +1431,50 @@ RI_FKey_setnull_del(PG_FUNCTION_ARGS)
/* ----------
- * RI_FKey_setnull_upd -
+ * RI_FKey_setdefault_del -
*
- * Set foreign key references to NULL at update event on PK table.
+ * Set foreign key references to defaults at delete event on PK table.
* ----------
*/
Datum
-RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
+RI_FKey_setdefault_del(PG_FUNCTION_ARGS)
{
- TriggerData *trigdata = (TriggerData *) fcinfo->context;
- const RI_ConstraintInfo *riinfo;
- Relation fk_rel;
- Relation pk_rel;
- HeapTuple new_row;
- 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_setnull_upd", RI_TRIGTYPE_UPDATE);
+ ri_CheckTrigger(fcinfo, "RI_FKey_setdefault_del", RI_TRIGTYPE_DELETE);
/*
- * Get arguments.
+ * Share code with UPDATE case
*/
- riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
- trigdata->tg_relation, true);
-
- /*
- * 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 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)
- {
- /* ----------
- * SQL:2008 15.17 <Execution of referential actions>
- * General rules 10) a) ii):
- * MATCH SIMPLE/FULL
- * ... ON UPDATE SET NULL
- * ----------
- */
- case FKCONSTR_MATCH_SIMPLE:
- case FKCONSTR_MATCH_FULL:
- switch (ri_NullCheck(old_row, riinfo, true))
- {
- case RI_KEYS_ALL_NULL:
- case RI_KEYS_SOME_NULL:
-
- /*
- * No check needed - there cannot be any reference to old
- * key if it contains a 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 set null update operation
- */
- ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_SETNULL_UPD_DOUPDATE);
-
- 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];
-
- /* ----------
- * The query string built is
- * UPDATE ONLY <fktable> SET fkatt1 = NULL [, ...]
- * WHERE $1 = fkatt1 [AND ...]
- * The type id's for the $ parameters are those of the
- * corresponding PK attributes.
- * ----------
- */
- initStringInfo(&querybuf);
- initStringInfo(&qualbuf);
- quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "UPDATE ONLY %s SET", fkrelname);
- querysep = "";
- qualsep = "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]));
- appendStringInfo(&querybuf,
- "%s %s = NULL",
- querysep, attname);
- sprintf(paramname, "$%d", i + 1);
- ri_GenerateQual(&qualbuf, qualsep,
- paramname, pk_type,
- riinfo->pf_eq_oprs[i],
- attname, fk_type);
- querysep = ",";
- qualsep = "AND";
- queryoids[i] = pk_type;
- }
- appendStringInfoString(&querybuf, qualbuf.data);
-
- /* Prepare and save the plan */
- qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
- &qkey, fk_rel, pk_rel, true);
- }
-
- /*
- * We have a plan now. Run it to update the existing references.
- */
- ri_PerformCheck(riinfo, &qkey, qplan,
- fk_rel, pk_rel,
- old_row, NULL,
- true, /* must detect new rows */
- SPI_OK_UPDATE);
-
- if (SPI_finish() != SPI_OK_FINISH)
- elog(ERROR, "SPI_finish failed");
-
- heap_close(fk_rel, RowExclusiveLock);
-
- return PointerGetDatum(NULL);
-
- /*
- * Handle MATCH PARTIAL set null update.
- */
- case FKCONSTR_MATCH_PARTIAL:
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("MATCH PARTIAL not yet implemented")));
- return PointerGetDatum(NULL);
-
- default:
- elog(ERROR, "unrecognized confmatchtype: %d",
- riinfo->confmatchtype);
- break;
- }
-
- /* Never reached */
- return PointerGetDatum(NULL);
+ return ri_setdefault(fcinfo, false);
}
-
-/* ----------
- * RI_FKey_setdefault_del -
- *
- * Set foreign key references to defaults at delete event on PK table.
- * ----------
- */
Datum
-RI_FKey_setdefault_del(PG_FUNCTION_ARGS)
+RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
{
- TriggerData *trigdata = (TriggerData *) fcinfo->context;
- const RI_ConstraintInfo *riinfo;
- Relation fk_rel;
- Relation pk_rel;
- HeapTuple old_row;
- RI_QueryKey qkey;
- SPIPlanPtr qplan;
-
/*
* Check that this is a valid trigger call on the right time and event.
*/
- ri_CheckTrigger(fcinfo, "RI_FKey_setdefault_del", RI_TRIGTYPE_DELETE);
-
- /*
- * Get arguments.
- */
- riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
- trigdata->tg_relation, true);
+ ri_CheckTrigger(fcinfo, "RI_FKey_setdefault_upd", RI_TRIGTYPE_UPDATE);
/*
- * 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 UPDATE will get on it.
+ * Share code with DELETE case
*/
- fk_rel = heap_open(riinfo->fk_relid, RowExclusiveLock);
- pk_rel = trigdata->tg_relation;
- old_row = trigdata->tg_trigtuple;
-
- switch (riinfo->confmatchtype)
- {
- /* ----------
- * SQL:2008 15.17 <Execution of referential actions>
- * General rules 9) a) iii):
- * MATCH SIMPLE/FULL
- * ... ON DELETE SET DEFAULT
- * ----------
- */
- case FKCONSTR_MATCH_SIMPLE:
- case FKCONSTR_MATCH_FULL:
- switch (ri_NullCheck(old_row, riinfo, true))
- {
- case RI_KEYS_ALL_NULL:
- case RI_KEYS_SOME_NULL:
-
- /*
- * No check needed - there cannot be any reference to old
- * key if it contains a 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 set default delete
- * operation
- */
- ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_SETDEFAULT_DEL_DOUPDATE);
-
- 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];
- int i;
-
- /* ----------
- * The query string built is
- * UPDATE ONLY <fktable> SET fkatt1 = DEFAULT [, ...]
- * WHERE $1 = fkatt1 [AND ...]
- * The type id's for the $ parameters are those of the
- * corresponding PK attributes.
- * ----------
- */
- initStringInfo(&querybuf);
- initStringInfo(&qualbuf);
- quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "UPDATE ONLY %s SET", fkrelname);
- querysep = "";
- qualsep = "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]));
- appendStringInfo(&querybuf,
- "%s %s = DEFAULT",
- querysep, attname);
- sprintf(paramname, "$%d", i + 1);
- ri_GenerateQual(&qualbuf, qualsep,
- paramname, pk_type,
- riinfo->pf_eq_oprs[i],
- attname, fk_type);
- querysep = ",";
- qualsep = "AND";
- queryoids[i] = pk_type;
- }
- appendStringInfoString(&querybuf, qualbuf.data);
-
- /* Prepare and save the plan */
- qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
- &qkey, fk_rel, pk_rel, true);
- }
-
- /*
- * We have a plan now. Run it to update the existing references.
- */
- ri_PerformCheck(riinfo, &qkey, qplan,
- fk_rel, pk_rel,
- old_row, NULL,
- true, /* must detect new rows */
- SPI_OK_UPDATE);
-
- if (SPI_finish() != SPI_OK_FINISH)
- elog(ERROR, "SPI_finish failed");
-
- heap_close(fk_rel, RowExclusiveLock);
-
- /*
- * If we just deleted the PK row whose key was equal to the FK
- * columns' default values, and a referencing row exists in the FK
- * table, we would have updated that row to the same values it
- * already had --- and RI_FKey_fk_upd_check_required would hence
- * believe no check is necessary. So we need to do another lookup
- * now and in case a reference still exists, abort the operation.
- * That is already implemented in the NO ACTION trigger, so just
- * run it. (This recheck is only needed in the SET DEFAULT case,
- * since CASCADE would remove such rows, while SET NULL is certain
- * to result in rows that satisfy the FK constraint.)
- */
- RI_FKey_noaction_del(fcinfo);
-
- return PointerGetDatum(NULL);
-
- /*
- * Handle MATCH PARTIAL set default delete.
- */
- case FKCONSTR_MATCH_PARTIAL:
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("MATCH PARTIAL not yet implemented")));
- return PointerGetDatum(NULL);
-
- default:
- elog(ERROR, "unrecognized confmatchtype: %d",
- riinfo->confmatchtype);
- break;
- }
-
- /* Never reached */
- return PointerGetDatum(NULL);
+ return ri_setdefault(fcinfo, true);
}
-
-/* ----------
- * RI_FKey_setdefault_upd -
- *
- * Set foreign key references to defaults at update event on PK table.
- * ----------
- */
-Datum
-RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
+static Datum
+ri_setdefault(FunctionCallInfo fcinfo, bool is_update)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
const RI_ConstraintInfo *riinfo;
Relation fk_rel;
Relation pk_rel;
- HeapTuple new_row;
HeapTuple old_row;
RI_QueryKey qkey;
SPIPlanPtr qplan;
-
- /*
- * Check that this is a valid trigger call on the right time and event.
- */
- ri_CheckTrigger(fcinfo, "RI_FKey_setdefault_upd", RI_TRIGTYPE_UPDATE);
+ int32 qtype;
/*
* Get arguments.
@@ -1919,13 +1490,15 @@ RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
*/
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)
{
/* ----------
* SQL:2008 15.17 <Execution of referential actions>
+ * General rules 9) a) iii):
+ * MATCH SIMPLE/FULL
+ * ... ON DELETE SET DEFAULT
* General rules 10) a) iii):
* MATCH SIMPLE/FULL
* ... ON UPDATE SET DEFAULT
@@ -1953,23 +1526,31 @@ RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
break;
}
- /*
- * No need to do anything if old and new keys are equal
- */
- if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
+ if (is_update)
{
- heap_close(fk_rel, RowExclusiveLock);
- return PointerGetDatum(NULL);
+ HeapTuple new_row = trigdata->tg_newtuple;
+
+ /*
+ * 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);
+ }
+ qtype = RI_PLAN_SETDEFAULT_DEL_DOUPDATE;
}
+ else
+ qtype = RI_PLAN_SETDEFAULT_UPD_DOUPDATE;
if (SPI_connect() != SPI_OK_CONNECT)
elog(ERROR, "SPI_connect failed");
/*
- * Fetch or prepare a saved plan for the set default update
- * operation
+ * Fetch or prepare a saved plan for the set default delete
+ * or update operation
*/
- ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_SETDEFAULT_UPD_DOUPDATE);
+ ri_BuildQueryKey(&qkey, riinfo, qtype);
if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
{
@@ -2038,23 +1619,28 @@ RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
heap_close(fk_rel, RowExclusiveLock);
/*
- * If we just updated the PK row whose key was equal to the FK
- * columns' default values, and a referencing row exists in the FK
- * table, we would have updated that row to the same values it
- * already had --- and RI_FKey_fk_upd_check_required would hence
- * believe no check is necessary. So we need to do another lookup
- * now and in case a reference still exists, abort the operation.
- * That is already implemented in the NO ACTION trigger, so just
- * run it. (This recheck is only needed in the SET DEFAULT case,
- * since CASCADE must change the FK key values, while SET NULL is
- * certain to result in rows that satisfy the FK constraint.)
+ * If we just deleted or updated the PK row whose key was equal
+ * to the FK columns' default values, and a referencing row exists
+ * in the FK table, we would have updated that row to the same
+ * values it already had --- and RI_FKey_fk_upd_check_required
+ * would hence believe no check is necessary. So we need to do
+ * another lookup now and in case a reference still exists, abort
+ * the operation. That is already implemented in the NO ACTION
+ * trigger, so just run it. (This recheck is only needed in the
+ * SET DEFAULT case, since CASCADE would remove such rows in case
+ * of DELETE operation or would change the FK key values in case
+ * of UPDATE, while SET NULL is certain to result in rows that
+ * satisfy the FK constraint.)
*/
- RI_FKey_noaction_upd(fcinfo);
+ if (is_update)
+ RI_FKey_noaction_upd(fcinfo);
+ else
+ RI_FKey_noaction_del(fcinfo);
return PointerGetDatum(NULL);
/*
- * Handle MATCH PARTIAL set default update.
+ * Handle MATCH PARTIAL set default delete or update.
*/
case FKCONSTR_MATCH_PARTIAL:
ereport(ERROR,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
