Re: ON CONFLICT DO SELECT (take 3)

On 4 Feb 2026 at 11:23 +0100, jian he <jian(dot)universality(at)gmail(dot)com>, wrote:
> hi.
>
> v22 looks good to me.
> I have marked this as "Ready for Committer".
Thanks! Appreciate the reviews!
>
> + <para>
> + <command>INSERT</command> with an <literal>ON CONFLICT DO
> + SELECT</literal> clause behaves similarly to <literal>ON CONFLICT DO
> + UPDATE</literal>. In Read Committed mode, if a conflict originates
> + in another transaction whose effects are not yet visible to the
> + <command>INSERT</command>, the <literal>SELECT</literal> clause will
> + return that row, even though possibly <emphasis>no</emphasis> version
> + of that row is conventionally visible to the command.
> + </para>
> +
> I think I understand the above paragraph.
>
> however the preceding paragraph (doc/src/sgml/mvcc.sgml), we have:
> """
> In Read Committed mode, each row proposed for insertion
> will either insert or update. Unless there are unrelated errors, one of
> those two outcomes is guaranteed.
> """
> Maybe we can write something similar for INSERT ON CONFLICT?
The wording was quite confusing and overly theoretical. I’ve re-written it to try and explain it a more plainly:

"
INSERT with an ON CONFLICT DO SELECT clause behaves similarly to ON CONFLICT DO UPDATE. In Read Committed mode, each row proposed for insertion is guaranteed to either insert or return the conflicting row (unless there are unrelated errors). If a conflict originates in another transaction whose effects are not yet visible to the INSERT, the command will wait for that transaction to commit or roll back, then return the conflicting row if it was committed (even though that row was not visible when the command started).
"

That change is squashed into v23. I was thinking about also updating the DO UPDATE paragraph but decided against it.