2 files changed, 39 insertions, 0 deletions
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 04668a8886c..0299bfe8730 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1132,6 +1132,15 @@ CREATE TEMP TABLE fktable (
     id int primary key,
     fk int references pktable deferrable initially deferred
 );
+-- check ALTER CONSTRAINT
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
+-- illegal option
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY DEFERRED;
+ERROR:  constraint declared INITIALLY DEFERRED must be DEFERRABLE
+LINE 1: ...e ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY ...
+                                                             ^
+-- reset
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
 INSERT INTO pktable VALUES (5, 10);
 BEGIN;
 -- doesn't match PK, but no error yet
@@ -1142,6 +1151,16 @@ UPDATE fktable SET id = id + 1;
 COMMIT;
 ERROR:  insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
 DETAIL:  Key (fk)=(20) is not present in table "pktable".
+-- change the constraint definition and retest
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY IMMEDIATE;
+BEGIN;
+-- doesn't match PK, should throw error now
+INSERT INTO fktable VALUES (0, 20);
+ERROR:  insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
+DETAIL:  Key (fk)=(20) is not present in table "pktable".
+COMMIT;
+-- reset
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
 -- check same case when insert is in a different subtransaction than update
 BEGIN;
 -- doesn't match PK, but no error yet
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 377b36c226b..531c881f631 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -818,6 +818,13 @@ CREATE TEMP TABLE fktable (
     fk int references pktable deferrable initially deferred
 );
 
+-- check ALTER CONSTRAINT
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
+-- illegal option
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY DEFERRED;
+-- reset
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
+
 INSERT INTO pktable VALUES (5, 10);
 
 BEGIN;
@@ -831,6 +838,19 @@ UPDATE fktable SET id = id + 1;
 -- should catch error from initial INSERT
 COMMIT;
 
+-- change the constraint definition and retest
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY IMMEDIATE;
+
+BEGIN;
+
+-- doesn't match PK, should throw error now
+INSERT INTO fktable VALUES (0, 20);
+
+COMMIT;
+
+-- reset
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
+
 -- check same case when insert is in a different subtransaction than update
 
 BEGIN;