Horton
Menu

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

Michael Paquier <michael.paquier@gmail.com>

View thread
From: Michael Paquier <michael.paquier@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: tushar <tushar.ahuja@enterprisedb.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2017-07-27T14:58:46Z
Lists: pgsql-hackers

Attachments

On Wed, Jul 26, 2017 at 4:50 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Wed, Jul 26, 2017 at 4:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Not sure what's involved there code-wise, though, nor whether we'd want
>> to back-patch.
>
> I'll try to look at the code around that to come up with a clear
> conclusion in the next couple of days, likely more as that's a
> vacation period. Surely anything invasive would not be backpatched.

So I think that the attached patch is able to do the legwork. While
looking at the code, I have bumped into index_check_primary_key() that
discarded the case of sending SET NOT NULL to child tables, as
introduced by 88452d5b. But that's clearly an oversight IMO, and the
comment is wrong to begin with because SET NOT NULL is spread to child
tables. Using is_alter_table instead of a plain true in
index_check_primary_key() for the call of AlterTableInternal() is
defensive, but I don't think that we want to impact any modules
relying on this API, so recursing only when ALTER TABLE is used is the
safest course of action to me. With this logic, we rely as well on the
fact that ADD PRIMARY KEY is not recursive in tablecmds.c. Comments
are welcome, I'll park that into the CF app so as it does not get lost
in the void.
-- 
Michael

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.