Date: Tue, 21 Nov 2023 21:01:12 -0500
From: Bruce Momjian <bruce@momjian.us>
To: Peter Geoghegan <pg@bowt.ie>
Cc: neil@fairwindsoft.com,
	PostgreSQL-documentation <pgsql-docs@postgresql.org>,
	Andres Freund <andres@anarazel.de>,
	Heikki Linnakangas <heikki.linnakangas@iki.fi>
Subject: Re: [DOCS] Confusing Trigger Docs.
Message-ID: <ZV1g6BsylqHAFYEL@momjian.us>
References: <20170703200710.27956.64565@wrigleys.postgresql.org>
 <20170831132544.GC2355@momjian.us>
 <CAH2-WzkiHHJue-DRt_zdSvKXJFUU=7CNdxkA31m1Vmyr5G2Jcg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="L7G0y8AuqFMuGRTe"
Content-Disposition: inline
In-Reply-To: 
 <CAH2-WzkiHHJue-DRt_zdSvKXJFUU=7CNdxkA31m1Vmyr5G2Jcg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ZV1g6BsylqHAFYEL%40momjian.us>
Precedence: bulk


--L7G0y8AuqFMuGRTe
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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:
> >>
> >> &quot;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.&quot;
> >>
> >> 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.

--L7G0y8AuqFMuGRTe
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="excluded.diff"

diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
new file mode 100644
index 6e1f370..0615f8a
*** a/doc/src/sgml/trigger.sgml
--- b/doc/src/sgml/trigger.sgml
***************
*** 140,160 ****
     </para>
  
     <para>
!     If an <command>INSERT</command> contains an <literal>ON CONFLICT
!     DO UPDATE</literal> clause, it is possible that the effects of
!     row-level <literal>BEFORE</literal> <command>INSERT</command> triggers and
!     row-level <literal>BEFORE</literal> <command>UPDATE</command> triggers can
!     both be applied in a way that is apparent from the final state of
!     the updated row, if an <varname>EXCLUDED</varname> column is referenced.
!     There need not be an <varname>EXCLUDED</varname> column reference for
!     both sets of row-level <literal>BEFORE</literal> triggers to execute,
!     though.  The
!     possibility of surprising outcomes should be considered when there
!     are both <literal>BEFORE</literal> <command>INSERT</command> and
!     <literal>BEFORE</literal> <command>UPDATE</command> row-level triggers
!     that change a row being inserted/updated (this can be
!     problematic even if the modifications are more or less equivalent, if
!     they're not also idempotent).  Note that statement-level
      <command>UPDATE</command> triggers are executed when <literal>ON
      CONFLICT DO UPDATE</literal> is specified, regardless of whether or not
      any rows were affected by the <command>UPDATE</command> (and
--- 140,158 ----
     </para>
  
     <para>
!     If an <command>INSERT</command> contains an <literal>ON
!     CONFLICT DO UPDATE</literal> clause, it is possible for both
!     row-level <literal>BEFORE</literal> <command>INSERT</command> and
!     <literal>BEFORE</literal> <command>UPDATE</command> triggers to be
!     executed on the same row.  The possibility of surprising outcomes
!     should be considered when they change rows being inserted/updated
!     (this can be problematic even if the modifications are more or less
!     equivalent, if they're not also idempotent).  The modification of
!     <varname>EXCLUDED</varname> columns has similar interactions.
!    </para>
! 
!    <para>
!     Note that statement-level
      <command>UPDATE</command> triggers are executed when <literal>ON
      CONFLICT DO UPDATE</literal> is specified, regardless of whether or not
      any rows were affected by the <command>UPDATE</command> (and

--L7G0y8AuqFMuGRTe--