From eb61fe10d0d7399573ebb3a911aed4114742cf25 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Tue, 7 Feb 2023 14:33:20 +0900 Subject: [PATCH v2 1/4] Refactor regression tests of pg_stat_statements pg_stat_statements.sql acts as the main file for all the core tests of the module, but things have become a bit hairy over the years as some of the sub-scenarios tested rely on assumptions that may have been set in a completely different block, like a GUC setup or a different relation. This commit refactors the tests of pg_stat_statements a bit, by moving a few test cases out of pg_stat_statements.sql into their own file, as of: - Planning-related tests in planning.sql. - Utilities in utility.sql. Test scenarios and their results remain the same as the originals. --- contrib/pg_stat_statements/Makefile | 2 +- .../pg_stat_statements/expected/cleanup.out | 1 + .../expected/pg_stat_statements.out | 284 ++---------------- .../pg_stat_statements/expected/planning.out | 195 ++++++++++++ .../pg_stat_statements/expected/utility.out | 72 +++++ contrib/pg_stat_statements/meson.build | 3 + contrib/pg_stat_statements/sql/cleanup.sql | 1 + .../sql/pg_stat_statements.sql | 118 +------- contrib/pg_stat_statements/sql/planning.sql | 78 +++++ contrib/pg_stat_statements/sql/utility.sql | 34 +++ 10 files changed, 417 insertions(+), 371 deletions(-) create mode 100644 contrib/pg_stat_statements/expected/cleanup.out create mode 100644 contrib/pg_stat_statements/expected/planning.out create mode 100644 contrib/pg_stat_statements/expected/utility.out create mode 100644 contrib/pg_stat_statements/sql/cleanup.sql create mode 100644 contrib/pg_stat_statements/sql/planning.sql create mode 100644 contrib/pg_stat_statements/sql/utility.sql diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile index edc40c8bbf..78dc4c1d07 100644 --- a/contrib/pg_stat_statements/Makefile +++ b/contrib/pg_stat_statements/Makefile @@ -17,7 +17,7 @@ PGFILEDESC = "pg_stat_statements - execution statistics of SQL statements" LDFLAGS_SL += $(filter -lm, $(LIBS)) REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf -REGRESS = pg_stat_statements oldextversions +REGRESS = pg_stat_statements utility planning cleanup oldextversions # Disabled because these tests require "shared_preload_libraries=pg_stat_statements", # which typical installcheck users do not have (e.g. buildfarm clients). NO_INSTALLCHECK = 1 diff --git a/contrib/pg_stat_statements/expected/cleanup.out b/contrib/pg_stat_statements/expected/cleanup.out new file mode 100644 index 0000000000..36bec35c40 --- /dev/null +++ b/contrib/pg_stat_statements/expected/cleanup.out @@ -0,0 +1 @@ +DROP EXTENSION pg_stat_statements; diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out index 8c0b2235e8..09ceb6dd2b 100644 --- a/contrib/pg_stat_statements/expected/pg_stat_statements.out +++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out @@ -550,131 +550,10 @@ SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; (12 rows) DROP TABLE pgss_a, pgss_b CASCADE; --- --- utility commands --- -SET pg_stat_statements.track_utility = TRUE; -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - -(1 row) - -SELECT 1; - ?column? ----------- - 1 -(1 row) - -CREATE INDEX test_b ON test(b); -DROP TABLE test \; -DROP TABLE IF EXISTS test \; -DROP FUNCTION PLUS_ONE(INTEGER); -NOTICE: table "test" does not exist, skipping --- This DROP query uses two different strings, still they count as one entry. -DROP TABLE IF EXISTS test \; -Drop Table If Exists test \; -DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER); -NOTICE: table "test" does not exist, skipping -NOTICE: table "test" does not exist, skipping -NOTICE: function plus_one(pg_catalog.int4) does not exist, skipping -DROP FUNCTION PLUS_TWO(INTEGER); --- This SET query uses two different strings, still they count as one entry. -SET work_mem = '1MB'; -Set work_mem = '1MB'; -SET work_mem = '2MB'; -RESET work_mem; -SET enable_seqscan = off; -SET enable_seqscan = on; -RESET enable_seqscan; -SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls | rows -------------------------------------------------------------------------------+-------+------ - CREATE INDEX test_b ON test(b) | 1 | 0 - DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER) | 1 | 0 - DROP FUNCTION PLUS_ONE(INTEGER) | 1 | 0 - DROP FUNCTION PLUS_TWO(INTEGER) | 1 | 0 - DROP TABLE IF EXISTS test | 3 | 0 - DROP TABLE test | 1 | 0 - RESET enable_seqscan | 1 | 0 - RESET work_mem | 1 | 0 - SELECT $1 | 1 | 1 - SELECT pg_stat_statements_reset() | 1 | 1 - SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0 - SET enable_seqscan = off | 1 | 0 - SET enable_seqscan = on | 1 | 0 - SET work_mem = '1MB' | 2 | 0 - SET work_mem = '2MB' | 1 | 0 -(15 rows) - --- --- Track the total number of rows retrieved or affected by the utility --- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW, --- REFRESH MATERIALIZED VIEW and SELECT INTO --- -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - -(1 row) - -CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a; -SELECT generate_series(1, 10) c INTO pgss_select_into; -COPY pgss_ctas (a, b) FROM STDIN; -CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas; -REFRESH MATERIALIZED VIEW pgss_matv; -BEGIN; -DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv; -FETCH NEXT pgss_cursor; - a | b ----+------ - 1 | ctas -(1 row) - -FETCH FORWARD 5 pgss_cursor; - a | b ----+------ - 2 | ctas - 3 | ctas - 4 | ctas - 5 | ctas - 6 | ctas -(5 rows) - -FETCH FORWARD ALL pgss_cursor; - a | b -----+------ - 7 | ctas - 8 | ctas - 9 | ctas - 10 | ctas - 11 | copy - 12 | copy - 13 | copy -(7 rows) - -COMMIT; -SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | plans | calls | rows --------------------------------------------------------------------------------------+-------+-------+------ - BEGIN | 0 | 1 | 0 - COMMIT | 0 | 1 | 0 - COPY pgss_ctas (a, b) FROM STDIN | 0 | 1 | 3 - CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas | 0 | 1 | 13 - CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a | 0 | 1 | 10 - DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv | 0 | 1 | 0 - FETCH FORWARD 5 pgss_cursor | 0 | 1 | 5 - FETCH FORWARD ALL pgss_cursor | 0 | 1 | 7 - FETCH NEXT pgss_cursor | 0 | 1 | 1 - REFRESH MATERIALIZED VIEW pgss_matv | 0 | 1 | 13 - SELECT generate_series(1, 10) c INTO pgss_select_into | 0 | 1 | 10 - SELECT pg_stat_statements_reset() | 0 | 1 | 1 - SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 | 0 | 0 -(13 rows) - -- -- Track user activity and reset them -- +SET pg_stat_statements.track_utility = TRUE; SELECT pg_stat_statements_reset(); pg_stat_statements_reset -------------------------- @@ -872,84 +751,6 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- DROP ROLE regress_stats_user1; DROP ROLE regress_stats_user2; -DROP MATERIALIZED VIEW pgss_matv; -DROP TABLE pgss_ctas; -DROP TABLE pgss_select_into; --- --- [re]plan counting --- -SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - -(1 row) - -CREATE TABLE test (); -PREPARE prep1 AS SELECT COUNT(*) FROM test; -EXECUTE prep1; - count -------- - 0 -(1 row) - -EXECUTE prep1; - count -------- - 0 -(1 row) - -EXECUTE prep1; - count -------- - 0 -(1 row) - -ALTER TABLE test ADD COLUMN x int; -EXECUTE prep1; - count -------- - 0 -(1 row) - -SELECT 42; - ?column? ----------- - 42 -(1 row) - -SELECT 42; - ?column? ----------- - 42 -(1 row) - -SELECT 42; - ?column? ----------- - 42 -(1 row) - -SELECT query, plans, calls, rows FROM pg_stat_statements - WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C"; - query | plans | calls | rows -----------------------------------------------------------+-------+-------+------ - ALTER TABLE test ADD COLUMN x int | 0 | 1 | 0 - CREATE TABLE test () | 0 | 1 | 0 - SELECT $1 | 3 | 3 | 3 - SELECT pg_stat_statements_reset() | 0 | 1 | 1 - SELECT query, plans, calls, rows FROM pg_stat_statements+| 1 | 0 | 0 - WHERE query NOT LIKE $1 ORDER BY query COLLATE "C" | | | -(5 rows) - --- for the prepared statement we expect at least one replan, but cache --- invalidations could force more -SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_stat_statements - WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C"; - query | plans_ok | calls | rows ---------------------------------------------+----------+-------+------ - PREPARE prep1 AS SELECT COUNT(*) FROM test | t | 4 | 4 -(1 row) - -- -- access to pg_stat_statements_info view -- @@ -965,67 +766,6 @@ SELECT dealloc FROM pg_stat_statements_info; 0 (1 row) --- --- top level handling --- -SET pg_stat_statements.track = 'top'; -DELETE FROM test; -DO $$ -BEGIN - DELETE FROM test; -END; -$$ LANGUAGE plpgsql; -SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel; - query | toplevel | plans | calls ------------------------+----------+-------+------- - DELETE FROM test | t | 1 | 1 - DO $$ +| t | 0 | 1 - BEGIN +| | | - DELETE FROM test;+| | | - END; +| | | - $$ LANGUAGE plpgsql | | | -(2 rows) - -SET pg_stat_statements.track = 'all'; -DELETE FROM test; -DO $$ -BEGIN - DELETE FROM test; -END; -$$ LANGUAGE plpgsql; -SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel; - query | toplevel | plans | calls ------------------------+----------+-------+------- - DELETE FROM test | f | 1 | 1 - DELETE FROM test | t | 2 | 2 - DO $$ +| t | 0 | 2 - BEGIN +| | | - DELETE FROM test;+| | | - END; +| | | - $$ LANGUAGE plpgsql | | | -(3 rows) - --- FROM [ONLY] -CREATE TABLE tbl_inh(id integer); -CREATE TABLE tbl_inh_1() INHERITS (tbl_inh); -INSERT INTO tbl_inh_1 SELECT 1; -SELECT * FROM tbl_inh; - id ----- - 1 -(1 row) - -SELECT * FROM ONLY tbl_inh; - id ----- -(0 rows) - -SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FROM%tbl_inh%'; - count -------- - 2 -(1 row) - -- WITH TIES CREATE TABLE limitoption AS SELECT 0 AS val FROM generate_series(1, 10); SELECT * @@ -1156,4 +896,24 @@ SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%'; 2 (1 row) -DROP EXTENSION pg_stat_statements; +-- FROM [ONLY] +CREATE TABLE tbl_inh(id integer); +CREATE TABLE tbl_inh_1() INHERITS (tbl_inh); +INSERT INTO tbl_inh_1 SELECT 1; +SELECT * FROM tbl_inh; + id +---- + 1 +(1 row) + +SELECT * FROM ONLY tbl_inh; + id +---- +(0 rows) + +SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FROM%tbl_inh%'; + count +------- + 2 +(1 row) + diff --git a/contrib/pg_stat_statements/expected/planning.out b/contrib/pg_stat_statements/expected/planning.out new file mode 100644 index 0000000000..216e46ea2f --- /dev/null +++ b/contrib/pg_stat_statements/expected/planning.out @@ -0,0 +1,195 @@ +-- +-- Information related to planning +-- +-- These tests require track_planning to be enabled. +SET pg_stat_statements.track_planning = TRUE; +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +-- +-- Track the total number of rows retrieved or affected by the utility +-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW, +-- REFRESH MATERIALIZED VIEW and SELECT INTO +-- +CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a; +SELECT generate_series(1, 10) c INTO pgss_select_into; +COPY pgss_ctas (a, b) FROM STDIN; +CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas; +REFRESH MATERIALIZED VIEW pgss_matv; +BEGIN; +DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv; +FETCH NEXT pgss_cursor; + a | b +---+------ + 1 | ctas +(1 row) + +FETCH FORWARD 5 pgss_cursor; + a | b +---+------ + 2 | ctas + 3 | ctas + 4 | ctas + 5 | ctas + 6 | ctas +(5 rows) + +FETCH FORWARD ALL pgss_cursor; + a | b +----+------ + 7 | ctas + 8 | ctas + 9 | ctas + 10 | ctas + 11 | copy + 12 | copy + 13 | copy +(7 rows) + +COMMIT; +SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | plans | calls | rows +-------------------------------------------------------------------------------------+-------+-------+------ + BEGIN | 0 | 1 | 0 + COMMIT | 0 | 1 | 0 + COPY pgss_ctas (a, b) FROM STDIN | 0 | 1 | 3 + CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas | 0 | 1 | 13 + CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a | 0 | 1 | 10 + DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv | 0 | 1 | 0 + FETCH FORWARD 5 pgss_cursor | 0 | 1 | 5 + FETCH FORWARD ALL pgss_cursor | 0 | 1 | 7 + FETCH NEXT pgss_cursor | 0 | 1 | 1 + REFRESH MATERIALIZED VIEW pgss_matv | 0 | 1 | 13 + SELECT generate_series(1, 10) c INTO pgss_select_into | 0 | 1 | 10 + SELECT pg_stat_statements_reset() | 0 | 1 | 1 + SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 | 0 | 0 +(13 rows) + +DROP MATERIALIZED VIEW pgss_matv; +DROP TABLE pgss_ctas; +DROP TABLE pgss_select_into; +-- +-- [re]plan counting +-- +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +CREATE TABLE test (); +PREPARE prep1 AS SELECT COUNT(*) FROM test; +EXECUTE prep1; + count +------- + 0 +(1 row) + +EXECUTE prep1; + count +------- + 0 +(1 row) + +EXECUTE prep1; + count +------- + 0 +(1 row) + +ALTER TABLE test ADD COLUMN x int; +EXECUTE prep1; + count +------- + 0 +(1 row) + +SELECT 42; + ?column? +---------- + 42 +(1 row) + +SELECT 42; + ?column? +---------- + 42 +(1 row) + +SELECT 42; + ?column? +---------- + 42 +(1 row) + +SELECT query, plans, calls, rows FROM pg_stat_statements + WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C"; + query | plans | calls | rows +----------------------------------------------------------+-------+-------+------ + ALTER TABLE test ADD COLUMN x int | 0 | 1 | 0 + CREATE TABLE test () | 0 | 1 | 0 + SELECT $1 | 3 | 3 | 3 + SELECT pg_stat_statements_reset() | 0 | 1 | 1 + SELECT query, plans, calls, rows FROM pg_stat_statements+| 1 | 0 | 0 + WHERE query NOT LIKE $1 ORDER BY query COLLATE "C" | | | +(5 rows) + +-- for the prepared statement we expect at least one replan, but cache +-- invalidations could force more +SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_stat_statements + WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C"; + query | plans_ok | calls | rows +--------------------------------------------+----------+-------+------ + PREPARE prep1 AS SELECT COUNT(*) FROM test | t | 4 | 4 +(1 row) + +-- +-- top level handling +-- +SET pg_stat_statements.track = 'top'; +SET pg_stat_statements.track_utility = TRUE; +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +DELETE FROM test; +DO $$ +BEGIN + DELETE FROM test; +END; +$$ LANGUAGE plpgsql; +SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel; + query | toplevel | plans | calls +-----------------------+----------+-------+------- + DELETE FROM test | t | 1 | 1 + DO $$ +| t | 0 | 1 + BEGIN +| | | + DELETE FROM test;+| | | + END; +| | | + $$ LANGUAGE plpgsql | | | +(2 rows) + +SET pg_stat_statements.track = 'all'; +DELETE FROM test; +DO $$ +BEGIN + DELETE FROM test; +END; +$$ LANGUAGE plpgsql; +SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel; + query | toplevel | plans | calls +-----------------------+----------+-------+------- + DELETE FROM test | f | 1 | 1 + DELETE FROM test | t | 2 | 2 + DO $$ +| t | 0 | 2 + BEGIN +| | | + DELETE FROM test;+| | | + END; +| | | + $$ LANGUAGE plpgsql | | | +(3 rows) + diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out new file mode 100644 index 0000000000..8d3bb7bf6d --- /dev/null +++ b/contrib/pg_stat_statements/expected/utility.out @@ -0,0 +1,72 @@ +-- +-- Utility commands +-- +-- These tests require track_utility to be enabled. +SET pg_stat_statements.track_utility = TRUE; +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +SELECT 1; + ?column? +---------- + 1 +(1 row) + +CREATE TEMP TABLE test (a int, b char(20)); +CREATE INDEX test_b ON test(b); +DROP TABLE test \; +DROP TABLE IF EXISTS test \; +DROP FUNCTION PLUS_ONE(INTEGER); +NOTICE: table "test" does not exist, skipping +-- This DROP query uses two different strings, still they count as one entry. +DROP TABLE IF EXISTS test \; +Drop Table If Exists test \; +DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER); +NOTICE: table "test" does not exist, skipping +NOTICE: table "test" does not exist, skipping +NOTICE: function plus_one(pg_catalog.int4) does not exist, skipping +DROP FUNCTION PLUS_TWO(INTEGER); +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls | rows +--------------------------------------------+-------+------ + CREATE INDEX test_b ON test(b) | 1 | 0 + CREATE TEMP TABLE test (a int, b char(20)) | 1 | 0 + DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER) | 1 | 0 + DROP FUNCTION PLUS_ONE(INTEGER) | 1 | 0 + DROP FUNCTION PLUS_TWO(INTEGER) | 1 | 0 + DROP TABLE IF EXISTS test | 3 | 0 + DROP TABLE test | 1 | 0 + SELECT $1 | 1 | 1 + SELECT pg_stat_statements_reset() | 1 | 1 +(9 rows) + +-- SET statements. +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +-- These use two different strings, still they count as one entry. +SET work_mem = '1MB'; +Set work_mem = '1MB'; +SET work_mem = '2MB'; +RESET work_mem; +SET enable_seqscan = off; +SET enable_seqscan = on; +RESET enable_seqscan; +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls | rows +-----------------------------------+-------+------ + RESET enable_seqscan | 1 | 0 + RESET work_mem | 1 | 0 + SELECT pg_stat_statements_reset() | 1 | 1 + SET enable_seqscan = off | 1 | 0 + SET enable_seqscan = on | 1 | 0 + SET work_mem = '1MB' | 2 | 0 + SET work_mem = '2MB' | 1 | 0 +(7 rows) + diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build index 508b53b4a2..64a6c0a58d 100644 --- a/contrib/pg_stat_statements/meson.build +++ b/contrib/pg_stat_statements/meson.build @@ -41,6 +41,9 @@ tests += { 'regress': { 'sql': [ 'pg_stat_statements', + 'utility', + 'planning', + 'cleanup', 'oldextversions', ], 'regress_args': ['--temp-config', files('pg_stat_statements.conf')], diff --git a/contrib/pg_stat_statements/sql/cleanup.sql b/contrib/pg_stat_statements/sql/cleanup.sql new file mode 100644 index 0000000000..36bec35c40 --- /dev/null +++ b/contrib/pg_stat_statements/sql/cleanup.sql @@ -0,0 +1 @@ +DROP EXTENSION pg_stat_statements; diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql index cebde7392b..168bb4b46a 100644 --- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql +++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql @@ -254,61 +254,10 @@ SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; DROP TABLE pgss_a, pgss_b CASCADE; --- --- utility commands --- -SET pg_stat_statements.track_utility = TRUE; -SELECT pg_stat_statements_reset(); - -SELECT 1; -CREATE INDEX test_b ON test(b); -DROP TABLE test \; -DROP TABLE IF EXISTS test \; -DROP FUNCTION PLUS_ONE(INTEGER); --- This DROP query uses two different strings, still they count as one entry. -DROP TABLE IF EXISTS test \; -Drop Table If Exists test \; -DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER); -DROP FUNCTION PLUS_TWO(INTEGER); --- This SET query uses two different strings, still they count as one entry. -SET work_mem = '1MB'; -Set work_mem = '1MB'; -SET work_mem = '2MB'; -RESET work_mem; -SET enable_seqscan = off; -SET enable_seqscan = on; -RESET enable_seqscan; - -SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; - --- --- Track the total number of rows retrieved or affected by the utility --- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW, --- REFRESH MATERIALIZED VIEW and SELECT INTO --- -SELECT pg_stat_statements_reset(); - -CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a; -SELECT generate_series(1, 10) c INTO pgss_select_into; -COPY pgss_ctas (a, b) FROM STDIN; -11 copy -12 copy -13 copy -\. -CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas; -REFRESH MATERIALIZED VIEW pgss_matv; -BEGIN; -DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv; -FETCH NEXT pgss_cursor; -FETCH FORWARD 5 pgss_cursor; -FETCH FORWARD ALL pgss_cursor; -COMMIT; - -SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; - -- -- Track user activity and reset them -- +SET pg_stat_statements.track_utility = TRUE; SELECT pg_stat_statements_reset(); CREATE ROLE regress_stats_user1; CREATE ROLE regress_stats_user2; @@ -369,30 +318,6 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- DROP ROLE regress_stats_user1; DROP ROLE regress_stats_user2; -DROP MATERIALIZED VIEW pgss_matv; -DROP TABLE pgss_ctas; -DROP TABLE pgss_select_into; - --- --- [re]plan counting --- -SELECT pg_stat_statements_reset(); -CREATE TABLE test (); -PREPARE prep1 AS SELECT COUNT(*) FROM test; -EXECUTE prep1; -EXECUTE prep1; -EXECUTE prep1; -ALTER TABLE test ADD COLUMN x int; -EXECUTE prep1; -SELECT 42; -SELECT 42; -SELECT 42; -SELECT query, plans, calls, rows FROM pg_stat_statements - WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C"; --- for the prepared statement we expect at least one replan, but cache --- invalidations could force more -SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_stat_statements - WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C"; -- -- access to pg_stat_statements_info view @@ -400,37 +325,6 @@ SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_sta SELECT pg_stat_statements_reset(); SELECT dealloc FROM pg_stat_statements_info; --- --- top level handling --- -SET pg_stat_statements.track = 'top'; -DELETE FROM test; -DO $$ -BEGIN - DELETE FROM test; -END; -$$ LANGUAGE plpgsql; -SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel; - -SET pg_stat_statements.track = 'all'; -DELETE FROM test; -DO $$ -BEGIN - DELETE FROM test; -END; -$$ LANGUAGE plpgsql; -SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel; - --- FROM [ONLY] -CREATE TABLE tbl_inh(id integer); -CREATE TABLE tbl_inh_1() INHERITS (tbl_inh); -INSERT INTO tbl_inh_1 SELECT 1; - -SELECT * FROM tbl_inh; -SELECT * FROM ONLY tbl_inh; - -SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FROM%tbl_inh%'; - -- WITH TIES CREATE TABLE limitoption AS SELECT 0 AS val FROM generate_series(1, 10); SELECT * @@ -473,4 +367,12 @@ SELECT ( SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%'; -DROP EXTENSION pg_stat_statements; +-- FROM [ONLY] +CREATE TABLE tbl_inh(id integer); +CREATE TABLE tbl_inh_1() INHERITS (tbl_inh); +INSERT INTO tbl_inh_1 SELECT 1; + +SELECT * FROM tbl_inh; +SELECT * FROM ONLY tbl_inh; + +SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FROM%tbl_inh%'; diff --git a/contrib/pg_stat_statements/sql/planning.sql b/contrib/pg_stat_statements/sql/planning.sql new file mode 100644 index 0000000000..93fa7eb45d --- /dev/null +++ b/contrib/pg_stat_statements/sql/planning.sql @@ -0,0 +1,78 @@ +-- +-- Information related to planning +-- + +-- These tests require track_planning to be enabled. +SET pg_stat_statements.track_planning = TRUE; +SELECT pg_stat_statements_reset(); + +-- +-- Track the total number of rows retrieved or affected by the utility +-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW, +-- REFRESH MATERIALIZED VIEW and SELECT INTO +-- +CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a; +SELECT generate_series(1, 10) c INTO pgss_select_into; +COPY pgss_ctas (a, b) FROM STDIN; +11 copy +12 copy +13 copy +\. +CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas; +REFRESH MATERIALIZED VIEW pgss_matv; +BEGIN; +DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv; +FETCH NEXT pgss_cursor; +FETCH FORWARD 5 pgss_cursor; +FETCH FORWARD ALL pgss_cursor; +COMMIT; + +SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; + +DROP MATERIALIZED VIEW pgss_matv; +DROP TABLE pgss_ctas; +DROP TABLE pgss_select_into; + +-- +-- [re]plan counting +-- +SELECT pg_stat_statements_reset(); +CREATE TABLE test (); +PREPARE prep1 AS SELECT COUNT(*) FROM test; +EXECUTE prep1; +EXECUTE prep1; +EXECUTE prep1; +ALTER TABLE test ADD COLUMN x int; +EXECUTE prep1; +SELECT 42; +SELECT 42; +SELECT 42; +SELECT query, plans, calls, rows FROM pg_stat_statements + WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C"; +-- for the prepared statement we expect at least one replan, but cache +-- invalidations could force more +SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_stat_statements + WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C"; + +-- +-- top level handling +-- +SET pg_stat_statements.track = 'top'; +SET pg_stat_statements.track_utility = TRUE; +SELECT pg_stat_statements_reset(); +DELETE FROM test; +DO $$ +BEGIN + DELETE FROM test; +END; +$$ LANGUAGE plpgsql; +SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel; + +SET pg_stat_statements.track = 'all'; +DELETE FROM test; +DO $$ +BEGIN + DELETE FROM test; +END; +$$ LANGUAGE plpgsql; +SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel; diff --git a/contrib/pg_stat_statements/sql/utility.sql b/contrib/pg_stat_statements/sql/utility.sql new file mode 100644 index 0000000000..163f0a3069 --- /dev/null +++ b/contrib/pg_stat_statements/sql/utility.sql @@ -0,0 +1,34 @@ +-- +-- Utility commands +-- + +-- These tests require track_utility to be enabled. +SET pg_stat_statements.track_utility = TRUE; +SELECT pg_stat_statements_reset(); + +SELECT 1; +CREATE TEMP TABLE test (a int, b char(20)); +CREATE INDEX test_b ON test(b); +DROP TABLE test \; +DROP TABLE IF EXISTS test \; +DROP FUNCTION PLUS_ONE(INTEGER); +-- This DROP query uses two different strings, still they count as one entry. +DROP TABLE IF EXISTS test \; +Drop Table If Exists test \; +DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER); +DROP FUNCTION PLUS_TWO(INTEGER); + +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- SET statements. +SELECT pg_stat_statements_reset(); +-- These use two different strings, still they count as one entry. +SET work_mem = '1MB'; +Set work_mem = '1MB'; +SET work_mem = '2MB'; +RESET work_mem; +SET enable_seqscan = off; +SET enable_seqscan = on; +RESET enable_seqscan; + +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- 2.39.1