Re: pg_dump restore time and Foreign Keys

2008/6/9 Simon Riggs <simon(at)2ndquadrant(dot)com>:

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

just a guess, but maybe "create FK concurrently" feature combined with
"synchronized scan" feature _does_ resolve original problem.

if you run many "create FK concurrently" one after another, wouldn't the seq
scan be reused?

>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Training, Services and Support
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Filip Rembiałkowski