From b91faf704700134e7b9b0ee1559d4d57a563e642 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Thu, 16 Feb 2023 09:05:48 +0900 Subject: [PATCH v2 2/4] Add more test for utility queries in pg_stat_statements This adds coverage for utility statements that are directly impacted by the normalization of their internal queries, showing the different in older versions of Postgres and what's on HEAD now that all the utility nodes are jumbled through their nodes. This also provides coverage when more normalization is applied to A_Const and pg_stat_statements, that should check all the DDL patterns with nodes related to utilities that make use of constants. --- contrib/pg_stat_statements/Makefile | 2 +- .../pg_stat_statements/expected/cursors.out | 70 +++ .../expected/pg_stat_statements.out | 1 + .../pg_stat_statements/expected/utility.out | 413 ++++++++++++++++-- contrib/pg_stat_statements/meson.build | 1 + contrib/pg_stat_statements/sql/cursors.sql | 30 ++ .../sql/pg_stat_statements.sql | 1 + contrib/pg_stat_statements/sql/utility.sql | 216 ++++++++- 8 files changed, 681 insertions(+), 53 deletions(-) create mode 100644 contrib/pg_stat_statements/expected/cursors.out create mode 100644 contrib/pg_stat_statements/sql/cursors.sql diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile index 78dc4c1d07..6a4d134c3a 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 utility planning cleanup oldextversions +REGRESS = pg_stat_statements cursors 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/cursors.out b/contrib/pg_stat_statements/expected/cursors.out new file mode 100644 index 0000000000..b31a4c77bb --- /dev/null +++ b/contrib/pg_stat_statements/expected/cursors.out @@ -0,0 +1,70 @@ +-- +-- Cursors +-- +-- 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) + +-- DECLARE +-- SELECT is normalized. +DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1; +CLOSE cursor_stats_1; +DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2; +CLOSE cursor_stats_1; +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls | rows +------------------------------------------------------+-------+------ + CLOSE cursor_stats_1 | 2 | 0 + DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1 | 2 | 0 + SELECT pg_stat_statements_reset() | 1 | 1 +(3 rows) + +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +-- FETCH +BEGIN; +DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2; +DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT 3; +FETCH 1 IN cursor_stats_1; + ?column? +---------- + 2 +(1 row) + +FETCH 1 IN cursor_stats_2; + ?column? +---------- + 3 +(1 row) + +CLOSE cursor_stats_1; +CLOSE cursor_stats_2; +COMMIT; +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls | rows +------------------------------------------------------+-------+------ + BEGIN | 1 | 0 + CLOSE cursor_stats_1 | 1 | 0 + CLOSE cursor_stats_2 | 1 | 0 + COMMIT | 1 | 0 + DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2 | 1 | 0 + DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT 3 | 1 | 0 + FETCH 1 IN cursor_stats_1 | 1 | 1 + FETCH 1 IN cursor_stats_2 | 1 | 1 + SELECT pg_stat_statements_reset() | 1 | 1 +(9 rows) + +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out index 09ceb6dd2b..c7b6035268 100644 --- a/contrib/pg_stat_statements/expected/pg_stat_statements.out +++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out @@ -462,6 +462,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0 (6 rows) +DROP FUNCTION PLUS_ONE(INTEGER); -- -- queries with locking clauses -- diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out index 8d3bb7bf6d..031fd42be4 100644 --- a/contrib/pg_stat_statements/expected/utility.out +++ b/contrib/pg_stat_statements/expected/utility.out @@ -9,47 +9,365 @@ SELECT pg_stat_statements_reset(); (1 row) +-- Use unaligned format for the whole file, easing diff generation on test +-- additions. +\pset format unaligned SELECT 1; - ?column? ----------- - 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 +-- Tables, indexes, triggers +CREATE TEMP TABLE tab_stats (a int, b char(20)); +CREATE INDEX index_stats ON tab_stats(b, (b || 'data1'), (b || 'data2')) WHERE a > 0; +ALTER TABLE tab_stats ALTER COLUMN b set default 'a'; +ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING 'data' || b; +ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> 0); +DROP TABLE tab_stats \; +DROP TABLE IF EXISTS tab_stats \; -- 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); +DROP TABLE IF EXISTS tab_stats \; +Drop Table If Exists tab_stats \; 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 +NOTICE: table "tab_stats" does not exist, skipping +NOTICE: table "tab_stats" does not exist, skipping +NOTICE: table "tab_stats" does not exist, skipping +query|calls|rows +ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> 0)|1|0 +ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING 'data' || b|1|0 +ALTER TABLE tab_stats ALTER COLUMN b set default 'a'|1|0 +CREATE INDEX index_stats ON tab_stats(b, (b || 'data1'), (b || 'data2')) WHERE a > 0|1|0 +CREATE TEMP TABLE tab_stats (a int, b char(20))|1|0 +DROP TABLE IF EXISTS tab_stats|3|0 +DROP TABLE tab_stats|1|0 +SELECT $1|1|1 +SELECT pg_stat_statements_reset()|1|1 (9 rows) +SELECT pg_stat_statements_reset(); +pg_stat_statements_reset +(1 row) +-- Partitions +CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a); +CREATE TABLE pt_stats1 (a int, b int); +ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM (0) TO (100); +CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM (100) TO (200); +CREATE INDEX pt_stats_index ON ONLY pt_stats (a); +CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a); +ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index; +DROP TABLE pt_stats; +-- Views +CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b; +ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2; +DROP VIEW view_stats; +-- Foreign tables +CREATE FOREIGN DATA WRAPPER wrapper_stats; +CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats; +CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats; +ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT 1; +ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> 0); +DROP FOREIGN TABLE foreign_stats; +DROP SERVER server_stats; +DROP FOREIGN DATA WRAPPER wrapper_stats; +-- Functions +CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data')) + RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL; +DROP FUNCTION func_stats; +-- Rules +CREATE TABLE tab_rule_stats (a int, b int); +CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int); +CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD + INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2); +DROP RULE rules_stats ON tab_rule_stats; +DROP TABLE tab_rule_stats, tab_rule_stats_2; +-- Types +CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(35, 2)); +DROP TYPE stats_type; +-- Triggers +CREATE TABLE trigger_tab_stats (a int, b int); +CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql + AS $$ BEGIN return OLD; end; $$; +CREATE TRIGGER trigger_tab_stats + AFTER UPDATE ON trigger_tab_stats + FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true) + EXECUTE FUNCTION trigger_func_stats(); +DROP TABLE trigger_tab_stats; +-- Policies +CREATE TABLE tab_policy_stats (a int, b int); +CREATE POLICY policy_stats ON tab_policy_stats USING (a = 5) WITH CHECK (b < 5); +DROP TABLE tab_policy_stats; +-- Statistics +CREATE TABLE tab_expr_stats (a int, b int); +CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats; +DROP TABLE tab_expr_stats; +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +query|calls|rows +ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT 1|1|0 +ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> 0)|1|0 +ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index|1|0 +ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM (0) TO (100)|1|0 +ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2|1|0 +CREATE FOREIGN DATA WRAPPER wrapper_stats|1|0 +CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats|1|0 +CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data')) + RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL|1|0 +CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql + AS $$ BEGIN return OLD; end; $$|1|0 +CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a)|1|0 +CREATE INDEX pt_stats_index ON ONLY pt_stats (a)|1|0 +CREATE POLICY policy_stats ON tab_policy_stats USING (a = 5) WITH CHECK (b < 5)|1|0 +CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD + INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2)|1|0 +CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats|1|0 +CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats|1|0 +CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a)|1|0 +CREATE TABLE pt_stats1 (a int, b int)|1|0 +CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM (100) TO (200)|1|0 +CREATE TABLE tab_expr_stats (a int, b int)|1|0 +CREATE TABLE tab_policy_stats (a int, b int)|1|0 +CREATE TABLE tab_rule_stats (a int, b int)|1|0 +CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int)|1|0 +CREATE TABLE trigger_tab_stats (a int, b int)|1|0 +CREATE TRIGGER trigger_tab_stats + AFTER UPDATE ON trigger_tab_stats + FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true) + EXECUTE FUNCTION trigger_func_stats()|1|0 +CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(35, 2))|1|0 +CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b|1|0 +DROP FOREIGN DATA WRAPPER wrapper_stats|1|0 +DROP FOREIGN TABLE foreign_stats|1|0 +DROP FUNCTION func_stats|1|0 +DROP RULE rules_stats ON tab_rule_stats|1|0 +DROP SERVER server_stats|1|0 +DROP TABLE pt_stats|1|0 +DROP TABLE tab_expr_stats|1|0 +DROP TABLE tab_policy_stats|1|0 +DROP TABLE tab_rule_stats, tab_rule_stats_2|1|0 +DROP TABLE trigger_tab_stats|1|0 +DROP TYPE stats_type|1|0 +DROP VIEW view_stats|1|0 +SELECT pg_stat_statements_reset()|1|1 +(39 rows) +-- Transaction statements +SELECT pg_stat_statements_reset(); +pg_stat_statements_reset + +(1 row) +BEGIN; +ABORT; +BEGIN; +ROLLBACK; +-- WORK +BEGIN WORK; +COMMIT WORK; +BEGIN WORK; +ABORT WORK; +-- TRANSACTION +BEGIN TRANSACTION; +COMMIT TRANSACTION; +BEGIN TRANSACTION; +ABORT TRANSACTION; +-- More isolation levels +BEGIN TRANSACTION DEFERRABLE; +COMMIT TRANSACTION AND NO CHAIN; +BEGIN ISOLATION LEVEL SERIALIZABLE; +COMMIT; +BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; +COMMIT; +-- List of A_Const nodes +BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE; +COMMIT; +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +query|calls|rows +ABORT|4|0 +BEGIN|6|0 +BEGIN ISOLATION LEVEL SERIALIZABLE|2|0 +BEGIN TRANSACTION DEFERRABLE|1|0 +BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE|1|0 +COMMIT WORK|6|0 +SELECT pg_stat_statements_reset()|1|1 +(7 rows) +SELECT pg_stat_statements_reset(); +pg_stat_statements_reset + +(1 row) +-- EXPLAIN statements +-- A Query is used, normalized by the query jumbling. +EXPLAIN (costs off) SELECT 1; +QUERY PLAN +Result +(1 row) +EXPLAIN (costs off) SELECT 2; +QUERY PLAN +Result +(1 row) +EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3; +QUERY PLAN +Function Scan on generate_series tab + Filter: (a = 3) +(2 rows) +EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 7; +QUERY PLAN +Function Scan on generate_series tab + Filter: (a = 7) +(2 rows) +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +query|calls|rows +EXPLAIN (costs off) SELECT 1|2|0 +EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3|2|0 +SELECT pg_stat_statements_reset()|1|1 +(3 rows) +-- CALL +CREATE OR REPLACE PROCEDURE sum_one(i int) AS $$ +DECLARE + r int; +BEGIN + SELECT (i + i)::int INTO r; +END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE PROCEDURE sum_two(i int, j int) AS $$ +DECLARE + r int; +BEGIN + SELECT (i + j)::int INTO r; +END; $$ LANGUAGE plpgsql; +SELECT pg_stat_statements_reset(); +pg_stat_statements_reset + +(1 row) +CALL sum_one(3); +CALL sum_one(199); +CALL sum_two(1,1); +CALL sum_two(1,2); +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +query|calls|rows +CALL sum_one(199)|1|0 +CALL sum_one(3)|1|0 +CALL sum_two(1,1)|1|0 +CALL sum_two(1,2)|1|0 +SELECT pg_stat_statements_reset()|1|1 +(5 rows) +-- COPY +CREATE TABLE copy_stats (a int, b int); +SELECT pg_stat_statements_reset(); +pg_stat_statements_reset + +(1 row) +-- Some queries with A_Const nodes. +COPY (SELECT 1) TO STDOUT; +1 +COPY (SELECT 2) TO STDOUT; +2 +COPY (INSERT INTO copy_stats VALUES (1, 1) RETURNING *) TO STDOUT; +1 1 +COPY (INSERT INTO copy_stats VALUES (2, 2) RETURNING *) TO STDOUT; +2 2 +COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT; +1 2 +2 3 +COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT; +1 4 +2 5 +COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT; +1 4 +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +query|calls|rows +COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT|1|1 +COPY (INSERT INTO copy_stats VALUES (1, 1) RETURNING *) TO STDOUT|1|1 +COPY (INSERT INTO copy_stats VALUES (2, 2) RETURNING *) TO STDOUT|1|1 +COPY (SELECT 1) TO STDOUT|1|1 +COPY (SELECT 2) TO STDOUT|1|1 +COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT|1|2 +COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT|1|2 +SELECT pg_stat_statements_reset()|1|1 +(8 rows) +DROP TABLE copy_stats; +SELECT pg_stat_statements_reset(); +pg_stat_statements_reset + +(1 row) +-- CREATE TABLE AS +-- SELECT queries are normalized, creating matching query IDs. +CREATE TABLE ctas_stats_1 AS SELECT 1 AS a; +DROP TABLE ctas_stats_1; +CREATE TABLE ctas_stats_1 AS SELECT 2 AS a; +DROP TABLE ctas_stats_1; +CREATE TABLE ctas_stats_2 AS + SELECT a AS col1, 2::int AS col2 + FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2; +DROP TABLE ctas_stats_2; +CREATE TABLE ctas_stats_2 AS + SELECT a AS col1, 4::int AS col2 + FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 1; +DROP TABLE ctas_stats_2; +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +query|calls|rows +CREATE TABLE ctas_stats_1 AS SELECT 1 AS a|2|2 +CREATE TABLE ctas_stats_2 AS + SELECT a AS col1, 2::int AS col2 + FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2|2|4 +DROP TABLE ctas_stats_1|2|0 +DROP TABLE ctas_stats_2|2|0 +SELECT pg_stat_statements_reset()|1|1 +(5 rows) +SELECT pg_stat_statements_reset(); +pg_stat_statements_reset + +(1 row) +-- CREATE MATERIALIZED VIEW +-- SELECT queries are normalized, creating matching query IDs. +CREATE MATERIALIZED VIEW matview_stats_1 AS + SELECT a AS col1, 2::int AS col2 + FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2; +DROP MATERIALIZED VIEW matview_stats_1; +CREATE MATERIALIZED VIEW matview_stats_1 AS + SELECT a AS col1, 4::int AS col2 + FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3; +DROP MATERIALIZED VIEW matview_stats_1; +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +query|calls|rows +CREATE MATERIALIZED VIEW matview_stats_1 AS + SELECT a AS col1, 2::int AS col2 + FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2|2|2 +DROP MATERIALIZED VIEW matview_stats_1|2|0 +SELECT pg_stat_statements_reset()|1|1 +(3 rows) +SELECT pg_stat_statements_reset(); +pg_stat_statements_reset + +(1 row) +-- CREATE VIEW +CREATE VIEW view_stats_1 AS + SELECT a AS col1, 2::int AS col2 + FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2; +DROP VIEW view_stats_1; +CREATE VIEW view_stats_1 AS + SELECT a AS col1, 4::int AS col2 + FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3; +DROP VIEW view_stats_1; +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +query|calls|rows +CREATE VIEW view_stats_1 AS + SELECT a AS col1, 2::int AS col2 + FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2|1|0 +CREATE VIEW view_stats_1 AS + SELECT a AS col1, 4::int AS col2 + FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3|1|0 +DROP VIEW view_stats_1|2|0 +SELECT pg_stat_statements_reset()|1|1 +(4 rows) +SELECT pg_stat_statements_reset(); +pg_stat_statements_reset + +(1 row) +-- Domains +CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0); +ALTER DOMAIN domain_stats SET DEFAULT '3'; +ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1); +DROP DOMAIN domain_stats; -- SET statements. SELECT pg_stat_statements_reset(); - pg_stat_statements_reset --------------------------- - -(1 row) +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'; @@ -58,15 +376,24 @@ RESET work_mem; SET enable_seqscan = off; SET enable_seqscan = on; RESET enable_seqscan; +-- SET TRANSACTION ISOLATION +BEGIN; +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; +COMMIT; 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) - +query|calls|rows +BEGIN|1|0 +COMMIT|1|0 +RESET enable_seqscan|1|0 +RESET work_mem|1|0 +SELECT pg_stat_statements_reset()|1|1 +SET TRANSACTION ISOLATION LEVEL READ COMMITTED|1|0 +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ|1|0 +SET TRANSACTION ISOLATION LEVEL SERIALIZABLE|1|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 +(12 rows) diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build index 64a6c0a58d..21bf5ee2e9 100644 --- a/contrib/pg_stat_statements/meson.build +++ b/contrib/pg_stat_statements/meson.build @@ -41,6 +41,7 @@ tests += { 'regress': { 'sql': [ 'pg_stat_statements', + 'cursors', 'utility', 'planning', 'cleanup', diff --git a/contrib/pg_stat_statements/sql/cursors.sql b/contrib/pg_stat_statements/sql/cursors.sql new file mode 100644 index 0000000000..60894e675b --- /dev/null +++ b/contrib/pg_stat_statements/sql/cursors.sql @@ -0,0 +1,30 @@ +-- +-- Cursors +-- + +-- These tests require track_utility to be enabled. +SET pg_stat_statements.track_utility = TRUE; +SELECT pg_stat_statements_reset(); + +-- DECLARE +-- SELECT is normalized. +DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1; +CLOSE cursor_stats_1; +DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2; +CLOSE cursor_stats_1; + +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +SELECT pg_stat_statements_reset(); + +-- FETCH +BEGIN; +DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2; +DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT 3; +FETCH 1 IN cursor_stats_1; +FETCH 1 IN cursor_stats_2; +CLOSE cursor_stats_1; +CLOSE cursor_stats_2; +COMMIT; + +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +SELECT pg_stat_statements_reset(); diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql index 168bb4b46a..d715ec120a 100644 --- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql +++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql @@ -222,6 +222,7 @@ SELECT PLUS_ONE(3); SELECT PLUS_ONE(1); SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +DROP FUNCTION PLUS_ONE(INTEGER); -- -- queries with locking clauses diff --git a/contrib/pg_stat_statements/sql/utility.sql b/contrib/pg_stat_statements/sql/utility.sql index 163f0a3069..7f9080821c 100644 --- a/contrib/pg_stat_statements/sql/utility.sql +++ b/contrib/pg_stat_statements/sql/utility.sql @@ -5,21 +5,213 @@ -- These tests require track_utility to be enabled. SET pg_stat_statements.track_utility = TRUE; SELECT pg_stat_statements_reset(); +-- Use unaligned format for the whole file, easing diff generation on test +-- additions. +\pset format unaligned 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); + +-- Tables, indexes, triggers +CREATE TEMP TABLE tab_stats (a int, b char(20)); +CREATE INDEX index_stats ON tab_stats(b, (b || 'data1'), (b || 'data2')) WHERE a > 0; +ALTER TABLE tab_stats ALTER COLUMN b set default 'a'; +ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING 'data' || b; +ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> 0); +DROP TABLE tab_stats \; +DROP TABLE IF EXISTS tab_stats \; -- 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); +DROP TABLE IF EXISTS tab_stats \; +Drop Table If Exists tab_stats \; +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +SELECT pg_stat_statements_reset(); + +-- Partitions +CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a); +CREATE TABLE pt_stats1 (a int, b int); +ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM (0) TO (100); +CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM (100) TO (200); +CREATE INDEX pt_stats_index ON ONLY pt_stats (a); +CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a); +ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index; +DROP TABLE pt_stats; + +-- Views +CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b; +ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2; +DROP VIEW view_stats; + +-- Foreign tables +CREATE FOREIGN DATA WRAPPER wrapper_stats; +CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats; +CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats; +ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT 1; +ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> 0); +DROP FOREIGN TABLE foreign_stats; +DROP SERVER server_stats; +DROP FOREIGN DATA WRAPPER wrapper_stats; + +-- Functions +CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data')) + RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL; +DROP FUNCTION func_stats; + +-- Rules +CREATE TABLE tab_rule_stats (a int, b int); +CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int); +CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD + INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2); +DROP RULE rules_stats ON tab_rule_stats; +DROP TABLE tab_rule_stats, tab_rule_stats_2; + +-- Types +CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(35, 2)); +DROP TYPE stats_type; + +-- Triggers +CREATE TABLE trigger_tab_stats (a int, b int); +CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql + AS $$ BEGIN return OLD; end; $$; +CREATE TRIGGER trigger_tab_stats + AFTER UPDATE ON trigger_tab_stats + FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true) + EXECUTE FUNCTION trigger_func_stats(); +DROP TABLE trigger_tab_stats; + +-- Policies +CREATE TABLE tab_policy_stats (a int, b int); +CREATE POLICY policy_stats ON tab_policy_stats USING (a = 5) WITH CHECK (b < 5); +DROP TABLE tab_policy_stats; + +-- Statistics +CREATE TABLE tab_expr_stats (a int, b int); +CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats; +DROP TABLE tab_expr_stats; SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- Transaction statements +SELECT pg_stat_statements_reset(); +BEGIN; +ABORT; +BEGIN; +ROLLBACK; +-- WORK +BEGIN WORK; +COMMIT WORK; +BEGIN WORK; +ABORT WORK; +-- TRANSACTION +BEGIN TRANSACTION; +COMMIT TRANSACTION; +BEGIN TRANSACTION; +ABORT TRANSACTION; +-- More isolation levels +BEGIN TRANSACTION DEFERRABLE; +COMMIT TRANSACTION AND NO CHAIN; +BEGIN ISOLATION LEVEL SERIALIZABLE; +COMMIT; +BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; +COMMIT; +-- List of A_Const nodes +BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE; +COMMIT; + +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +SELECT pg_stat_statements_reset(); + +-- EXPLAIN statements +-- A Query is used, normalized by the query jumbling. +EXPLAIN (costs off) SELECT 1; +EXPLAIN (costs off) SELECT 2; +EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3; +EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 7; + +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- CALL +CREATE OR REPLACE PROCEDURE sum_one(i int) AS $$ +DECLARE + r int; +BEGIN + SELECT (i + i)::int INTO r; +END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE PROCEDURE sum_two(i int, j int) AS $$ +DECLARE + r int; +BEGIN + SELECT (i + j)::int INTO r; +END; $$ LANGUAGE plpgsql; +SELECT pg_stat_statements_reset(); +CALL sum_one(3); +CALL sum_one(199); +CALL sum_two(1,1); +CALL sum_two(1,2); +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- COPY +CREATE TABLE copy_stats (a int, b int); +SELECT pg_stat_statements_reset(); +-- Some queries with A_Const nodes. +COPY (SELECT 1) TO STDOUT; +COPY (SELECT 2) TO STDOUT; +COPY (INSERT INTO copy_stats VALUES (1, 1) RETURNING *) TO STDOUT; +COPY (INSERT INTO copy_stats VALUES (2, 2) RETURNING *) TO STDOUT; +COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT; +COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT; +COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT; + +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +DROP TABLE copy_stats; +SELECT pg_stat_statements_reset(); + +-- CREATE TABLE AS +-- SELECT queries are normalized, creating matching query IDs. +CREATE TABLE ctas_stats_1 AS SELECT 1 AS a; +DROP TABLE ctas_stats_1; +CREATE TABLE ctas_stats_1 AS SELECT 2 AS a; +DROP TABLE ctas_stats_1; +CREATE TABLE ctas_stats_2 AS + SELECT a AS col1, 2::int AS col2 + FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2; +DROP TABLE ctas_stats_2; +CREATE TABLE ctas_stats_2 AS + SELECT a AS col1, 4::int AS col2 + FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 1; +DROP TABLE ctas_stats_2; +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +SELECT pg_stat_statements_reset(); + +-- CREATE MATERIALIZED VIEW +-- SELECT queries are normalized, creating matching query IDs. +CREATE MATERIALIZED VIEW matview_stats_1 AS + SELECT a AS col1, 2::int AS col2 + FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2; +DROP MATERIALIZED VIEW matview_stats_1; +CREATE MATERIALIZED VIEW matview_stats_1 AS + SELECT a AS col1, 4::int AS col2 + FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3; +DROP MATERIALIZED VIEW matview_stats_1; +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +SELECT pg_stat_statements_reset(); + +-- CREATE VIEW +CREATE VIEW view_stats_1 AS + SELECT a AS col1, 2::int AS col2 + FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2; +DROP VIEW view_stats_1; +CREATE VIEW view_stats_1 AS + SELECT a AS col1, 4::int AS col2 + FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3; +DROP VIEW view_stats_1; +SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; +SELECT pg_stat_statements_reset(); + +-- Domains +CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0); +ALTER DOMAIN domain_stats SET DEFAULT '3'; +ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1); +DROP DOMAIN domain_stats; + -- SET statements. SELECT pg_stat_statements_reset(); -- These use two different strings, still they count as one entry. @@ -30,5 +222,11 @@ RESET work_mem; SET enable_seqscan = off; SET enable_seqscan = on; RESET enable_seqscan; +-- SET TRANSACTION ISOLATION +BEGIN; +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; +COMMIT; SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; -- 2.39.1