Horton
Menu

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

Ali Akbar <the.apaan@gmail.com>

View thread
From: Ali Akbar <the.apaan@gmail.com>
To: Michael Paquier <michael.paquier@gmail.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, Tom Lane <tgl@sss.pgh.pa.us>, tushar <tushar.ahuja@enterprisedb.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2017-12-13T06:59:56Z
Lists: pgsql-hackers 
2017-12-13 9:10 GMT+07:00 Michael Paquier <michael.paquier@gmail.com>:

>
> It is not the first time that this topic shows up. See for example
> this thread from myself's version of last year:
> https://www.postgresql.org/message-id/CAB7nPqTPXgX9HiyhhtAgpW7jbA1is
> kMCSoqXPEEB_KYXYy1E1Q@mail.gmail.com
> Or this one:
> http://www.postgresql.org/message-id/21633.1448383428@sss.pgh.pa.us
>
> And the conclusion is that we don't want to do what you are doing
> here, and that it would be better to store NOT NULL constraints in a
> way similar to CHECK constraints.
>

Thanks for the link to those thread.

Judging from the discussion there, it will be a long way to prevent DROP
NOT NULL. So for this problem (pg_upgrade failing because of it), i propose
that we only add a check in pg_upgrade, so anyone using pg_upgrade can know
and fix the issue before the error?

If it's OK, i can write the patch.


> > How about: ".. if some parent has the same"
> >
> > +               heap_close(parent, AccessShareLock);
> >
> > Maybe, we shouldn't be dropping the lock so soon.
>
> Yes, such things usually need to be kept until the end of the
> transaction, and usually you need to be careful about potential lock
> upgrades that could cause deadlocks. This patch looks wrong for both
> of those things.


Thanks. Judging from above, it's better that we continue the DROP NOT NULL
problem in another patch (and another thread)

Best Regards,
Ali Akbar

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.