From 396a8a3f877cfb3bdd253d5483932ab3cd9ae3e4 Mon Sep 17 00:00:00 2001 From: jian he Date: Thu, 20 Nov 2025 12:52:22 +0800 Subject: [PATCH v14 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