From 2eb53e684cfaa9015bcde1031d6153709b420a8d Mon Sep 17 00:00:00 2001 From: Jan Wieck Date: Mon, 6 Dec 1999 19:50:49 +0000 Subject: [PATCH] Added ON DELETE/UPDATE SET NULL Jan --- src/backend/utils/adt/ri_triggers.c | 429 ++++++++++++++++++++++++++++++++++-- 1 file changed, 411 insertions(+), 18 deletions(-) diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index 6b07cc3824..71b4685ba7 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -6,7 +6,7 @@ * * 1999 Jan Wieck * - * $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.4 1999/12/06 18:02:44 wieck Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.5 1999/12/06 19:50:49 wieck Exp $ * * ---------- */ @@ -72,6 +72,8 @@ #define RI_PLAN_CASCADE_UPD_DOUPDATE 1 #define RI_PLAN_RESTRICT_DEL_CHECKREF 1 #define RI_PLAN_RESTRICT_UPD_CHECKREF 1 +#define RI_PLAN_SETNULL_DEL_DOUPDATE 1 +#define RI_PLAN_SETNULL_UPD_DOUPDATE 1 /* ---------- @@ -210,6 +212,10 @@ RI_FKey_check (FmgrInfo *proinfo) * Gereral rules 2) a): * If Rf and Rt are empty (no columns to compare given) * constraint is true if 0 < (SELECT COUNT(*) FROM T) + * + * Note: The special case that no columns are given cannot + * occur up to now in Postgres, it's just there for + * future enhancements. * ---------- */ if (tgnargs == 4) { @@ -562,7 +568,7 @@ RI_FKey_cascade_del (FmgrInfo *proinfo) heap_close(fk_rel, NoLock); if (SPI_connect() != SPI_OK_CONNECT) - elog(NOTICE, "SPI_connect() failed in RI_FKey_check()"); + elog(NOTICE, "SPI_connect() failed in RI_FKey_cascade_del()"); /* ---------- * Fetch or prepare a saved plan for the cascaded delete @@ -714,7 +720,7 @@ RI_FKey_cascade_upd (FmgrInfo *proinfo) /* ---------- * Get the relation descriptors of the FK and PK tables and - * the old tuple. + * the new and old tuple. * ---------- */ fk_rel = heap_openr(tgargs[RI_FK_RELNAME_ARGNO], NoLock); @@ -768,11 +774,11 @@ RI_FKey_cascade_upd (FmgrInfo *proinfo) return NULL; if (SPI_connect() != SPI_OK_CONNECT) - elog(NOTICE, "SPI_connect() failed in RI_FKey_restrict_upd()"); + elog(NOTICE, "SPI_connect() failed in RI_FKey_cascade_upd()"); /* ---------- - * Fetch or prepare a saved plan for the restrict delete - * lookup for foreign references + * Fetch or prepare a saved plan for the cascaded update + * of foreign references * ---------- */ if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL) @@ -864,7 +870,7 @@ RI_FKey_cascade_upd (FmgrInfo *proinfo) return NULL; /* ---------- - * Handle MATCH PARTIAL restrict update. + * Handle MATCH PARTIAL cascade update. * ---------- */ case RI_MATCH_TYPE_PARTIAL: @@ -876,7 +882,7 @@ RI_FKey_cascade_upd (FmgrInfo *proinfo) * Never reached * ---------- */ - elog(ERROR, "internal error #4 in ri_triggers.c"); + elog(ERROR, "internal error #3 in ri_triggers.c"); return NULL; } @@ -988,7 +994,7 @@ RI_FKey_restrict_del (FmgrInfo *proinfo) /* ---------- * Fetch or prepare a saved plan for the restrict delete - * lookup for foreign references + * lookup if foreign references exist * ---------- */ if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL) @@ -1079,7 +1085,7 @@ RI_FKey_restrict_del (FmgrInfo *proinfo) * Never reached * ---------- */ - elog(ERROR, "internal error #3 in ri_triggers.c"); + elog(ERROR, "internal error #4 in ri_triggers.c"); return NULL; } @@ -1143,7 +1149,7 @@ RI_FKey_restrict_upd (FmgrInfo *proinfo) /* ---------- * Get the relation descriptors of the FK and PK tables and - * the old tuple. + * the new and old tuple. * ---------- */ fk_rel = heap_openr(tgargs[RI_FK_RELNAME_ARGNO], NoLock); @@ -1200,8 +1206,8 @@ RI_FKey_restrict_upd (FmgrInfo *proinfo) elog(NOTICE, "SPI_connect() failed in RI_FKey_restrict_upd()"); /* ---------- - * Fetch or prepare a saved plan for the restrict delete - * lookup for foreign references + * Fetch or prepare a saved plan for the restrict update + * lookup if foreign references exist * ---------- */ if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL) @@ -1292,7 +1298,7 @@ RI_FKey_restrict_upd (FmgrInfo *proinfo) * Never reached * ---------- */ - elog(ERROR, "internal error #4 in ri_triggers.c"); + elog(ERROR, "internal error #5 in ri_triggers.c"); return NULL; } @@ -1306,12 +1312,199 @@ RI_FKey_restrict_upd (FmgrInfo *proinfo) HeapTuple RI_FKey_setnull_del (FmgrInfo *proinfo) { - TriggerData *trigdata; + TriggerData *trigdata; + 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; trigdata = CurrentTriggerData; CurrentTriggerData = NULL; - elog(ERROR, "RI_FKey_setnull_del() called\n"); + /* ---------- + * Check that this is a valid trigger call on the right time and event. + * ---------- + */ + if (trigdata == NULL) + elog(ERROR, "RI_FKey_setnull_del() not fired by trigger manager"); + if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) || + !TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) + 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"); + + /* ---------- + * Check for the correct # of call arguments + * ---------- + */ + tgnargs = trigdata->tg_trigger->tgnargs; + tgargs = trigdata->tg_trigger->tgargs; + 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()", + RI_MAX_NUMKEYS); + + /* ---------- + * Nothing to do if no column names to compare given + * ---------- + */ + if (tgnargs == 4) + return NULL; + + /* ---------- + * Get the relation descriptors of the FK and PK tables and + * the old tuple. + * ---------- + */ + fk_rel = heap_openr(tgargs[RI_FK_RELNAME_ARGNO], NoLock); + pk_rel = trigdata->tg_relation; + old_row = trigdata->tg_trigtuple; + + switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO])) + { + /* ---------- + * SQL3 11.9 + * Gereral rules 6) a) ii): + * MATCH or MATCH FULL + * ... ON DELETE SET NULL + * ---------- + */ + case RI_MATCH_TYPE_UNSPECIFIED: + case RI_MATCH_TYPE_FULL: + ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid, + RI_PLAN_SETNULL_DEL_DOUPDATE, + fk_rel, pk_rel, + tgnargs, tgargs); + + 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, NoLock); + return NULL; + + case RI_KEYS_NONE_NULL: + /* ---------- + * Have a full qualified key - continue below + * ---------- + */ + break; + } + heap_close(fk_rel, NoLock); + + if (SPI_connect() != SPI_OK_CONNECT) + elog(NOTICE, "SPI_connect() failed in RI_FKey_setnull_del()"); + + /* ---------- + * Fetch or prepare a saved plan for the set null delete + * operation + * ---------- + */ + if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL) + { + char buf[256]; + char querystr[8192]; + char qualstr[8192]; + char *querysep; + char *qualsep; + Oid queryoids[RI_MAX_NUMKEYS]; + + /* ---------- + * The query string built is + * UPDATE SET fkatt1 = NULL [, ...] + * 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 '='. + * ---------- + */ + sprintf(querystr, "UPDATE \"%s\" SET", + tgargs[RI_FK_RELNAME_ARGNO]); + qualstr[0] = '\0'; + querysep = ""; + qualsep = "WHERE"; + for (i = 0; i < qkey.nkeypairs; i++) + { + sprintf(buf, "%s \"%s\" = NULL", querysep, + tgargs[4 + i * 2]); + strcat(querystr, buf); + sprintf(buf, " %s \"%s\" = $%d", qualsep, + tgargs[4 + i * 2], i + 1); + strcat(qualstr, buf); + querysep = ","; + qualsep = "AND"; + queryoids[i] = SPI_gettypeid(pk_rel->rd_att, + qkey.keypair[i][RI_KEYPAIR_PK_IDX]); + } + strcat(querystr, qualstr); + + /* ---------- + * Prepare, save and remember the new plan. + * ---------- + */ + qplan = SPI_prepare(querystr, qkey.nkeypairs * 2, 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 updated PK tuple. + * ---------- + */ + for (i = 0; i < qkey.nkeypairs; i++) + { + upd_values[i] = SPI_getbinval(old_row, + pk_rel->rd_att, + qkey.keypair[i][RI_KEYPAIR_PK_IDX], + &isnull); + if (isnull) + upd_nulls[i] = 'n'; + else + upd_nulls[i] = ' '; + } + upd_nulls[i] = '\0'; + + /* ---------- + * Now update the existing references + * ---------- + */ + if (SPI_execp(qplan, upd_values, upd_nulls, 0) != SPI_OK_UPDATE) + elog(ERROR, "SPI_execp() failed in RI_FKey_setnull_del()"); + + if (SPI_finish() != SPI_OK_FINISH) + elog(NOTICE, "SPI_finish() failed in RI_FKey_setnull_del()"); + + return NULL; + + /* ---------- + * Handle MATCH PARTIAL set null delete. + * ---------- + */ + case RI_MATCH_TYPE_PARTIAL: + elog(ERROR, "MATCH PARTIAL not yet supported"); + return NULL; + } + + /* ---------- + * Never reached + * ---------- + */ + elog(ERROR, "internal error #6 in ri_triggers.c"); return NULL; } @@ -1325,12 +1518,212 @@ RI_FKey_setnull_del (FmgrInfo *proinfo) HeapTuple RI_FKey_setnull_upd (FmgrInfo *proinfo) { - TriggerData *trigdata; + TriggerData *trigdata; + 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; trigdata = CurrentTriggerData; CurrentTriggerData = NULL; - elog(ERROR, "RI_FKey_setnull_upd() called\n"); + /* ---------- + * Check that this is a valid trigger call on the right time and event. + * ---------- + */ + if (trigdata == NULL) + elog(ERROR, "RI_FKey_setnull_upd() not fired by trigger manager"); + if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) || + !TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) + 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"); + + /* ---------- + * Check for the correct # of call arguments + * ---------- + */ + tgnargs = trigdata->tg_trigger->tgnargs; + tgargs = trigdata->tg_trigger->tgargs; + 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()", + RI_MAX_NUMKEYS); + + /* ---------- + * Nothing to do if no column names to compare given + * ---------- + */ + if (tgnargs == 4) + return NULL; + + /* ---------- + * Get the relation descriptors of the FK and PK tables and + * the old tuple. + * ---------- + */ + fk_rel = heap_openr(tgargs[RI_FK_RELNAME_ARGNO], NoLock); + pk_rel = trigdata->tg_relation; + new_row = trigdata->tg_newtuple; + old_row = trigdata->tg_trigtuple; + + switch (ri_DetermineMatchType(tgargs[RI_MATCH_TYPE_ARGNO])) + { + /* ---------- + * SQL3 11.9 + * Gereral rules 7) a) ii) 2): + * MATCH FULL + * ... ON UPDATE SET NULL + * ---------- + */ + case RI_MATCH_TYPE_UNSPECIFIED: + elog(ERROR, "MATCH UNSPECIFIED not yet supported"); + return NULL; + + case RI_MATCH_TYPE_FULL: + ri_BuildQueryKeyFull(&qkey, trigdata->tg_trigger->tgoid, + RI_PLAN_SETNULL_UPD_DOUPDATE, + fk_rel, pk_rel, + tgnargs, tgargs); + + 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, NoLock); + return NULL; + + case RI_KEYS_NONE_NULL: + /* ---------- + * Have a full qualified key - continue below + * ---------- + */ + break; + } + heap_close(fk_rel, NoLock); + + /* ---------- + * No need to do anything if old and new keys are equal + * ---------- + */ + if (ri_KeysEqual(pk_rel, old_row, new_row, &qkey, + RI_KEYPAIR_PK_IDX)) + return NULL; + + if (SPI_connect() != SPI_OK_CONNECT) + elog(NOTICE, "SPI_connect() failed in RI_FKey_setnull_upd()"); + + /* ---------- + * Fetch or prepare a saved plan for the set null update + * operation + * ---------- + */ + if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL) + { + char buf[256]; + char querystr[8192]; + char qualstr[8192]; + char *querysep; + char *qualsep; + Oid queryoids[RI_MAX_NUMKEYS]; + + /* ---------- + * The query string built is + * UPDATE SET fkatt1 = NULL [, ...] + * 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 '='. + * ---------- + */ + sprintf(querystr, "UPDATE \"%s\" SET", + tgargs[RI_FK_RELNAME_ARGNO]); + qualstr[0] = '\0'; + querysep = ""; + qualsep = "WHERE"; + for (i = 0; i < qkey.nkeypairs; i++) + { + sprintf(buf, "%s \"%s\" = NULL", querysep, + tgargs[4 + i * 2]); + strcat(querystr, buf); + sprintf(buf, " %s \"%s\" = $%d", qualsep, + tgargs[4 + i * 2], i + 1); + strcat(qualstr, buf); + querysep = ","; + qualsep = "AND"; + queryoids[i] = SPI_gettypeid(pk_rel->rd_att, + qkey.keypair[i][RI_KEYPAIR_PK_IDX]); + } + strcat(querystr, qualstr); + + /* ---------- + * Prepare, save and remember the new plan. + * ---------- + */ + qplan = SPI_prepare(querystr, qkey.nkeypairs * 2, 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 updated PK tuple. + * ---------- + */ + for (i = 0; i < qkey.nkeypairs; i++) + { + upd_values[i] = SPI_getbinval(old_row, + pk_rel->rd_att, + qkey.keypair[i][RI_KEYPAIR_PK_IDX], + &isnull); + if (isnull) + upd_nulls[i] = 'n'; + else + upd_nulls[i] = ' '; + } + upd_nulls[i] = '\0'; + + /* ---------- + * Now update the existing references + * ---------- + */ + if (SPI_execp(qplan, upd_values, upd_nulls, 0) != SPI_OK_UPDATE) + elog(ERROR, "SPI_execp() failed in RI_FKey_setnull_upd()"); + + if (SPI_finish() != SPI_OK_FINISH) + elog(NOTICE, "SPI_finish() failed in RI_FKey_setnull_upd()"); + + return NULL; + + /* ---------- + * Handle MATCH PARTIAL set null update. + * ---------- + */ + case RI_MATCH_TYPE_PARTIAL: + elog(ERROR, "MATCH PARTIAL not yet supported"); + return NULL; + } + + /* ---------- + * Never reached + * ---------- + */ + elog(ERROR, "internal error #7 in ri_triggers.c"); return NULL; } -- 2.11.0