Re: Help with details of what happens when I create a constraint NOT VALID

From: Torsten Förtsch <torsten(dot)foertsch(at)gmx(dot)net>
To: Bill Moran <wmoran(at)potentialtech(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Help with details of what happens when I create a constraint NOT VALID
Date: 2014-01-24 03:44:50
Message-ID: 52E1E1B2.5060603@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 23/01/14 14:46, Bill Moran wrote:
>
> Some quickie background: I'm on a project to migrate a fairly large
> database from MySQL to PostgreSQL (~2T). As a result of a number of
> factors, I have to do it in one shot and I have a limited time window
> in which things can be down while I switch it over.
>
> As one of many, many things I'm considering to make this work, I'm
> looking at adding constraints after the data move using NOT VALID to
> allow them to be applied quickly. This seems pretty straight forward,
> but I'm trying to understand if there are any troublesome side-effects
> to leaving the constraints unvalidated.
>
> Because of the uptime requirements, there are some very large tables
> with may foreign keys that I will never be allowed to take a lock on
> long enough to validate all the constraints. It was suggested that
> leaving the constraints as NOT VALID might affect the planner, causing
> it to use less optimal plans because it doesn't think it can trust
> the constraint. Is this true?
>
> It has also been suggested that manually changing the status to valid
> in the catalog without going through the validation process could cause
> problems, although I haven't found an explanation of what those
> problems might be.
>
> I understand that the best way is to go through and do all the steps,
> but that may simply be impossible for me because of the lock it
> requires and the time involved. Is there any negative effect to
> leaving the constraint unvalidated? Is there any actual danger in
> manually flipping the value in the catalog (The constraint can be
> consider safe because it was previously enforced on the source
> database system)

I had a similar problem some time ago. The way I solved it is as
follows. First, add the constraint as NOT VALID. That prevents further
changes to violate it. Then make sure the constraint is met. Then update
pg_constraint.

UPDATE pg_constraint
SET convalidated = true
WHERE conrelid='schema.table'::regclass::oid
AND conname='constraintname'

Not sure if that way can be recommended but it worked for me. In my case
it was a check constraint ensuring an interdependence between the
columns in a row.

Torsten

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Sameer Kumar 2014-01-24 04:15:30 Re: Fully-automatic streaming replication failover when master dies?
Previous Message Tatsuo Ishii 2014-01-24 03:28:32 Re: Fully-automatic streaming replication failover when master dies?