Re: Problems with pg_upgrade after change of unix user running db. - Mailing list pgsql-general

On 3 October 2016 at 21:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Benedikt Grundmann <bgrundmann@janestreet.com> writes:
> proddb_testing=# SELECT
> conname,convalidated,conislocal,coninhcount,connoinherit
> proddb_testing-# FROM pg_constraint WHERE conrelid =
> 'js_activity_20110101'::regclass;
>                    conname                   | convalidated | conislocal |
> coninhcount | connoinherit
> ---------------------------------------------+--------------+------------+-------------+--------------
>  seqno_not_null                              | f            | t          |
>           1 | f

After some tracing through the code, I think it's the combination of all
three of coninhcount>0, conislocal, and !convalidated that is producing
the problem, and even then possibly only in binary-upgrade mode.  pg_dump
is jumping through some hoops to try to restore that state, and evidently
not getting it entirely right.

Is there a reason you've left all these constraints in NOT VALID state?
They're kinda useless that way. 

Not at all.  I consider the ability to add constraints in not validated form one of the 10 best things that happened in postgres in recent years.  They helped us a lot when slowly improving our schemas. 

Often just preventing any new or modified rows to validate the constraint is really all we need or most that is needed.  Which is the only thing I really care about in this case. And given the size of these tables and their importance validating the constraints during production hours is tricky.  Which means to validate them one of us has to sacrifice part of their Saturday to do these and the marginal utility of having the constraint validated was just never worth it.  But if that is what's required to do the upgrade we will do so (the upgrade itself we will have to do on a Saturday anyway).  

Probably if you updated them to be valid
(see ALTER TABLE ... VALIDATE CONSTRAINT), the upgrade would go through
without difficulty.

I'm running all the upgrade attempts on our testing instance (which is nightly restored from the latest backup), it's not a problem to run the validate command there so I'll do that now and find out if you are right. 

I'll look into fixing this, but depending on how messy it turns out to be,
it might be something we choose to fix only in HEAD.

                        regards, tom lane