v15-0003-extra-tests-for-ONCONFLICT_SELECT-ExecInitPartit.patch

From 396a8a3f877cfb3bdd253d5483932ab3cd9ae3e4 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 20 Nov 2025 12:52:22 +0800
Subject: [PATCH v15 3/4] extra tests for ONCONFLICT_SELECT
 ExecInitPartitionInfo & Permission tests

from Jian

discussion: https://postgr.es/m/d631b406-13b7-433e-8c0b-c6040c4b4663@Spark
---
 src/test/regress/expected/insert_conflict.out | 79 ++++++++++++++++++-
 src/test/regress/sql/insert_conflict.sql      | 39 ++++++++-
 2 files changed, 116 insertions(+), 2 deletions(-)

diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 8a4d6f540df..92d2f38aa08 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -249,6 +249,71 @@ insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key)
 insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
+----- INSERT ON CONFLICT DO SELECT PERMISSION TESTS ---
+create table conflictselect_perv(key int4, fruit text);
+create unique index x_idx on conflictselect_perv(key);
+create role regress_conflict_alice;
+grant all on schema public to regress_conflict_alice;
+grant insert on conflictselect_perv to regress_conflict_alice;
+grant select(key) on conflictselect_perv to regress_conflict_alice;
+set role regress_conflict_alice;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select where i.key = 1 returning 1; --ok
+ ?column? 
+----------
+        1
+(1 row)
+
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Apple' returning 1; --fail
+ERROR:  permission denied for table conflictselect_perv
+reset role;
+grant select(fruit) on conflictselect_perv to regress_conflict_alice;
+set role regress_conflict_alice;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Apple' returning 1; --ok
+ ?column? 
+----------
+        1
+(1 row)
+
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for update where i.fruit = 'Apple' returning 1; --fail
+ERROR:  permission denied for table conflictselect_perv
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for no key update where i.fruit = 'Apple' returning 1; --fail
+ERROR:  permission denied for table conflictselect_perv
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for share where i.fruit = 'Apple' returning 1; --fail
+ERROR:  permission denied for table conflictselect_perv
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for key share where i.fruit = 'Apple' returning 1; --fail
+ERROR:  permission denied for table conflictselect_perv
+reset role;
+grant update (fruit) on conflictselect_perv to regress_conflict_alice;
+set role regress_conflict_alice;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for update where i.fruit = 'Apple' returning *;
+ key | fruit 
+-----+-------
+   1 | Apple
+(1 row)
+
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for no key update where i.fruit = 'Apple' returning *;
+ key | fruit 
+-----+-------
+   1 | Apple
+(1 row)
+
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for share where i.fruit = 'Apple' returning *;
+ key | fruit 
+-----+-------
+   1 | Apple
+(1 row)
+
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for key share where i.fruit = 'Apple' returning *;
+ key | fruit 
+-----+-------
+   1 | Apple
+(1 row)
+
+reset role;
+drop table conflictselect_perv;
+revoke all on schema public from regress_conflict_alice;
+drop role regress_conflict_alice;
+------- END OF PERMISSION TESTS ------------
 -- DO SELECT
 delete from insertconflicttest where fruit = 'Apple';
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do select; -- fails
@@ -928,7 +993,7 @@ select * from parted_conflict_test order by a;
  2 | b
 (1 row)
 
--- now check that DO UPDATE works correctly for target partition with
+-- now check that DO UPDATE/SELECT works correctly for target partition with
 -- different attribute numbers
 create table parted_conflict_test_2 (b char, a int unique);
 alter table parted_conflict_test attach partition parted_conflict_test_2 for values in (3);
@@ -941,6 +1006,18 @@ insert into parted_conflict_test values (3, 'a') on conflict (a) do select retur
  b
 (1 row)
 
+insert into parted_conflict_test values (3, 'a') on conflict (a) do select where excluded.b = 'a' returning parted_conflict_test;
+ parted_conflict_test 
+----------------------
+ (3,b)
+(1 row)
+
+insert into parted_conflict_test values (3, 'a') on conflict (a) do select where parted_conflict_test.b = 'b' returning b;
+ b 
+---
+ b
+(1 row)
+
 -- should see (3, 'b')
 select * from parted_conflict_test order by a;
  a | b 
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 213b9fa96ab..495c193a763 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -101,6 +101,41 @@ insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
 
+----- INSERT ON CONFLICT DO SELECT PERMISSION TESTS ---
+create table conflictselect_perv(key int4, fruit text);
+create unique index x_idx on conflictselect_perv(key);
+create role regress_conflict_alice;
+grant all on schema public to regress_conflict_alice;
+grant insert on conflictselect_perv to regress_conflict_alice;
+grant select(key) on conflictselect_perv to regress_conflict_alice;
+
+set role regress_conflict_alice;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select where i.key = 1 returning 1; --ok
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Apple' returning 1; --fail
+
+reset role;
+grant select(fruit) on conflictselect_perv to regress_conflict_alice;
+set role regress_conflict_alice;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Apple' returning 1; --ok
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for update where i.fruit = 'Apple' returning 1; --fail
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for no key update where i.fruit = 'Apple' returning 1; --fail
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for share where i.fruit = 'Apple' returning 1; --fail
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for key share where i.fruit = 'Apple' returning 1; --fail
+
+reset role;
+grant update (fruit) on conflictselect_perv to regress_conflict_alice;
+set role regress_conflict_alice;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for update where i.fruit = 'Apple' returning *;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for no key update where i.fruit = 'Apple' returning *;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for share where i.fruit = 'Apple' returning *;
+insert into conflictselect_perv as i values (1, 'Apple') on conflict (key) do select for key share where i.fruit = 'Apple' returning *;
+
+reset role;
+drop table conflictselect_perv;
+revoke all on schema public from regress_conflict_alice;
+drop role regress_conflict_alice;
+------- END OF PERMISSION TESTS ------------
+
 -- DO SELECT
 delete from insertconflicttest where fruit = 'Apple';
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do select; -- fails
@@ -531,7 +566,7 @@ insert into parted_conflict_test_1 values (2, 'b') on conflict (b) do update set
 -- should see (2, 'b')
 select * from parted_conflict_test order by a;
 
--- now check that DO UPDATE works correctly for target partition with
+-- now check that DO UPDATE/SELECT works correctly for target partition with
 -- different attribute numbers
 create table parted_conflict_test_2 (b char, a int unique);
 alter table parted_conflict_test attach partition parted_conflict_test_2 for values in (3);
@@ -539,6 +574,8 @@ truncate parted_conflict_test;
 insert into parted_conflict_test values (3, 'a') on conflict (a) do update set b = excluded.b;
 insert into parted_conflict_test values (3, 'b') on conflict (a) do update set b = excluded.b;
 insert into parted_conflict_test values (3, 'a') on conflict (a) do select returning b;
+insert into parted_conflict_test values (3, 'a') on conflict (a) do select where excluded.b = 'a' returning parted_conflict_test;
+insert into parted_conflict_test values (3, 'a') on conflict (a) do select where parted_conflict_test.b = 'b' returning b;
 
 -- should see (3, 'b')
 select * from parted_conflict_test order by a;
-- 
2.48.1