From f8db3ac3d6cada103876f2f6c1f5194c1effb0c2 Mon Sep 17 00:00:00 2001 From: jian he Date: Mon, 19 Jan 2026 11:45:15 +0800 Subject: [PATCH v19 1/1] ON CONFLICT DO SELECT tests on updatable_views.sql discussion: https://postgr.es/m/ --- src/test/regress/expected/updatable_views.out | 74 +++++++++++++++++-- src/test/regress/sql/updatable_views.sql | 33 +++++++-- 2 files changed, 95 insertions(+), 12 deletions(-) diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 4aa9c29ce2b..134e995c9d2 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -328,7 +328,7 @@ DO SELECT RETURNING *; -- needs RETURNING, should return existing row -- WHERE on view column (uppercase) INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) -DO SELECT WHERE excluded.upper = 'UNSPECIFIED' RETURNING *; +DO SELECT WHERE excluded.upper = 'UNSPECIFIED' RETURNING *; a | upper ---+------------- 3 | UNSPECIFIED @@ -336,7 +336,7 @@ DO SELECT WHERE excluded.upper = 'UNSPECIFIED' RETURNING *; -- compare DO UPDATE with same WHERE INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) -DO UPDATE SET a = excluded.a WHERE excluded.upper = 'UNSPECIFIED' RETURNING *; +DO UPDATE SET a = excluded.a WHERE excluded.upper = 'UNSPECIFIED' RETURNING *; a | upper ---+------------- 3 | UNSPECIFIED @@ -344,14 +344,14 @@ DO UPDATE SET a = excluded.a WHERE excluded.upper = 'UNSPECIFIED' RETURNING *; -- WHERE on excluded value (mixed case) INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) -DO SELECT WHERE excluded.upper = 'Unspecified' RETURNING *; +DO SELECT WHERE excluded.upper = 'Unspecified' RETURNING *; a | upper ---+------- (0 rows) -- compare DO UPDATE with same WHERE INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) -DO UPDATE SET a = excluded.a WHERE excluded.upper = 'Unspecified' RETURNING *; +DO UPDATE SET a = excluded.a WHERE excluded.upper = 'Unspecified' RETURNING *; a | upper ---+------- (0 rows) @@ -3686,7 +3686,7 @@ ERROR: new row violates check option for view "wcowrtest_v2" DETAIL: Failing row contains (2, no such row in sometable). drop view wcowrtest_v, wcowrtest_v2; drop table wcowrtest, sometable; --- Check INSERT .. ON CONFLICT DO UPDATE works correctly when the view's +-- Check INSERT .. ON CONFLICT DO SELECT/UPDATE works correctly when the view's -- columns are named and ordered differently than the underlying table's. create table uv_iocu_tab (a text unique, b float); insert into uv_iocu_tab values ('xyxyxy', 0); @@ -3708,6 +3708,13 @@ select * from uv_iocu_tab; xyxyxy | 1 (1 row) +insert into uv_iocu_view (a, b) values ('xyxyxy', 1) + on conflict (a) do select returning *; + b | c | a | two +---+---+--------+----- + 1 | 2 | xyxyxy | 2.0 +(1 row) + -- OK to access view columns that are not present in underlying base -- relation in the ON CONFLICT portion of the query insert into uv_iocu_view (a, b) values ('xyxyxy', 3) @@ -3771,6 +3778,24 @@ select * from uv_iocu_view; Rejected: (y,1,"(1,y)") | 1 | (1,"Rejected: (y,1,""(1,y)"")") (1 row) +explain (costs off) +insert into uv_iocu_view (aa,bb) values (1,'y') + on conflict (aa) do select returning *; + QUERY PLAN +----------------------------------------------- + Insert on uv_iocu_tab + Conflict Resolution: SELECT + Conflict Arbiter Indexes: uv_iocu_tab_a_key + -> Result +(4 rows) + +insert into uv_iocu_view (aa,bb) values (1,'y') + on conflict (aa) do select returning *; + bb | aa | cc +-------------------------+----+--------------------------------- + Rejected: (y,1,"(1,y)") | 1 | (1,"Rejected: (y,1,""(1,y)"")") +(1 row) + -- Test omitting a column of the base relation delete from uv_iocu_view; insert into uv_iocu_view (aa,bb) values (1,'x'); @@ -3791,6 +3816,13 @@ select * from uv_iocu_view; Rejected: ("table default",1,"(1,""table default"")") | 1 | (1,"Rejected: (""table default"",1,""(1,""""table default"""")"")") (1 row) +insert into uv_iocu_view (aa) values (1) + on conflict (aa) do select returning *; + bb | aa | cc +-------------------------------------------------------+----+--------------------------------------------------------------------- + Rejected: ("table default",1,"(1,""table default"")") | 1 | (1,"Rejected: (""table default"",1,""(1,""""table default"""")"")") +(1 row) + alter view uv_iocu_view alter column bb set default 'view default'; insert into uv_iocu_view (aa) values (1) on conflict (aa) do update set bb = 'Rejected: '||excluded.*; @@ -3800,6 +3832,13 @@ select * from uv_iocu_view; Rejected: ("view default",1,"(1,""view default"")") | 1 | (1,"Rejected: (""view default"",1,""(1,""""view default"""")"")") (1 row) +insert into uv_iocu_view (aa) values (1) + on conflict (aa) do select returning *; + bb | aa | cc +-----------------------------------------------------+----+------------------------------------------------------------------- + Rejected: ("view default",1,"(1,""view default"")") | 1 | (1,"Rejected: (""view default"",1,""(1,""""view default"""")"")") +(1 row) + -- Should fail to update non-updatable columns insert into uv_iocu_view (aa) values (1) on conflict (aa) do update set cc = 'XXX'; @@ -3807,7 +3846,7 @@ ERROR: cannot insert into column "cc" of view "uv_iocu_view" DETAIL: View columns that are not columns of their base relation are not updatable. drop view uv_iocu_view; drop table uv_iocu_tab; --- ON CONFLICT DO UPDATE permissions checks +-- ON CONFLICT DO SELECT/UPDATE permissions checks create user regress_view_user1; create user regress_view_user2; set session authorization regress_view_user1; @@ -3831,6 +3870,16 @@ insert into rw_view1 values ('zzz',2.0,1) insert into rw_view1 values ('zzz',2.0,1) on conflict (aa) do update set cc = 3.0; -- Not allowed ERROR: permission denied for view rw_view1 +insert into rw_view1 values ('yyy',2.0,1) + on conflict (aa) do select for update returning cc; -- Not allowed +ERROR: permission denied for view rw_view1 +insert into rw_view1 values ('yyy',2.0,1) + on conflict (aa) do select for update returning aa, bb; + aa | bb +----+-------- + 1 | yyyxxx +(1 row) + reset session authorization; select * from base_tbl; a | b | c @@ -3847,9 +3896,19 @@ create view rw_view2 as select b as bb, c as cc, a as aa from base_tbl; insert into rw_view2 (aa,bb) values (1,'xxx') on conflict (aa) do update set bb = excluded.bb; -- Not allowed ERROR: permission denied for table base_tbl +insert into rw_view2 (aa,bb) values (1,'xxx') + on conflict (aa) do select returning 1; -- Not allowed +ERROR: permission denied for table base_tbl create view rw_view3 as select b as bb, a as aa from base_tbl; insert into rw_view3 (aa,bb) values (1,'xxx') on conflict (aa) do update set bb = excluded.bb; -- OK +insert into rw_view3 (aa,bb) values (1,'xxx') + on conflict (aa) do select returning aa, bb; -- OK + aa | bb +----+----- + 1 | xxx +(1 row) + reset session authorization; select * from base_tbl; a | b | c @@ -3862,6 +3921,9 @@ create view rw_view4 as select aa, bb, cc FROM rw_view1; insert into rw_view4 (aa,bb) values (1,'yyy') on conflict (aa) do update set bb = excluded.bb; -- Not allowed ERROR: permission denied for view rw_view1 +insert into rw_view4 (aa,bb) values (1,'yyy') + on conflict (aa) do select returning 1; -- Not allowed +ERROR: permission denied for view rw_view1 create view rw_view5 as select aa, bb FROM rw_view1; insert into rw_view5 (aa,bb) values (1,'yyy') on conflict (aa) do update set bb = excluded.bb; -- OK diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index 323f661b3ec..e73cbac1ae1 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -114,19 +114,19 @@ DO SELECT RETURNING *; -- needs RETURNING, should return existing row -- WHERE on view column (uppercase) INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) -DO SELECT WHERE excluded.upper = 'UNSPECIFIED' RETURNING *; +DO SELECT WHERE excluded.upper = 'UNSPECIFIED' RETURNING *; -- compare DO UPDATE with same WHERE INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) -DO UPDATE SET a = excluded.a WHERE excluded.upper = 'UNSPECIFIED' RETURNING *; +DO UPDATE SET a = excluded.a WHERE excluded.upper = 'UNSPECIFIED' RETURNING *; -- WHERE on excluded value (mixed case) INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) -DO SELECT WHERE excluded.upper = 'Unspecified' RETURNING *; +DO SELECT WHERE excluded.upper = 'Unspecified' RETURNING *; -- compare DO UPDATE with same WHERE INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) -DO UPDATE SET a = excluded.a WHERE excluded.upper = 'Unspecified' RETURNING *; +DO UPDATE SET a = excluded.a WHERE excluded.upper = 'Unspecified' RETURNING *; SELECT * FROM rw_view15; ALTER VIEW rw_view15 ALTER COLUMN upper SET DEFAULT 'NOT SET'; @@ -1872,7 +1872,7 @@ insert into wcowrtest_v2 values (2, 'no such row in sometable'); drop view wcowrtest_v, wcowrtest_v2; drop table wcowrtest, sometable; --- Check INSERT .. ON CONFLICT DO UPDATE works correctly when the view's +-- Check INSERT .. ON CONFLICT DO SELECT/UPDATE works correctly when the view's -- columns are named and ordered differently than the underlying table's. create table uv_iocu_tab (a text unique, b float); insert into uv_iocu_tab values ('xyxyxy', 0); @@ -1885,6 +1885,8 @@ select * from uv_iocu_tab; insert into uv_iocu_view (a, b) values ('xyxyxy', 1) on conflict (a) do update set b = excluded.b; select * from uv_iocu_tab; +insert into uv_iocu_view (a, b) values ('xyxyxy', 1) + on conflict (a) do select returning *; -- OK to access view columns that are not present in underlying base -- relation in the ON CONFLICT portion of the query @@ -1921,6 +1923,11 @@ insert into uv_iocu_view (aa,bb) values (1,'y') and excluded.bb != '' and excluded.cc is not null; select * from uv_iocu_view; +explain (costs off) +insert into uv_iocu_view (aa,bb) values (1,'y') + on conflict (aa) do select returning *; +insert into uv_iocu_view (aa,bb) values (1,'y') + on conflict (aa) do select returning *; -- Test omitting a column of the base relation delete from uv_iocu_view; @@ -1933,11 +1940,15 @@ alter table uv_iocu_tab alter column b set default 'table default'; insert into uv_iocu_view (aa) values (1) on conflict (aa) do update set bb = 'Rejected: '||excluded.*; select * from uv_iocu_view; +insert into uv_iocu_view (aa) values (1) + on conflict (aa) do select returning *; alter view uv_iocu_view alter column bb set default 'view default'; insert into uv_iocu_view (aa) values (1) on conflict (aa) do update set bb = 'Rejected: '||excluded.*; select * from uv_iocu_view; +insert into uv_iocu_view (aa) values (1) + on conflict (aa) do select returning *; -- Should fail to update non-updatable columns insert into uv_iocu_view (aa) values (1) @@ -1946,7 +1957,7 @@ insert into uv_iocu_view (aa) values (1) drop view uv_iocu_view; drop table uv_iocu_tab; --- ON CONFLICT DO UPDATE permissions checks +-- ON CONFLICT DO SELECT/UPDATE permissions checks create user regress_view_user1; create user regress_view_user2; @@ -1970,6 +1981,10 @@ insert into rw_view1 values ('zzz',2.0,1) on conflict (aa) do update set bb = rw_view1.bb||'xxx'; -- OK insert into rw_view1 values ('zzz',2.0,1) on conflict (aa) do update set cc = 3.0; -- Not allowed +insert into rw_view1 values ('yyy',2.0,1) + on conflict (aa) do select for update returning cc; -- Not allowed +insert into rw_view1 values ('yyy',2.0,1) + on conflict (aa) do select for update returning aa, bb; reset session authorization; select * from base_tbl; @@ -1982,9 +1997,13 @@ set session authorization regress_view_user2; create view rw_view2 as select b as bb, c as cc, a as aa from base_tbl; insert into rw_view2 (aa,bb) values (1,'xxx') on conflict (aa) do update set bb = excluded.bb; -- Not allowed +insert into rw_view2 (aa,bb) values (1,'xxx') + on conflict (aa) do select returning 1; -- Not allowed create view rw_view3 as select b as bb, a as aa from base_tbl; insert into rw_view3 (aa,bb) values (1,'xxx') on conflict (aa) do update set bb = excluded.bb; -- OK +insert into rw_view3 (aa,bb) values (1,'xxx') + on conflict (aa) do select returning aa, bb; -- OK reset session authorization; select * from base_tbl; @@ -1992,6 +2011,8 @@ set session authorization regress_view_user2; create view rw_view4 as select aa, bb, cc FROM rw_view1; insert into rw_view4 (aa,bb) values (1,'yyy') on conflict (aa) do update set bb = excluded.bb; -- Not allowed +insert into rw_view4 (aa,bb) values (1,'yyy') + on conflict (aa) do select returning 1; -- Not allowed create view rw_view5 as select aa, bb FROM rw_view1; insert into rw_view5 (aa,bb) values (1,'yyy') on conflict (aa) do update set bb = excluded.bb; -- OK -- 2.34.1