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

On Jan 14, 2011, at 5:15 AM, Simon Riggs wrote:
> On Mon, 2010-12-13 at 17:15 +0000, Peter Geoghegan wrote:
>> On 13 December 2010 16:08, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> On Mon, Dec 13, 2010 at 10:49 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>>> 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.
>>
>> +1. I think that a DDL statement is more appropriate, because it makes
>> the process sort of symmetrical.
>
> Patch to implement the proposed feature attached, for CFJan2011.
>
> 2 sub-command changes:
>
> ALTER TABLE foo ADD FOREIGN KEY fkoo ... NOT VALID;
>
> ALTER TABLE foo VALIDATE CONSTRAINT fkoo;

Sorry for the late reply; I just saw this...

Is there any way to be able to get the bad records out of the ALTER ... VALIDATE? I know it's pretty unusual, but for a set of large tables, it could take hours to run a separate query that gives you the results.

BTW, I agree that this should be a DDL command, it would be very odd if it wasn't. But I also see it being very useful to be able to get the set of bad rows at the same time. Maybe if there was an SRF that did the real work and the ALTER just ignored the resultset?
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net