From: Bruce Momjian <bruce@momjian.us>
To: Peter Geoghegan <pg@bowt.ie>
Cc: neil@fairwindsoft.com, PostgreSQL-documentation <pgsql-docs@postgresql.org>
Cc: Andres Freund <andres@anarazel.de>
Cc: Heikki Linnakangas <heikki.linnakangas@iki.fi>
Subject: Re: [DOCS] Confusing Trigger Docs.
Date: Tue, 21 Nov 2023 21:01:12 -0500
Message-ID: <ZV1g6BsylqHAFYEL@momjian.us> (raw)
In-Reply-To: <CAH2-WzkiHHJue-DRt_zdSvKXJFUU=7CNdxkA31m1Vmyr5G2Jcg@mail.gmail.com>
References: <20170703200710.27956.64565@wrigleys.postgresql.org>
<20170831132544.GC2355@momjian.us>
<CAH2-WzkiHHJue-DRt_zdSvKXJFUU=7CNdxkA31m1Vmyr5G2Jcg@mail.gmail.com>
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.
Attachments:
[text/x-diff] excluded.diff (2.4K, ../ZV1g6BsylqHAFYEL@momjian.us/2-excluded.diff)
download | inline diff:diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgmlnew file mode 100644index 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, pg@bowt.ie, andres@anarazel.de, heikki.linnakangas@iki.fi
Subject: Re: [DOCS] Confusing Trigger Docs.
In-Reply-To: <ZV1g6BsylqHAFYEL@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox