Re: Problem with trigger makes Detail record be invalid

On Thu, Apr 19, 2018 at 12:21 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabrizio(at)timbira(dot)com(dot)br> writes:
> > 2018-04-19 15:57 GMT-03:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.