v11-0002-ON-CONFLICT-DO-SELCT-Fixes-after-review.patch

From 77a92ccba1dba02c62cbad86b56992fdda292b17 Mon Sep 17 00:00:00 2001
From: Viktor Holmberg <v@viktorh.net>
Date: Mon, 17 Nov 2025 14:10:57 +0100
Subject: [PATCH v11 2/2] ON CONFLICT DO SELCT - Fixes after review

- updatable views fixed + tested
- comments
- (more) tests for RLS
- tests and fixes for exclusion constraints
---
 contrib/pgrowlocks/Makefile                   |  2 +-
 .../expected/on-conflict-do-select.out        | 80 +++++++++++++++++++
 contrib/pgrowlocks/meson.build                |  1 +
 .../specs/on-conflict-do-select.spec          | 39 +++++++++
 src/backend/optimizer/util/plancat.c          | 10 ++-
 src/backend/rewrite/rewriteHandler.c          | 39 ++++-----
 src/include/nodes/primnodes.h                 |  6 +-
 src/test/regress/expected/constraints.out     |  8 +-
 src/test/regress/expected/rowsecurity.out     | 50 +++++++++++-
 src/test/regress/expected/updatable_views.out | 31 +++++++
 src/test/regress/sql/constraints.sql          |  7 +-
 src/test/regress/sql/rowsecurity.sql          | 45 ++++++++++-
 src/test/regress/sql/updatable_views.sql      |  8 ++
 13 files changed, 297 insertions(+), 29 deletions(-)
 create mode 100644 contrib/pgrowlocks/expected/on-conflict-do-select.out
 create mode 100644 contrib/pgrowlocks/specs/on-conflict-do-select.spec

diff --git a/contrib/pgrowlocks/Makefile b/contrib/pgrowlocks/Makefile
index e8080646643..a1e25b101a9 100644
--- a/contrib/pgrowlocks/Makefile
+++ b/contrib/pgrowlocks/Makefile
@@ -9,7 +9,7 @@ EXTENSION = pgrowlocks
 DATA = pgrowlocks--1.2.sql pgrowlocks--1.1--1.2.sql pgrowlocks--1.0--1.1.sql
 PGFILEDESC = "pgrowlocks - display row locking information"
 
-ISOLATION = pgrowlocks
+ISOLATION = pgrowlocks on-conflict-do-select
 ISOLATION_OPTS = --load-extension=pgrowlocks
 
 ifdef USE_PGXS
