On Wed, Nov 19, 2025 at 10:08 PM Dean Rasheed <dean.a.rasheed@gmail.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/
