v16-0002-ON-CONFLICT-DO-SELECT-misc-fix.patch

From d180b13175c8f22c295bd7a28544371b21649d00 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 25 Nov 2025 16:23:46 +0800
Subject: [PATCH v16 2/2] ON CONFLICT DO SELECT misc fix white space also
 fixed.

based on https://postgr.es/m/9284d41a-57a6-4a37-ac9f-873cb5c509d4@Spark
---
 doc/src/sgml/ref/create_policy.sgml           |  2 +-
 src/backend/executor/nodeModifyTable.c        | 13 ++-
 src/test/modules/injection_points/Makefile    |  3 +-
 .../expected/onconflictdoselect.out           | 93 +++++++++++++++++++
 src/test/modules/injection_points/meson.build |  1 +
 .../specs/onconflictdoselect.spec             | 62 +++++++++++++
 src/test/regress/expected/rowsecurity.out     |  2 +-
 src/test/regress/expected/triggers.out        | 13 +++
 src/test/regress/sql/triggers.sql             |  2 +
 9 files changed, 185 insertions(+), 6 deletions(-)
 create mode 100644 src/test/modules/injection_points/expected/onconflictdoselect.out
 create mode 100644 src/test/modules/injection_points/specs/onconflictdoselect.spec

diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml
index e798eacfb42..c1510e212c0 100644
--- a/doc/src/sgml/ref/create_policy.sgml
+++ b/doc/src/sgml/ref/create_policy.sgml
@@ -584,7 +584,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
        <entry><command>ON CONFLICT DO SELECT FOR UPDATE/SHARE</command></entry>
        <entry>Check existing row</entry>
        <entry>&mdash;</entry>
-       <entry>Existing row</entry>
+       <entry>Check existing row</entry>
        <entry>&mdash;</entry>
        <entry>&mdash;</entry>
       </row>
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 9d3cd430084..926359a24f4 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -3050,12 +3050,21 @@ ExecOnConflictSelect(ModifyTableContext *context,
 				lockmode = LockTupleExclusive;
 				break;
 			default:
+				lockmode = LockTupleExclusive;
 				elog(ERROR, "unexpected lock strength %d", lockStrength);
+				break;
 		}
 
+		INJECTION_POINT("exec-onconflictselect-before-lockrow", NULL);
+
 		if (!ExecOnConflictLockRow(context, existing, conflictTid,
 								   resultRelInfo->ri_RelationDesc, lockmode, false))
