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;