agora inbox for pgsql-hackers@postgresql.orghelp / color / mirror / Atom feed
[PATCH 2/7] exercise explain_regress 9+ messages / 2 participants [nested] [flat]
* [PATCH 2/7] exercise explain_regress @ 2021-11-16 03:54 Justin Pryzby <pryzbyj@telsasoft.com> 0 siblings, 0 replies; 9+ messages in thread From: Justin Pryzby @ 2021-11-16 03:54 UTC (permalink / raw) not intended to be merged, since it creates backpatch hazards (unless the GUC is also backpatched) --- src/test/regress/expected/matview.out | 12 ++++++------ src/test/regress/expected/select_into.out | 20 ++++++++++---------- src/test/regress/expected/tidscan.out | 6 +++--- src/test/regress/sql/matview.sql | 12 ++++++------ src/test/regress/sql/select_into.sql | 20 ++++++++++---------- src/test/regress/sql/tidscan.sql | 6 +++--- 6 files changed, 38 insertions(+), 38 deletions(-) diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 313c72a2685..e7ce5bb1887 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -606,7 +606,7 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; QUERY PLAN @@ -618,7 +618,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; QUERY PLAN @@ -651,11 +651,11 @@ ERROR: relation "matview_ine_tab" already exists CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping @@ -663,11 +663,11 @@ NOTICE: relation "matview_ine_tab" already exists, skipping ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out index 43b8209d229..3de0be2a154 100644 --- a/src/test/regress/expected/select_into.out +++ b/src/test/regress/expected/select_into.out @@ -25,7 +25,7 @@ CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); ERROR: permission denied for table tbl_withdata1 -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; QUERY PLAN @@ -37,7 +37,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; QUERY PLAN @@ -50,7 +50,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; QUERY PLAN @@ -62,7 +62,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; QUERY PLAN @@ -188,20 +188,20 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN @@ -209,10 +209,10 @@ NOTICE: relation "ctas_ine_tbl" already exists, skipping (0 rows) PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out index 13c3c360c25..de93145bf0d 100644 --- a/src/test/regress/expected/tidscan.out +++ b/src/test/regress/expected/tidscan.out @@ -189,7 +189,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -205,7 +205,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -229,7 +229,7 @@ FETCH NEXT FROM c; (0 rows) -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ERROR: cursor "c" is not positioned on a row ROLLBACK; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 68b9ccfd452..e0b562933d0 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -255,13 +255,13 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2; @@ -282,16 +282,16 @@ CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok DROP MATERIALIZED VIEW matview_ine_tab; diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql index 7e903c339a8..7031e86a2c9 100644 --- a/src/test/regress/sql/select_into.sql +++ b/src/test/regress/sql/select_into.sql @@ -30,26 +30,26 @@ SET SESSION AUTHORIZATION regress_selinto_user; CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; -- EXECUTE and WITH DATA, passes. PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; RESET SESSION AUTHORIZATION; @@ -122,17 +122,17 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok DROP TABLE ctas_ine_tbl; diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql index 313e0fb9b67..3d1f447088f 100644 --- a/src/test/regress/sql/tidscan.sql +++ b/src/test/regress/sql/tidscan.sql @@ -68,17 +68,17 @@ DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan; FETCH NEXT FROM c; -- skip one row FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; SELECT * FROM tidscan; -- position cursor past any rows FETCH NEXT FROM c; -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ROLLBACK; -- 2.17.1 --ELVYuRnMxQ5nnKRy Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-Make-explain-default-to-BUFFERS-TRUE.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH 02/11] exercise explain_regress @ 2021-11-16 03:54 Justin Pryzby <pryzbyj@telsasoft.com> 0 siblings, 0 replies; 9+ messages in thread From: Justin Pryzby @ 2021-11-16 03:54 UTC (permalink / raw) not intended to be merged, since it creates backpatch hazards (unless the GUC is also backpatched) --- src/test/regress/expected/matview.out | 12 ++++++------ src/test/regress/expected/select_into.out | 20 ++++++++++---------- src/test/regress/expected/tidscan.out | 6 +++--- src/test/regress/sql/matview.sql | 12 ++++++------ src/test/regress/sql/select_into.sql | 20 ++++++++++---------- src/test/regress/sql/tidscan.sql | 6 +++--- 6 files changed, 38 insertions(+), 38 deletions(-) diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 313c72a268..e7ce5bb188 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -606,7 +606,7 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; QUERY PLAN @@ -618,7 +618,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; QUERY PLAN @@ -651,11 +651,11 @@ ERROR: relation "matview_ine_tab" already exists CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping @@ -663,11 +663,11 @@ NOTICE: relation "matview_ine_tab" already exists, skipping ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out index 43b8209d22..3de0be2a15 100644 --- a/src/test/regress/expected/select_into.out +++ b/src/test/regress/expected/select_into.out @@ -25,7 +25,7 @@ CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); ERROR: permission denied for table tbl_withdata1 -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; QUERY PLAN @@ -37,7 +37,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; QUERY PLAN @@ -50,7 +50,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; QUERY PLAN @@ -62,7 +62,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; QUERY PLAN @@ -188,20 +188,20 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN @@ -209,10 +209,10 @@ NOTICE: relation "ctas_ine_tbl" already exists, skipping (0 rows) PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out index 13c3c360c2..de93145bf0 100644 --- a/src/test/regress/expected/tidscan.out +++ b/src/test/regress/expected/tidscan.out @@ -189,7 +189,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -205,7 +205,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -229,7 +229,7 @@ FETCH NEXT FROM c; (0 rows) -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ERROR: cursor "c" is not positioned on a row ROLLBACK; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 68b9ccfd45..e0b562933d 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -255,13 +255,13 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2; @@ -282,16 +282,16 @@ CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok DROP MATERIALIZED VIEW matview_ine_tab; diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql index 7e903c339a..7031e86a2c 100644 --- a/src/test/regress/sql/select_into.sql +++ b/src/test/regress/sql/select_into.sql @@ -30,26 +30,26 @@ SET SESSION AUTHORIZATION regress_selinto_user; CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; -- EXECUTE and WITH DATA, passes. PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; RESET SESSION AUTHORIZATION; @@ -122,17 +122,17 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok DROP TABLE ctas_ine_tbl; diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql index 313e0fb9b6..3d1f447088 100644 --- a/src/test/regress/sql/tidscan.sql +++ b/src/test/regress/sql/tidscan.sql @@ -68,17 +68,17 @@ DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan; FETCH NEXT FROM c; -- skip one row FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; SELECT * FROM tidscan; -- position cursor past any rows FETCH NEXT FROM c; -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ROLLBACK; -- 2.17.0 --MOLI6A2k/lnHENyK Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-Make-explain-default-to-BUFFERS-TRUE.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH 2/6] exercise explain_regress @ 2021-11-16 03:54 Justin Pryzby <pryzbyj@telsasoft.com> 0 siblings, 0 replies; 9+ messages in thread From: Justin Pryzby @ 2021-11-16 03:54 UTC (permalink / raw) not intended to be merged, since it creates backpatch hazards (unless the GUC is also backpatched) --- src/test/regress/expected/matview.out | 12 ++++++------ src/test/regress/expected/select_into.out | 20 ++++++++++---------- src/test/regress/expected/tidscan.out | 6 +++--- src/test/regress/sql/matview.sql | 12 ++++++------ src/test/regress/sql/select_into.sql | 20 ++++++++++---------- src/test/regress/sql/tidscan.sql | 6 +++--- 6 files changed, 38 insertions(+), 38 deletions(-) diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 313c72a268..e7ce5bb188 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -606,7 +606,7 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; QUERY PLAN @@ -618,7 +618,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; QUERY PLAN @@ -651,11 +651,11 @@ ERROR: relation "matview_ine_tab" already exists CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping @@ -663,11 +663,11 @@ NOTICE: relation "matview_ine_tab" already exists, skipping ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out index b79fe9a1c0..03f2e9e158 100644 --- a/src/test/regress/expected/select_into.out +++ b/src/test/regress/expected/select_into.out @@ -25,7 +25,7 @@ CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); ERROR: permission denied for table tbl_withdata1 -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; QUERY PLAN @@ -37,7 +37,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; QUERY PLAN @@ -50,7 +50,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; QUERY PLAN @@ -62,7 +62,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; QUERY PLAN @@ -188,20 +188,20 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN @@ -209,10 +209,10 @@ NOTICE: relation "ctas_ine_tbl" already exists, skipping (0 rows) PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out index 13c3c360c2..de93145bf0 100644 --- a/src/test/regress/expected/tidscan.out +++ b/src/test/regress/expected/tidscan.out @@ -189,7 +189,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -205,7 +205,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -229,7 +229,7 @@ FETCH NEXT FROM c; (0 rows) -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ERROR: cursor "c" is not positioned on a row ROLLBACK; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 68b9ccfd45..e0b562933d 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -255,13 +255,13 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2; @@ -282,16 +282,16 @@ CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok DROP MATERIALIZED VIEW matview_ine_tab; diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql index 689c448cc2..85bfb2bf16 100644 --- a/src/test/regress/sql/select_into.sql +++ b/src/test/regress/sql/select_into.sql @@ -30,26 +30,26 @@ SET SESSION AUTHORIZATION regress_selinto_user; CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; -- EXECUTE and WITH DATA, passes. PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; RESET SESSION AUTHORIZATION; @@ -122,17 +122,17 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok DROP TABLE ctas_ine_tbl; diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql index 313e0fb9b6..3d1f447088 100644 --- a/src/test/regress/sql/tidscan.sql +++ b/src/test/regress/sql/tidscan.sql @@ -68,17 +68,17 @@ DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan; FETCH NEXT FROM c; -- skip one row FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; SELECT * FROM tidscan; -- position cursor past any rows FETCH NEXT FROM c; -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ROLLBACK; -- 2.17.1 --Nj4mAaUCx+wbOcQD Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-Make-explain-default-to-BUFFERS-TRUE.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH 2/7] exercise explain_regress @ 2021-11-16 03:54 Justin Pryzby <pryzbyj@telsasoft.com> 0 siblings, 0 replies; 9+ messages in thread From: Justin Pryzby @ 2021-11-16 03:54 UTC (permalink / raw) not intended to be merged, since it creates backpatch hazards (unless the GUC is also backpatched) --- src/test/regress/expected/matview.out | 12 ++++++------ src/test/regress/expected/select_into.out | 20 ++++++++++---------- src/test/regress/expected/tidscan.out | 6 +++--- src/test/regress/sql/matview.sql | 12 ++++++------ src/test/regress/sql/select_into.sql | 20 ++++++++++---------- src/test/regress/sql/tidscan.sql | 6 +++--- 6 files changed, 38 insertions(+), 38 deletions(-) diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index c109d97635b..e124a20f250 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -606,7 +606,7 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; QUERY PLAN @@ -618,7 +618,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; QUERY PLAN @@ -651,11 +651,11 @@ ERROR: relation "matview_ine_tab" already exists CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping @@ -663,11 +663,11 @@ NOTICE: relation "matview_ine_tab" already exists, skipping ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out index b79fe9a1c0e..03f2e9e158b 100644 --- a/src/test/regress/expected/select_into.out +++ b/src/test/regress/expected/select_into.out @@ -25,7 +25,7 @@ CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); ERROR: permission denied for table tbl_withdata1 -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; QUERY PLAN @@ -37,7 +37,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; QUERY PLAN @@ -50,7 +50,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; QUERY PLAN @@ -62,7 +62,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; QUERY PLAN @@ -188,20 +188,20 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN @@ -209,10 +209,10 @@ NOTICE: relation "ctas_ine_tbl" already exists, skipping (0 rows) PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out index 13c3c360c25..de93145bf0d 100644 --- a/src/test/regress/expected/tidscan.out +++ b/src/test/regress/expected/tidscan.out @@ -189,7 +189,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -205,7 +205,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -229,7 +229,7 @@ FETCH NEXT FROM c; (0 rows) -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ERROR: cursor "c" is not positioned on a row ROLLBACK; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 68b9ccfd452..e0b562933d0 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -255,13 +255,13 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2; @@ -282,16 +282,16 @@ CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok DROP MATERIALIZED VIEW matview_ine_tab; diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql index 689c448cc20..85bfb2bf163 100644 --- a/src/test/regress/sql/select_into.sql +++ b/src/test/regress/sql/select_into.sql @@ -30,26 +30,26 @@ SET SESSION AUTHORIZATION regress_selinto_user; CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; -- EXECUTE and WITH DATA, passes. PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; RESET SESSION AUTHORIZATION; @@ -122,17 +122,17 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok DROP TABLE ctas_ine_tbl; diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql index 313e0fb9b67..3d1f447088f 100644 --- a/src/test/regress/sql/tidscan.sql +++ b/src/test/regress/sql/tidscan.sql @@ -68,17 +68,17 @@ DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan; FETCH NEXT FROM c; -- skip one row FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; SELECT * FROM tidscan; -- position cursor past any rows FETCH NEXT FROM c; -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ROLLBACK; -- 2.25.1 --YiEDa0DAkWCtVeE4 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-Make-explain-default-to-BUFFERS-TRUE.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH 2/7] exercise explain_regress @ 2021-11-16 03:54 Justin Pryzby <pryzbyj@telsasoft.com> 0 siblings, 0 replies; 9+ messages in thread From: Justin Pryzby @ 2021-11-16 03:54 UTC (permalink / raw) not intended to be merged, since it creates backpatch hazards (unless the GUC is also backpatched) --- src/test/regress/expected/matview.out | 12 ++++++------ src/test/regress/expected/select_into.out | 20 ++++++++++---------- src/test/regress/expected/tidscan.out | 6 +++--- src/test/regress/sql/matview.sql | 12 ++++++------ src/test/regress/sql/select_into.sql | 20 ++++++++++---------- src/test/regress/sql/tidscan.sql | 6 +++--- 6 files changed, 38 insertions(+), 38 deletions(-) diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index c109d97635b..e124a20f250 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -606,7 +606,7 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; QUERY PLAN @@ -618,7 +618,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; QUERY PLAN @@ -651,11 +651,11 @@ ERROR: relation "matview_ine_tab" already exists CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping @@ -663,11 +663,11 @@ NOTICE: relation "matview_ine_tab" already exists, skipping ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out index b79fe9a1c0e..03f2e9e158b 100644 --- a/src/test/regress/expected/select_into.out +++ b/src/test/regress/expected/select_into.out @@ -25,7 +25,7 @@ CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); ERROR: permission denied for table tbl_withdata1 -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; QUERY PLAN @@ -37,7 +37,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; QUERY PLAN @@ -50,7 +50,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; QUERY PLAN @@ -62,7 +62,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; QUERY PLAN @@ -188,20 +188,20 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN @@ -209,10 +209,10 @@ NOTICE: relation "ctas_ine_tbl" already exists, skipping (0 rows) PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out index f133b5a4ac7..5f639a461fd 100644 --- a/src/test/regress/expected/tidscan.out +++ b/src/test/regress/expected/tidscan.out @@ -189,7 +189,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -205,7 +205,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -229,7 +229,7 @@ FETCH NEXT FROM c; (0 rows) -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ERROR: cursor "c" is not positioned on a row ROLLBACK; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 68b9ccfd452..e0b562933d0 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -255,13 +255,13 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2; @@ -282,16 +282,16 @@ CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok DROP MATERIALIZED VIEW matview_ine_tab; diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql index 689c448cc20..85bfb2bf163 100644 --- a/src/test/regress/sql/select_into.sql +++ b/src/test/regress/sql/select_into.sql @@ -30,26 +30,26 @@ SET SESSION AUTHORIZATION regress_selinto_user; CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; -- EXECUTE and WITH DATA, passes. PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; RESET SESSION AUTHORIZATION; @@ -122,17 +122,17 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok DROP TABLE ctas_ine_tbl; diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql index 313e0fb9b67..3d1f447088f 100644 --- a/src/test/regress/sql/tidscan.sql +++ b/src/test/regress/sql/tidscan.sql @@ -68,17 +68,17 @@ DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan; FETCH NEXT FROM c; -- skip one row FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; SELECT * FROM tidscan; -- position cursor past any rows FETCH NEXT FROM c; -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ROLLBACK; -- 2.25.1 --XJ5wUQMT/jAb5xue Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-Make-explain-default-to-BUFFERS-TRUE.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH 2/7] exercise explain_regress @ 2021-11-16 03:54 Justin Pryzby <pryzbyj@telsasoft.com> 0 siblings, 0 replies; 9+ messages in thread From: Justin Pryzby @ 2021-11-16 03:54 UTC (permalink / raw) not intended to be merged, since it creates backpatch hazards (unless the GUC is also backpatched) --- src/test/regress/expected/matview.out | 12 ++++++------ src/test/regress/expected/select_into.out | 20 ++++++++++---------- src/test/regress/expected/tidscan.out | 6 +++--- src/test/regress/sql/matview.sql | 12 ++++++------ src/test/regress/sql/select_into.sql | 20 ++++++++++---------- src/test/regress/sql/tidscan.sql | 6 +++--- 6 files changed, 38 insertions(+), 38 deletions(-) diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index c109d97635b..e124a20f250 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -606,7 +606,7 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; QUERY PLAN @@ -618,7 +618,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; QUERY PLAN @@ -651,11 +651,11 @@ ERROR: relation "matview_ine_tab" already exists CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping @@ -663,11 +663,11 @@ NOTICE: relation "matview_ine_tab" already exists, skipping ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out index b79fe9a1c0e..03f2e9e158b 100644 --- a/src/test/regress/expected/select_into.out +++ b/src/test/regress/expected/select_into.out @@ -25,7 +25,7 @@ CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); ERROR: permission denied for table tbl_withdata1 -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; QUERY PLAN @@ -37,7 +37,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; QUERY PLAN @@ -50,7 +50,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; QUERY PLAN @@ -62,7 +62,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; QUERY PLAN @@ -188,20 +188,20 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN @@ -209,10 +209,10 @@ NOTICE: relation "ctas_ine_tbl" already exists, skipping (0 rows) PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out index 13c3c360c25..de93145bf0d 100644 --- a/src/test/regress/expected/tidscan.out +++ b/src/test/regress/expected/tidscan.out @@ -189,7 +189,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -205,7 +205,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -229,7 +229,7 @@ FETCH NEXT FROM c; (0 rows) -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ERROR: cursor "c" is not positioned on a row ROLLBACK; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 68b9ccfd452..e0b562933d0 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -255,13 +255,13 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2; @@ -282,16 +282,16 @@ CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok DROP MATERIALIZED VIEW matview_ine_tab; diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql index 689c448cc20..85bfb2bf163 100644 --- a/src/test/regress/sql/select_into.sql +++ b/src/test/regress/sql/select_into.sql @@ -30,26 +30,26 @@ SET SESSION AUTHORIZATION regress_selinto_user; CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; -- EXECUTE and WITH DATA, passes. PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; RESET SESSION AUTHORIZATION; @@ -122,17 +122,17 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok DROP TABLE ctas_ine_tbl; diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql index 313e0fb9b67..3d1f447088f 100644 --- a/src/test/regress/sql/tidscan.sql +++ b/src/test/regress/sql/tidscan.sql @@ -68,17 +68,17 @@ DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan; FETCH NEXT FROM c; -- skip one row FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; SELECT * FROM tidscan; -- position cursor past any rows FETCH NEXT FROM c; -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ROLLBACK; -- 2.17.1 --jfWagoTHmfL/c8Ax Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-Make-explain-default-to-BUFFERS-TRUE.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH 2/7] exercise explain_regress @ 2021-11-16 03:54 Justin Pryzby <pryzbyj@telsasoft.com> 0 siblings, 0 replies; 9+ messages in thread From: Justin Pryzby @ 2021-11-16 03:54 UTC (permalink / raw) not intended to be merged, since it creates backpatch hazards (unless the GUC is also backpatched) --- src/test/regress/expected/matview.out | 12 ++++++------ src/test/regress/expected/select_into.out | 20 ++++++++++---------- src/test/regress/expected/tidscan.out | 6 +++--- src/test/regress/sql/matview.sql | 12 ++++++------ src/test/regress/sql/select_into.sql | 20 ++++++++++---------- src/test/regress/sql/tidscan.sql | 6 +++--- 6 files changed, 38 insertions(+), 38 deletions(-) diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index c109d97635b..e124a20f250 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -606,7 +606,7 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; QUERY PLAN @@ -618,7 +618,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; QUERY PLAN @@ -651,11 +651,11 @@ ERROR: relation "matview_ine_tab" already exists CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping @@ -663,11 +663,11 @@ NOTICE: relation "matview_ine_tab" already exists, skipping ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out index b79fe9a1c0e..03f2e9e158b 100644 --- a/src/test/regress/expected/select_into.out +++ b/src/test/regress/expected/select_into.out @@ -25,7 +25,7 @@ CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); ERROR: permission denied for table tbl_withdata1 -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; QUERY PLAN @@ -37,7 +37,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; QUERY PLAN @@ -50,7 +50,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; QUERY PLAN @@ -62,7 +62,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; QUERY PLAN @@ -188,20 +188,20 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN @@ -209,10 +209,10 @@ NOTICE: relation "ctas_ine_tbl" already exists, skipping (0 rows) PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out index 13c3c360c25..de93145bf0d 100644 --- a/src/test/regress/expected/tidscan.out +++ b/src/test/regress/expected/tidscan.out @@ -189,7 +189,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -205,7 +205,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -229,7 +229,7 @@ FETCH NEXT FROM c; (0 rows) -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ERROR: cursor "c" is not positioned on a row ROLLBACK; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 68b9ccfd452..e0b562933d0 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -255,13 +255,13 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2; @@ -282,16 +282,16 @@ CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok DROP MATERIALIZED VIEW matview_ine_tab; diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql index 689c448cc20..85bfb2bf163 100644 --- a/src/test/regress/sql/select_into.sql +++ b/src/test/regress/sql/select_into.sql @@ -30,26 +30,26 @@ SET SESSION AUTHORIZATION regress_selinto_user; CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; -- EXECUTE and WITH DATA, passes. PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; RESET SESSION AUTHORIZATION; @@ -122,17 +122,17 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok DROP TABLE ctas_ine_tbl; diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql index 313e0fb9b67..3d1f447088f 100644 --- a/src/test/regress/sql/tidscan.sql +++ b/src/test/regress/sql/tidscan.sql @@ -68,17 +68,17 @@ DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan; FETCH NEXT FROM c; -- skip one row FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; SELECT * FROM tidscan; -- position cursor past any rows FETCH NEXT FROM c; -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ROLLBACK; -- 2.17.1 --LQ77YLfPrO/qF/pM Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-Make-explain-default-to-BUFFERS-TRUE.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH 2/6] exercise explain_regress @ 2021-11-16 03:54 Justin Pryzby <pryzbyj@telsasoft.com> 0 siblings, 0 replies; 9+ messages in thread From: Justin Pryzby @ 2021-11-16 03:54 UTC (permalink / raw) not intended to be merged, since it creates backpatch hazards (unless the GUC is also backpatched) --- src/test/regress/expected/matview.out | 12 ++++++------ src/test/regress/expected/select_into.out | 20 ++++++++++---------- src/test/regress/expected/tidscan.out | 6 +++--- src/test/regress/sql/matview.sql | 12 ++++++------ src/test/regress/sql/select_into.sql | 20 ++++++++++---------- src/test/regress/sql/tidscan.sql | 6 +++--- 6 files changed, 38 insertions(+), 38 deletions(-) diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 313c72a2685..e7ce5bb1887 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -606,7 +606,7 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; QUERY PLAN @@ -618,7 +618,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; QUERY PLAN @@ -651,11 +651,11 @@ ERROR: relation "matview_ine_tab" already exists CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping @@ -663,11 +663,11 @@ NOTICE: relation "matview_ine_tab" already exists, skipping ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "matview_ine_tab" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "matview_ine_tab" already exists, skipping diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out index b79fe9a1c0e..03f2e9e158b 100644 --- a/src/test/regress/expected/select_into.out +++ b/src/test/regress/expected/select_into.out @@ -25,7 +25,7 @@ CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); ERROR: permission denied for table tbl_withdata1 -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; QUERY PLAN @@ -37,7 +37,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; QUERY PLAN @@ -50,7 +50,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; QUERY PLAN @@ -62,7 +62,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; QUERY PLAN @@ -188,20 +188,20 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN ------------ (0 rows) -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN @@ -209,10 +209,10 @@ NOTICE: relation "ctas_ine_tbl" already exists, skipping (0 rows) PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error ERROR: relation "ctas_ine_tbl" already exists -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok NOTICE: relation "ctas_ine_tbl" already exists, skipping QUERY PLAN diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out index 13c3c360c25..de93145bf0d 100644 --- a/src/test/regress/expected/tidscan.out +++ b/src/test/regress/expected/tidscan.out @@ -189,7 +189,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -205,7 +205,7 @@ FETCH NEXT FROM c; (1 row) -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; QUERY PLAN --------------------------------------------------- @@ -229,7 +229,7 @@ FETCH NEXT FROM c; (0 rows) -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ERROR: cursor "c" is not positioned on a row ROLLBACK; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 68b9ccfd452..e0b562933d0 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -255,13 +255,13 @@ GRANT ALL ON SCHEMA matview_schema TO public; SET SESSION AUTHORIZATION regress_matview_user; CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS SELECT generate_series(1, 10) WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS SELECT generate_series(1, 10) WITH DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS SELECT generate_series(1, 10) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS SELECT generate_series(1, 10) WITH NO DATA; REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2; @@ -282,16 +282,16 @@ CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok DROP MATERIALIZED VIEW matview_ine_tab; diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql index 689c448cc20..85bfb2bf163 100644 --- a/src/test/regress/sql/select_into.sql +++ b/src/test/regress/sql/select_into.sql @@ -30,26 +30,26 @@ SET SESSION AUTHORIZATION regress_selinto_user; CREATE TABLE selinto_schema.tbl_withdata1 (a) AS SELECT generate_series(1,3) WITH DATA; INSERT INTO selinto_schema.tbl_withdata1 VALUES (4); -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata2 (a) AS SELECT generate_series(1,3) WITH DATA; -- WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata1 (a) AS SELECT generate_series(1,3) WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata2 (a) AS SELECT generate_series(1,3) WITH NO DATA; -- EXECUTE and WITH DATA, passes. PREPARE data_sel AS SELECT generate_series(1,3); CREATE TABLE selinto_schema.tbl_withdata3 (a) AS EXECUTE data_sel WITH DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_withdata4 (a) AS EXECUTE data_sel WITH DATA; -- EXECUTE and WITH NO DATA, passes. CREATE TABLE selinto_schema.tbl_nodata3 (a) AS EXECUTE data_sel WITH NO DATA; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE selinto_schema.tbl_nodata4 (a) AS EXECUTE data_sel WITH NO DATA; RESET SESSION AUTHORIZATION; @@ -122,17 +122,17 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok PREPARE ctas_ine_query AS SELECT 1 / 0; -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok DROP TABLE ctas_ine_tbl; diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql index 313e0fb9b67..3d1f447088f 100644 --- a/src/test/regress/sql/tidscan.sql +++ b/src/test/regress/sql/tidscan.sql @@ -68,17 +68,17 @@ DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan; FETCH NEXT FROM c; -- skip one row FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; FETCH NEXT FROM c; -- perform update -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; SELECT * FROM tidscan; -- position cursor past any rows FETCH NEXT FROM c; -- should error out -EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +EXPLAIN (ANALYZE) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ROLLBACK; -- 2.17.1 --AXo2lOxbfudqq8ta Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-Make-explain-default-to-BUFFERS-TRUE.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v13 2/2] Add injection point test for vacuum skip_locked stats @ 2026-07-24 13:45 Sami Imseih <samimseih@gmail.com> 0 siblings, 0 replies; 9+ messages in thread From: Sami Imseih @ 2026-07-24 13:45 UTC (permalink / raw) Add an isolation test exercising the race window between VACUUM (SKIP_LOCKED) reporting a skipped vacuum and concurrent table drops. Two scenarios are tested: 1. Table dropped (committed) while vacuumer is blocked at the injection point: no orphaned stats entry is created. 2. DROP TABLE rolled back while vacuumer is blocked: skip is still recorded since the table and its stats entry survive. --- src/backend/utils/activity/pgstat_relation.c | 2 + src/test/modules/injection_points/Makefile | 3 +- .../expected/vacuum_skip_lock_stats.out | 93 +++++++++++++++++++ src/test/modules/injection_points/meson.build | 1 + .../specs/vacuum_skip_lock_stats.spec | 68 ++++++++++++++ 5 files changed, 166 insertions(+), 1 deletion(-) create mode 100644 src/test/modules/injection_points/expected/vacuum_skip_lock_stats.out create mode 100644 src/test/modules/injection_points/specs/vacuum_skip_lock_stats.spec diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index d8259823925..6c1e2a6c0f2 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -21,6 +21,7 @@ #include "access/twophase_rmgr.h" #include "access/xact.h" #include "catalog/catalog.h" +#include "utils/injection_point.h" #include "utils/inval.h" #include "utils/memutils.h" #include "utils/pgstat_internal.h" @@ -391,6 +392,7 @@ pgstat_report_skipped_vacuum_analyze(Oid relid, int flags) return; /* somebody deleted the rel, forget it */ isshared = ((Form_pg_class) GETSTRUCT(classTup))->relisshared; ReleaseSysCache(classTup); + INJECTION_POINT("skipped-vacuum-analyze-before-entry-lock", NULL); /* Store the data in the table's hash table entry. */ ts = GetCurrentTimestamp(); diff --git a/src/test/modules/injection_points/Makefile b/src/test/modules/injection_points/Makefile index fac80f3a4a7..b833d9d481d 100644 --- a/src/test/modules/injection_points/Makefile +++ b/src/test/modules/injection_points/Makefile @@ -20,7 +20,8 @@ ISOLATION = basic \ repack_toast \ syscache-update-pruned \ wait_cleanup \ - heap_lock_update + heap_lock_update \ + vacuum_skip_lock_stats # some isolation tests require wal_level=replica ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/injection_points/extra.conf diff --git a/src/test/modules/injection_points/expected/vacuum_skip_lock_stats.out b/src/test/modules/injection_points/expected/vacuum_skip_lock_stats.out new file mode 100644 index 00000000000..0efed3db2db --- /dev/null +++ b/src/test/modules/injection_points/expected/vacuum_skip_lock_stats.out @@ -0,0 +1,93 @@ +Parsed test spec with 3 sessions + +starting permutation: lock vacuum unlock drop_table wakeup noop check_stats detach +injection_points_attach +----------------------- + +(1 row) + +step lock: + BEGIN; + LOCK TABLE test_skip IN ACCESS EXCLUSIVE MODE; + +s2: WARNING: skipping vacuum of "test_skip" --- lock not available +step vacuum: VACUUM (SKIP_LOCKED) test_skip; <waiting ...> +step unlock: COMMIT; +step drop_table: DROP TABLE test_skip; +step wakeup: SELECT injection_points_wakeup('skipped-vacuum-analyze-before-entry-lock'); +injection_points_wakeup +----------------------- + +(1 row) + +step vacuum: <... completed> +step noop: +step check_stats: + SELECT pg_stat_force_next_flush(); + SELECT pg_stat_get_lock_skipped_vacuum_count(oid_val) AS skip_count + FROM saved_oid; + +pg_stat_force_next_flush +------------------------ + +(1 row) + +skip_count +---------- + 0 +(1 row) + +step detach: SELECT injection_points_detach('skipped-vacuum-analyze-before-entry-lock'); +injection_points_detach +----------------------- + +(1 row) + + +starting permutation: lock vacuum unlock rollback_drop wakeup noop check_stats detach +injection_points_attach +----------------------- + +(1 row) + +step lock: + BEGIN; + LOCK TABLE test_skip IN ACCESS EXCLUSIVE MODE; + +s2: WARNING: skipping vacuum of "test_skip" --- lock not available +step vacuum: VACUUM (SKIP_LOCKED) test_skip; <waiting ...> +step unlock: COMMIT; +step rollback_drop: + BEGIN; + DROP TABLE test_skip; + ROLLBACK; + +step wakeup: SELECT injection_points_wakeup('skipped-vacuum-analyze-before-entry-lock'); +injection_points_wakeup +----------------------- + +(1 row) + +step vacuum: <... completed> +step noop: +step check_stats: + SELECT pg_stat_force_next_flush(); + SELECT pg_stat_get_lock_skipped_vacuum_count(oid_val) AS skip_count + FROM saved_oid; + +pg_stat_force_next_flush +------------------------ + +(1 row) + +skip_count +---------- + 1 +(1 row) + +step detach: SELECT injection_points_detach('skipped-vacuum-analyze-before-entry-lock'); +injection_points_detach +----------------------- + +(1 row) + diff --git a/src/test/modules/injection_points/meson.build b/src/test/modules/injection_points/meson.build index 163b6374ebc..a96753fc326 100644 --- a/src/test/modules/injection_points/meson.build +++ b/src/test/modules/injection_points/meson.build @@ -52,6 +52,7 @@ tests += { 'syscache-update-pruned', 'wait_cleanup', 'heap_lock_update', + 'vacuum_skip_lock_stats', ], '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/vacuum_skip_lock_stats.spec b/src/test/modules/injection_points/specs/vacuum_skip_lock_stats.spec new file mode 100644 index 00000000000..b277b1e80f7 --- /dev/null +++ b/src/test/modules/injection_points/specs/vacuum_skip_lock_stats.spec @@ -0,0 +1,68 @@ +# Test for race conditions between VACUUM (SKIP_LOCKED) stats reporting +# and concurrent DROP TABLE. +# +# When VACUUM (SKIP_LOCKED) cannot acquire a lock, it reports skipped +# statistics via pgstat_report_skipped_vacuum_analyze(). An injection +# point after the syscache lookup but before the stats update allows us +# to verify that a concurrent DROP does not leave orphaned stats entries. + +setup +{ + CREATE EXTENSION injection_points; + CREATE TABLE test_skip (id int); + INSERT INTO test_skip VALUES (1); + ANALYZE test_skip; + SELECT pg_stat_force_next_flush(); + CREATE TABLE saved_oid (oid_val oid); + INSERT INTO saved_oid SELECT oid FROM pg_class WHERE relname = 'test_skip'; +} + +teardown +{ + DROP TABLE IF EXISTS test_skip; + DROP TABLE IF EXISTS saved_oid; + DROP EXTENSION injection_points; +} + +# s1: holds the lock so VACUUM skips the table +session s1 +step lock +{ + BEGIN; + LOCK TABLE test_skip IN ACCESS EXCLUSIVE MODE; +} +step unlock { COMMIT; } + +# s2: runs VACUUM (SKIP_LOCKED), blocks at injection point after skip +session s2 +setup +{ + SELECT injection_points_set_local(); + SELECT injection_points_attach('skipped-vacuum-analyze-before-entry-lock', 'wait'); +} +step vacuum { VACUUM (SKIP_LOCKED) test_skip; } +step noop {} +step detach { SELECT injection_points_detach('skipped-vacuum-analyze-before-entry-lock'); } + +# s3: drops table or wakes up the vacuumer +session s3 +step drop_table { DROP TABLE test_skip; } +step rollback_drop +{ + BEGIN; + DROP TABLE test_skip; + ROLLBACK; +} +step wakeup { SELECT injection_points_wakeup('skipped-vacuum-analyze-before-entry-lock'); } +step check_stats +{ + SELECT pg_stat_force_next_flush(); + SELECT pg_stat_get_lock_skipped_vacuum_count(oid_val) AS skip_count + FROM saved_oid; +} + +# Table dropped while vacuumer is blocked: no orphaned stats entry. +permutation lock vacuum(wakeup) unlock drop_table wakeup noop check_stats detach + +# DROP rolled back while vacuumer is blocked: skip is still recorded. +permutation lock vacuum(wakeup) unlock rollback_drop wakeup noop check_stats detach -- 2.43.0 --Multipart=_Mon__27_Jul_2026_13_03_41_+0900_q9gi0JK8Af.Ckleb Content-Type: text/x-diff; name="v13-0001-Track-skipped-vacuum-and-analyze-activity-per-re.patch" Content-Disposition: attachment; filename="v13-0001-Track-skipped-vacuum-and-analyze-activity-per-re.patch" Content-Transfer-Encoding: 7bit ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-07-24 13:45 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-11-16 03:54 [PATCH 2/7] exercise explain_regress Justin Pryzby <pryzbyj@telsasoft.com> 2021-11-16 03:54 [PATCH 2/6] exercise explain_regress Justin Pryzby <pryzbyj@telsasoft.com> 2021-11-16 03:54 [PATCH 2/7] exercise explain_regress Justin Pryzby <pryzbyj@telsasoft.com> 2021-11-16 03:54 [PATCH 2/6] exercise explain_regress Justin Pryzby <pryzbyj@telsasoft.com> 2021-11-16 03:54 [PATCH 02/11] exercise explain_regress Justin Pryzby <pryzbyj@telsasoft.com> 2021-11-16 03:54 [PATCH 2/7] exercise explain_regress Justin Pryzby <pryzbyj@telsasoft.com> 2021-11-16 03:54 [PATCH 2/7] exercise explain_regress Justin Pryzby <pryzbyj@telsasoft.com> 2021-11-16 03:54 [PATCH 2/7] exercise explain_regress Justin Pryzby <pryzbyj@telsasoft.com> 2026-07-24 13:45 [PATCH v13 2/2] Add injection point test for vacuum skip_locked stats Sami Imseih <samimseih@gmail.com>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox