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

2017-12-13 9:10 GMT+07:00 Michael Paquier <michael(dot)paquier(at)gmail(dot)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(at)mail(dot)gmail(dot)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