On Sun, Feb 3, 2019 at 8:28 AM Andres Freund <and...@anarazel.de> wrote:
>
> >
> > It compiled, worked as expected, but some tests broke executing make
> check:
> >
> > test create_table ... FAILED
> > constraints ... FAILED
> > inherit ... FAILED
> > foreign_data ... FAILED
> > alter_table ... FAILED
> >
> > I didn't test the bugfix, just the v3 patch.
>
> Given that the patch hasn't been updated since, and the CF has ended,
> I'm marking this patch as returned with feedback. Please resubmit once
> that's fixed.
>
>
Hi all.
Sorry for the long delay. I've rebased the patch to current master (at
f2004f19ed now), attached as postgresql-alter-constraint.v4.patch. All
tests passed cleanly.
Best regards,
--
Matheus de Oliveira
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index e360728c02..b2866f467a 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -55,7 +55,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>
+ [ 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 ]
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 515c29072c..56847b90f9 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8305,8 +8305,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;
@@ -8324,6 +8359,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,
@@ -8360,23 +8397,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 createForeignKeyTriggers 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 a8a735c247..f28acf818f 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2900,6 +2900,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/outfuncs.c b/src/backend/nodes/outfuncs.c
index 69179a07c3..71492df3b3 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3435,6 +3435,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 e814939a25..f0d68e8e92 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);
@@ -541,7 +542,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
@@ -2273,7 +2275,7 @@ alter_table_cmd:
$$ = (Node *)n;
}
/* ALTER TABLE <name> ALTER CONSTRAINT ... */
- | ALTER CONSTRAINT name ConstraintAttributeSpec
+ | ALTER CONSTRAINT name opt_key_actions ConstraintAttributeSpec
{
AlterTableCmd *n = makeNode(AlterTableCmd);
Constraint *c = makeNode(Constraint);
@@ -2281,9 +2283,11 @@ 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);
+ processCASbits($5, @4, "ALTER CONSTRAINT statement",
+ &c->deferrable, &c->was_deferrable_set,
+ &c->initdeferred, &c->was_initdeferred_set,
NULL, NULL, yyscanner);
$$ = (Node *)n;
}
@@ -3616,7 +3620,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;
@@ -3633,8 +3637,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
@@ -3648,8 +3652,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
@@ -3664,8 +3668,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
@@ -3679,8 +3683,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 ')'
@@ -3698,8 +3702,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
@@ -3715,7 +3719,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;
@@ -3795,7 +3800,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
@@ -3810,6 +3815,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; }
;
@@ -5335,8 +5357,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;
}
@@ -16172,7 +16194,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 */
@@ -16183,6 +16206,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 fcfba4be4c..ab8475e22f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2103,6 +2103,7 @@ 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'
@@ -2120,6 +2121,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 2a26aa3a89..cd94101448 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -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) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
+(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) ON DELETE CASCADE DEFERRABLE
+(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) ON DELETE CASCADE
+(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 401514a3e0..6fa279efe0 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -2019,3 +2019,127 @@ ERROR: constraint "my_fkey" of relation "fk_part_1_1" does not exist
drop schema fkpart0, fkpart1, fkpart2 cascade;
NOTICE: drop cascades to 8 other objects
\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 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 5bda7febde..dfb792661b 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 beeaf3277d..d6f3bb28e4 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1443,3 +1443,80 @@ alter table fkpart2.fk_part_1_1 drop constraint my_fkey; -- doesn't exist
\set VERBOSITY terse \\ -- suppress cascade details
drop schema fkpart0, fkpart1, fkpart2 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 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;
