diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/commands/tablecmds.c | 31 | ||||
| -rw-r--r-- | src/test/regress/expected/alter_table.out | 60 | ||||
| -rw-r--r-- | src/test/regress/sql/alter_table.sql | 33 |
3 files changed, 119 insertions, 5 deletions
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index f822ed9597e..2137372c234 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -6740,16 +6740,34 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd, while (HeapTupleIsValid(tgtuple = systable_getnext(tgscan))) { + Form_pg_trigger tgform = (Form_pg_trigger) GETSTRUCT(tgtuple); Form_pg_trigger copy_tg; + /* + * Remember OIDs of other relation(s) involved in FK constraint. + * (Note: it's likely that we could skip forcing a relcache inval + * for other rels that don't have a trigger whose properties + * change, but let's be conservative.) + */ + if (tgform->tgrelid != RelationGetRelid(rel)) + 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); - /* Remember OIDs of other relation(s) involved in FK constraint */ - if (copy_tg->tgrelid != RelationGetRelid(rel)) - otherrelids = list_append_unique_oid(otherrelids, - copy_tg->tgrelid); - copy_tg->tgdeferrable = cmdcon->deferrable; copy_tg->tginitdeferred = cmdcon->initdeferred; simple_heap_update(tgrel, ©Tuple->t_self, copyTuple); @@ -7512,6 +7530,9 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint, /* * Create the triggers that implement an FK constraint. + * + * NB: if you change any trigger properties here, see also + * ATExecAlterConstraint. */ static void createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint, diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 3232cda0236..a1ab823acfe 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -534,6 +534,66 @@ ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1) references pktable(ptest1, ptest2); ERROR: foreign key constraint "fktable_ftest2_fkey" cannot be implemented DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer. +DROP TABLE FKTABLE; +DROP TABLE PKTABLE; +-- Test that ALTER CONSTRAINT updates trigger deferrability properly +CREATE TEMP TABLE PKTABLE (ptest1 int primary key); +CREATE TEMP TABLE FKTABLE (ftest1 int); +ALTER TABLE FKTABLE ADD CONSTRAINT fknd FOREIGN KEY(ftest1) REFERENCES pktable + ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE; +ALTER TABLE FKTABLE ADD CONSTRAINT fkdd FOREIGN KEY(ftest1) REFERENCES pktable + ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE FKTABLE ADD CONSTRAINT fkdi FOREIGN KEY(ftest1) REFERENCES pktable + ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE; +ALTER TABLE FKTABLE ADD CONSTRAINT fknd2 FOREIGN KEY(ftest1) REFERENCES pktable + ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE FKTABLE ALTER CONSTRAINT fknd2 NOT DEFERRABLE; +ALTER TABLE FKTABLE ADD CONSTRAINT fkdd2 FOREIGN KEY(ftest1) REFERENCES pktable + ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE; +ALTER TABLE FKTABLE ALTER CONSTRAINT fkdd2 DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE FKTABLE ADD CONSTRAINT fkdi2 FOREIGN KEY(ftest1) REFERENCES pktable + ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE; +ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY IMMEDIATE; +SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred +FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint +WHERE tgrelid = 'pktable'::regclass +ORDER BY 1,2,3; + conname | tgfoid | tgtype | tgdeferrable | tginitdeferred +---------+------------------------+--------+--------------+---------------- + fkdd | "RI_FKey_cascade_del" | 9 | f | f + fkdd | "RI_FKey_noaction_upd" | 17 | t | t + fkdd2 | "RI_FKey_cascade_del" | 9 | f | f + fkdd2 | "RI_FKey_noaction_upd" | 17 | t | t + fkdi | "RI_FKey_cascade_del" | 9 | f | f + fkdi | "RI_FKey_noaction_upd" | 17 | t | f + fkdi2 | "RI_FKey_cascade_del" | 9 | f | f + fkdi2 | "RI_FKey_noaction_upd" | 17 | t | f + fknd | "RI_FKey_cascade_del" | 9 | f | f + fknd | "RI_FKey_noaction_upd" | 17 | f | f + fknd2 | "RI_FKey_cascade_del" | 9 | f | f + fknd2 | "RI_FKey_noaction_upd" | 17 | f | f +(12 rows) + +SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred +FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint +WHERE tgrelid = 'fktable'::regclass +ORDER BY 1,2,3; + conname | tgfoid | tgtype | tgdeferrable | tginitdeferred +---------+---------------------+--------+--------------+---------------- + fkdd | "RI_FKey_check_ins" | 5 | t | t + fkdd | "RI_FKey_check_upd" | 17 | t | t + fkdd2 | "RI_FKey_check_ins" | 5 | t | t + fkdd2 | "RI_FKey_check_upd" | 17 | t | t + fkdi | "RI_FKey_check_ins" | 5 | t | f + fkdi | "RI_FKey_check_upd" | 17 | t | f + fkdi2 | "RI_FKey_check_ins" | 5 | t | f + fkdi2 | "RI_FKey_check_upd" | 17 | t | f + fknd | "RI_FKey_check_ins" | 5 | f | f + fknd | "RI_FKey_check_upd" | 17 | f | f + fknd2 | "RI_FKey_check_ins" | 5 | f | f + fknd2 | "RI_FKey_check_upd" | 17 | f | f +(12 rows) + -- temp tables should go away by themselves, need not drop them. -- test check constraint adding create table atacc1 ( test int ); diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 72e65d4ee05..c8eed3ec646 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -407,6 +407,39 @@ ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) -- As does this... ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1) references pktable(ptest1, ptest2); +DROP TABLE FKTABLE; +DROP TABLE PKTABLE; + +-- Test that ALTER CONSTRAINT updates trigger deferrability properly + +CREATE TEMP TABLE PKTABLE (ptest1 int primary key); +CREATE TEMP TABLE FKTABLE (ftest1 int); + +ALTER TABLE FKTABLE ADD CONSTRAINT fknd FOREIGN KEY(ftest1) REFERENCES pktable + ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE; +ALTER TABLE FKTABLE ADD CONSTRAINT fkdd FOREIGN KEY(ftest1) REFERENCES pktable + ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE FKTABLE ADD CONSTRAINT fkdi FOREIGN KEY(ftest1) REFERENCES pktable + ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE; + +ALTER TABLE FKTABLE ADD CONSTRAINT fknd2 FOREIGN KEY(ftest1) REFERENCES pktable + ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE FKTABLE ALTER CONSTRAINT fknd2 NOT DEFERRABLE; +ALTER TABLE FKTABLE ADD CONSTRAINT fkdd2 FOREIGN KEY(ftest1) REFERENCES pktable + ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE; +ALTER TABLE FKTABLE ALTER CONSTRAINT fkdd2 DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE FKTABLE ADD CONSTRAINT fkdi2 FOREIGN KEY(ftest1) REFERENCES pktable + ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE; +ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY IMMEDIATE; + +SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred +FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint +WHERE tgrelid = 'pktable'::regclass +ORDER BY 1,2,3; +SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred +FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint +WHERE tgrelid = 'fktable'::regclass +ORDER BY 1,2,3; -- temp tables should go away by themselves, need not drop them. |