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(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.

Isn't the real use-case here that if constraints were valid when you dumped then we shouldn't have to *any* re-validate when we 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