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

On Thu, Jun 2, 2011 at 5:34 PM, Thom Brown <thom(at)linux(dot)com> wrote:
> 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.

Sounds like someone needs to extract and apply that portion of
Alvaro's patch. I've added this to the open items list.

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