Bug in detaching a partition with a foreign key.

[Amul Sul]

Hi,

While detaching a partition with a foreign key referencing a partitioned table,
I am getting the following error:

ERROR:  could not find ON INSERT check triggers of foreign key constraint 16636

I haven’t looked closely at what the issue might be, but it seems the logic
inside DetachPartitionFinalize(), which is supposed to iterate over inherited
foreign keys, is lacking. The attached trial patch fixes the issue for me, but
I’m not sure if it’s the correct fix. I’ll take a closer look later.

Here is the test:

CREATE TABLE bar(id int PRIMARY KEY) PARTITION BY RANGE(id);
CREATE TABLE bar_p0 PARTITION OF bar FOR VALUES FROM (0) TO (100);

CREATE TABLE foo(id int) PARTITION BY RANGE(id);

CREATE TABLE foo_p0 PARTITION OF foo FOR VALUES FROM (0) TO (100)
PARTITION BY RANGE(id);
CREATE TABLE foo_p0_p0 PARTITION OF foo_p0 FOR VALUES FROM (0) TO (100);
ALTER TABLE foo_p0 ADD CONSTRAINT child_fk_con FOREIGN KEY (id) REFERENCES bar;

ALTER TABLE foo DETACH PARTITION foo_p0;

--
Regards,
Amul Sul
EDB: http://www.enterprisedb.com

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index c42a740ccef..cacdb9b475d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -19926,6 +19926,7 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
 	HeapTuple	tuple,
 				newtuple;
 	Relation	trigrel = NULL;
+	List	   *fksids;
 
 	if (concurrent)
 	{
@@ -19946,6 +19947,11 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
 	fks = copyObject(RelationGetFKeyList(partRel));
 	if (fks != NIL)
 		trigrel = table_open(TriggerRelationId, RowExclusiveLock);
+
+	/* Collect all the constraint ids */
+	foreach(cell, fks)
+		fksids = lappend_oid(fksids, lfirst_node(ForeignKeyCacheInfo, cell)->conoid);
+
 	foreach(cell, fks)
 	{
 		ForeignKeyCacheInfo *fk = lfirst(cell);
@@ -19961,7 +19967,8 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
 
 		/* consider only the inherited foreign keys */
 		if (conform->contype != CONSTRAINT_FOREIGN ||
-			!OidIsValid(conform->conparentid))
+			!OidIsValid(conform->conparentid) ||
+			list_member_oid(fksids, conform->conparentid))
 		{
 			ReleaseSysCache(contup);
 			continue;