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

From: Benedikt Grundmann <bgrundmann(at)janestreet(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Bruce Momjian <bruce(at)momjian(dot)us>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Problems with pg_upgrade after change of unix user running db.
Date: 2016-10-04 08:28:36
Message-ID: CADbMkNN2+5tVcEF=NAL22JLRk7ndzKhR9qB=G7VpzY5rGGRWgg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 4 October 2016 at 08:17, Benedikt Grundmann <bgrundmann(at)janestreet(dot)com>
wrote:

>
> On 3 October 2016 at 21:01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Benedikt Grundmann <bgrundmann(at)janestreet(dot)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.
>

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
>>
>
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Durumdara 2016-10-04 08:53:29 Re: Restricted access on DataBases
Previous Message Benedikt Grundmann 2016-10-04 07:17:07 Re: Problems with pg_upgrade after change of unix user running db.