2 files changed, 51 insertions, 39 deletions
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 0299bfe8730..8c47babb6dc 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1132,15 +1132,6 @@ 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
@@ -1151,16 +1142,6 @@ 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
@@ -1198,6 +1179,30 @@ ROLLBACK TO savept1;
 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".
+--
+-- check ALTER CONSTRAINT
+--
+INSERT INTO fktable VALUES (1, 5);
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY IMMEDIATE;
+BEGIN;
+-- doesn't match FK, should throw error now
+UPDATE pktable SET id = 10 WHERE id = 5;
+ERROR:  update or delete on table "pktable" violates foreign key constraint "fktable_fk_fkey" on table "fktable"
+DETAIL:  Key (id)=(5) is still referenced from table "fktable".
+COMMIT;
+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;
+-- try additional syntax
+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 ...
+                                                             ^
 -- test order of firing of FK triggers when several RI-induced changes need to
 -- be made to the same row.  This was broken by subtransaction-related
 -- changes in 8.0.
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 531c881f631..53276e4d673 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -818,13 +818,6 @@ 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;
@@ -838,19 +831,6 @@ 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;
@@ -900,6 +880,33 @@ ROLLBACK TO savept1;
 -- should catch error from initial INSERT
 COMMIT;
 
+--
+-- check ALTER CONSTRAINT
+--
+
+INSERT INTO fktable VALUES (1, 5);
+
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY IMMEDIATE;
+
+BEGIN;
+
+-- doesn't match FK, should throw error now
+UPDATE pktable SET id = 10 WHERE id = 5;
+
+COMMIT;
+
+BEGIN;
+
+-- doesn't match PK, should throw error now
+INSERT INTO fktable VALUES (0, 20);
+
+COMMIT;
+
+-- try additional syntax
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
+-- illegal option
+ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY DEFERRED;
+
 -- test order of firing of FK triggers when several RI-induced changes need to
 -- be made to the same row.  This was broken by subtransaction-related
 -- changes in 8.0.