Re: Teaching pg_dump to use NOT VALID constraints

On 10 November 2014 17:33, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:

>> pg_dump --no-revalidaton
>>
>> will add "NOT VALID" onto the recreation SQL for any FKs, but only for
>> ones that were already known to be valid.
>
> Well. Constraints that haven't been validated already have a NOT VALID
> emitted by ruleutils.c, yes? So what this patch does is add such a
> clause for all *other* constraints. Right? In other words what it aims
> to do is speed up loading of data by skipping the validation step on
> restore. Is that right?

Correct. CHECK constraints are added onto main table so they validate at load.

> ISTM we could have the default pg_dump behavior emit NOT VALID
> constraints, and add VALIDATE CONSTRAINT commands at the end; that way
> the database is usable sooner but the constraints end up marked as
> validated by the time the dump is finished.

Yes, may be an even better idea. We'd still want the --no-revalidation
option, AFAICS.

FKs are already "at the end". Perhaps we should add another
"validation" section?

I like the idea, just not sure how long it would take.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services