Re: ON CONFLICT DO SELECT (take 3)

On Sat, Nov 15, 2025 at 5:24 AM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> On Fri, Nov 14, 2025 at 10:34 PM Viktor Holmberg <v(at)viktorh(dot)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/