Horton
Menu

Re: [HACKERS] pg_upgrade failed with error - ERROR: column "a" in child table must be marked NOT NULL

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Michael Paquier <michael@paquier.xyz>, Ali Akbar <the.apaan@gmail.com>, Michael Paquier <michael.paquier@gmail.com>, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, tushar <tushar.ahuja@enterprisedb.com>, Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgresql.org, rmt@lists.postgresql.org
Date: 2025-07-03T18:53:01Z
Lists: pgsql-hackers

Attachments

Hello,

We seem to have forgotten about this issue.  I think this is even more
pressing with the changes to 18 for not-null constraints, though
strictly speaking it's always been a problem.  Here's an updated patch.
I simplified the query (no need for a recursive CTE as far as I can
tell) and updated to use the pg_upgrade "task" infrastructure.

I think from 18 on, the problem can no longer be recreated, since
removing the attnotnull bit from a child table is not possible.

I should add some testing too before pushing, of course.  I only tested
manually.

I'd like to hear from RMT about getting this in 18.  Actually I think we
should consider backporting to all live versions ... thoughts?

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Investigación es lo que hago cuando no sé lo que estoy haciendo"
(Wernher von Braun)

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix new pg_upgrade query not to rely on regnamespace

  2. pg_upgrade: check for inconsistencies in not-null constraints w/inheritance

  3. Apply ALTER ... SET NOT NULL recursively in ALTER ... ADD PRIMARY KEY.