On Wed, Nov 19, 2025 at 10:08 PM Dean Rasheed <[email protected]> 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/
v12-0001-regress-tests-for-ONCONFLICT_SELECT-ExecInitP.no-cfbot
Description: Binary data
v12-0002-permission-tests-for-ON-CONFLICT-DO-SELECT.no-cfbot
Description: Binary data
