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/
>From e08007504e1c0ec88e583b7cba6f6b08effe26d7 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@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
