diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 5be56d4..3f5f4ea 100644 *** a/doc/src/sgml/ref/alter_table.sgml --- b/doc/src/sgml/ref/alter_table.sgml *************** *** 55,61 **** ALTER TABLE [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } ADD table_constraint [ NOT VALID ] ADD table_constraint_using_index ! ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] VALIDATE CONSTRAINT constraint_name DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] DISABLE TRIGGER [ trigger_name | ALL | USER ] --- 55,63 ---- ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } ADD table_constraint [ NOT VALID ] ADD table_constraint_using_index ! ALTER CONSTRAINT constraint_name ! [ ON DELETE action ] [ ON UPDATE action ] ! [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] VALIDATE CONSTRAINT constraint_name DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] DISABLE TRIGGER [ trigger_name | ALL | USER ] diff --git a/src/backend/commands/tablecindex db6c8ff..c489616 100644 *** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *************** *** 7623,7630 **** ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint", cmdcon->conname, RelationGetRelationName(rel)))); if (currcon->condeferrable != cmdcon->deferrable || ! currcon->condeferred != cmdcon->initdeferred) { HeapTuple copyTuple; HeapTuple tgtuple; --- 7623,7665 ---- 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->confdeltype != cmdcon->fk_del_action || ! currcon->confupdtype != cmdcon->fk_upd_action) { HeapTuple copyTuple; HeapTuple tgtuple; *************** *** 7642,7647 **** ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, --- 7677,7684 ---- 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, *************** *** 7678,7700 **** 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); ! copy_tg->tgdeferrable = cmdcon->deferrable; copy_tg->tginitdeferred = cmdcon->initdeferred; CatalogTupleUpdate(tgrel, ©Tuple->t_self, copyTuple); InvokeObjectPostAlterHook(TriggerRelationId, --- 7715,7818 ---- otherrelids = list_append_unique_oid(otherrelids, tgform->tgrelid); copyTuple = heap_copytuple(tgtuple); copy_tg = (Form_pg_trigger) GETSTRUCT(copyTuple); ! /* ! * Always set deferrability. Note that it may be overridden bellow ! * if the pg_trigger entry is on the referencing 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, diff --git a/src/backend/nodes/copyfuncindex 82255b0..37a6352 100644 *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** *** 2849,2854 **** _copyConstraint(const Constraint *from) --- 2849,2856 ---- 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/outfuindex 011d2a3..03adf6f 100644 *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** *** 3451,3456 **** _outConstraint(StringInfo str, const Constraint *node) --- 3451,3458 ---- 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/graindex d99f2be..b38bd7f 100644 *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 184,190 **** 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 *no_inherit, core_yyscan_t yyscanner); static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); --- 184,191 ---- List **constraintList, CollateClause **collClause, core_yyscan_t yyscanner); static void processCASbits(int cas_bits, int location, const char *constrType, ! 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); *************** *** 535,541 **** static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type TableLikeOptionList TableLikeOption %type ColQualList %type ColConstraint ColConstraintElem ConstraintAttr ! %type key_actions key_delete key_match key_update key_action %type ConstraintAttributeSpec ConstraintAttributeElem %type ExistingIndex --- 536,543 ---- %type TableLikeOptionList TableLikeOption %type ColQualList %type ColConstraint ColConstraintElem ConstraintAttr ! %type key_actions opt_key_actions ! %type key_delete key_match key_update key_action %type ConstraintAttributeSpec ConstraintAttributeElem %type ExistingIndex *************** *** 2268,2274 **** alter_table_cmd: $$ = (Node *)n; } /* ALTER TABLE ALTER CONSTRAINT ... */ ! | ALTER CONSTRAINT name ConstraintAttributeSpec { AlterTableCmd *n = makeNode(AlterTableCmd); Constraint *c = makeNode(Constraint); --- 2270,2276 ---- $$ = (Node *)n; } /* ALTER TABLE ALTER CONSTRAINT ... */ ! | ALTER CONSTRAINT name opt_key_actions ConstraintAttributeSpec { AlterTableCmd *n = makeNode(AlterTableCmd); Constraint *c = makeNode(Constraint); *************** *** 2276,2284 **** 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, NULL, NULL, yyscanner); $$ = (Node *)n; } --- 2278,2288 ---- n->def = (Node *) c; c->contype = CONSTR_FOREIGN; /* others not supported, yet */ c->conname = $3; ! 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; } *************** *** 3670,3676 **** ConstraintElem: n->raw_expr = $3; n->cooked_expr = NULL; processCASbits($5, @5, "CHECK", ! NULL, NULL, &n->skip_validation, &n->is_no_inherit, yyscanner); n->initially_valid = !n->skip_validation; $$ = (Node *)n; --- 3674,3680 ---- n->raw_expr = $3; n->cooked_expr = NULL; processCASbits($5, @5, "CHECK", ! NULL, NULL, NULL, NULL, &n->skip_validation, &n->is_no_inherit, yyscanner); n->initially_valid = !n->skip_validation; $$ = (Node *)n; *************** *** 3686,3693 **** ConstraintElem: n->indexname = NULL; n->indexspace = $6; processCASbits($7, @7, "UNIQUE", ! &n->deferrable, &n->initdeferred, NULL, ! NULL, yyscanner); $$ = (Node *)n; } | UNIQUE ExistingIndex ConstraintAttributeSpec --- 3690,3697 ---- n->indexname = NULL; n->indexspace = $6; processCASbits($7, @7, "UNIQUE", ! &n->deferrable, NULL, &n->initdeferred, NULL, ! NULL, NULL, yyscanner); $$ = (Node *)n; } | UNIQUE ExistingIndex ConstraintAttributeSpec *************** *** 3700,3707 **** ConstraintElem: n->indexname = $2; n->indexspace = NULL; processCASbits($3, @3, "UNIQUE", ! &n->deferrable, &n->initdeferred, NULL, ! NULL, yyscanner); $$ = (Node *)n; } | PRIMARY KEY '(' columnList ')' opt_definition OptConsTableSpace --- 3704,3711 ---- n->indexname = $2; n->indexspace = NULL; processCASbits($3, @3, "UNIQUE", ! &n->deferrable, NULL, &n->initdeferred, NULL, ! NULL, NULL, yyscanner); $$ = (Node *)n; } | PRIMARY KEY '(' columnList ')' opt_definition OptConsTableSpace *************** *** 3715,3722 **** ConstraintElem: n->indexname = NULL; n->indexspace = $7; processCASbits($8, @8, "PRIMARY KEY", ! &n->deferrable, &n->initdeferred, NULL, ! NULL, yyscanner); $$ = (Node *)n; } | PRIMARY KEY ExistingIndex ConstraintAttributeSpec --- 3719,3726 ---- n->indexname = NULL; n->indexspace = $7; processCASbits($8, @8, "PRIMARY KEY", ! &n->deferrable, NULL, &n->initdeferred, NULL, ! NULL, NULL, yyscanner); $$ = (Node *)n; } | PRIMARY KEY ExistingIndex ConstraintAttributeSpec *************** *** 3729,3736 **** ConstraintElem: n->indexname = $3; n->indexspace = NULL; processCASbits($4, @4, "PRIMARY KEY", ! &n->deferrable, &n->initdeferred, NULL, ! NULL, yyscanner); $$ = (Node *)n; } | EXCLUDE access_method_clause '(' ExclusionConstraintList ')' --- 3733,3740 ---- n->indexname = $3; n->indexspace = NULL; processCASbits($4, @4, "PRIMARY KEY", ! &n->deferrable, NULL, &n->initdeferred, NULL, ! NULL, NULL, yyscanner); $$ = (Node *)n; } | EXCLUDE access_method_clause '(' ExclusionConstraintList ')' *************** *** 3747,3754 **** ConstraintElem: n->indexspace = $7; n->where_clause = $8; processCASbits($9, @9, "EXCLUDE", ! &n->deferrable, &n->initdeferred, NULL, ! NULL, yyscanner); $$ = (Node *)n; } | FOREIGN KEY '(' columnList ')' REFERENCES qualified_name --- 3751,3758 ---- n->indexspace = $7; n->where_clause = $8; processCASbits($9, @9, "EXCLUDE", ! &n->deferrable, NULL, &n->initdeferred, NULL, ! NULL, NULL, yyscanner); $$ = (Node *)n; } | FOREIGN KEY '(' columnList ')' REFERENCES qualified_name *************** *** 3764,3770 **** 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->skip_validation, NULL, yyscanner); n->initially_valid = !n->skip_validation; --- 3768,3775 ---- n->fk_upd_action = (char) ($10 >> 8); n->fk_del_action = (char) ($10 & 0xFF); processCASbits($11, @11, "FOREIGN KEY", ! &n->deferrable, NULL, ! &n->initdeferred, NULL, &n->skip_validation, NULL, yyscanner); n->initially_valid = !n->skip_validation; *************** *** 3840,3846 **** 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. */ key_actions: key_update --- 3845,3851 ---- * 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. See also opt_key_actions. */ key_actions: key_update *************** *** 3855,3860 **** key_actions: --- 3860,3882 ---- { $$ = (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; } ; *************** *** 5365,5372 **** CreateTrigStmt: n->transitionRels = NIL; n->isconstraint = true; processCASbits($10, @10, "TRIGGER", ! &n->deferrable, &n->initdeferred, NULL, ! NULL, yyscanner); n->constrrel = $9; $$ = (Node *)n; } --- 5387,5394 ---- n->transitionRels = NIL; n->isconstraint = true; processCASbits($10, @10, "TRIGGER", ! &n->deferrable, NULL, &n->initdeferred, NULL, ! NULL, NULL, yyscanner); n->constrrel = $9; $$ = (Node *)n; } *************** *** 5633,5640 **** CreateAssertStmt: n->args = list_make1($6); n->isconstraint = true; processCASbits($8, @8, "ASSERTION", ! &n->deferrable, &n->initdeferred, NULL, ! NULL, yyscanner); ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), --- 5655,5662 ---- n->args = list_make1($6); n->isconstraint = true; processCASbits($8, @8, "ASSERTION", ! &n->deferrable, NULL, &n->initdeferred, NULL, ! NULL, NULL, yyscanner); ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), *************** *** 16139,16145 **** SplitColQualList(List *qualList, */ static void processCASbits(int cas_bits, int location, const char *constrType, ! bool *deferrable, bool *initdeferred, bool *not_valid, bool *no_inherit, core_yyscan_t yyscanner) { /* defaults */ --- 16161,16168 ---- */ static void processCASbits(int cas_bits, int location, const char *constrType, ! bool *deferrable, bool *was_deferrable_set, ! bool *initdeferred, bool *was_initdeferred_set, bool *not_valid, bool *no_inherit, core_yyscan_t yyscanner) { /* defaults */ *************** *** 16150,16155 **** processCASbits(int cas_bits, int location, const char *constrType, --- 16173,16185 ---- 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/pindex c7a43b8..ea63bbf 100644 *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** *** 2077,2082 **** typedef enum ConstrType /* types of constraints */ --- 2077,2083 ---- #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' *************** *** 2094,2099 **** typedef struct Constraint --- 2095,2104 ---- 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/expecteindex fef072e..e8365fd 100644 *** a/src/test/regress/expected/foreign_key.out --- b/src/test/regress/expected/foreign_key.out *************** *** 1415,1417 **** alter table fktable2 drop constraint fktable2_f1_fkey; --- 1415,5012 ---- ERROR: cannot ALTER TABLE "pktable2" because it has pending trigger events commit; drop table pktable2, fktable2; + CREATE SCHEMA createtest; + CREATE SCHEMA altertest; + -- ALTER CONSTRAINT changing ON UPDATE/DELETE. + -- Try all combinations and validate the diff with a created constraint + WITH act(action) AS ( + SELECT unnest('{NO ACTION,RESTRICT,CASCADE,SET DEFAULT,SET NULL}'::text[]) + ) + SELECT + unnest(string_to_array(format($$ + -- 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); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON %1$s %3$s; + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + DELETE FROM altertest.foo WHERE id = 2; + + + SELECT * FROM altertest.bar; + + + -- 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) + ; + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + $$, m.method, a1.action, a2.action), '')) + FROM unnest('{UPDATE,DELETE}'::text[]) AS m(method), act a1, act a2 \gexec + + -- Alter from ON UPDATE NO ACTION to ON UPDATE NO ACTION + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE NO ACTION, 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 UPDATE NO ACTION, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE NO ACTION; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE RESTRICT to ON UPDATE NO ACTION + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE NO ACTION, 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 UPDATE RESTRICT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE NO ACTION; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE CASCADE to ON UPDATE NO ACTION + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE NO ACTION, 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 UPDATE CASCADE, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE NO ACTION; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE SET DEFAULT to ON UPDATE NO ACTION + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE NO ACTION, 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 UPDATE SET DEFAULT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE NO ACTION; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE SET NULL to ON UPDATE NO ACTION + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE NO ACTION, 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 UPDATE SET NULL, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE NO ACTION; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE NO ACTION to ON DELETE NO ACTION + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE NO ACTION, 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 DELETE NO ACTION, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE NO ACTION; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE RESTRICT to ON DELETE NO ACTION + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE NO ACTION, 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 DELETE RESTRICT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE NO ACTION; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE CASCADE to ON DELETE NO ACTION + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE NO ACTION, 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 DELETE CASCADE, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE NO ACTION; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE SET DEFAULT to ON DELETE NO ACTION + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE NO ACTION, 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 DELETE SET DEFAULT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE NO ACTION; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE SET NULL to ON DELETE NO ACTION + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE NO ACTION, 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 DELETE SET NULL, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE NO ACTION; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE NO ACTION to ON UPDATE RESTRICT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE RESTRICT, 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 UPDATE NO ACTION, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE RESTRICT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE RESTRICT to ON UPDATE RESTRICT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE RESTRICT, 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 UPDATE RESTRICT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE RESTRICT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE CASCADE to ON UPDATE RESTRICT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE RESTRICT, 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 UPDATE CASCADE, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE RESTRICT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE SET DEFAULT to ON UPDATE RESTRICT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE RESTRICT, 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 UPDATE SET DEFAULT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE RESTRICT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE SET NULL to ON UPDATE RESTRICT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE RESTRICT, 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 UPDATE SET NULL, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE RESTRICT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE NO ACTION to ON DELETE RESTRICT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE RESTRICT, 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 DELETE NO ACTION, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE RESTRICT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE RESTRICT to ON DELETE RESTRICT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE RESTRICT, 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 DELETE RESTRICT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE RESTRICT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE CASCADE to ON DELETE RESTRICT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE RESTRICT, 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 DELETE CASCADE, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE RESTRICT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE SET DEFAULT to ON DELETE RESTRICT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE RESTRICT, 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 DELETE SET DEFAULT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE RESTRICT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE SET NULL to ON DELETE RESTRICT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE RESTRICT, 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 DELETE SET NULL, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE RESTRICT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 2 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE NO ACTION to ON UPDATE CASCADE + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE CASCADE, 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 UPDATE NO ACTION, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE CASCADE; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 2 | B + 10 | A + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE RESTRICT to ON UPDATE CASCADE + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE CASCADE, 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 UPDATE RESTRICT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE CASCADE; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 2 | B + 10 | A + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE CASCADE to ON UPDATE CASCADE + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE CASCADE, 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 UPDATE CASCADE, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE CASCADE; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 2 | B + 10 | A + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE SET DEFAULT to ON UPDATE CASCADE + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE CASCADE, 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 UPDATE SET DEFAULT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE CASCADE; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 2 | B + 10 | A + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE SET NULL to ON UPDATE CASCADE + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE CASCADE, 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 UPDATE SET NULL, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE CASCADE; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 2 | B + 10 | A + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE NO ACTION to ON DELETE CASCADE + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE CASCADE, 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 DELETE NO ACTION, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE CASCADE; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + (1 row) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE RESTRICT to ON DELETE CASCADE + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE CASCADE, 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 DELETE RESTRICT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE CASCADE; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + (1 row) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE CASCADE to ON DELETE CASCADE + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE CASCADE, 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 DELETE CASCADE, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE CASCADE; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + (1 row) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE SET DEFAULT to ON DELETE CASCADE + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE CASCADE, 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 DELETE SET DEFAULT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE CASCADE; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + (1 row) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE SET NULL to ON DELETE CASCADE + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE CASCADE, 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 DELETE SET NULL, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE CASCADE; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + (1 row) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE NO ACTION to ON UPDATE SET DEFAULT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE SET DEFAULT, 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 UPDATE NO ACTION, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE SET DEFAULT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 2 | B + 0 | A + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE RESTRICT to ON UPDATE SET DEFAULT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE SET DEFAULT, 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 UPDATE RESTRICT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE SET DEFAULT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 2 | B + 0 | A + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE CASCADE to ON UPDATE SET DEFAULT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE SET DEFAULT, 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 UPDATE CASCADE, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE SET DEFAULT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 2 | B + 0 | A + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE SET DEFAULT to ON UPDATE SET DEFAULT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE SET DEFAULT, 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 UPDATE SET DEFAULT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE SET DEFAULT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 2 | B + 0 | A + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE SET NULL to ON UPDATE SET DEFAULT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE SET DEFAULT, 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 UPDATE SET NULL, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE SET DEFAULT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 2 | B + 0 | A + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE NO ACTION to ON DELETE SET DEFAULT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE SET DEFAULT, 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 DELETE NO ACTION, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE SET DEFAULT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 0 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE RESTRICT to ON DELETE SET DEFAULT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE SET DEFAULT, 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 DELETE RESTRICT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE SET DEFAULT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 0 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE CASCADE to ON DELETE SET DEFAULT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE SET DEFAULT, 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 DELETE CASCADE, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE SET DEFAULT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 0 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE SET DEFAULT to ON DELETE SET DEFAULT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE SET DEFAULT, 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 DELETE SET DEFAULT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE SET DEFAULT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 0 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE SET NULL to ON DELETE SET DEFAULT + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE SET DEFAULT, 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 DELETE SET NULL, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE SET DEFAULT; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + 0 | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE NO ACTION to ON UPDATE SET NULL + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE SET NULL, 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 UPDATE NO ACTION, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE SET NULL; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 2 | B + | A + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE RESTRICT to ON UPDATE SET NULL + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE SET NULL, 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 UPDATE RESTRICT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE SET NULL; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 2 | B + | A + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE CASCADE to ON UPDATE SET NULL + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE SET NULL, 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 UPDATE CASCADE, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE SET NULL; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 2 | B + | A + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE SET DEFAULT to ON UPDATE SET NULL + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE SET NULL, 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 UPDATE SET DEFAULT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE SET NULL; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 2 | B + | A + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON UPDATE SET NULL to ON UPDATE SET NULL + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON UPDATE SET NULL, 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 UPDATE SET NULL, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON UPDATE SET NULL; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + + DELETE FROM altertest.foo WHERE id = 2; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(2) is still referenced from table "bar". + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 2 | B + | A + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE NO ACTION to ON DELETE SET NULL + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE SET NULL, 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 DELETE NO ACTION, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE SET NULL; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE RESTRICT to ON DELETE SET NULL + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE SET NULL, 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 DELETE RESTRICT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE SET NULL; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE CASCADE to ON DELETE SET NULL + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE SET NULL, 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 DELETE CASCADE, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE SET NULL; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE SET DEFAULT to ON DELETE SET NULL + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE SET NULL, 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 DELETE SET DEFAULT, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE SET NULL; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + + + -- Alter from ON DELETE SET NULL to ON DELETE SET NULL + CREATE TABLE createtest.foo(id integer primary key); + CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON DELETE SET NULL, 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 DELETE SET NULL, val text); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON DELETE SET NULL; + + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar" + DETAIL: Key (id)=(1) is still referenced from table "bar". + + DELETE FROM altertest.foo WHERE id = 2; + + + + SELECT * FROM altertest.bar; + + + foo_id | val + --------+----- + 1 | A + | B + (2 rows) + + + -- 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) + ; + + + relname | tgname | tgfoid | conname | confupdtype | confdeltype | tgdeferrable | condef + ---------+--------+--------+---------+-------------+-------------+--------------+-------- + (0 rows) + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + diff --git a/src/test/regress/sql/foreign_key.sqindex 5f19dad..bfadf77 100644 *** a/src/test/regress/sql/foreign_key.sql --- b/src/test/regress/sql/foreign_key.sql *************** *** 1055,1057 **** alter table fktable2 drop constraint fktable2_f1_fkey; --- 1055,1120 ---- commit; drop table pktable2, fktable2; + + CREATE SCHEMA createtest; + CREATE SCHEMA altertest; + + -- ALTER CONSTRAINT changing ON UPDATE/DELETE. + -- Try all combinations and validate the diff with a created constraint + WITH act(action) AS ( + SELECT unnest('{NO ACTION,RESTRICT,CASCADE,SET DEFAULT,SET NULL}'::text[]) + ) + SELECT + unnest(string_to_array(format($$ + -- 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); + INSERT INTO altertest.bar VALUES(1, 'A'),(2, 'B'); + + + ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON %1$s %3$s; + + + UPDATE altertest.foo SET id = 10 WHERE id = 1; + + + DELETE FROM altertest.foo WHERE id = 2; + + + SELECT * FROM altertest.bar; + + + -- 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) + ; + + + DROP TABLE createtest.bar; + DROP TABLE createtest.foo; + DROP TABLE altertest.bar; + DROP TABLE altertest.foo; + $$, m.method, a1.action, a2.action), '')) + + FROM unnest('{UPDATE,DELETE}'::text[]) AS m(method), act a1, act a2 \gexec