Feedback-ID: ia2694551:Fastmail
Date: Wed, 28 May 2025 14:26:06 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Tender Wang <tndrwang@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org, Antonin Houska <ah@cybertec.at>,
	Amul Sul <sulamul@gmail.com>
Subject: Re: Foreign key validation failure in 18beta1
Message-ID: <202505281226.ohdyrr2jrw6r@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="2wgq6flqmqnvq5nc"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAHewXNmx+wA_cs6mTv+uHNTCCxXddNDSKgFbcW+y9to2jKXMLg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202505281226.ohdyrr2jrw6r%40alvherre.pgsql>
Precedence: bulk


--2wgq6flqmqnvq5nc
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On 2025-May-28, Tender Wang wrote:

> I dided the codes, in QueueFKConstraintValidation(),  we add three
> newconstraint for the
> fk rel, because the pk rel is partition table.
> 
> During phase 3 of AlterTable, in ATRewriteTables(),
> call validateForeignKeyConstraint() three times.
> The first time the pk rel is pk, and it's ok.
> The second time the pk rel is only pk_1, and the type(1) is not in pk_1, so
> an error is reported.
> 
> In this case, the two children newconstraint  should not be added to the
> queue.

Yeah, I reached the same conclusion and this is the preliminary fix I
had written for it.  I don't like that I had to duplicate a few lines of
code, but maybe it's not too bad.  Also the comments need to be
clarified a bit more.

Clearly b663b9436e75 lacked tests.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Sallah, I said NO camels! That's FIVE camels; can't you count?"
(Indiana Jones)

--2wgq6flqmqnvq5nc
Content-Type: text/x-diff; charset=utf-8
Content-Disposition: attachment;
	filename="0001-Don-t-validate-referenced-side-FK-subconstraints.patch"

From 4c11da193991b95de8baf7689a9938ca9bb171aa Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <alvherre@kurilemu.de>
Date: Wed, 28 May 2025 14:22:58 +0200
Subject: [PATCH] Don't validate referenced-side FK subconstraints

Silly bug in b663b9436e75.

Reported-by: Antonin Houska <ah@cybertec.at>
Discussion: https://postgr.es/m/26983.1748418675@localhost
---
 src/backend/commands/tablecmds.c | 24 ++++++++++++++++++++++--
 1 file changed, 22 insertions(+), 2 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 54ad38247aa..581c59f117b 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -12988,8 +12988,10 @@ QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel,
 	}
 
 	/*
-	 * If the table at either end of the constraint is partitioned, we need to
-	 * recurse and handle every constraint that is a child of this constraint.
+	 * If the referencing table is partitioned, we need to recurse and handle
+	 * every constraint that is a child of this constraint.  Also update the
+	 * sub-constraints on the referenced side of the constraint to appear as
+	 * validated; no actual validation is needed for them.
 	 */
 	if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ||
 		get_rel_relkind(con->confrelid) == RELKIND_PARTITIONED_TABLE)
@@ -13021,6 +13023,24 @@ QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel,
 			if (childcon->convalidated)
 				continue;
 
+			/*
+			 * For child constraints in the referenced side of the constraint,
+			 * we don't need to do any actual validation, but we do need to
+			 * update the status to validated.
+			 */
+			if (childcon->confrelid != con->confrelid)
+			{
+				copyTuple = heap_copytuple(childtup);
+				copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
+				copy_con->convalidated = true;
+				CatalogTupleUpdate(conrel, &copyTuple->t_self, copyTuple);
+
+				InvokeObjectPostAlterHook(ConstraintRelationId, con->oid, 0);
+
+				heap_freetuple(copyTuple);
+				continue;
+			}
+
 			childrel = table_open(childcon->conrelid, lockmode);
 
 			QueueFKConstraintValidation(wqueue, conrel, childrel, childtup,
-- 
2.39.5


--2wgq6flqmqnvq5nc--