+		{
+			INJECTION_POINT("exec-onconflictselect-after-lockrow", NULL);
+			elog(INFO, "this part is reached");
+
 			return false;
+		}
 	}
 
 	/*
@@ -3099,9 +3108,7 @@ ExecOnConflictSelect(ModifyTableContext *context,
 							 mtstate->ps.state);
 	}
 
-	/* Parse analysis should already have disallowed this, as RETURNING
-	 * is required for DO SELECT.
-	 */
+	/* RETURNING is required for DO SELECT */
 	Assert(resultRelInfo->ri_projectReturning);
 
 	*rslot = ExecProcessReturning(context, resultRelInfo, CMD_INSERT,
diff --git a/src/test/modules/injection_points/Makefile b/src/test/modules/injection_points/Makefile
index 7b3c0c4b716..52559975dc0 100644
--- a/src/test/modules/injection_points/Makefile
+++ b/src/test/modules/injection_points/Makefile
@@ -19,7 +19,8 @@ ISOLATION = basic \
 	    syscache-update-pruned \
 	    index-concurrently-upsert \
 	    reindex-concurrently-upsert \
-	    index-concurrently-upsert-predicate
+	    index-concurrently-upsert-predicate \
+		onconflictdoselect
 
 TAP_TESTS = 1
 
diff --git a/src/test/modules/injection_points/expected/onconflictdoselect.out b/src/test/modules/injection_points/expected/onconflictdoselect.out
new file mode 100644
index 00000000000..c51b8981f9e
--- /dev/null
+++ b/src/test/modules/injection_points/expected/onconflictdoselect.out
@@ -0,0 +1,93 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1_start_upsert s2_update s3_wakeup_s1_before s3_wakeup_s1_after
+injection_points_attach
+-----------------------
+                       
+(1 row)
+
+step s1_start_upsert: 
+    INSERT INTO test.tbl VALUES (13, 100) ON CONFLICT (i) DO SELECT FOR UPDATE RETURNING OLD.*, NEW.*;
+ <waiting ...>
+step s2_update: UPDATE test.tbl SET i = 14 WHERE i = 13;
+step s3_wakeup_s1_before: 
+	SELECT injection_points_detach('exec-onconflictselect-before-lockrow');
+	SELECT injection_points_wakeup('exec-onconflictselect-before-lockrow');
+
+injection_points_detach
+-----------------------
+                       
+(1 row)
+
+injection_points_wakeup
+-----------------------
+                       
+(1 row)
+
+step s3_wakeup_s1_after: 
+	SELECT injection_points_detach('exec-onconflictselect-after-lockrow');
+	SELECT injection_points_wakeup('exec-onconflictselect-after-lockrow');
+
+injection_points_detach
+-----------------------
+                       
+(1 row)
+
+injection_points_wakeup
+-----------------------
+                       
+(1 row)
+
+s1: INFO:  this part is reached
+step s1_start_upsert: <... completed>
+i|b| i|  b
+-+-+--+---
+ | |13|100
+(1 row)
+
+
+starting permutation: s1_start_upsert s2_delete s3_wakeup_s1_before s3_wakeup_s1_after
+injection_points_attach
+-----------------------
+                       
+(1 row)
+
+step s1_start_upsert: 
+    INSERT INTO test.tbl VALUES (13, 100) ON CONFLICT (i) DO SELECT FOR UPDATE RETURNING OLD.*, NEW.*;
+ <waiting ...>
+step s2_delete: DELETE FROM test.tbl WHERE i = 13;
+step s3_wakeup_s1_before: 
+	SELECT injection_points_detach('exec-onconflictselect-before-lockrow');
+	SELECT injection_points_wakeup('exec-onconflictselect-before-lockrow');
+
+injection_points_detach
+-----------------------
+                       
+(1 row)
+
+injection_points_wakeup
+-----------------------
+                       
+(1 row)
+
+step s3_wakeup_s1_after: 
+	SELECT injection_points_detach('exec-onconflictselect-after-lockrow');
+	SELECT injection_points_wakeup('exec-onconflictselect-after-lockrow');
+
+injection_points_detach
+-----------------------
+                       
+(1 row)
+
+injection_points_wakeup
+-----------------------
+                       
+(1 row)
+
+s1: INFO:  this part is reached
+step s1_start_upsert: <... completed>
+i|b| i|  b
+-+-+--+---
+ | |13|100
+(1 row)
+
diff --git a/src/test/modules/injection_points/meson.build b/src/test/modules/injection_points/meson.build
index 485b483e3ca..1f6c5c11c95 100644
--- a/src/test/modules/injection_points/meson.build
+++ b/src/test/modules/injection_points/meson.build
@@ -51,6 +51,7 @@ tests += {
       'index-concurrently-upsert',
       'reindex-concurrently-upsert',
       'index-concurrently-upsert-predicate',
+      'onconflictdoselect',
     ],
     'runningcheck': false, # see syscache-update-pruned
     # Some tests wait for all snapshots, so avoid parallel execution
diff --git a/src/test/modules/injection_points/specs/onconflictdoselect.spec b/src/test/modules/injection_points/specs/onconflictdoselect.spec
new file mode 100644
index 00000000000..af56a6b10db
--- /dev/null
+++ b/src/test/modules/injection_points/specs/onconflictdoselect.spec
@@ -0,0 +1,62 @@
+# This test verifies INSERT ON CONFLICT DO SELECT behavior concurrent with
+# DELETE/UPDATE.
+#
+# - s1: ON CONFLICT DO SELECT a tuple
+# - s2: UPDATE the same tuple
+# - s3: controls concurrency via injection points
+
+setup
+{
+	CREATE EXTENSION injection_points;
+	CREATE SCHEMA test;
+	CREATE UNLOGGED TABLE test.tbl(i int primary key, b int);
+	ALTER TABLE test.tbl SET (parallel_workers=0);
+    INSERT INTO test.tbl VALUES (13, 14);
+}
+
+teardown
+{
+	DROP SCHEMA test CASCADE;
+	DROP EXTENSION injection_points;
+}
+
+session s1
+setup
+{
+	SELECT injection_points_set_local();
+	SELECT injection_points_attach('exec-onconflictselect-before-lockrow', 'wait');
+	SELECT injection_points_attach('exec-onconflictselect-after-lockrow', 'wait');
+}
+step s1_start_upsert
+{
+    INSERT INTO test.tbl VALUES (13, 100) ON CONFLICT (i) DO SELECT FOR UPDATE RETURNING OLD.*, NEW.*;
+}
+
+session s2
+step s2_update  { UPDATE test.tbl SET i = 14 WHERE i = 13; }
+step s2_delete  { DELETE FROM test.tbl WHERE i = 13; }
+
+session s3
+step s3_wakeup_s1_before
+{
+	SELECT injection_points_detach('exec-onconflictselect-before-lockrow');
+	SELECT injection_points_wakeup('exec-onconflictselect-before-lockrow');
+}
+
+step s3_wakeup_s1_after
+{
+	SELECT injection_points_detach('exec-onconflictselect-after-lockrow');
+	SELECT injection_points_wakeup('exec-onconflictselect-after-lockrow');
+}
+
+permutation
+    s1_start_upsert
+    s2_update
+    s3_wakeup_s1_before
+    s3_wakeup_s1_after
+
+permutation
+    s1_start_upsert
+    s2_delete
+    s3_wakeup_s1_before
+    s3_wakeup_s1_after
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index d6a2be1f96e..e45031f7391 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -218,7 +218,7 @@ NOTICE:  SELECT USING on rls_test_tgt.(3,"tgt d","TGT D")
 (1 row)
 
 ROLLBACK;
--- ON CONFLICT DO SELECT should be similar to DO UPDATE, except there 
+-- ON CONFLICT DO SELECT should be similar to DO UPDATE, except there
 -- is not need to check the UPDATE policy in that case.
 BEGIN;
 INSERT INTO rls_test_tgt VALUES (4, 'tgt a') ON CONFLICT (a) DO SELECT RETURNING *;
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 1eb8fba0953..98e56ecaef8 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -1745,6 +1745,19 @@ insert into upsert values(8, 'yellow') on conflict (key) do update set color = '
 WARNING:  before insert (new): (8,yellow)
 WARNING:  before insert (new, modified): (9,"yellow trig modified")
 WARNING:  after insert (new): (9,"yellow trig modified")
+insert into upsert values(3, 'orange') on conflict (key) do select for update returning old.*, new.*, upsert.*;
+WARNING:  before insert (new): (3,orange)
+ key |           color           | key |           color           | key |           color           
+-----+---------------------------+-----+---------------------------+-----+---------------------------
+   3 | updated red trig modified |   3 | updated red trig modified |   3 | updated red trig modified
+(1 row)
+
+insert into upsert values(3, 'orange') on conflict (key) do select for update where upsert.key = 4 returning old.*, new.*, upsert.*;
+WARNING:  before insert (new): (3,orange)
+ key | color | key | color | key | color 
+-----+-------+-----+-------+-----+-------
+(0 rows)
+
 select * from upsert;
  key |            color            
 -----+-----------------------------
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 5f7f75d7ba5..ee451ec7ed3 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -1197,6 +1197,8 @@ insert into upsert values(5, 'purple') on conflict (key) do update set color = '
 insert into upsert values(6, 'white') on conflict (key) do update set color = 'updated ' || upsert.color;
 insert into upsert values(7, 'pink') on conflict (key) do update set color = 'updated ' || upsert.color;
 insert into upsert values(8, 'yellow') on conflict (key) do update set color = 'updated ' || upsert.color;
+insert into upsert values(3, 'orange') on conflict (key) do select for update returning old.*, new.*, upsert.*;
+insert into upsert values(3, 'orange') on conflict (key) do select for update where upsert.key = 4 returning old.*, new.*, upsert.*;
 
 select * from upsert;
 
-- 
2.34.1