Re: [DOCS] Confusing Trigger Docs.

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(at)momjian(dot)us> wrote:
> > > On Mon, Jul 3, 2017 at 08:07:10PM +0000, neil(at)fairwindsoft(dot)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.

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