Feedback-ID: ia2694551:Fastmail
Date: Fri, 4 Jul 2025 19:19:23 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Justin Pryzby <pryzby@telsasoft.com>,
	Michael Paquier <michael@paquier.xyz>, Ali Akbar <the.apaan@gmail.com>,
	Michael Paquier <michael.paquier@gmail.com>,
 tushar <tushar.ahuja@enterprisedb.com>,
	pgsql-hackers@postgresql.org, rmt@lists.postgresql.org
Subject: Re: [HACKERS] pg_upgrade failed with error - ERROR: column "a" in
 child table must be marked NOT NULL
Message-ID: <202507041719.l5qh2rme2bbx@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="ygvjcb67ii7yk42r"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <202507041645.afjl5rssvrgu@alvherre.pgsql>
Archived-At: 
 <https://www.postgresql.org/message-id/202507041719.l5qh2rme2bbx%40alvherre.pgsql>
Precedence: bulk


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

On 2025-Jul-04, Alvaro Herrera wrote:

> Hmm, crake doesn't like the fact that there's no regnamespace in 9.4.
> Apparently in version 13 we claim to support back to 9.0.  I only tried
> with an old server version 12.  Should be an easy fix ...

It goes like this.  It's tested with 9.2 only though, as 9.1 doesn't
compile from Git (lack of Flex) and I didn't want to try a tarball.

(This is for master.  17 and back require a small change for lack of
task.c)

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"I apologize for the confusion in my previous responses.
 There appears to be an error."                   (ChatGPT)

--ygvjcb67ii7yk42r
Content-Type: text/x-diff; charset=utf-8
Content-Disposition: attachment;
	filename="0001-Fix-new-pg_upgrade-query-not-to-rely-on-regnamespace.patch"

From 9da54743029ff9c3db0928acde3cd8c0f57c6d50 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <alvherre@kurilemu.de>
Date: Fri, 4 Jul 2025 19:00:37 +0200
Subject: [PATCH] Fix new pg_upgrade query not to rely on regnamespace

That was invented in 9.5, and pg_upgrade claims to support back to 9.0

I couldn't test 9.0 and 9.1 though, since they don't compile anymore.
At least 9.2 is fine with this coding.
---
 src/bin/pg_upgrade/check.c | 7 ++++---
 1 file changed, 4 insertions(+), 3 deletions(-)

diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index a756ddbd254..30579ef2051 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -1689,12 +1689,13 @@ check_for_not_null_inheritance(ClusterInfo *cluster)
 			 log_opts.basedir,
 			 "not_null_inconsistent_columns.txt");
 
-	query = "SELECT cc.relnamespace::pg_catalog.regnamespace AS nspname, "
-		"       cc.relname, ac.attname "
+	query = "SELECT nspname, cc.relname, ac.attname "
 		"FROM pg_catalog.pg_inherits i, pg_catalog.pg_attribute ac, "
-		"     pg_catalog.pg_attribute ap, pg_catalog.pg_class cc "
+		"     pg_catalog.pg_attribute ap, pg_catalog.pg_class cc, "
+		"     pg_catalog.pg_namespace nc "
 		"WHERE cc.oid = ac.attrelid AND i.inhrelid = ac.attrelid "
 		"      AND i.inhparent = ap.attrelid AND ac.attname = ap.attname "
+		"      AND cc.relnamespace = nc.oid "
 		"      AND ap.attnum > 0 and ap.attnotnull AND NOT ac.attnotnull";
 
 	task = upgrade_task_create();
-- 
2.39.5


--ygvjcb67ii7yk42r--