diff --git a/contrib/pgrowlocks/expected/on-conflict-do-select.out b/contrib/pgrowlocks/expected/on-conflict-do-select.out
new file mode 100644
index 00000000000..0bafa556844
--- /dev/null
+++ b/contrib/pgrowlocks/expected/on-conflict-do-select.out
@@ -0,0 +1,80 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1_begin s1_doselect_nolock s2_rowlocks s1_rollback
+step s1_begin: BEGIN;
+step s1_doselect_nolock: INSERT INTO conflict_test VALUES (1, 'new') ON CONFLICT (key) DO SELECT RETURNING *;
+key|val     
+---+--------
+  1|original
+(1 row)
+
+step s2_rowlocks: SELECT locked_row, multi, modes FROM pgrowlocks('conflict_test');
+locked_row|multi|modes
+----------+-----+-----
+(0 rows)
+
+step s1_rollback: ROLLBACK;
+
+starting permutation: s1_begin s1_doselect_keyshare s2_rowlocks s1_rollback
+step s1_begin: BEGIN;
+step s1_doselect_keyshare: INSERT INTO conflict_test VALUES (1, 'new') ON CONFLICT (key) DO SELECT FOR KEY SHARE RETURNING *;
+key|val     
+---+--------
+  1|original
+(1 row)
+
+step s2_rowlocks: SELECT locked_row, multi, modes FROM pgrowlocks('conflict_test');
+locked_row|multi|modes            
+----------+-----+-----------------
+(0,1)     |f    |{"For Key Share"}
+(1 row)
+
+step s1_rollback: ROLLBACK;
+
+starting permutation: s1_begin s1_doselect_share s2_rowlocks s1_rollback
+step s1_begin: BEGIN;
+step s1_doselect_share: INSERT INTO conflict_test VALUES (1, 'new') ON CONFLICT (key) DO SELECT FOR SHARE RETURNING *;
+key|val     
+---+--------
+  1|original
+(1 row)
+
+step s2_rowlocks: SELECT locked_row, multi, modes FROM pgrowlocks('conflict_test');
+locked_row|multi|modes        
+----------+-----+-------------
+(0,1)     |f    |{"For Share"}
+(1 row)
+
+step s1_rollback: ROLLBACK;
+
+starting permutation: s1_begin s1_doselect_nokeyupd s2_rowlocks s1_rollback
+step s1_begin: BEGIN;
+step s1_doselect_nokeyupd: INSERT INTO conflict_test VALUES (1, 'new') ON CONFLICT (key) DO SELECT FOR NO KEY UPDATE RETURNING *;
+key|val     
+---+--------
+  1|original
+(1 row)
+
+step s2_rowlocks: SELECT locked_row, multi, modes FROM pgrowlocks('conflict_test');
+locked_row|multi|modes                
+----------+-----+---------------------
+(0,1)     |f    |{"For No Key Update"}
+(1 row)
+
+step s1_rollback: ROLLBACK;
+
+starting permutation: s1_begin s1_doselect_update s2_rowlocks s1_rollback
+step s1_begin: BEGIN;
+step s1_doselect_update: INSERT INTO conflict_test VALUES (1, 'new') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *;
+key|val     
+---+--------
+  1|original
+(1 row)
+
+step s2_rowlocks: SELECT locked_row, multi, modes FROM pgrowlocks('conflict_test');
+locked_row|multi|modes         
+----------+-----+--------------
+(0,1)     |f    |{"For Update"}
+(1 row)
+
+step s1_rollback: ROLLBACK;
diff --git a/contrib/pgrowlocks/meson.build b/contrib/pgrowlocks/meson.build
index 6007a76ae75..7ebeae55395 100644
--- a/contrib/pgrowlocks/meson.build
+++ b/contrib/pgrowlocks/meson.build
@@ -31,6 +31,7 @@ tests += {
   'isolation': {
     'specs': [
       'pgrowlocks',
+      'on-conflict-do-select',
     ],
     'regress_args': ['--load-extension=pgrowlocks'],
   },
diff --git a/contrib/pgrowlocks/specs/on-conflict-do-select.spec b/contrib/pgrowlocks/specs/on-conflict-do-select.spec
new file mode 100644
index 00000000000..bbd571f4c21
--- /dev/null
+++ b/contrib/pgrowlocks/specs/on-conflict-do-select.spec
@@ -0,0 +1,39 @@
+# Tests for ON CONFLICT DO SELECT with row-level locking
+
+setup
+{
+  CREATE TABLE conflict_test (key int PRIMARY KEY, val text);
+  INSERT INTO conflict_test VALUES (1, 'original');
+}
+
+teardown
+{
+  DROP TABLE conflict_test;
+}
+
+session s1
+step s1_begin { BEGIN; }
+step s1_doselect_nolock { INSERT INTO conflict_test VALUES (1, 'new') ON CONFLICT (key) DO SELECT RETURNING *; }
+step s1_doselect_keyshare { INSERT INTO conflict_test VALUES (1, 'new') ON CONFLICT (key) DO SELECT FOR KEY SHARE RETURNING *; }
+step s1_doselect_share { INSERT INTO conflict_test VALUES (1, 'new') ON CONFLICT (key) DO SELECT FOR SHARE RETURNING *; }
+step s1_doselect_nokeyupd { INSERT INTO conflict_test VALUES (1, 'new') ON CONFLICT (key) DO SELECT FOR NO KEY UPDATE RETURNING *; }
+step s1_doselect_update { INSERT INTO conflict_test VALUES (1, 'new') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; }
+step s1_rollback { ROLLBACK; }
+
+session s2
+step s2_rowlocks { SELECT locked_row, multi, modes FROM pgrowlocks('conflict_test'); }
+
+# Test 1: No locking - should not show in pgrowlocks
+permutation s1_begin s1_doselect_nolock s2_rowlocks s1_rollback
+
+# Test 2: FOR KEY SHARE - should show lock
+permutation s1_begin s1_doselect_keyshare s2_rowlocks s1_rollback
+
+# Test 3: FOR SHARE - should show lock
+permutation s1_begin s1_doselect_share s2_rowlocks s1_rollback
+
+# Test 4: FOR NO KEY UPDATE - should show lock
+permutation s1_begin s1_doselect_nokeyupd s2_rowlocks s1_rollback
+
+# Test 5: FOR UPDATE - should show lock
+permutation s1_begin s1_doselect_update s2_rowlocks s1_rollback
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index d950bd93002..0a0335fedb7 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -923,10 +923,16 @@ infer_arbiter_indexes(PlannerInfo *root)
 		 */
 		if (indexOidFromConstraint == idxForm->indexrelid)
 		{
-			if (idxForm->indisexclusion && onconflict->action == ONCONFLICT_UPDATE)
+			if (idxForm->indisexclusion &&
+				(onconflict->action == ONCONFLICT_UPDATE ||
+				 onconflict->action == ONCONFLICT_SELECT))
+				/* INSERT into an exclusion constraint can conflict with multiple rows.
+				 * So ON CONFLICT UPDATE OR SELECT would have to update/select mutliple rows
+				 * in those cases. Which seems weird - so block it with an error. */
 				ereport(ERROR,
 						(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-						 errmsg("ON CONFLICT DO UPDATE not supported with exclusion constraints")));
+						 errmsg("ON CONFLICT DO %s not supported with exclusion constraints",
+								onconflict->action == ONCONFLICT_UPDATE ? "UPDATE" : "SELECT")));
 
 			results = lappend_oid(results, idxForm->indexrelid);
 			list_free(indexList);
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index f3cd32b7222..cf91c72d40b 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3653,11 +3653,12 @@ rewriteTargetView(Query *parsetree, Relation view)
 	}
 
 	/*
-	 * For INSERT .. ON CONFLICT .. DO UPDATE, we must also update assorted
-	 * stuff in the onConflict data structure.
+	 * For INSERT .. ON CONFLICT .. DO UPDATE/SELECT, we must also update 
+	 * assorted stuff in the onConflict data structure.
 	 */
 	if (parsetree->onConflict &&
-		parsetree->onConflict->action == ONCONFLICT_UPDATE)
+		(parsetree->onConflict->action == ONCONFLICT_UPDATE ||
+		 parsetree->onConflict->action == ONCONFLICT_SELECT))
 	{
 		Index		old_exclRelIndex,
 					new_exclRelIndex;
@@ -3666,28 +3667,30 @@ rewriteTargetView(Query *parsetree, Relation view)
 		List	   *tmp_tlist;
 
 		/*
-		 * Like the INSERT/UPDATE code above, update the resnos in the
-		 * auxiliary UPDATE targetlist to refer to columns of the base
-		 * relation.
+		 * For ON CONFLICT DO UPDATE, update the resnos in the auxiliary
+		 * UPDATE targetlist to refer to columns of the base relation.
 		 */
-		foreach(lc, parsetree->onConflict->onConflictSet)
+		if (parsetree->onConflict->action == ONCONFLICT_UPDATE)
 		{
-			TargetEntry *tle = (TargetEntry *) lfirst(lc);
-			TargetEntry *view_tle;
+			foreach(lc, parsetree->onConflict->onConflictSet)
+			{
+				TargetEntry *tle = (TargetEntry *) lfirst(lc);
+				TargetEntry *view_tle;
 
-			if (tle->resjunk)
-				continue;
+				if (tle->resjunk)
+					continue;
 
-			view_tle = get_tle_by_resno(view_targetlist, tle->resno);
-			if (view_tle != NULL && !view_tle->resjunk && IsA(view_tle->expr, Var))
-				tle->resno = ((Var *) view_tle->expr)->varattno;
-			else
-				elog(ERROR, "attribute number %d not found in view targetlist",
-					 tle->resno);
+				view_tle = get_tle_by_resno(view_targetlist, tle->resno);
+				if (view_tle != NULL && !view_tle->resjunk && IsA(view_tle->expr, Var))
+					tle->resno = ((Var *) view_tle->expr)->varattno;
+				else
+					elog(ERROR, "attribute number %d not found in view targetlist",
+						 tle->resno);
+			}
 		}
 
 		/*
-		 * Also, create a new RTE for the EXCLUDED pseudo-relation, using the
+		 * Create a new RTE for the EXCLUDED pseudo-relation, using the
 		 * query's new base rel (which may well have a different column list
 		 * from the view, hence we need a new column alias list).  This should
 		 * match transformOnConflictClause.  In particular, note that the
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index d87686de000..fe9677bdf3c 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -2364,14 +2364,14 @@ typedef struct FromExpr
  *
  * The optimizer requires a list of inference elements, and optionally a WHERE
  * clause to infer a unique index.  The unique index (or, occasionally,
- * indexes) inferred are used to arbitrate whether or not the alternative ON
- * CONFLICT path is taken.
+ * indexes) inferred are used to arbitrate whether or not the alternative
+ * ON CONFLICT path is taken.
  *----------
  */
 typedef struct OnConflictExpr
 {
 	NodeTag		type;
-	OnConflictAction action;	/* DO NOTHING or UPDATE? */
+	OnConflictAction action;	/* NONE, DO NOTHING, DO UPDATE, DO SELECT ? */
 
 	/* Arbiter */
 	List	   *arbiterElems;	/* unique index arbiter list (of
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 1bbf59cca02..8bc1f0cd5ab 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -776,10 +776,16 @@ DETAIL:  Key (c1, (c2::circle))=(<(20,20),10>, <(0,0),4>) conflicts with existin
 -- succeed, because violation is ignored
 INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>')
   ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO NOTHING;
--- fail, because DO UPDATE variant requires unique index
+-- fail, because DO UPDATE variant requires unique index.
+-- (without a unique index, we can't know which row to update)
 INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>')
   ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO UPDATE SET c2 = EXCLUDED.c2;
 ERROR:  ON CONFLICT DO UPDATE not supported with exclusion constraints
+-- fail, just like DO UPDATE.
+-- otherwise, we could return multiple rows which seems odd, if not exactly wrong
+INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>')
+  ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO SELECT RETURNING *;
+ERROR:  ON CONFLICT DO SELECT not supported with exclusion constraints
 -- succeed because c1 doesn't overlap
 INSERT INTO circles VALUES('<(20,20), 1>', '<(0,0), 5>');
 -- succeed because c2 doesn't overlap
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index a01a2c883fd..d6a2be1f96e 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -2436,10 +2436,58 @@ INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel')
     ON CONFLICT (did) DO UPDATE SET dauthor = 'regress_rls_carol';
 ERROR:  new row violates row-level security policy for table "document"
 --
+-- INSERT ... ON CONFLICT DO SELECT and Row-level security
+--
+SET SESSION AUTHORIZATION regress_rls_alice;
+DROP POLICY p3_with_all ON document;
+CREATE POLICY p1_select_novels ON document FOR SELECT
+  USING (cid = (SELECT cid from category WHERE cname = 'novel'));
+CREATE POLICY p2_insert_own ON document FOR INSERT
+  WITH CHECK (dauthor = current_user);
+CREATE POLICY p3_update_novels ON document FOR UPDATE
+  USING (cid = (SELECT cid from category WHERE cname = 'novel'))
+  WITH CHECK (dauthor = current_user);
+SET SESSION AUTHORIZATION regress_rls_bob;
+-- DO SELECT requires SELECT rights, should succeed for novel
+INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel')
+    ON CONFLICT (did) DO SELECT RETURNING did, dauthor, dtitle;
+ did |     dauthor     |     dtitle     
+-----+-----------------+----------------
+   1 | regress_rls_bob | my first novel
+(1 row)
+
+-- DO SELECT requires SELECT rights, should fail for non-novel
+INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'science fiction'), 1, 'regress_rls_bob', 'another sci-fi')
+    ON CONFLICT (did) DO SELECT RETURNING did, dauthor, dtitle;
+ERROR:  new row violates row-level security policy for table "document"
+-- DO SELECT with WHERE and EXCLUDED reference
+INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel')
+    ON CONFLICT (did) DO SELECT WHERE excluded.dlevel = 1 RETURNING did, dauthor, dtitle;
+ did |     dauthor     |     dtitle     
+-----+-----------------+----------------
+   1 | regress_rls_bob | my first novel
+(1 row)
+
+-- DO SELECT FOR UPDATE requires both SELECT and UPDATE rights, should succeed for novel
+INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel')
+    ON CONFLICT (did) DO SELECT FOR UPDATE RETURNING did, dauthor, dtitle;
+ did |     dauthor     |     dtitle     
+-----+-----------------+----------------
+   1 | regress_rls_bob | my first novel
+(1 row)
+
+-- DO SELECT FOR UPDATE requires UPDATE rights, should fail for non-novel
+INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'science fiction'), 1, 'regress_rls_bob', 'another sci-fi')
+    ON CONFLICT (did) DO SELECT FOR UPDATE RETURNING did, dauthor, dtitle;
+ERROR:  new row violates row-level security policy for table "document"
+SET SESSION AUTHORIZATION regress_rls_alice;
+DROP POLICY p1_select_novels ON document;
+DROP POLICY p2_insert_own ON document;
+DROP POLICY p3_update_novels ON document;
+--
 -- MERGE
 --
 RESET SESSION AUTHORIZATION;
-DROP POLICY p3_with_all ON document;
 ALTER TABLE document ADD COLUMN dnotes text DEFAULT '';
 -- all documents are readable
 CREATE POLICY p1 ON document FOR SELECT USING (true);
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 03df7e75b7b..a3c811effc8 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -316,6 +316,37 @@ SELECT * FROM rw_view15;
   3 | UNSPECIFIED
 (6 rows)
 
+-- Test ON CONFLICT DO SELECT with updatable views
+-- This tests behavior consistency between DO SELECT and DO UPDATE when using WHERE clauses
+-- Note: rw_view15 is defined as "SELECT a, upper(b) FROM base_tbl" where base_tbl.b has DEFAULT 'Unspecified'
+INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO SELECT RETURNING *; -- needs RETURNING, should return existing row
+ a |    upper    
+---+-------------
+ 3 | UNSPECIFIED
+(1 row)
+
+INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO SELECT WHERE excluded.upper = 'UNSPECIFIED' RETURNING *; -- WHERE on view column (uppercase)
+ a |    upper    
+---+-------------
+ 3 | UNSPECIFIED
+(1 row)
+
+INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE SET a = excluded.a WHERE excluded.upper = 'UNSPECIFIED' RETURNING *; -- compare DO UPDATE with same WHERE
+ a |    upper    
+---+-------------
+ 3 | UNSPECIFIED
+(1 row)
+
+INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO SELECT WHERE excluded.upper = 'Unspecified' RETURNING *; -- WHERE on excluded value (mixed case)
+ a | upper 
+---+-------
+(0 rows)
+
+INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE SET a = excluded.a WHERE excluded.upper = 'Unspecified' RETURNING *; -- compare DO UPDATE with same WHERE
+ a | upper 
+---+-------
+(0 rows)
+
 SELECT * FROM rw_view15;
  a  |    upper    
 ----+-------------
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 733a1dbccfe..b093e92850f 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -565,9 +565,14 @@ INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>');
 -- succeed, because violation is ignored
 INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>')
   ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO NOTHING;
--- fail, because DO UPDATE variant requires unique index
+-- fail, because DO UPDATE variant requires unique index.
+-- (without a unique index, we can't know which row to update)
 INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>')
   ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO UPDATE SET c2 = EXCLUDED.c2;
+-- fail, just like DO UPDATE.
+-- otherwise, we could return multiple rows which seems odd, if not exactly wrong
+INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>')
+  ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO SELECT RETURNING *;
 -- succeed because c1 doesn't overlap
 INSERT INTO circles VALUES('<(20,20), 1>', '<(0,0), 5>');
 -- succeed because c2 doesn't overlap
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index e5b78810e69..9d3c4f21b17 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -966,11 +966,52 @@ INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel')
     ON CONFLICT (did) DO UPDATE SET dauthor = 'regress_rls_carol';
 
 --
--- MERGE
+-- INSERT ... ON CONFLICT DO SELECT and Row-level security
 --
-RESET SESSION AUTHORIZATION;
+
+SET SESSION AUTHORIZATION regress_rls_alice;
 DROP POLICY p3_with_all ON document;
 
+CREATE POLICY p1_select_novels ON document FOR SELECT
+  USING (cid = (SELECT cid from category WHERE cname = 'novel'));
+CREATE POLICY p2_insert_own ON document FOR INSERT
+  WITH CHECK (dauthor = current_user);
+CREATE POLICY p3_update_novels ON document FOR UPDATE
+  USING (cid = (SELECT cid from category WHERE cname = 'novel'))
+  WITH CHECK (dauthor = current_user);
+
+SET SESSION AUTHORIZATION regress_rls_bob;
+
+-- DO SELECT requires SELECT rights, should succeed for novel
+INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel')
+    ON CONFLICT (did) DO SELECT RETURNING did, dauthor, dtitle;
+
+-- DO SELECT requires SELECT rights, should fail for non-novel
+INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'science fiction'), 1, 'regress_rls_bob', 'another sci-fi')
+    ON CONFLICT (did) DO SELECT RETURNING did, dauthor, dtitle;
+
+-- DO SELECT with WHERE and EXCLUDED reference
+INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel')
+    ON CONFLICT (did) DO SELECT WHERE excluded.dlevel = 1 RETURNING did, dauthor, dtitle;
+
+-- DO SELECT FOR UPDATE requires both SELECT and UPDATE rights, should succeed for novel
+INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'another novel')
+    ON CONFLICT (did) DO SELECT FOR UPDATE RETURNING did, dauthor, dtitle;
+
+-- DO SELECT FOR UPDATE requires UPDATE rights, should fail for non-novel
+INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'science fiction'), 1, 'regress_rls_bob', 'another sci-fi')
+    ON CONFLICT (did) DO SELECT FOR UPDATE RETURNING did, dauthor, dtitle;
+
+SET SESSION AUTHORIZATION regress_rls_alice;
+DROP POLICY p1_select_novels ON document;
+DROP POLICY p2_insert_own ON document;
+DROP POLICY p3_update_novels ON document;
+
+
+--
+-- MERGE
+--
+RESET SESSION AUTHORIZATION;
 
 ALTER TABLE document ADD COLUMN dnotes text DEFAULT '';
 -- all documents are readable
diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql
index c071fffc116..d9f1ca5bd97 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -106,6 +106,14 @@ INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set a = excluded.
 SELECT * FROM rw_view15;
 INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set upper = 'blarg'; -- fails
 SELECT * FROM rw_view15;
+-- Test ON CONFLICT DO SELECT with updatable views
+-- This tests behavior consistency between DO SELECT and DO UPDATE when using WHERE clauses
+-- Note: rw_view15 is defined as "SELECT a, upper(b) FROM base_tbl" where base_tbl.b has DEFAULT 'Unspecified'
+INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO SELECT RETURNING *; -- needs RETURNING, should return existing row
+INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO SELECT WHERE excluded.upper = 'UNSPECIFIED' RETURNING *; -- WHERE on view column (uppercase)
+INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE SET a = excluded.a WHERE excluded.upper = 'UNSPECIFIED' RETURNING *; -- compare DO UPDATE with same WHERE
+INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO SELECT WHERE excluded.upper = 'Unspecified' RETURNING *; -- WHERE on excluded value (mixed case)
+INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE SET a = excluded.a WHERE excluded.upper = 'Unspecified' RETURNING *; -- compare DO UPDATE with same WHERE
 SELECT * FROM rw_view15;
 ALTER VIEW rw_view15 ALTER COLUMN upper SET DEFAULT 'NOT SET';
 INSERT INTO rw_view15 (a) VALUES (4); -- should fail
-- 
2.48.1