Hi Maksim,
On 26.09.2017 11:51, Maksim Milyutin wrote:
On 19.09.2017 11:09, Daniel Gustafsson wrote:
Removing reviewer Maksim Milyutin from patch entry due to inactivity and
community account email bouncing. Maksim: if you are indeed reviewing
this
patch, then please update the community account and re-add to the
patch entry.
cheers ./daniel
Daniel, thanks for noticing. I have updated my account and re-added to
the patch entry.
Ildar, your patch is conflicting with the current HEAD of master branch,
please update it.
Thank you for checking the patch out. Yes, it seems that original code
was reformatted and this led to merging conflicts. I've fixed that and
also introduced some minor improvements. The new version is in attachment.
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 c2891e6..25cdf7d 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -196,8 +196,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);
+static Datum ri_setnull(TriggerData *trigdata);
+static Datum ri_setdefault(FunctionCallInfo fcinfo);
static void quoteOneName(char *buffer, const char *name);
static void quoteRelationName(char *buffer, Relation rel);
static void ri_GenerateQual(StringInfo buf,
@@ -605,7 +606,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);
}
/* ----------
@@ -630,175 +631,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);
}
/* ----------
- * 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
@@ -817,7 +653,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);
}
/* ----------
@@ -842,26 +678,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);
}
/* ----------
- * 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)
{
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.
@@ -870,21 +706,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
@@ -912,14 +749,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 (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
{
- 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
@@ -938,9 +784,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)
{
@@ -950,10 +797,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.
* ----------
@@ -1002,7 +851,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,
@@ -1367,21 +1216,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);
+}
+
+/* ----------
+ * 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);
+}
+
+/* ----------
+ * ri_setnull -
+ *
+ * Common code for ON DELETE SET NULL and ON UPDATE SET NULL
+ * ----------
+ */
+static Datum
+ri_setnull(TriggerData *trigdata)
+{
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,
@@ -1404,6 +1288,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:
@@ -1428,13 +1315,31 @@ RI_FKey_setnull_del(PG_FUNCTION_ARGS)
break;
}
+ if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+ {
+ 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)
{
@@ -1488,7 +1393,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,
@@ -1504,7 +1409,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,
@@ -1524,383 +1429,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);
-
- /*
- * Get arguments.
- */
- riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
- trigdata->tg_relation, true);
+ ri_CheckTrigger(fcinfo, "RI_FKey_setdefault_del", RI_TRIGTYPE_DELETE);
/*
- * 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 UPDATE case
*/
- 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);
}
-
-/* ----------
- * 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);
}
-
-/* ----------
- * 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)
{
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.
@@ -1916,13 +1488,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
@@ -1950,23 +1524,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 (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
{
- 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_UPD_DOUPDATE;
}
+ else
+ qtype = RI_PLAN_SETDEFAULT_DEL_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)
{
@@ -2035,23 +1617,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 (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+ 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
