Re: pg_dump restore time and Foreign Keys

On Mon, 2008-06-09 at 10:57 -0400, Tom Lane wrote:
> Decibel! <decibel(at)decibel(dot)org> writes:
> > Actually, in the interest of stating the problem and not the
> > solution, what we need is a way to add FKs that doesn't lock
> > everything up to perform the key checks.
>
> Ah, finally a useful comment. I think it might be possible to do an
> "add FK concurrently" type of command that would take exclusive lock
> for just long enough to add the triggers, then scan the tables with just
> AccessShareLock to see if the existing rows meet the constraint, and
> if so finally mark the constraint "valid". Meanwhile the constraint
> would be enforced against newly-added rows by the triggers, so nothing
> gets missed. You'd still get a small hiccup in system performance
> from the transient exclusive lock, but nothing like as bad as it is
> now. Would that solve your problem?

That's good, but it doesn't solve the original user complaint about
needing to re-run many, many large queries to which we already know the
answer.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support