agora inbox for pgsql-hackers@postgresql.orghelp / color / mirror / Atom feed
[PATCH 2/6] exercise explain_regress 9+ messages / 2 participants [nested] [flat]
* [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 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/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 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 v2 1/2] Do not lock tables in get_tables_to_repack(). @ 2026-06-16 06:49 ChangAo Chen <cca5507@qq.com> 0 siblings, 0 replies; 9+ messages in thread From: ChangAo Chen @ 2026-06-16 06:49 UTC (permalink / raw) When doing a whole database repack, we build a list of repackable tables and take a lock on them to prevent concurrent drops. But concurrent drops can always happen after we build the list because we process each table in a separate transaction. The ConditionalLockRelationOid() also makes the default behavior like SKIP_LOCKED, which is unexpected. To remove the locks, we need to make repack_is_permitted_for_relation() handles concurrent drops correctly: it should not report an error when failing to search the syscache in pg_class_aclcheck(). Use pg_class_aclcheck_ext() instead to detect a concurrent drop. Also check the return value of get_rel_name(). While at it, replace relation_close() with table_close() to match the table_open(). --- src/backend/commands/repack.c | 67 ++++++++++------------------------- 1 file changed, 19 insertions(+), 48 deletions(-) diff --git a/src/backend/commands/repack.c b/src/backend/commands/repack.c index faa07d1a118..2879c8af574 100644 --- a/src/backend/commands/repack.c +++ b/src/backend/commands/repack.c @@ -2169,22 +2169,9 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt) index = (Form_pg_index) GETSTRUCT(tuple); - /* - * Try to obtain a light lock on the index's table, to ensure it - * doesn't go away while we collect the list. If we cannot, just - * disregard it. Be sure to release this if we ultimately decide - * not to process the table! - */ - if (!ConditionalLockRelationOid(index->indrelid, AccessShareLock)) - continue; - - /* Verify that the table still exists; skip if not */ classtup = SearchSysCache1(RELOID, ObjectIdGetDatum(index->indrelid)); if (!HeapTupleIsValid(classtup)) - { - UnlockRelationOid(index->indrelid, AccessShareLock); continue; - } classForm = (Form_pg_class) GETSTRUCT(classtup); /* Skip temp relations belonging to other sessions */ @@ -2192,7 +2179,6 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt) !isTempOrTempToastNamespace(classForm->relnamespace)) { ReleaseSysCache(classtup); - UnlockRelationOid(index->indrelid, AccessShareLock); continue; } @@ -2201,10 +2187,7 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt) /* noisily skip rels which the user can't process */ if (!repack_is_permitted_for_relation(cmd, index->indrelid, GetUserId())) - { - UnlockRelationOid(index->indrelid, AccessShareLock); continue; - } /* Use a permanent memory context for the result list */ oldcxt = MemoryContextSwitchTo(permcxt); @@ -2228,45 +2211,20 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt) class = (Form_pg_class) GETSTRUCT(tuple); - /* - * Try to obtain a light lock on the table, to ensure it doesn't - * go away while we collect the list. If we cannot, just - * disregard the table. Be sure to release this if we ultimately - * decide not to process the table! - */ - if (!ConditionalLockRelationOid(class->oid, AccessShareLock)) - continue; - - /* Verify that the table still exists */ - if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(class->oid))) - { - UnlockRelationOid(class->oid, AccessShareLock); - continue; - } - /* Can only process plain tables and matviews */ if (class->relkind != RELKIND_RELATION && class->relkind != RELKIND_MATVIEW) - { - UnlockRelationOid(class->oid, AccessShareLock); continue; - } /* Skip temp relations belonging to other sessions */ if (class->relpersistence == RELPERSISTENCE_TEMP && !isTempOrTempToastNamespace(class->relnamespace)) - { - UnlockRelationOid(class->oid, AccessShareLock); continue; - } /* noisily skip rels which the user can't process */ if (!repack_is_permitted_for_relation(cmd, class->oid, GetUserId())) - { - UnlockRelationOid(class->oid, AccessShareLock); continue; - } /* Use a permanent memory context for the result list */ oldcxt = MemoryContextSwitchTo(permcxt); @@ -2279,7 +2237,7 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt) } table_endscan(scan); - relation_close(catalog, AccessShareLock); + table_close(catalog, AccessShareLock); return rtcs; } @@ -2357,15 +2315,28 @@ get_tables_to_repack_partitioned(RepackCommand cmd, Oid relid, static bool repack_is_permitted_for_relation(RepackCommand cmd, Oid relid, Oid userid) { + bool is_missing = false; + Assert(cmd == REPACK_COMMAND_CLUSTER || cmd == REPACK_COMMAND_REPACK); - if (pg_class_aclcheck(relid, userid, ACL_MAINTAIN) == ACLCHECK_OK) + if (pg_class_aclcheck_ext(relid, userid, ACL_MAINTAIN, &is_missing) == ACLCHECK_OK) return true; - ereport(WARNING, - errmsg("permission denied to execute %s on \"%s\", skipping it", - RepackCommandAsString(cmd), - get_rel_name(relid))); + /* Report a warning if the relation still exists. */ + if (!is_missing) + { + char *relname; + + relname = get_rel_name(relid); + if (relname != NULL) + { + ereport(WARNING, + errmsg("permission denied to execute %s on \"%s\", skipping it", + RepackCommandAsString(cmd), relname)); + + pfree(relname); + } + } return false; } -- 2.47.3 --op6mnexl7cn72cto Content-Type: text/x-diff; charset=utf-8 Content-Disposition: attachment; filename=v2-0002-fixups.patch ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-06-16 06:49 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/7] 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/6] 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> 2026-06-16 06:49 [PATCH v2 1/2] Do not lock tables in get_tables_to_repack(). ChangAo Chen <cca5507@qq.com>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox