Date: Tue, 5 Nov 2019 16:19:48 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: deferrable FK constraints on partitioned rels
Message-ID: <20191105191948.GA11182@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="EeQfGwPcQSOJBaQU"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk


--EeQfGwPcQSOJBaQU
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

While messing around, I noticed that SET CONSTRAINTS ... DEFERRED
does not work with partitioned tables.  I had some code to cover this
case, but it has a bug that prevents it from working at all: the sanity
check that verifies whether triggers exist fails.

The attached patch fixes this problem: it merely removes the sanity
check.  With that, everything works.

(Another approach I tried was to split out constraints in partitioned
tables vs. constraints in regular ones.  That's indeed workable, but it
requires us to do two additional syscache access per partition for
get_rel_relkind, which seems excessive.)

The UNIQUE DEFERRABLE case works after the patch.  (I didn't try without
the patch.)

-- 
Álvaro Herrera                         Developer, https://www.PostgreSQL.org/

--EeQfGwPcQSOJBaQU
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="0001-Fix-deferred-constraints-on-partitioned-rels.patch"

From e08007504e1c0ec88e583b7cba6f6b08effe26d7 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Tue, 5 Nov 2019 12:52:27 -0300
Subject: [PATCH] Fix deferred constraints on partitioned rels

---
 src/backend/commands/trigger.c | 29 ++++++++++++++++++++++-------
 1 file changed, 22 insertions(+), 7 deletions(-)

diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 57c98912d5..5e302fee64 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -5362,6 +5362,7 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
 		Relation	conrel;
 		Relation	tgrel;
 		List	   *conoidlist = NIL;
+		List	   *pcl = NIL;
 		List	   *tgoidlist = NIL;
 		ListCell   *lc;
 
@@ -5440,7 +5441,18 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
 					Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tup);
 
 					if (con->condeferrable)
-						conoidlist = lappend_oid(conoidlist, con->oid);
+					{
+						/*
+						 * If we found it and it's deferrable, save it to one
+						 * of the lists depending on whether it belongs to a
+						 * partitioned table.
+						 */
+						if (get_rel_relkind(con->conrelid) ==
+							RELKIND_PARTITIONED_TABLE)
+							pcl = lappend_oid(pcl, con->oid);
+						else
+							conoidlist = lappend_oid(conoidlist, con->oid);
+					}
 					else if (stmt->deferred)
 						ereport(ERROR,
 								(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -5472,12 +5484,12 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
 		}
 
 		/*
-		 * Scan for any possible descendants of the constraints.  We append
-		 * whatever we find to the same list that we're scanning; this has the
-		 * effect that we create new scans for those, too, so if there are
-		 * further descendents, we'll also catch them.
+		 * Scan for any possible descendants of constraints in partitioned
+		 * tables.  If we find any that are on further partitioned tables,
+		 * append them to the list we're scanning.  This ensures we process
+		 * all descendant relations.
 		 */
-		foreach(lc, conoidlist)
+		foreach(lc, pcl)
 		{
 			Oid			parent = lfirst_oid(lc);
 			ScanKeyData key;
@@ -5495,7 +5507,10 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
 			{
 				Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple);
 
-				conoidlist = lappend_oid(conoidlist, con->oid);
+				if (get_rel_relkind(con->conrelid) == RELKIND_PARTITIONED_TABLE)
+					pcl = lappend_oid(pcl, con->oid);
+				else
+					conoidlist = lappend_oid(conoidlist, con->oid);
 			}
 
 			systable_endscan(scan);
-- 
2.20.1


--EeQfGwPcQSOJBaQU--