Re: pg_dump restore time and Foreign Keys

On Mon, 2008-06-09 at 12:37 -0400, Robert Treat wrote:
> On Monday 09 June 2008 11:59:27 Tom Lane wrote:
> > Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > > On Mon, 2008-06-09 at 11:33 -0400, Tom Lane wrote:
> > >> No, we are running a large query to which the user *thinks* he knows the
> > >> answer. There are any number of reasons why he might be wrong.
> > >
> > > Of course. I should have said "to which we already know the answer" to
> > > indicate I'm passing on others' criticisms of us.
> >
> > [ shrug... ] We don't know the answer either, and anyone who says
> > we do is merely betraying his ignorance of the number of ways to load
> > a foot-gun.
> >
>
> I think the more realistic scenario (based on the FK idea) is that you want to
> prevent any future rows from coming without validating the FK, and you're
> willing to clean up any violators after the fact, since you can make that
> an "out of the critical path" operation.
>
> if you extend this to a more general "create constraint concurrently" (to
> handle normal constraint, not null constraints, etc...), it would certainly
> be a big win, and i think most would see it as a reasonable compromise.

Agreed. I think the "out of the critical path" action is more likely to
be the intended path rather than the "never check at all" route.

If we break down the action into two parts.

ALTER TABLE ... ADD CONSTRAINT foo FOREIGN KEY ... NOVALIDATE;
which holds exclusive lock, but only momentarily
After this runs any new data is validated at moment of data change, but
the older data has yet to be validated.

ALTER TABLE ... VALIDATE CONSTRAINT foo
which runs lengthy check, though only grabs lock as last part of action

This way we have the ability to add them concurrently if we choose by
running one after the other, or we can run first part only for now and
run the other one at a more convenient moment.

On a full set of checks on a large complex database can easily take
hours or even days.

We should allow this. It's not a footgun, its an honest attempt by
people to add RI checks to their database. The only other alternative
for some people is to not add FKs at all, which is also a footgun, but
we don't seem bothered that they might take that option.

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