Re: [DOCS] Confusing Trigger Docs.

On Thu, Aug 31, 2017 at 09:22:22AM -0700, Peter Geoghegan wrote:
> On Thu, Aug 31, 2017 at 6:25 AM, Bruce Momjian <bruce@momjian.us> wrote:
> > On Mon, Jul  3, 2017 at 08:07:10PM +0000, neil@fairwindsoft.com wrote:
> >> The following documentation comment has been logged on the website:
> >>
> >> Page: https://www.postgresql.org/docs/9.6/static/trigger-definition.html
> >> Description:
> >>
> >> https://www.postgresql.org/docs/devel/static/trigger-definition.html
> >>
> >> This sentence:
> >>
> >> "If an INSERT contains an ON CONFLICT DO UPDATE clause, it is possible that
> >> the effects of all row-level BEFORE INSERT triggers and all row-level BEFORE
> >> UPDATE triggers can both be applied in a way that is apparent from the final
> >> state of the updated row, if an EXCLUDED column is referenced."
> >>
> >> is very hard to digest.
> 
> EXCLUDED.* is exactly what the name suggests -- the tuple that was not
> inserted because of a conflict. So, naturally it has the effects of
> any before insert trigger, and carries them forward. But you still
> have before triggers on the update side.
> 
> Typically, this won't matter at all, because before triggers tend to
> be written in an idempotent fashion -- something gets filled in. But I
> can imagine cases where it is not idempotent, and apply a before
> update trigger modifies the row in a way that is surprising. Just
> because ON CONFLICT DO UPDATE was used rather than UPDATE. That's what
> the documentation warns about.

I know this thread is six years old, but I still found it confusing, so
the attached patch tries to simplify it.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.