Re: Confusing Trigger Docs.

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.

--
Peter Geoghegan