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

On 8 February 2011 03:50, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Feb 3, 2011 at 11:00 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Fri, Jan 14, 2011 at 6:15 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> 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;
>>
>> This patch, which seems to be the latest version, no longer applies,
>> and has not been updated based on the previous provided review
>> comments.
>>
>> Also, this diff hunk looks scary to me:
>>
>> +       if (must_use_query)
>> +               ereport(ERROR,
>> +                               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
>> +                                errmsg("cannot SELECT from primary
>> key of relation \"%s\"",
>> +                                               RelationGetRelationName(rel))));
>> +
>>
>> What's the justification for that?
>
> Since this patch was reviewed on January 23rd by Marko Tiikkaja and
> more briefly on February 3rd by me, and has not been updated, I am
> marking it Returned with Feedback.

Just a note that since Alvaro created a patch to provide similar
functionality for constraints, I identified an issue with database
dumps, which apparently affects invalid foreign keys too:
http://archives.postgresql.org/pgsql-hackers/2011-06/msg00162.php

In other words, a database containing foreign keys that hasn't yet
been validated will not produce a dump containing the necessary NOT
VALID parameters. This would be fixed by Alvaro's patch.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company