public inbox for [email protected]  

help / color / mirror / Atom feed

From: Amul Sul <[email protected]>
To: PostgreSQL-development <[email protected]>
Subject: Bug in detaching a partition with a foreign key.
Date: Thu, 16 Jan 2025 19:23:31 +0530
Message-ID: <CAAJ_b97GuPh6wQPbxQS-Zpy16Oh+0aMv-w64QcGrLhCOZZ6p+g@mail.gmail.com> (raw)

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


Attachments:

  [application/x-patch] trial.patch (1.2K, ../CAAJ_b97GuPh6wQPbxQS-Zpy16Oh+0aMv-w64QcGrLhCOZZ6p+g@mail.gmail.com/2-trial.patch)
  download | inline diff:
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;

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Bug in detaching a partition with a foreign key.
  In-Reply-To: <CAAJ_b97GuPh6wQPbxQS-Zpy16Oh+0aMv-w64QcGrLhCOZZ6p+g@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox