Re: ON CONFLICT DO SELECT (take 3)

On Fri, Nov 14, 2025 at 10:34 PM Viktor Holmberg <v@viktorh.net> wrote:

Here are some updates that needed to be done after the improvements to the RLS docs / tests in 7dc4fa & 2e8424.

hi.

I see this:
https://commitfest.postgresql.org/patch/6109/
already mentioned all the related discussion links.

Could you also include the discussion link in the commit message?
currently this
spread across several email threads, adding the link would help others
understand the context.

----------------------------
create table x(key int4, fruit text);
create unique index x_idx on x(key);
create role alice;
grant all on schema public to alice;
grant insert on x to alice;
grant select(key) on x to alice;
set role alice;
explain (costs off, verbose) insert into x as i values (1, 'Apple') on
conflict (key) do select where i.fruit = 'Apple' returning 1;
explain (costs off, verbose) insert into x as i values (1, 'Apple') on
conflict (key) do select returning 1;

The above simple tests seem to show ON CONFLICT DO SELECT
permission working as intended, (I didn't try harder this time).

It looks like we currently lack permission-related regression tests
(no ``ERROR: permission denied for``),
we obviously need more.

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

On Sat, Nov 15, 2025 at 5:24 AM jian he <jian.universality@gmail.com> wrote:

On Fri, Nov 14, 2025 at 10:34 PM Viktor Holmberg <v@viktorh.net> wrote:

Here are some updates that needed to be done after the improvements to the RLS docs / tests in 7dc4fa & 2e8424.

hi.

I did some simple tests, found out that
SELECT FOR UPDATE, the lock mechanism seems to be working as intended.
We can add some tests on contrib/pgrowlocks to demonstrate that.

infer_arbiter_indexes
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("ON CONFLICT DO UPDATE not supported
with exclusion constraints")));
I guess this works for ON CONFLICT SELECT?
we can leave some comments on the function infer_arbiter_indexes,
and also add some tests on src/test/regress/sql/constraints.sql after line 570.

changing
OnConflictSetState
to
OnConflictActionState
could make it a separate patch.

all these 3 patches can be merged together, I think.
----------------------------------------
typedef struct OnConflictExpr
{
NodeTag type;
OnConflictAction action; /* DO NOTHING or UPDATE? */

"/* DO NOTHING or UPDATE? */"
this comment needs to be changed?
----------------------------------------
src/backend/rewrite/rewriteHandler.c
parsetree->onConflict->action == ONCONFLICT_UPDATE
maybe we also need to do some logic to the ONCONFLICT_SELECT
(I didn't check this part deeply)

src/test/regress/sql/updatable_views.sql, there are many occurence of
"on conflict".
I think we also need tests for ON CONFLICT DO SELECT.

CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);
CREATE VIEW rw_view15 AS SELECT a, upper(b) FROM base_tbl;
INSERT INTO rw_view15 (a) VALUES (3);
truncate base_tbl;
INSERT INTO rw_view15 (a) VALUES (3);
INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO SELECT WHERE
excluded.upper = 'UNSPECIFIED' RETURNING *;
INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE SET a =
excluded.a WHERE excluded.upper = 'UNSPECIFIED' RETURNING *;
INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO SELECT WHERE
excluded.upper = 'Unspecified' RETURNING *;
INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE SET a =
excluded.a WHERE excluded.upper = 'Unspecified' RETURNING *;

If you compare it with the result above, it seems the updatable view behaves
inconsistent with ON CONFLICT DO SELECT versus ON CONFLICT DO UPDATE.

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