On Wed, Nov 22, 2023 at 2:13 PM Bruce Momjian <bruce@momjian.us> wrote:
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.
There is too much exposition drowning out the main purpose here which is to explain how the dual trigger situation introduced with on conflict gets handled. The following is a more direct approach.
If an insert command contains an on conflict do update clause, before insert row triggers will be applied to the proposed row before conflict detection. If the update branch is taken, before update row triggers will also be applied. Either an insert or an update after row trigger will fire for each row. Before statement triggers fire for insertions first and then for updates, while after statement triggers fire in the reverse order, updates and then inserts. Statement triggers fire regardless if any rows were actually inserted or updated.
Tangentially, having the partition table content between this and the merge content seems odd. There also seems to be room to integrate this and merge a bit better but that is beyond what I want to try right now.