On Thu, Aug 31, 2017 at 09:22:22AM -0700, Peter Geoghegan wrote:
> On Thu, Aug 31, 2017 at 6:25 AM, Bruce Momjian <br...@momjian.us> wrote:
> > On Mon, Jul 3, 2017 at 08:07:10PM +0000, n...@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 <br...@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
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
