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

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)

--
Bill Moran <wmoran@potentialtech.com>

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

On Thu, Jan 23, 2014 at 3:46 PM, Bill Moran <wmoran@potentialtech.com> wrote:
> 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?

AFAICT the planner doesn't currently rely on FOREIGN KEY constriants
for anything, so there's no downside to leaving those NOT VALID.

UNIQUE constraints affect the planner the most, but they must always
be valid anyway.

If you use table inheritance (partitioning), then valid CHECK
constraints are necessary to use that effectively.

> It has also been suggested that manually changing the status to valid
> in the catalog without going through the validation process could cause
> problems

It's unsupported: if you break something when manually messing with
the system catalog, you get to keep the pieces and people will just
tell you "we told you so". But if you know what you're doing, it's OK.
Just make sure you double-check the source code that you're not
missing something critical that it does.

Regards,
Marti