On Wed, Nov 22, 2023 at 10:31:25AM +0100, Laurenz Albe wrote:
> 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.
I think I found out what it trying to say by looking at the INSERT
manual page:
Note that the effects of all per-row <literal>BEFORE
INSERT</literal> triggers are reflected in
<varname>excluded</varname> values, since those effects may
have contributed to the row being excluded from insertion.
I modified the attached patch to explain this since it is not really the
same as modifying the actual row. Does that add any value? If not,
let's remove it.
--
Bruce Momjian <[email protected]> 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..e3ec784
*** 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,160 ----
</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. Surprising outcomes are possible when
! they change rows being inserted/updated; this can happen even if
! the modifications are more or less equivalent, if they're not also
! idempotent. <literal>BEFORE</literal> <command>INSERT</command>
! triggers can also affect the value of <varname>EXCLUDED</varname>
! columns that are seen by <literal>BEFORE</literal>
! <command>UPDATE</command> triggers.
! </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
