pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed

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>
Cc: Amul Sul <sulamul@gmail.com>
Subject: Re: Foreign key validation failure in 18beta1
Date: Wed, 28 May 2025 14:26:06 +0200
Message-ID: <202505281226.ohdyrr2jrw6r@alvherre.pgsql> (raw)
In-Reply-To: <CAHewXNmx+wA_cs6mTv+uHNTCCxXddNDSKgFbcW+y9to2jKXMLg@mail.gmail.com>

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)

Attachments:

0001-Don-t-validate-referenced-side-FK-subconstraints.patch (text/x-diff, 2.2K) download
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

view thread (19+ messages)  latest in thread

Message-ID: <202505281226.ohdyrr2jrw6r@alvherre.pgsql>
Permalink:  ../202505281226.ohdyrr2jrw6r@alvherre.pgsql/
Also on:    postgresql.org/message-id/202505281226.ohdyrr2jrw6r@alvherre.pgsql

copy link · copy postgr.es · copy git am

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: pgsql-hackers@postgresql.org
  Cc: alvherre@alvh.no-ip.org, tndrwang@gmail.com, ah@cybertec.at, sulamul@gmail.com
  Subject: Re: Foreign key validation failure in 18beta1
  In-Reply-To: <202505281226.ohdyrr2jrw6r@alvherre.pgsql>

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

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