On 4 October 2016 at 08:17, Benedikt Grundmann <bgrundmann@xxxxxxxxxxxxxx> wrote:
On 3 October 2016 at 21:01, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:Benedikt Grundmann <bgrundmann@xxxxxxxxxxxxxx> writes:
> proddb_testing=# SELECT
> conname,convalidated,conislocal,coninhcount,connoinherit > seqno_not_null | f | t |
> proddb_testing-# FROM pg_constraint WHERE conrelid =
> 'js_activity_20110101'::regclass;
> conname | convalidated | conislocal |
> coninhcount | connoinherit
> ---------------------------------------------+-------------- +------------+-------------+-- ------------
> 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.
It looks like you might be right but I don't know for sure yet. And it will take me a long time to find out. Rationale: After validating seqno_not_null I could proceed a bit further but failed at another constraint like that (valid_counterparty). However that constraint actually is violated by lots of rows in the past and we had no plans (or easy way) to fix this. The constraint was put in like this to prevent future rows.
I guess I could drop the constraint do the restore and then put the constraint in again. Sigh. This is all relatively sad.
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
