Fix SET CONSTRAINTS .. DEFERRED on partitioned tables

SET CONSTRAINTS ... DEFERRED failed on partitioned tables, because of a
sanity check that ensures that the affected constraints have triggers.
On partitioned tables, the triggers are in the leaf partitions, not in
the partitioned relations themselves, so the sanity check fails.
Removing the sanity check solves the problem, because the code needed to
support the case is already there.

Backpatch to 11.

Note: deferred unique constraints are not affected by this bug, because
they do have triggers in the parent partitioned table.  I did not add a
test for this scenario.

Discussion: https://postgr.es/m/20191105212915.GA11324@alvherre.pgsql

3 files changed, 38 insertions, 13 deletions

diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 97ab357a248..ad26fb77f36 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -5519,13 +5519,10 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
 		foreach(lc, conoidlist)
 		{
 			Oid			conoid = lfirst_oid(lc);
-			bool		found;
 			ScanKeyData skey;
 			SysScanDesc tgscan;
 			HeapTuple	htup;
 
-			found = false;
-
 			ScanKeyInit(&skey,
 						Anum_pg_trigger_tgconstraint,
 						BTEqualStrategyNumber, F_OIDEQ,
@@ -5547,16 +5544,9 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
 				if (pg_trigger->tgdeferrable)
 					tgoidlist = lappend_oid(tgoidlist,
 											HeapTupleGetOid(htup));
-
-				found = true;
 			}
 
 			systable_endscan(tgscan);
-
-			/* Safety check: a deferrable constraint should have triggers */
-			if (!found)
-				elog(ERROR, "no triggers found for constraint with OID %u",
-					 conoid);
 		}
 
 		heap_close(tgrel, AccessShareLock);
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 754a9d5ae1b..ef1b2bc97d4 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1926,6 +1926,23 @@ alter table fkpart2.fk_part detach partition fkpart2.fk_part_1;
 alter table fkpart2.fk_part_1 drop constraint fkey;	-- ok
 alter table fkpart2.fk_part_1_1 drop constraint my_fkey;	-- doesn't exist
 ERROR:  constraint "my_fkey" of relation "fk_part_1_1" does not exist
+-- verify constraint deferrability
+create schema fkpart3
+  create table pkey (a int primary key)
+  create table fk_part (a int, constraint fkey foreign key (a) references fkpart3.pkey deferrable initially immediate) partition by list (a)
+  create table fk_part_1 partition of fkpart3.fk_part for values in (1) partition by list (a)
+  create table fk_part_1_1 partition of fkpart3.fk_part_1 for values in (1)
+  create table fk_part_2 partition of fkpart3.fk_part for values in (2);
+begin;
+set constraints fkpart3.fkey deferred;
+insert into fkpart3.fk_part values (1);
+insert into fkpart3.pkey values (1);
+commit;
+begin;
+set constraints fkpart3.fkey deferred;
+delete from fkpart3.pkey;
+delete from fkpart3.fk_part;
+commit;
 -- ensure we check partitions are "not used" when dropping constraints
 CREATE SCHEMA fkpart8
   CREATE TABLE tbl1(f1 int PRIMARY KEY)
@@ -1938,6 +1955,6 @@ ALTER TABLE fkpart8.tbl2 DROP CONSTRAINT tbl2_f1_fkey;
 ERROR:  cannot ALTER TABLE "tbl2_p1" because it has pending trigger events
 COMMIT;
 \set VERBOSITY terse	\\ -- suppress cascade details
-drop schema fkpart0, fkpart1, fkpart2, fkpart8 cascade;
-NOTICE:  drop cascades to 10 other objects
+drop schema fkpart0, fkpart1, fkpart2, fkpart3, fkpart8 cascade;
+NOTICE:  drop cascades to 12 other objects
 \set VERBOSITY default
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 3f8e9b83d34..2ddda959ace 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1380,6 +1380,24 @@ alter table fkpart2.fk_part detach partition fkpart2.fk_part_1;
 alter table fkpart2.fk_part_1 drop constraint fkey;	-- ok
 alter table fkpart2.fk_part_1_1 drop constraint my_fkey;	-- doesn't exist
 
+-- verify constraint deferrability
+create schema fkpart3
+  create table pkey (a int primary key)
+  create table fk_part (a int, constraint fkey foreign key (a) references fkpart3.pkey deferrable initially immediate) partition by list (a)
+  create table fk_part_1 partition of fkpart3.fk_part for values in (1) partition by list (a)
+  create table fk_part_1_1 partition of fkpart3.fk_part_1 for values in (1)
+  create table fk_part_2 partition of fkpart3.fk_part for values in (2);
+begin;
+set constraints fkpart3.fkey deferred;
+insert into fkpart3.fk_part values (1);
+insert into fkpart3.pkey values (1);
+commit;
+begin;
+set constraints fkpart3.fkey deferred;
+delete from fkpart3.pkey;
+delete from fkpart3.fk_part;
+commit;
+
 -- ensure we check partitions are "not used" when dropping constraints
 CREATE SCHEMA fkpart8
   CREATE TABLE tbl1(f1 int PRIMARY KEY)
@@ -1392,5 +1410,5 @@ ALTER TABLE fkpart8.tbl2 DROP CONSTRAINT tbl2_f1_fkey;
 COMMIT;
 
 \set VERBOSITY terse	\\ -- suppress cascade details
-drop schema fkpart0, fkpart1, fkpart2, fkpart8 cascade;
+drop schema fkpart0, fkpart1, fkpart2, fkpart3, fkpart8 cascade;
 \set VERBOSITY default