WIP: ALTER TABLE ... ALTER CONSTRAINT ... SET DEFERRABLE on UNIQUE or PK

From c0a041f0ca5d884842820538b56d82472a701c3c Mon Sep 17 00:00:00 2001
From: Craig Ringer <craig@2ndquadrant.com>
Date: Fri, 26 Jun 2015 11:59:30 +0800
Subject: [PATCH] Allow ALTER TABLE...ALTER CONSTRAINT on PK and UNIQUE

We presently support DEFERRABLE constraints on PRIMARY KEY and UNIQUE
constraints, but do not permit ALTER TABLE to modify their deferrable
state.

Fix that and add some regression test coverage.
---
 src/backend/commands/tablecmds.c     | 72 +++++++++++++++++++-----------------
 src/test/regress/sql/alter_table.sql | 37 ++++++++++++++++++
 2 files changed, 76 insertions(+), 33 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index d394713..5875987 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -6647,22 +6647,20 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
 				 errmsg("constraint \"%s\" of relation \"%s\" does not exist",
 						cmdcon->conname, RelationGetRelationName(rel))));
 
-	if (currcon->contype != CONSTRAINT_FOREIGN)
+	if (currcon->contype != CONSTRAINT_FOREIGN &&
+		currcon->contype != CONSTRAINT_PRIMARY &&
+		currcon->contype != CONSTRAINT_UNIQUE)
 		ereport(ERROR,
 				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-				 errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
+				 errmsg("constraint \"%s\" of relation \"%s\" must be FOREIGN KEY, PRIMARY KEY or UNIQUE",
 						cmdcon->conname, RelationGetRelationName(rel))));
 
 	if (currcon->condeferrable != cmdcon->deferrable ||
 		currcon->condeferred != cmdcon->initdeferred)
 	{
 		HeapTuple	copyTuple;
-		HeapTuple	tgtuple;
 		Form_pg_constraint copy_con;
 		List	   *otherrelids = NIL;
-		ScanKeyData tgkey;
-		SysScanDesc tgscan;
-		Relation	tgrel;
 		ListCell   *lc;
 
 		/*
@@ -6682,44 +6680,52 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
 
 		/*
 		 * Now we need to update the multiple entries in pg_trigger that
-		 * implement the constraint.
+		 * implement the constraint if it's a foreign key constraint.
 		 */
-		tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
+		if (currcon->contype == CONSTRAINT_FOREIGN)
+		{
+			HeapTuple	tgtuple;
+			ScanKeyData tgkey;
+			SysScanDesc tgscan;
+			Relation	tgrel;
 
-		ScanKeyInit(&tgkey,
-					Anum_pg_trigger_tgconstraint,
-					BTEqualStrategyNumber, F_OIDEQ,
-					ObjectIdGetDatum(HeapTupleGetOid(contuple)));
+			tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
 
-		tgscan = systable_beginscan(tgrel, TriggerConstraintIndexId, true,
-									NULL, 1, &tgkey);
+			ScanKeyInit(&tgkey,
+						Anum_pg_trigger_tgconstraint,
+						BTEqualStrategyNumber, F_OIDEQ,
+						ObjectIdGetDatum(HeapTupleGetOid(contuple)));
 
-		while (HeapTupleIsValid(tgtuple = systable_getnext(tgscan)))
-		{
-			Form_pg_trigger copy_tg;
+			tgscan = systable_beginscan(tgrel, TriggerConstraintIndexId, true,
+										NULL, 1, &tgkey);
 
-			copyTuple = heap_copytuple(tgtuple);
-			copy_tg = (Form_pg_trigger) GETSTRUCT(copyTuple);
+			while (HeapTupleIsValid(tgtuple = systable_getnext(tgscan)))
+			{
+				Form_pg_trigger copy_tg;
 
-			/* 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);
+				copyTuple = heap_copytuple(tgtuple);
+				copy_tg = (Form_pg_trigger) GETSTRUCT(copyTuple);
 
-			copy_tg->tgdeferrable = cmdcon->deferrable;
-			copy_tg->tginitdeferred = cmdcon->initdeferred;
-			simple_heap_update(tgrel, &copyTuple->t_self, copyTuple);
-			CatalogUpdateIndexes(tgrel, 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);
 
-			InvokeObjectPostAlterHook(TriggerRelationId,
-									  HeapTupleGetOid(tgtuple), 0);
+				copy_tg->tgdeferrable = cmdcon->deferrable;
+				copy_tg->tginitdeferred = cmdcon->initdeferred;
+				simple_heap_update(tgrel, &copyTuple->t_self, copyTuple);
+				CatalogUpdateIndexes(tgrel, copyTuple);
 
-			heap_freetuple(copyTuple);
-		}
+				InvokeObjectPostAlterHook(TriggerRelationId,
+										  HeapTupleGetOid(tgtuple), 0);
+
+				heap_freetuple(copyTuple);
+			}
 
-		systable_endscan(tgscan);
+			systable_endscan(tgscan);
 
-		heap_close(tgrel, RowExclusiveLock);
+			heap_close(tgrel, RowExclusiveLock);
+		}
 
 		/*
 		 * Invalidate relcache so that others see the new attributes.  We must
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 29c1875..ec31597 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -1688,3 +1688,40 @@ ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing
 DROP TABLE logged3;
 DROP TABLE logged2;
 DROP TABLE logged1;
+-- Make sure that ALTERing a table to make a PK or UNIQUE constraint deferrable works
+CREATE TABLE pkuk(
+   pk INTEGER PRIMARY KEY,
+   uk INTEGER,
+   CONSTRAINT pkuk_unique_uk UNIQUE(uk)
+);
+INSERT INTO pkuk(pk, uk) VALUES (1,1), (2,2);
+ALTER TABLE pkuk ALTER CONSTRAINT pkuk_pkey DEFERRABLE;
+ALTER TABLE pkuk ALTER CONSTRAINT pkuk_unique_uk DEFERRABLE;
+\d pkuk
+BEGIN;
+INSERT INTO pkuk(pk,uk) VALUES (2,2);
+COMMIT;
+BEGIN;
+SET CONSTRAINTS ALL DEFERRED;
+INSERT INTO pkuk(pk,uk) VALUES (2,2);
+COMMIT;
+ALTER TABLE pkuk ALTER CONSTRAINT pkuk_pkey DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE pkuk ALTER CONSTRAINT pkuk_unique_uk DEFERRABLE INITIALLY DEFERRED;
+\d pkuk
+BEGIN;
+INSERT INTO pkuk(pk,uk) VALUES (2,2);
+COMMIT;
+BEGIN;
+SET CONSTRAINTS ALL IMMEDIATE;
+INSERT INTO pkuk(pk,uk) VALUES (2,2);
+COMMIT;
+ALTER TABLE pkuk ALTER CONSTRAINT pkuk_pkey DEFERRABLE INITIALLY IMMEDIATE;
+ALTER TABLE pkuk ALTER CONSTRAINT pkuk_unique_uk DEFERRABLE INITIALLY IMMEDIATE;
+\d pkuk
+BEGIN;
+INSERT INTO pkuk(pk,uk) VALUES (2,2);
+COMMIT;
+BEGIN;
+SET CONSTRAINTS ALL DEFERRED;
+INSERT INTO pkuk(pk,uk) VALUES (2,2);
+COMMIT;
-- 
2.1.0