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

On Mon, Dec 13, 2010 at 10:49 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> (a) ALTER TABLE ... ADD FOREIGN KEY ... NOT VALIDATED INITIALLY;
> will add a FK but NOT run the check - we mark it as "check pending".
> Lock held: ShareRowExclusiveLock

Seems about right. Not sure whether the lock strength is correct.

> (b) Every new change to the table has the FK enforced - the triggers are
> fully enabled and active. (That could mean we update a row and have the
> update fail because of a FK violation.)

Also seems about right.

> 2. pg_validate_foreign_key('constraint name');
> Returns immediately if FK is valid
> Returns SETOF rows that violate the constraint, or if no rows are
> returned it updates constraint to show it is now valid.
> Lock held: AccessShareLock

I'm less sure about this part. I think there should be a DDL
statement to validate the foreign key. The "return the problem" rows
behavior could be done some other way, or just left to the user to
write their own query.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company