On Thu, Apr 19, 2018 at 12:21 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabri...@timbira.com.br> writes: > > 2018-04-19 15:57 GMT-03:00 Tom Lane <t...@sss.pgh.pa.us>: > >> (I'm not sure that this issue is adequately documented, though. > >> I'd have expected to find something about it in triggers.sgml and/or > >> create_trigger.sgml, but in a quick look neither of them mentions > foreign > >> keys.) > > > We don't have it properly documented... at the time I answered this > > question on pt-br stackoverflow I noticed the lack o documentation and > > unfortunately I completely forgot to propose a small patch for it. > > It strikes me that there are basically two things a trigger could do to > break FK consistency: > > 1. Turn an FK-commanded update into a no-op by returning NULL. > > 2. Change the content of the FK-related columns during an FK-commanded > update. > > Both of these apply only to BEFORE ROW triggers, of course. > > It might not be unreasonable or unduly expensive to throw an error for > case #1. I don't think I want to get into the expense of checking > for case #2, but covering case #1 would be enough to catch all of the > reports of this type of problem that I can remember. > > IIRC, you can also break FK consistency with poorly-thought-out rules, > but given that rules are close-to-deprecated, I'm not very concerned > about sanding down rough edges in that case. > > (But if you feel like writing a documentation patch, please do, because > we'd not be likely to back-patch a behavioral change like this even > if we get around to making it.) > > regards, tom lane > > I'm gonna chime in here from a simple user perspective. I'm kinda shocked reading this thread that any of this is possible. I had always understood and relied on foreign keys being a _guarantee_ of referential integrity. I'd personally be in favor of at least an option to disallow this, even with a performance cost. Maybe you could even call it "Strict Mode." ;) But regardless, I think some better documentation is in order, and not just in the triggers section. I'd suggest this be prominently mentioned as a big asterisk in any places that talk about constratints. This page seems like an obvious candidate: https://www.postgresql.org/docs/9.5/static/ddl-constraints.html), as it has nothing qualifying lots of statements such as "If a user attempts to store data in a column that would violate a constraint, an error is raised." I do understand none of this happens unless you break it yourself, but it might change both how I write and test triggers, and how I might look at using other people's triggers or materials. Knowing my referential integrity can't be broken is a nice guard rail to have, but if you can't necessarily count on it, some prominent signs saying "warning, no guard rail ahead" seem like a good idea. Thanks for listening! Ken -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://demo.agency-software.org/client <https://demo.agency-software.org/client>* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.
