Message-ID: <ef350902caa5bdb5a26e6152e75c9509a47acd5b.camel@cybertec.at>
Subject: Re: [DOCS] Confusing Trigger Docs.
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Bruce Momjian <bruce@momjian.us>, 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>
Date: Wed, 22 Nov 2023 10:31:25 +0100
In-Reply-To: <ZV1g6BsylqHAFYEL@momjian.us>
References: <20170703200710.27956.64565@wrigleys.postgresql.org>
	 <20170831132544.GC2355@momjian.us>
	 <CAH2-WzkiHHJue-DRt_zdSvKXJFUU=7CNdxkA31m1Vmyr5G2Jcg@mail.gmail.com>
	 <ZV1g6BsylqHAFYEL@momjian.us>
Autocrypt: addr=laurenz.albe@cybertec.at; prefer-encrypt=mutual;
 keydata=mQINBGGDwAQBEADgbWy5cKXQld3N2mF+DFyiNFbi2oBl2T+XgxpPF8wTRw2D/u4bBKXP0SYSE/lA86jIVNWWU0gf1KODIkVvgJm2w4vH2VBV1b7ddVViGl1Iu+9zaRnv9wulhnH42KefepXnoean6UT1EzLM0opF/Ik0j+40TxdRtobkBprkQUyHDXWlHc2ffPs3SipyFEP9AVLf7ejRC46CXWDnsqjOBSMEW8Z4HiK/8RrPZBsKLts8dJxKF4pygOdJb0CWk8k/X1jbcfdxo+zOLjOMvJcSJ2pFdJmQHU+JufB3rePziqQ2S9Ur6sccr9XnTC1GVBWN4Lf5VHq+vf+bFJjVwg+2hrySZnAVfcOrxoqFLErr7ug1zN2nM1kcpgA4VWn4gxlJtYNYYq+9WxX5dtvnNANlG3ZCrRKQzl8lxtzoF6Zo7LUhEqPaHDwn7Rvs+IdbOn41lF5UDTJGqmC4gS/bZydW2Fy3YWm4aSaN9fgFf8D+PVkrlKAZB7gBLz1TyHjbcRf85cYF+GKKrDld5SzMB/V60VX3oP/Eo8ikFpyWaqiz1f9X7MBot3/PjJkY+wDzp3nmb19QEcOBuQiSQ4xds2r0HewbuHTAR68u8jNNMGmpm2j4x+g09Jd/WQDjqlTBZ/jEltH41fYCCPWMfljXTOOXu2eLNGdfi7ETZogtwjM9oTtSPQARAQABtCdMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5hdD6JAk4EEwEIADgWIQR0CqhbZGGABqoaSbdi8bhXA2EdmAUCYYPABAIbAwULCQgHAgYVCgkICwIEFgIDAQIeAQIXgAAKCRBi8bhXA2EdmM/6EADK232JCwmBzhlj8h7U9CjG6kx0JHP3uJGv+XfsHtHAlmY/RCwF1BHMEsRlk
 bT5UrLvJ2jb99bA9QARzhFaxzyn0F/BUKzuIjRGNs/n6d5dNUFA0kOt8sX+TacmC
 GEyjEBCrVCm4ranBiUyePn9NhHNWnaex7pJyqvMLLdwW9BEMJx0Fqo+DN8ukbXmYRsmhEtd3ue+x/luYmOmJnaGtzInaY5aOJYbW9XqoRIZkZvOCgbi1FfvNmoqWa+3oVxTOgw9RafjJDyW0lTHzKGjbGI5ofMU98l+/hKJFYJqWUF6VpFJY5YIcN/1lf4ZICMwDl+MPIVo/tpq8L10seJL28nLlvw3K+cI+TVW8IW/qL/LyVoDofI3USeOORuYmhpWRhik8JXX6xf3v6GrRilJIPWNFIJbxm1ZblQiQnOw3IOW7T+8nAmPin1HKqM3VrOrJQ2VtShsefNBibNAsr1oFaqcDBkn3yGG8i6CTW+FyO4PZ+/EwNxMVgktxbYdy5AT1/lpXr5tB+phhLIyVfiBvrWs5EThxYMQ/L8Y85c3GMsAy1l/x4h3jqySIYy3SCU9+jc5UVuNnXljbvkEzJ+NLWJ6C1rACFWrMszgPdh5tCrlRY9PpmYll4JbCgb8BtxEIUmR+xr50/ZElEK5iml7Q00KUekCcDt+36PsyGFTXBzNOrkCDQRhg8AEARAAzOZ2tLHlI4rrhG411h6cdCFjBZxuljaFCxFyHn3m6wbGLqwBUWC5k8UrRqjHMz88KcTSaNO7XGAmCqPdWd2SeflPZRnNTbjsVpw7mLdffsBm4JX7kki2Pvk5h0NtYeidXT1PSpc2ri4DutYXuT9uD8RAm1wUDCE5HQNUihT/WH6opt+hskHW21uHao0+y822tG0QQcGMqdQR5Vxdxj89wiEPdqW+HpU/oOZIhrf2E7prduAppxixjHy/o1rcnoznnJvc8D3+YgI9O0LrBMij89dM55pRGbLovTR1oGR3U74sX774+0xmSzeIKwZfiMUz7Atlvfk5SHOsRUFPN2Ux9kaXiiBibQpHFxt7b
 lDrT4wxdLJ/XCdbPPAyl+lZtOLsaHEEZvYNyTXwZc35dVf3R4/oz20HoG6s7ct8e1
 AQygj43XAERzty9SkWgxs8+grp1PrGx6FHVSYRqBM8dS/ZR6yRVwOwJXPyaSSqfIF21DkE4j1y4n+ItSewPGoRp8K/yWCikt6qlkVkO2ASNIiX04fAbtzwVOaNn8ZMRNqyvLc1fED4sr49onE4cAIcBLjcC3KL+w9DUGRQCdziROj5H2Yl/sXGPdMciUHo/Uz2rggc+2th3bQiMhrHWSsBpUkDQp0yWewemstPpPgBL3h2fHKaX8B9oH5Qu/H1IgrOuX8AEQEAAYkCNgQYAQgAIBYhBHQKqFtkYYAGqhpJt2LxuFcDYR2YBQJhg8AEAhsMAAoJEGLxuFcDYR2YuPwQAMkpGtR80pQ1gVsONhdkqj0H2eU66efP/gO3CoyaoIcvrpKYj7C2HipVSmkt1gpByL0X4AMQ/vKuknUz3wd28Ba+G1dCfbVs/Xiusq+SmpUj5rTwmYqdSjWMuCo1R6oS5hdJMdUUJYGMT0QkVlm1KnW8jkmCTl9GzjDxOAsN9O6/6lPzaGFtk9XF+34Bry/N4HKiJkqpC4+UTd0AprPfzJ2jdT64e1F0+W88X8y1bTTgNrHwK4mDiLnlE4SKRuEm54lNhJz//ar86Or5BErzNpM6TL7lk44QS06hwsMrEdKIy8J/SYJPjfzR8tIUnKscclVpOgjKaBqC+0iFiVaRqAgfOlIEiezX6kMh5Q2FIUfqs46qWhhXjRrdKOEoStYAaikdLu5ZXr7vfb0ZaDh+ZwTQtbSMFolyOkecwI81MCdbMfT/1TqIGTOdAj5as9fAakk0jb2pXgUYQ8X1DVTR8ahSDVEaw9VTmWiSvTxvguVJ1Mb7gG4Gmh6aviDTJhfXtH4rPUNXhDLqrTH8JkJjyKROOMakIF68Hjse5vUfUxreBEOtb5r1Coa2Fe7ncJayaSE7ryrDbFqpZ
 36UMAx4ulWMyqJajLNGY0DdG8qIsR5nxRhrnK/mrCidZ8F9/D3bWAl4rjtHlsztN59
 +AnW5l0HsQcY9ntFL/zEBOaonjdJf
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.50.1 (3.50.1-1.fc39) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/ef350902caa5bdb5a26e6152e75c9509a47acd5b.camel%40cybertec.at>
Precedence: bulk

On Tue, 2023-11-21 at 21:01 -0500, Bruce Momjian wrote:
> 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:
> > > >=20
> > > > Page: https://www.postgresql.org/docs/9.6/static/trigger-definition=
.html
> > > > Description:
> > > >=20
> > > > https://www.postgresql.org/docs/devel/static/trigger-definition.htm=
l
> > > >=20
> > > > This sentence:
> > > >=20
> > > > &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-lev=
el 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=
;
> > > >=20
> > > > is very hard to digest.
> >=20
> > 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.
> >=20
> > 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.
>=20
> I know this thread is six years old, but I still found it confusing, so
> the attached patch tries to simplify it.

I agree that the paragraph you are trying to improve needs it.

I am not sure about that last sentence you added:

  The modification of
  <varname>EXCLUDED</varname> columns has similar interactions.

How do you modify an EXCLUDED column?  Are you talking about a BEFORE
INSERT trigger?  Reading the original text, I get the impression that
it means "the behavior is obvious if you modify a column that is used
with EXCLUDED in the DO UPDATE clause, but it can also happen if that
column is not user with EXCLUDED".

Perhaps you should omit that sentence for clarity.

Yours,
Laurenz Albe