Re: Problems with pg_upgrade after change of unix user running db.

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.  Probably if you updated them to be valid
(see ALTER TABLE ... VALIDATE CONSTRAINT), the upgrade would go through
without difficulty.

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