Hi All,
I've took some time today to rebase the patch with master. Follows attached.
I'm still not sure if the chosen path is the best way. But I'd be glad to
follow any directions we all see fit.
For now, this patch applies two methods:
1. Changes full constraint definition (which keeps compatibility with
current ALTER CONSTRAINT):
ALTER CONSTRAINT [<on_update>] [<on_delete>] [<deferrability>]
2. Changes only the subset explicit seem in the command (a new way, I've
chosen to just add SET in the middle, similar to `ALTER COLUMN ... SET
{DEFAULT | NOT NULL}` ):
ALTER CONSTRAINT SET [<on_update>] [<on_delete>] [<deferrability>]
I'm OK with changing the approach, we just need to chose the color :D
I believe this is a small change in source code, but with huge impact for
users with big tables. Would be great if it could go in PG 13.
Best regards,
--
Matheus de Oliveira
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index e486196477..6a51014b6f 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -56,7 +56,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
- ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
+ [SET] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ]
+ [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
@@ -488,6 +490,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
This form alters the attributes of a constraint that was previously
created. Currently only foreign key constraints may be altered.
</para>
+ <para>
+ If <literal>SET</literal> keyword is ommitted, the full constraint
+ definition is changed, meaning that every option mentioned is set
+ accordingly and unmentioned options are set as default built-in values,
+ just like <literal>ADD CONSTRAINT</literal> would do, see definition of
+ default values on <xref linkend="sql-createtable"/>. With
+ <literal>SET</literal> keyword, only mentioned attributes are changed.
+ </para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 8e35c5bd1a..70fdea680e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9509,8 +9509,43 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
cmdcon->conname, RelationGetRelationName(rel))));
+ /*
+ * Verify for FKCONSTR_ACTION_UNKNOWN, if found, replace by current
+ * action. We could handle FKCONSTR_ACTION_UNKNOWN bellow, but since we
+ * already have to handle the case of changing to the same action, seems
+ * simpler to simple replace FKCONSTR_ACTION_UNKNOWN by the current action
+ * here.
+ */
+ if (cmdcon->fk_del_action == FKCONSTR_ACTION_UNKNOWN)
+ cmdcon->fk_del_action = currcon->confdeltype;
+
+ if (cmdcon->fk_upd_action == FKCONSTR_ACTION_UNKNOWN)
+ cmdcon->fk_upd_action = currcon->confupdtype;
+
+ /*
+ * Do the same for deferrable attributes. But consider that if changed
+ * only initdeferred attribute and to true, force deferrable to be also
+ * true. On the other hand, if changed only deferrable attribute and to
+ * false, force initdeferred to be also false.
+ */
+ if (!cmdcon->was_deferrable_set)
+ cmdcon->deferrable = cmdcon->initdeferred ? true : currcon->condeferrable;
+
+ if (!cmdcon->was_initdeferred_set)
+ cmdcon->initdeferred = !cmdcon->deferrable ? false : currcon->condeferred;
+
+ /*
+ * This is a safe check only, should never happen here.
+ */
+ if (cmdcon->initdeferred && !cmdcon->deferrable)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE")));
+
if (currcon->condeferrable != cmdcon->deferrable ||
- currcon->condeferred != cmdcon->initdeferred)
+ currcon->condeferred != cmdcon->initdeferred ||
+ currcon->confdeltype != cmdcon->fk_del_action ||
+ currcon->confupdtype != cmdcon->fk_upd_action)
{
HeapTuple copyTuple;
HeapTuple tgtuple;
@@ -9528,6 +9563,8 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
copy_con->condeferrable = cmdcon->deferrable;
copy_con->condeferred = cmdcon->initdeferred;
+ copy_con->confdeltype = cmdcon->fk_del_action;
+ copy_con->confupdtype = cmdcon->fk_upd_action;
CatalogTupleUpdate(conrel, ©Tuple->t_self, copyTuple);
InvokeObjectPostAlterHook(ConstraintRelationId,
@@ -9564,23 +9601,106 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
otherrelids = list_append_unique_oid(otherrelids,
tgform->tgrelid);
- /*
- * Update deferrability of RI_FKey_noaction_del,
- * RI_FKey_noaction_upd, RI_FKey_check_ins and RI_FKey_check_upd
- * triggers, but not others; see createForeignKeyActionTriggers
- * and CreateFKCheckTrigger.
- */
- if (tgform->tgfoid != F_RI_FKEY_NOACTION_DEL &&
- tgform->tgfoid != F_RI_FKEY_NOACTION_UPD &&
- tgform->tgfoid != F_RI_FKEY_CHECK_INS &&
- tgform->tgfoid != F_RI_FKEY_CHECK_UPD)
- continue;
-
copyTuple = heap_copytuple(tgtuple);
copy_tg = (Form_pg_trigger) GETSTRUCT(copyTuple);
+ /*
+ * Set deferrability here, but note that it may be overridden
+ * bellow if the pg_trigger entry is on the referencing table and
+ * depending on the action used for ON UPDATE/DELETE. But for
+ * check triggers (in the referenced table) it is kept as is
+ * (since ON UPDATE/DELETE actions makes no difference for the
+ * check triggers).
+ */
copy_tg->tgdeferrable = cmdcon->deferrable;
copy_tg->tginitdeferred = cmdcon->initdeferred;
+
+ /*
+ * Set ON DELETE action
+ */
+ if (tgform->tgfoid == F_RI_FKEY_NOACTION_DEL ||
+ tgform->tgfoid == F_RI_FKEY_RESTRICT_DEL ||
+ tgform->tgfoid == F_RI_FKEY_CASCADE_DEL ||
+ tgform->tgfoid == F_RI_FKEY_SETNULL_DEL ||
+ tgform->tgfoid == F_RI_FKEY_SETDEFAULT_DEL)
+ {
+ switch (cmdcon->fk_del_action)
+ {
+ case FKCONSTR_ACTION_NOACTION:
+ copy_tg->tgdeferrable = cmdcon->deferrable;
+ copy_tg->tginitdeferred = cmdcon->initdeferred;
+ copy_tg->tgfoid = F_RI_FKEY_NOACTION_DEL;
+ break;
+ case FKCONSTR_ACTION_RESTRICT:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_RESTRICT_DEL;
+ break;
+ case FKCONSTR_ACTION_CASCADE:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_CASCADE_DEL;
+ break;
+ case FKCONSTR_ACTION_SETNULL:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_SETNULL_DEL;
+ break;
+ case FKCONSTR_ACTION_SETDEFAULT:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_SETDEFAULT_DEL;
+ break;
+ default:
+ elog(ERROR, "unrecognized FK action type: %d",
+ (int) cmdcon->fk_del_action);
+ break;
+ }
+ }
+
+ /*
+ * Set ON UPDATE action
+ */
+ if (tgform->tgfoid == F_RI_FKEY_NOACTION_UPD ||
+ tgform->tgfoid == F_RI_FKEY_RESTRICT_UPD ||
+ tgform->tgfoid == F_RI_FKEY_CASCADE_UPD ||
+ tgform->tgfoid == F_RI_FKEY_SETNULL_UPD ||
+ tgform->tgfoid == F_RI_FKEY_SETDEFAULT_UPD)
+ {
+ switch (cmdcon->fk_upd_action)
+ {
+ case FKCONSTR_ACTION_NOACTION:
+ copy_tg->tgdeferrable = cmdcon->deferrable;
+ copy_tg->tginitdeferred = cmdcon->initdeferred;
+ copy_tg->tgfoid = F_RI_FKEY_NOACTION_UPD;
+ break;
+ case FKCONSTR_ACTION_RESTRICT:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_RESTRICT_UPD;
+ break;
+ case FKCONSTR_ACTION_CASCADE:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_CASCADE_UPD;
+ break;
+ case FKCONSTR_ACTION_SETNULL:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_SETNULL_UPD;
+ break;
+ case FKCONSTR_ACTION_SETDEFAULT:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_SETDEFAULT_UPD;
+ break;
+ default:
+ elog(ERROR, "unrecognized FK action type: %d",
+ (int) cmdcon->fk_upd_action);
+ break;
+ }
+ }
+
CatalogTupleUpdate(tgrel, ©Tuple->t_self, copyTuple);
InvokeObjectPostAlterHook(TriggerRelationId, currcon->oid, 0);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index eaab97f753..1b15ea2c6a 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2919,6 +2919,8 @@ _copyConstraint(const Constraint *from)
COPY_SCALAR_FIELD(deferrable);
COPY_SCALAR_FIELD(initdeferred);
COPY_LOCATION_FIELD(location);
+ COPY_SCALAR_FIELD(was_deferrable_set);
+ COPY_SCALAR_FIELD(was_initdeferred_set);
COPY_SCALAR_FIELD(is_no_inherit);
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 88b912977e..5dc4fd15b7 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2610,6 +2610,8 @@ _equalConstraint(const Constraint *a, const Constraint *b)
COMPARE_SCALAR_FIELD(deferrable);
COMPARE_SCALAR_FIELD(initdeferred);
COMPARE_LOCATION_FIELD(location);
+ COMPARE_SCALAR_FIELD(was_deferrable_set);
+ COMPARE_SCALAR_FIELD(was_initdeferred_set);
COMPARE_SCALAR_FIELD(is_no_inherit);
COMPARE_NODE_FIELD(raw_expr);
COMPARE_STRING_FIELD(cooked_expr);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index e084c3f069..059d69ed6f 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3471,6 +3471,8 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_BOOL_FIELD(deferrable);
WRITE_BOOL_FIELD(initdeferred);
WRITE_LOCATION_FIELD(location);
+ WRITE_BOOL_FIELD(was_deferrable_set);
+ WRITE_BOOL_FIELD(was_initdeferred_set);
appendStringInfoString(str, " :contype ");
switch (node->contype)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7e384f956c..73451ebea5 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -185,7 +185,8 @@ static void SplitColQualList(List *qualList,
List **constraintList, CollateClause **collClause,
core_yyscan_t yyscanner);
static void processCASbits(int cas_bits, int location, const char *constrType,
- bool *deferrable, bool *initdeferred, bool *not_valid,
+ bool *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner);
static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
@@ -545,7 +546,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> TableLikeOptionList TableLikeOption
%type <list> ColQualList
%type <node> ColConstraint ColConstraintElem ConstraintAttr
-%type <ival> key_actions key_delete key_match key_update key_action
+%type <ival> key_actions opt_key_actions
+%type <ival> key_delete key_match key_update key_action
%type <ival> ConstraintAttributeSpec ConstraintAttributeElem
%type <str> ExistingIndex
@@ -2298,8 +2300,25 @@ alter_table_cmd:
n->def = $2;
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> ALTER CONSTRAINT ... SET */
+ | ALTER CONSTRAINT name SET opt_key_actions ConstraintAttributeSpec
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ Constraint *c = makeNode(Constraint);
+ n->subtype = AT_AlterConstraint;
+ n->def = (Node *) c;
+ c->contype = CONSTR_FOREIGN; /* others not supported, yet */
+ c->conname = $3;
+ c->fk_upd_action = (char) ($5 >> 8);
+ c->fk_del_action = (char) ($5 & 0xFF);
+ processCASbits($6, @5, "ALTER CONSTRAINT statement",
+ &c->deferrable, &c->was_deferrable_set,
+ &c->initdeferred, &c->was_initdeferred_set,
+ NULL, NULL, yyscanner);
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> ALTER CONSTRAINT ... */
- | ALTER CONSTRAINT name ConstraintAttributeSpec
+ | ALTER CONSTRAINT name key_actions ConstraintAttributeSpec
{
AlterTableCmd *n = makeNode(AlterTableCmd);
Constraint *c = makeNode(Constraint);
@@ -2307,9 +2326,14 @@ alter_table_cmd:
n->def = (Node *) c;
c->contype = CONSTR_FOREIGN; /* others not supported, yet */
c->conname = $3;
- processCASbits($4, @4, "ALTER CONSTRAINT statement",
- &c->deferrable,
- &c->initdeferred,
+ c->fk_upd_action = (char) ($4 >> 8);
+ c->fk_del_action = (char) ($4 & 0xFF);
+ /* Without SET, always change deferrability */
+ c->was_deferrable_set = true;
+ c->was_initdeferred_set = true;
+ processCASbits($5, @4, "ALTER CONSTRAINT statement",
+ &c->deferrable, NULL,
+ &c->initdeferred, NULL,
NULL, NULL, yyscanner);
$$ = (Node *)n;
}
@@ -3666,7 +3690,7 @@ ConstraintElem:
n->raw_expr = $3;
n->cooked_expr = NULL;
processCASbits($5, @5, "CHECK",
- NULL, NULL, &n->skip_validation,
+ NULL, NULL, NULL, NULL, &n->skip_validation,
&n->is_no_inherit, yyscanner);
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
@@ -3683,8 +3707,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $7;
processCASbits($8, @8, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| UNIQUE ExistingIndex ConstraintAttributeSpec
@@ -3698,8 +3722,8 @@ ConstraintElem:
n->indexname = $2;
n->indexspace = NULL;
processCASbits($3, @3, "UNIQUE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY '(' columnList ')' opt_c_include opt_definition OptConsTableSpace
@@ -3714,8 +3738,8 @@ ConstraintElem:
n->indexname = NULL;
n->indexspace = $8;
processCASbits($9, @9, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| PRIMARY KEY ExistingIndex ConstraintAttributeSpec
@@ -3729,8 +3753,8 @@ ConstraintElem:
n->indexname = $3;
n->indexspace = NULL;
processCASbits($4, @4, "PRIMARY KEY",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| EXCLUDE access_method_clause '(' ExclusionConstraintList ')'
@@ -3748,8 +3772,8 @@ ConstraintElem:
n->indexspace = $8;
n->where_clause = $9;
processCASbits($10, @10, "EXCLUDE",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
$$ = (Node *)n;
}
| FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
@@ -3765,7 +3789,8 @@ ConstraintElem:
n->fk_upd_action = (char) ($10 >> 8);
n->fk_del_action = (char) ($10 & 0xFF);
processCASbits($11, @11, "FOREIGN KEY",
- &n->deferrable, &n->initdeferred,
+ &n->deferrable, NULL,
+ &n->initdeferred, NULL,
&n->skip_validation, NULL,
yyscanner);
n->initially_valid = !n->skip_validation;
@@ -3845,7 +3870,7 @@ ExclusionWhereClause:
* We combine the update and delete actions into one value temporarily
* for simplicity of parsing, and then break them down again in the
* calling production. update is in the left 8 bits, delete in the right.
- * Note that NOACTION is the default.
+ * Note that NOACTION is the default. See also opt_key_actions.
*/
key_actions:
key_update
@@ -3860,6 +3885,23 @@ key_actions:
{ $$ = (FKCONSTR_ACTION_NOACTION << 8) | (FKCONSTR_ACTION_NOACTION & 0xFF); }
;
+/*
+ * Basically the same as key_actions, but using FKCONSTR_ACTION_UNKNOWN
+ * as the default one instead of NOACTION.
+ */
+opt_key_actions:
+ key_update
+ { $$ = ($1 << 8) | (FKCONSTR_ACTION_UNKNOWN & 0xFF); }
+ | key_delete
+ { $$ = (FKCONSTR_ACTION_UNKNOWN << 8) | ($1 & 0xFF); }
+ | key_update key_delete
+ { $$ = ($1 << 8) | ($2 & 0xFF); }
+ | key_delete key_update
+ { $$ = ($2 << 8) | ($1 & 0xFF); }
+ | /*EMPTY*/
+ { $$ = (FKCONSTR_ACTION_UNKNOWN << 8) | (FKCONSTR_ACTION_UNKNOWN & 0xFF); }
+ ;
+
key_update: ON UPDATE key_action { $$ = $3; }
;
@@ -5419,8 +5461,8 @@ CreateTrigStmt:
n->transitionRels = NIL;
n->isconstraint = true;
processCASbits($10, @10, "TRIGGER",
- &n->deferrable, &n->initdeferred, NULL,
- NULL, yyscanner);
+ &n->deferrable, NULL, &n->initdeferred, NULL,
+ NULL, NULL, yyscanner);
n->constrrel = $9;
$$ = (Node *)n;
}
@@ -16357,7 +16399,8 @@ SplitColQualList(List *qualList,
*/
static void
processCASbits(int cas_bits, int location, const char *constrType,
- bool *deferrable, bool *initdeferred, bool *not_valid,
+ bool *deferrable, bool *was_deferrable_set,
+ bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
bool *no_inherit, core_yyscan_t yyscanner)
{
/* defaults */
@@ -16368,6 +16411,14 @@ processCASbits(int cas_bits, int location, const char *constrType,
if (not_valid)
*not_valid = false;
+ if (was_deferrable_set)
+ *was_deferrable_set = cas_bits & (CAS_DEFERRABLE
+ | CAS_NOT_DEFERRABLE) ? true : false;
+
+ if (was_initdeferred_set)
+ *was_initdeferred_set = cas_bits & (CAS_INITIALLY_DEFERRED
+ | CAS_INITIALLY_IMMEDIATE) ? true : false;
+
if (cas_bits & (CAS_DEFERRABLE | CAS_INITIALLY_DEFERRED))
{
if (deferrable)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 2039b42449..e685d2ba0b 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2137,6 +2137,8 @@ typedef enum ConstrType /* types of constraints */
#define FKCONSTR_ACTION_CASCADE 'c'
#define FKCONSTR_ACTION_SETNULL 'n'
#define FKCONSTR_ACTION_SETDEFAULT 'd'
+#define FKCONSTR_ACTION_UNKNOWN 'u' /* unknown is used only for ALTER
+ * CONSTRAINT */
/* Foreign key matchtype codes */
#define FKCONSTR_MATCH_FULL 'f'
@@ -2154,6 +2156,10 @@ typedef struct Constraint
bool initdeferred; /* INITIALLY DEFERRED? */
int location; /* token location, or -1 if unknown */
+ /* Fields used by ALTER CONSTRAINT to verify if a change was actually made */
+ bool was_deferrable_set; /* Was DEFERRABLE informed? */
+ bool was_initdeferred_set; /* Was INITIALLY DEFERRED informed? */
+
/* Fields used for constraints with expressions (CHECK and DEFAULT): */
bool is_no_inherit; /* is constraint non-inheritable? */
Node *raw_expr; /* expr, as untransformed parse tree */
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index fb6d86a269..e67e1953aa 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -704,15 +704,15 @@ ORDER BY 1,2,3;
---------+------------------------+--------+--------------+----------------
fkdd | "RI_FKey_cascade_del" | 9 | f | f
fkdd | "RI_FKey_noaction_upd" | 17 | t | t
- fkdd2 | "RI_FKey_cascade_del" | 9 | f | f
+ fkdd2 | "RI_FKey_noaction_del" | 9 | t | t
fkdd2 | "RI_FKey_noaction_upd" | 17 | t | t
fkdi | "RI_FKey_cascade_del" | 9 | f | f
fkdi | "RI_FKey_noaction_upd" | 17 | t | f
- fkdi2 | "RI_FKey_cascade_del" | 9 | f | f
+ fkdi2 | "RI_FKey_noaction_del" | 9 | t | f
fkdi2 | "RI_FKey_noaction_upd" | 17 | t | f
fknd | "RI_FKey_cascade_del" | 9 | f | f
fknd | "RI_FKey_noaction_upd" | 17 | f | f
- fknd2 | "RI_FKey_cascade_del" | 9 | f | f
+ fknd2 | "RI_FKey_noaction_del" | 9 | f | f
fknd2 | "RI_FKey_noaction_upd" | 17 | f | f
(12 rows)
@@ -736,6 +736,28 @@ ORDER BY 1,2,3;
fknd2 | "RI_FKey_check_upd" | 17 | f | f
(12 rows)
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+-------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1)
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+-------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1)
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+ pg_get_constraintdef
+-------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1)
+(1 row)
+
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
create table atacc1 ( test int );
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 07bd5b6434..dcab082d69 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -2470,3 +2470,128 @@ DROP SCHEMA fkpart9 CASCADE;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table fkpart9.pk
drop cascades to table fkpart9.fk
+\set VERBOSITY default
+-- ALTER CONSTRAINT changing ON UPDATE/DELETE.
+-- Try all combinations and validate the diff with a created constraint
+CREATE SCHEMA createtest; -- created constraints with target action, validation
+CREATE SCHEMA altertest; -- created with source and altered to target, test
+DO
+$test_alter_con$
+DECLARE
+ v_result json;
+ method text;
+ from_action text;
+ to_action text;
+BEGIN
+ FOR method, from_action, to_action IN
+ WITH act(action) AS (
+ SELECT unnest('{NO ACTION,RESTRICT,CASCADE,SET DEFAULT,SET NULL}'::text[])
+ )
+ SELECT
+ m.method, a1.action, a2.action
+ FROM unnest('{UPDATE,DELETE}'::text[]) AS m(method), act a1, act a2
+ LOOP
+ EXECUTE format(
+ $sql$
+ -- Alter from ON %1$s %2$s to ON %1$s %3$s
+ CREATE TABLE createtest.foo(id integer primary key);
+ CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON %1$s %3$s, val text);
+
+ CREATE TABLE altertest.foo(id integer primary key);
+ INSERT INTO altertest.foo VALUES(0),(1),(2),(3);
+
+ CREATE TABLE altertest.bar(foo_id integer DEFAULT 0 REFERENCES altertest.foo ON %1$s %2$s, val text);
+
+ ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey SET ON %1$s %3$s;
+
+ $sql$, method, from_action, to_action);
+
+ SELECT json_agg(t)
+ INTO v_result
+ FROM (
+ -- Do EXCEPT of the "altertest" and "createtest" constraints, if they are equal (as expected), it should return empty
+ SELECT
+ rel.relname, replace(tg.tgname, tg.oid::text, 'OID') AS tgname,
+ tg.tgfoid::regproc, con.conname, con.confupdtype, con.confdeltype, tg.tgdeferrable,
+ regexp_replace(pg_get_constraintdef(con.oid), '(createtest\.|altertest\.)', '') AS condef
+ FROM pg_trigger tg
+ JOIN pg_constraint con ON con.oid = tg.tgconstraint
+ JOIN pg_class rel ON tg.tgrelid = rel.oid
+ WHERE tg.tgrelid IN ('altertest.foo'::regclass, 'altertest.bar'::regclass)
+ EXCEPT
+ SELECT
+ rel.relname, replace(tg.tgname, tg.oid::text, 'OID') AS tgname,
+ tg.tgfoid::regproc, con.conname, con.confupdtype, con.confdeltype, tg.tgdeferrable,
+ regexp_replace(pg_get_constraintdef(con.oid), '(createtest\.|altertest\.)', '') AS condef
+ FROM pg_trigger tg
+ JOIN pg_constraint con ON con.oid = tg.tgconstraint
+ JOIN pg_class rel ON tg.tgrelid = rel.oid
+ WHERE tg.tgrelid IN ('createtest.foo'::regclass, 'createtest.bar'::regclass)
+ ) t;
+
+ DROP TABLE createtest.bar;
+ DROP TABLE createtest.foo;
+ DROP TABLE altertest.bar;
+ DROP TABLE altertest.foo;
+
+ IF (v_result IS NULL) THEN
+ RAISE INFO 'ON % from % to %: OK.', method, from_action, to_action;
+ ELSE
+ RAISE EXCEPTION 'ON % from % to %. FAILED! Unmatching rows: %', method, from_action, to_action, v_result;
+ END IF;
+ END LOOP;
+END;
+$test_alter_con$
+;
+INFO: ON UPDATE from NO ACTION to NO ACTION: OK.
+INFO: ON UPDATE from RESTRICT to NO ACTION: OK.
+INFO: ON UPDATE from CASCADE to NO ACTION: OK.
+INFO: ON UPDATE from SET DEFAULT to NO ACTION: OK.
+INFO: ON UPDATE from SET NULL to NO ACTION: OK.
+INFO: ON DELETE from NO ACTION to NO ACTION: OK.
+INFO: ON DELETE from RESTRICT to NO ACTION: OK.
+INFO: ON DELETE from CASCADE to NO ACTION: OK.
+INFO: ON DELETE from SET DEFAULT to NO ACTION: OK.
+INFO: ON DELETE from SET NULL to NO ACTION: OK.
+INFO: ON UPDATE from NO ACTION to RESTRICT: OK.
+INFO: ON UPDATE from RESTRICT to RESTRICT: OK.
+INFO: ON UPDATE from CASCADE to RESTRICT: OK.
+INFO: ON UPDATE from SET DEFAULT to RESTRICT: OK.
+INFO: ON UPDATE from SET NULL to RESTRICT: OK.
+INFO: ON DELETE from NO ACTION to RESTRICT: OK.
+INFO: ON DELETE from RESTRICT to RESTRICT: OK.
+INFO: ON DELETE from CASCADE to RESTRICT: OK.
+INFO: ON DELETE from SET DEFAULT to RESTRICT: OK.
+INFO: ON DELETE from SET NULL to RESTRICT: OK.
+INFO: ON UPDATE from NO ACTION to CASCADE: OK.
+INFO: ON UPDATE from RESTRICT to CASCADE: OK.
+INFO: ON UPDATE from CASCADE to CASCADE: OK.
+INFO: ON UPDATE from SET DEFAULT to CASCADE: OK.
+INFO: ON UPDATE from SET NULL to CASCADE: OK.
+INFO: ON DELETE from NO ACTION to CASCADE: OK.
+INFO: ON DELETE from RESTRICT to CASCADE: OK.
+INFO: ON DELETE from CASCADE to CASCADE: OK.
+INFO: ON DELETE from SET DEFAULT to CASCADE: OK.
+INFO: ON DELETE from SET NULL to CASCADE: OK.
+INFO: ON UPDATE from NO ACTION to SET DEFAULT: OK.
+INFO: ON UPDATE from RESTRICT to SET DEFAULT: OK.
+INFO: ON UPDATE from CASCADE to SET DEFAULT: OK.
+INFO: ON UPDATE from SET DEFAULT to SET DEFAULT: OK.
+INFO: ON UPDATE from SET NULL to SET DEFAULT: OK.
+INFO: ON DELETE from NO ACTION to SET DEFAULT: OK.
+INFO: ON DELETE from RESTRICT to SET DEFAULT: OK.
+INFO: ON DELETE from CASCADE to SET DEFAULT: OK.
+INFO: ON DELETE from SET DEFAULT to SET DEFAULT: OK.
+INFO: ON DELETE from SET NULL to SET DEFAULT: OK.
+INFO: ON UPDATE from NO ACTION to SET NULL: OK.
+INFO: ON UPDATE from RESTRICT to SET NULL: OK.
+INFO: ON UPDATE from CASCADE to SET NULL: OK.
+INFO: ON UPDATE from SET DEFAULT to SET NULL: OK.
+INFO: ON UPDATE from SET NULL to SET NULL: OK.
+INFO: ON DELETE from NO ACTION to SET NULL: OK.
+INFO: ON DELETE from RESTRICT to SET NULL: OK.
+INFO: ON DELETE from CASCADE to SET NULL: OK.
+INFO: ON DELETE from SET DEFAULT to SET NULL: OK.
+INFO: ON DELETE from SET NULL to SET NULL: OK.
+DROP SCHEMA createtest;
+DROP SCHEMA altertest;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 3801f19c58..590ff7e43b 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -528,6 +528,16 @@ FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
WHERE tgrelid = 'fktable'::regclass
ORDER BY 1,2,3;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
-- temp tables should go away by themselves, need not drop them.
-- test check constraint adding
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index c5c9011afc..73a586131f 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1738,3 +1738,81 @@ DELETE FROM fkpart9.pk WHERE a=35;
SELECT * FROM fkpart9.pk;
SELECT * FROM fkpart9.fk;
DROP SCHEMA fkpart9 CASCADE;
+\set VERBOSITY default
+
+-- ALTER CONSTRAINT changing ON UPDATE/DELETE.
+-- Try all combinations and validate the diff with a created constraint
+CREATE SCHEMA createtest; -- created constraints with target action, validation
+CREATE SCHEMA altertest; -- created with source and altered to target, test
+
+DO
+$test_alter_con$
+DECLARE
+ v_result json;
+ method text;
+ from_action text;
+ to_action text;
+BEGIN
+ FOR method, from_action, to_action IN
+ WITH act(action) AS (
+ SELECT unnest('{NO ACTION,RESTRICT,CASCADE,SET DEFAULT,SET NULL}'::text[])
+ )
+ SELECT
+ m.method, a1.action, a2.action
+ FROM unnest('{UPDATE,DELETE}'::text[]) AS m(method), act a1, act a2
+ LOOP
+ EXECUTE format(
+ $sql$
+ -- Alter from ON %1$s %2$s to ON %1$s %3$s
+ CREATE TABLE createtest.foo(id integer primary key);
+ CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON %1$s %3$s, val text);
+
+ CREATE TABLE altertest.foo(id integer primary key);
+ INSERT INTO altertest.foo VALUES(0),(1),(2),(3);
+
+ CREATE TABLE altertest.bar(foo_id integer DEFAULT 0 REFERENCES altertest.foo ON %1$s %2$s, val text);
+
+ ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey SET ON %1$s %3$s;
+
+ $sql$, method, from_action, to_action);
+
+ SELECT json_agg(t)
+ INTO v_result
+ FROM (
+ -- Do EXCEPT of the "altertest" and "createtest" constraints, if they are equal (as expected), it should return empty
+ SELECT
+ rel.relname, replace(tg.tgname, tg.oid::text, 'OID') AS tgname,
+ tg.tgfoid::regproc, con.conname, con.confupdtype, con.confdeltype, tg.tgdeferrable,
+ regexp_replace(pg_get_constraintdef(con.oid), '(createtest\.|altertest\.)', '') AS condef
+ FROM pg_trigger tg
+ JOIN pg_constraint con ON con.oid = tg.tgconstraint
+ JOIN pg_class rel ON tg.tgrelid = rel.oid
+ WHERE tg.tgrelid IN ('altertest.foo'::regclass, 'altertest.bar'::regclass)
+ EXCEPT
+ SELECT
+ rel.relname, replace(tg.tgname, tg.oid::text, 'OID') AS tgname,
+ tg.tgfoid::regproc, con.conname, con.confupdtype, con.confdeltype, tg.tgdeferrable,
+ regexp_replace(pg_get_constraintdef(con.oid), '(createtest\.|altertest\.)', '') AS condef
+ FROM pg_trigger tg
+ JOIN pg_constraint con ON con.oid = tg.tgconstraint
+ JOIN pg_class rel ON tg.tgrelid = rel.oid
+ WHERE tg.tgrelid IN ('createtest.foo'::regclass, 'createtest.bar'::regclass)
+ ) t;
+
+ DROP TABLE createtest.bar;
+ DROP TABLE createtest.foo;
+ DROP TABLE altertest.bar;
+ DROP TABLE altertest.foo;
+
+ IF (v_result IS NULL) THEN
+ RAISE INFO 'ON % from % to %: OK.', method, from_action, to_action;
+ ELSE
+ RAISE EXCEPTION 'ON % from % to %. FAILED! Unmatching rows: %', method, from_action, to_action, v_result;
+ END IF;
+ END LOOP;
+END;
+$test_alter_con$
+;
+
+DROP SCHEMA createtest;
+DROP SCHEMA altertest;
