Thread: FKey not enforced resulting in broken Dump/Reload
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.
The BEFORE DELETE trigger accidentally does RETURN NEW, which suppresses the DELETE action by the foreign key trigger. This allows the record from the referenced table to be deleted and the record in the referencing table to remain in place.
While I don't expect Pg to do what the coder meant, but it should throw an error and not leave foreign key'd data in an invalid state.
This applies to both 9.1 and 9.2.
This applies to both 9.1 and 9.2.
Attachment
On Fri, Jul 19, 2013 at 12:58 PM, Rod Taylor <rod.taylor@gmail.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. > > The BEFORE DELETE trigger accidentally does RETURN NEW, which suppresses the > DELETE action by the foreign key trigger. This allows the record from the > referenced table to be deleted and the record in the referencing table to > remain in place. > > While I don't expect Pg to do what the coder meant, but it should throw an > error and not leave foreign key'd data in an invalid state. 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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Jul 19, 2013 at 12:58 PM, Rod Taylor <rod.taylor@gmail.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".
regards, tom lane
On Fri, Jul 19, 2013 at 1:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Fri, Jul 19, 2013 at 12:58 PM, Rod Taylor <rod.taylor@gmail.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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Fri, Jul 19, 2013 at 2:15 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Fri, Jul 19, 2013 at 1:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Robert Haas <robertmhaas@gmail.com> writes: >>> On Fri, Jul 19, 2013 at 12:58 PM, Rod Taylor <rod.taylor@gmail.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