Re: ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED

On Tue, 2010-12-14 at 11:24 -0500, Chris Browne wrote:
> tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) writes:
> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >> ... On the
> >> other hand, there's clearly also a use case for this behavior. If a
> >> bulk load of prevalidated data forces an expensive revalidation of
> >> constraints that are already known to hold, there's a real chance the
> >> DBA will be backed into a corner where he simply has no choice but to
> >> not use foreign keys, even though he might really want to validate the
> >> foreign-key relationships on a going-forward basis.
> >
> > There may well be a case to be made for doing this on grounds of
> > practical usefulness. I'm just voicing extreme skepticism that it can
> > be supported by reference to the standard.
> >
> > Personally I'd prefer to see us look into whether we couldn't arrange
> > for low-impact establishment of a verified FK relationship, analogous to
> > CREATE INDEX CONCURRENTLY. We don't let people just arbitrarily claim
> > that a uniqueness condition exists, and ISTM that if we can handle that
> > case we probably ought to be able to handle FK checking similarly.
>
> I can point to a use case that has proven useful...
>
> Slony-I deactivates indices during the subscription process, because it
> is enormously more efficient to load the data into the tables
> sans-indices, and then re-index afterwards.
>
> The same would apply for FK constraints.
>
> I observe that the deactivation of indices is the sole remaining feature
> in Slony-I that still requires catalog access in a "corruptive" sense.
> (With the caveat that this corruption is now only a temporary one; the
> indexes are returned into play before the subscription process
> finishes.)
>
> That would be eliminated by adding in:
> "ALTER TABLE ... DISABLE INDEX ..."
> "ALTER TABLE ... ENABLE INDEX ..."
>
> For similar to apply to FK constraints would involve similar logic.

I just wanted to point out that the patch submitted here does not allow
what is requested here for FKs (nor indexes).

You can add an FK without an initial check, but the FK is enforced for
all further DML changes. You can then later validate the FK. So that
running these commands

ALTER TABLE foo ADD FOREIGN KEY ... NOT VALID;
ALTER TABLE foo VALIDATE CONSTRAINT ...;

is roughly equivalent to the concept of

ALTER TABLE foo ADD FOREIGN KEY ... CONCURRENTLY;

There is no command that makes the FK "NOT ENFORCED", so you can't turn
it off then back on again as was requested above.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services