On Thu, Apr 19, 2018 at 12:21 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabrizio@xxxxxxxxxxxxxx> writes: >> (I'm not sure that this issue is adequately documented, though.
> 2018-04-19 15:57 GMT-03:00 Tom Lane <tgl@xxxxxxxxxxxxx>:
>> 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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
