Re: FKey not enforced resulting in broken Dump/Reload

On Fri, Jul 19, 2013 at 2:15 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Jul 19, 2013 at 1:45 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> On Fri, Jul 19, 2013 at 12:58 PM, Rod Taylor <rod(dot)taylor(at)gmail(dot)com> wrote:
>>>> A poorly coded trigger on the referencing table has the ability to break
>>>> foreign keys, and as a result create a database which cannot be dumped and
>>>> reloaded.
>>
>>> This is a known limitation of our foreign key machinery. It might
>>> well be susceptible to improvement, but I wouldn't count on anyone
>>> rewriting it in the near future.
>>
>> If we failed to fire triggers on foreign-key actions, that would not be
>> an improvement. And trying to circumscribe the trigger's behavior so
>> that it couldn't break the FK would be (a) quite expensive, and
>> (b) subject to the halting problem, unless perhaps you circumscribed
>> it so narrowly as to break a lot of useful trigger behaviors. Thus,
>> there's basically no alternative that's better than "so don't do that".
>
> I think a lot of people would be happier if foreign keys were always
> checked after all regular triggers and couldn't be disabled. But,
> eh, that's not how it works.

Please ignore this fuzzy thinking. You're right.

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