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