From 1928e841060d33ae46c5f1ec9d2d172bf2f10fab Mon Sep 17 00:00:00 2001 From: "okbob@github.com" Date: Sun, 16 Jun 2024 08:13:53 +0200 Subject: [PATCH 2/3] simply check of strict-expr-check on regress test This patch enable strict-expr-check by default to be possible to see the impact of this option on regress test. Next commit will revert this option. The strict-expr-check should not be enabled by default. This commit is done just for testing. --- .../basic_archive/expected/basic_archive.out | 4 +-- contrib/basic_archive/sql/basic_archive.sql | 4 +-- src/pl/plpgsql/src/expected/plpgsql_array.out | 34 +++++++++++-------- src/pl/plpgsql/src/pl_handler.c | 4 +-- src/pl/plpgsql/src/sql/plpgsql_array.sql | 14 ++++---- .../recovery/t/026_overwrite_contrecord.pl | 4 +-- src/test/regress/expected/alter_table.out | 2 +- src/test/regress/expected/plancache.out | 2 +- src/test/regress/expected/plpgsql.out | 12 +++---- src/test/regress/expected/stats_ext.out | 2 +- src/test/regress/expected/transactions.out | 4 +-- src/test/regress/sql/alter_table.sql | 2 +- src/test/regress/sql/plancache.sql | 2 +- src/test/regress/sql/plpgsql.sql | 12 +++---- src/test/regress/sql/stats_ext.sql | 2 +- src/test/regress/sql/transactions.sql | 4 +-- 16 files changed, 56 insertions(+), 52 deletions(-) diff --git a/contrib/basic_archive/expected/basic_archive.out b/contrib/basic_archive/expected/basic_archive.out index 0015053e0f2..280ff3e022e 100644 --- a/contrib/basic_archive/expected/basic_archive.out +++ b/contrib/basic_archive/expected/basic_archive.out @@ -11,8 +11,8 @@ DECLARE loops int := 0; BEGIN LOOP - archived := count(*) > 0 FROM pg_ls_dir('.', false, false) a - WHERE a ~ '^[0-9A-F]{24}$'; + archived := (SELECT count(*) > 0 FROM pg_ls_dir('.', false, false) a + WHERE a ~ '^[0-9A-F]{24}$'); IF archived OR loops > 120 * 10 THEN EXIT; END IF; PERFORM pg_sleep(0.1); loops := loops + 1; diff --git a/contrib/basic_archive/sql/basic_archive.sql b/contrib/basic_archive/sql/basic_archive.sql index 14e236d57ab..2c127a821f1 100644 --- a/contrib/basic_archive/sql/basic_archive.sql +++ b/contrib/basic_archive/sql/basic_archive.sql @@ -7,8 +7,8 @@ DECLARE loops int := 0; BEGIN LOOP - archived := count(*) > 0 FROM pg_ls_dir('.', false, false) a - WHERE a ~ '^[0-9A-F]{24}$'; + archived := (SELECT count(*) > 0 FROM pg_ls_dir('.', false, false) a + WHERE a ~ '^[0-9A-F]{24}$'); IF archived OR loops > 120 * 10 THEN EXIT; END IF; PERFORM pg_sleep(0.1); loops := loops + 1; diff --git a/src/pl/plpgsql/src/expected/plpgsql_array.out b/src/pl/plpgsql/src/expected/plpgsql_array.out index 4c6b3ce998a..caf07e834e5 100644 --- a/src/pl/plpgsql/src/expected/plpgsql_array.out +++ b/src/pl/plpgsql/src/expected/plpgsql_array.out @@ -50,7 +50,7 @@ do $$ declare a quadarray; begin a.c1[1].i := 11; raise notice 'a = %, a.c1[1].i = %', a, a.c1[1].i; end$$; NOTICE: a = ("{""(,11)""}",), a.c1[1].i = 11 do $$ declare a int[]; -begin a := array_agg(x) from (values(1),(2),(3)) v(x); raise notice 'a = %', a; end$$; +begin a := (select array_agg(x) from (values(1),(2),(3)) v(x)); raise notice 'a = %', a; end$$; NOTICE: a = {1,2,3} do $$ declare a int[] := array[1,2,3]; begin @@ -64,30 +64,34 @@ end$$; NOTICE: a = {1,1,2,3,42,3,1,1,2,3,42,3} create temp table onecol as select array[1,2] as f1; do $$ declare a int[]; -begin a := f1 from onecol; raise notice 'a = %', a; end$$; +begin a := (select f1 from onecol); raise notice 'a = %', a; end$$; NOTICE: a = {1,2} do $$ declare a int[]; -begin a := * from onecol for update; raise notice 'a = %', a; end$$; +begin a := (select * from onecol for update); raise notice 'a = %', a; end$$; NOTICE: a = {1,2} -- error cases: do $$ declare a int[]; -begin a := from onecol; raise notice 'a = %', a; end$$; -ERROR: assignment source returned 0 columns -CONTEXT: PL/pgSQL assignment "a := from onecol" -PL/pgSQL function inline_code_block line 2 at assignment +begin a := (select from onecol); raise notice 'a = %', a; end$$; +ERROR: subquery must return only one column +LINE 1: a := (select from onecol) + ^ +QUERY: a := (select from onecol) +CONTEXT: PL/pgSQL function inline_code_block line 2 at assignment do $$ declare a int[]; -begin a := f1, f1 from onecol; raise notice 'a = %', a; end$$; -ERROR: assignment source returned 2 columns -CONTEXT: PL/pgSQL assignment "a := f1, f1 from onecol" -PL/pgSQL function inline_code_block line 2 at assignment +begin a := (select f1, f1 from onecol); raise notice 'a = %', a; end$$; +ERROR: subquery must return only one column +LINE 1: a := (select f1, f1 from onecol) + ^ +QUERY: a := (select f1, f1 from onecol) +CONTEXT: PL/pgSQL function inline_code_block line 2 at assignment insert into onecol values(array[11]); do $$ declare a int[]; -begin a := f1 from onecol; raise notice 'a = %', a; end$$; -ERROR: query returned more than one row -CONTEXT: query: a := f1 from onecol +begin a := (select f1 from onecol); raise notice 'a = %', a; end$$; +ERROR: more than one row returned by a subquery used as an expression +CONTEXT: PL/pgSQL assignment "a := (select f1 from onecol)" PL/pgSQL function inline_code_block line 2 at assignment do $$ declare a int[]; -begin a := f1 from onecol limit 1; raise notice 'a = %', a; end$$; +begin a := (select f1 from onecol limit 1); raise notice 'a = %', a; end$$; NOTICE: a = {1,2} do $$ declare a real; begin a[1] := 2; raise notice 'a = %', a; end$$; diff --git a/src/pl/plpgsql/src/pl_handler.c b/src/pl/plpgsql/src/pl_handler.c index b3ba3163e9a..11e4dd1b62c 100644 --- a/src/pl/plpgsql/src/pl_handler.c +++ b/src/pl/plpgsql/src/pl_handler.c @@ -53,7 +53,7 @@ bool plpgsql_check_asserts = true; static char *plpgsql_extra_warnings_string = NULL; static char *plpgsql_extra_errors_string = NULL; int plpgsql_extra_warnings; -int plpgsql_extra_errors; +int plpgsql_extra_errors = PLPGSQL_XCHECK_STRICTEXPRCHECK; /* Hook for plugins */ PLpgSQL_plugin **plpgsql_plugin_ptr = NULL; @@ -196,7 +196,7 @@ _PG_init(void) gettext_noop("List of programming constructs that should produce an error."), NULL, &plpgsql_extra_errors_string, - "none", + "strict_expr_check", PGC_USERSET, GUC_LIST_INPUT, plpgsql_extra_checks_check_hook, plpgsql_extra_errors_assign_hook, diff --git a/src/pl/plpgsql/src/sql/plpgsql_array.sql b/src/pl/plpgsql/src/sql/plpgsql_array.sql index da984a99414..09a76a8416b 100644 --- a/src/pl/plpgsql/src/sql/plpgsql_array.sql +++ b/src/pl/plpgsql/src/sql/plpgsql_array.sql @@ -46,7 +46,7 @@ do $$ declare a quadarray; begin a.c1[1].i := 11; raise notice 'a = %, a.c1[1].i = %', a, a.c1[1].i; end$$; do $$ declare a int[]; -begin a := array_agg(x) from (values(1),(2),(3)) v(x); raise notice 'a = %', a; end$$; +begin a := (select array_agg(x) from (values(1),(2),(3)) v(x)); raise notice 'a = %', a; end$$; do $$ declare a int[] := array[1,2,3]; begin @@ -61,26 +61,26 @@ end$$; create temp table onecol as select array[1,2] as f1; do $$ declare a int[]; -begin a := f1 from onecol; raise notice 'a = %', a; end$$; +begin a := (select f1 from onecol); raise notice 'a = %', a; end$$; do $$ declare a int[]; -begin a := * from onecol for update; raise notice 'a = %', a; end$$; +begin a := (select * from onecol for update); raise notice 'a = %', a; end$$; -- error cases: do $$ declare a int[]; -begin a := from onecol; raise notice 'a = %', a; end$$; +begin a := (select from onecol); raise notice 'a = %', a; end$$; do $$ declare a int[]; -begin a := f1, f1 from onecol; raise notice 'a = %', a; end$$; +begin a := (select f1, f1 from onecol); raise notice 'a = %', a; end$$; insert into onecol values(array[11]); do $$ declare a int[]; -begin a := f1 from onecol; raise notice 'a = %', a; end$$; +begin a := (select f1 from onecol); raise notice 'a = %', a; end$$; do $$ declare a int[]; -begin a := f1 from onecol limit 1; raise notice 'a = %', a; end$$; +begin a := (select f1 from onecol limit 1); raise notice 'a = %', a; end$$; do $$ declare a real; begin a[1] := 2; raise notice 'a = %', a; end$$; diff --git a/src/test/recovery/t/026_overwrite_contrecord.pl b/src/test/recovery/t/026_overwrite_contrecord.pl index f408d4f69b6..7d004b04031 100644 --- a/src/test/recovery/t/026_overwrite_contrecord.pl +++ b/src/test/recovery/t/026_overwrite_contrecord.pl @@ -33,7 +33,7 @@ $node->safe_psql( 'postgres', q{ DO $$ DECLARE - wal_segsize int := setting::int FROM pg_settings WHERE name = 'wal_segment_size'; + wal_segsize int := (SELECT setting::int FROM pg_settings WHERE name = 'wal_segment_size'); remain int; iters int := 0; BEGIN @@ -43,7 +43,7 @@ BEGIN from generate_series(1, 10) g; remain := wal_segsize - (pg_current_wal_insert_lsn() - '0/0') % wal_segsize; - IF remain < 2 * setting::int from pg_settings where name = 'block_size' THEN + IF (SELECT remain < 2 * setting::int from pg_settings where name = 'block_size') THEN RAISE log 'exiting after % iterations, % bytes to end of WAL segment', iters, remain; EXIT; END IF; diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 5e98bbf2425..faf078b22f5 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -2692,7 +2692,7 @@ LANGUAGE plpgsql AS $$ DECLARE v_relfilenode oid; BEGIN - v_relfilenode := relfilenode FROM pg_class WHERE oid = p_tablename; + v_relfilenode := (SELECT relfilenode FROM pg_class WHERE oid = p_tablename); EXECUTE p_ddl; diff --git a/src/test/regress/expected/plancache.out b/src/test/regress/expected/plancache.out index 4e59188196c..faae99515f7 100644 --- a/src/test/regress/expected/plancache.out +++ b/src/test/regress/expected/plancache.out @@ -139,7 +139,7 @@ create temp view v1 as select 2+2 as f1; create function cache_test_2() returns int as $$ begin - return f1 from v1; + return (select f1 from v1); end$$ language plpgsql; select cache_test_2(); cache_test_2 diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index a6a02d5454f..b3f7f88a74e 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -121,7 +121,7 @@ create trigger tg_room_ad after delete -- ************************************************************ create function tg_wslot_biu() returns trigger as $$ begin - if count(*) = 0 from Room where roomno = new.roomno then + if (select count(*) = 0 from Room where roomno = new.roomno) then raise exception 'Room % does not exist', new.roomno; end if; return new; @@ -286,7 +286,7 @@ begin raise exception ''no manual manipulation of HSlot''; end if; if tg_op = ''UPDATE'' and new.hubname != old.hubname then - if count(*) > 0 from Hub where name = old.hubname then + if (select count(*) > 0 from Hub where name = old.hubname) then raise exception ''no manual manipulation of HSlot''; end if; end if; @@ -942,12 +942,12 @@ begin return retval || pslot_backlink_view(psrec.slotlink); end if; if sltype = ''HS'' then - retval := comment from Hub H, HSlot HS + retval := (select comment from Hub H, HSlot HS where HS.slotname = psrec.slotlink - and H.name = HS.hubname; + and H.name = HS.hubname); retval := retval || '' slot ''; - retval := retval || slotno::text from HSlot - where slotname = psrec.slotlink; + retval := (select retval || slotno::text from HSlot + where slotname = psrec.slotlink); return retval; end if; return psrec.slotlink; diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 5a4077f8ed5..905dc4af044 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -384,7 +384,7 @@ CREATE STATISTICS tststats.s8 ON a, b FROM tststats.pt; CREATE STATISTICS tststats.s9 ON a, b FROM tststats.pt1; DO $$ DECLARE - relname text := reltoastrelid::regclass FROM pg_class WHERE oid = 'tststats.t'::regclass; + relname text := (SELECT reltoastrelid::regclass FROM pg_class WHERE oid = 'tststats.t'::regclass); BEGIN EXECUTE 'CREATE STATISTICS tststats.s10 ON a, b FROM ' || relname; EXCEPTION WHEN wrong_object_type THEN diff --git a/src/test/regress/expected/transactions.out b/src/test/regress/expected/transactions.out index 7f5757e89c4..3458dad1749 100644 --- a/src/test/regress/expected/transactions.out +++ b/src/test/regress/expected/transactions.out @@ -543,7 +543,7 @@ select * from xacttest; rollback; -- Now the same test with plpgsql (since it depends on SPI which is different) create or replace function max_xacttest() returns smallint language plpgsql as -'begin return max(a) from xacttest; end' stable; +'begin return (select max(a) from xacttest); end' stable; begin; update xacttest set a = max_xacttest() + 10 where a > 0; select * from xacttest; @@ -558,7 +558,7 @@ select * from xacttest; rollback; create or replace function max_xacttest() returns smallint language plpgsql as -'begin return max(a) from xacttest; end' volatile; +'begin return (select max(a) from xacttest); end' volatile; begin; update xacttest set a = max_xacttest() + 10 where a > 0; select * from xacttest; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 417202430a5..01a3fa7118f 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -1678,7 +1678,7 @@ LANGUAGE plpgsql AS $$ DECLARE v_relfilenode oid; BEGIN - v_relfilenode := relfilenode FROM pg_class WHERE oid = p_tablename; + v_relfilenode := (SELECT relfilenode FROM pg_class WHERE oid = p_tablename); EXECUTE p_ddl; diff --git a/src/test/regress/sql/plancache.sql b/src/test/regress/sql/plancache.sql index 4b2f11dcc64..a3dbd93468e 100644 --- a/src/test/regress/sql/plancache.sql +++ b/src/test/regress/sql/plancache.sql @@ -81,7 +81,7 @@ create temp view v1 as create function cache_test_2() returns int as $$ begin - return f1 from v1; + return (select f1 from v1); end$$ language plpgsql; select cache_test_2(); diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index dd0d908d422..238e0ec67f2 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -161,7 +161,7 @@ create trigger tg_room_ad after delete -- ************************************************************ create function tg_wslot_biu() returns trigger as $$ begin - if count(*) = 0 from Room where roomno = new.roomno then + if (select count(*) = 0 from Room where roomno = new.roomno) then raise exception 'Room % does not exist', new.roomno; end if; return new; @@ -348,7 +348,7 @@ begin raise exception ''no manual manipulation of HSlot''; end if; if tg_op = ''UPDATE'' and new.hubname != old.hubname then - if count(*) > 0 from Hub where name = old.hubname then + if (select count(*) > 0 from Hub where name = old.hubname) then raise exception ''no manual manipulation of HSlot''; end if; end if; @@ -1071,12 +1071,12 @@ begin return retval || pslot_backlink_view(psrec.slotlink); end if; if sltype = ''HS'' then - retval := comment from Hub H, HSlot HS + retval := (select comment from Hub H, HSlot HS where HS.slotname = psrec.slotlink - and H.name = HS.hubname; + and H.name = HS.hubname); retval := retval || '' slot ''; - retval := retval || slotno::text from HSlot - where slotname = psrec.slotlink; + retval := (select retval || slotno::text from HSlot + where slotname = psrec.slotlink); return retval; end if; return psrec.slotlink; diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index 94e2139c504..fe862fc53a5 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -244,7 +244,7 @@ CREATE STATISTICS tststats.s8 ON a, b FROM tststats.pt; CREATE STATISTICS tststats.s9 ON a, b FROM tststats.pt1; DO $$ DECLARE - relname text := reltoastrelid::regclass FROM pg_class WHERE oid = 'tststats.t'::regclass; + relname text := (SELECT reltoastrelid::regclass FROM pg_class WHERE oid = 'tststats.t'::regclass); BEGIN EXECUTE 'CREATE STATISTICS tststats.s10 ON a, b FROM ' || relname; EXCEPTION WHEN wrong_object_type THEN diff --git a/src/test/regress/sql/transactions.sql b/src/test/regress/sql/transactions.sql index 51ae1b31b30..1981955ac65 100644 --- a/src/test/regress/sql/transactions.sql +++ b/src/test/regress/sql/transactions.sql @@ -320,7 +320,7 @@ rollback; -- Now the same test with plpgsql (since it depends on SPI which is different) create or replace function max_xacttest() returns smallint language plpgsql as -'begin return max(a) from xacttest; end' stable; +'begin return (select max(a) from xacttest); end' stable; begin; update xacttest set a = max_xacttest() + 10 where a > 0; @@ -328,7 +328,7 @@ select * from xacttest; rollback; create or replace function max_xacttest() returns smallint language plpgsql as -'begin return max(a) from xacttest; end' volatile; +'begin return (select max(a) from xacttest); end' volatile; begin; update xacttest set a = max_xacttest() + 10 where a > 0; -- 2.51.1