Re: Teaching pg_dump to use NOT VALID constraints

On 11/10/14, 12:00 PM, Simon Riggs wrote:
> On 10 November 2014 17:33, Alvaro Herrera <alvherre@2ndquadrant.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.

Isn't the real use-case here that if constraints were valid when you dumped then we shouldn't have to *any* re-validate
whenwe load? (Though, we'd have to be careful of that with CHECK because that can call user code...)
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com