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.
Please see attached bug.sql.
Attachments:
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers