Skip site navigation (1) Skip section navigation (2)

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

From: Chris Browne <cbbrowne(at)acm(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
Date: 2010-12-14 16:24:44
Message-ID: 87hbegz5ir.fsf@cbbrowne.afilias-int.info (view raw or flat)
Thread:
Lists: pgsql-hackers
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.
-- 
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxdatabases.info/info/rdbms.html
"The code should be beaten into submission" -- Arthur Norman

In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2010-12-14 16:47:11
Subject: Re: Instrument checkpoint sync calls
Previous:From: Robert HaasDate: 2010-12-14 15:57:46
Subject: Re: SQL/MED - core functionality

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group