Re: ON CONFLICT DO SELECT (take 3)

On Wed, Nov 19, 2025 at 10:08 PM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>
> The latest set of changes look reasonable to me, so I've squashed
> those 2 commits together and made an initial stab at writing a more
> complete commit message.
>

hi.

"Note that exclusion constraints are not supported as arbiters with ON
CONFLICT DO UPDATE."
this sentence need update?

"""
If the INSERT command contains a RETURNING clause, the result will be similar to
that of a SELECT statement containing the columns and values defined in the
RETURNING list, computed over the row(s) inserted or updated by the command.
"""
this sentence need update?

HINT: Perhaps you meant to reference the column "excluded.fruit".
-- inference fails:
-insert into insertconflicttest values (3, 'Kiwi') on conflict (key,
fruit) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (4, 'Kiwi') on conflict (key,
fruit) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON
CONFLICT specification
-insert into insertconflicttest values (4, 'Mango') on conflict
(fruit, key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (5, 'Mango') on conflict
(fruit, key) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON
CONFLICT specification
-insert into insertconflicttest values (5, 'Lemon') on conflict
(fruit) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (6, 'Lemon') on conflict
(fruit) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON
CONFLICT specification
-insert into insertconflicttest values (6, 'Passionfruit') on conflict
(lower(fruit)) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (7, 'Passionfruit') on conflict
(lower(fruit)) do update set fruit = excluded.fruit;

all these changes is not necessary, we can just add
+truncate insertconflicttest;
+insert into insertconflicttest values (1, 'Apple'), (2, 'Orange');
after line
explain (costs off) insert into insertconflicttest values (1, 'Apple')
on conflict (key) do select for key share returning *;
to make table insertconflicttest have the same content as before ON
CONFLICT DO SELECT.

it seems we didn't test ExecInitPartitionInfo related changes,
I've attached a simple test for it.

https://www.postgresql.org/docs/current/ddl-priv.html#DDL-PRIV-UPDATE
says:
```
SELECT ... FOR UPDATE and SELECT ... FOR SHARE also require this privilege on at
least one column, in addition to the SELECT privilege.
```
I attached extensive permission tests for ON CONFLICT DO SELECT

in ExecOnConflictSelect, i change it to:
```
if (!table_tuple_fetch_row_version(relation, conflictTid,
SnapshotAny, existing))
{
elog(INFO, "this part reached");
return false;
}
```
all isolation tests passed, this seems unlikely to be reachable.

--
jian
https://www.enterprisedb.com/