diff options
| -rw-r--r-- | contrib/pg_stat_statements/Makefile | 3 | ||||
| -rw-r--r-- | contrib/pg_stat_statements/expected/cursors.out | 70 | ||||
| -rw-r--r-- | contrib/pg_stat_statements/expected/utility.out | 468 | ||||
| -rw-r--r-- | contrib/pg_stat_statements/meson.build | 1 | ||||
| -rw-r--r-- | contrib/pg_stat_statements/sql/cursors.sql | 30 | ||||
| -rw-r--r-- | contrib/pg_stat_statements/sql/utility.sql | 232 |
6 files changed, 774 insertions, 30 deletions
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile index f235f73df56..69fbc6a8580 100644 --- a/contrib/pg_stat_statements/Makefile +++ b/contrib/pg_stat_statements/Makefile @@ -17,7 +17,8 @@ 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 level_tracking planning cleanup oldextversions +REGRESS = pg_stat_statements cursors utility level_tracking 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 00000000000..5d0dc196f97 --- /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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | rows | query +-------+------+------------------------------------------------------ + 2 | 0 | CLOSE cursor_stats_1 + 2 | 0 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1 + 1 | 1 | SELECT pg_stat_statements_reset() +(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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | rows | query +-------+------+------------------------------------------------------ + 1 | 0 | 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 | 1 | FETCH 1 IN cursor_stats_1 + 1 | 1 | FETCH 1 IN cursor_stats_2 + 1 | 1 | SELECT pg_stat_statements_reset() +(9 rows) + +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out index 2395ccd6b45..dbb8f661c01 100644 --- a/contrib/pg_stat_statements/expected/utility.out +++ b/contrib/pg_stat_statements/expected/utility.out @@ -9,32 +9,456 @@ SELECT pg_stat_statements_reset(); (1 row) -SELECT 1; - ?column? ----------- - 1 +-- 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 tab_stats \; +Drop Table If Exists tab_stats \; +SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; +NOTICE: table "tab_stats" does not exist, skipping +NOTICE: table "tab_stats" does not exist, skipping +NOTICE: table "tab_stats" does not exist, skipping + calls | rows | query +-------+------+-------------------------------------------------------------------------------------- + 1 | 0 | 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)) + 3 | 0 | DROP TABLE IF EXISTS tab_stats + 1 | 0 | DROP TABLE tab_stats + 1 | 1 | SELECT pg_stat_statements_reset() +(8 rows) + +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + (1 row) -CREATE TEMP TABLE stats_util_tab (a int, b char(20)); -CREATE INDEX test_b ON stats_util_tab(b); -DROP TABLE stats_util_tab \; -DROP TABLE IF EXISTS stats_util_tab; -NOTICE: table "stats_util_tab" does not exist, skipping --- This DROP query uses two different strings, still they count as one entry. -DROP TABLE IF EXISTS stats_util_tab \; -Drop Table If Exists stats_util_tab; -NOTICE: table "stats_util_tab" does not exist, skipping -NOTICE: table "stats_util_tab" does not exist, skipping +-- 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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | rows | query +-------+------+------------------------------------------------------------------------------------- + 1 | 0 | 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 | 1 | SELECT pg_stat_statements_reset() +(39 rows) + +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +-- Transaction statements +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, same lists. +BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE; +COMMIT; +BEGIN TRANSACTION NOT DEFERRABLE, READ ONLY, READ WRITE, DEFERRABLE; +COMMIT; +SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | rows | query +-------+------+--------------------------------------------------------------------- + 4 | 0 | ABORT + 6 | 0 | BEGIN + 2 | 0 | BEGIN ISOLATION LEVEL SERIALIZABLE + 1 | 0 | BEGIN TRANSACTION DEFERRABLE + 1 | 0 | BEGIN TRANSACTION NOT DEFERRABLE, READ ONLY, READ WRITE, DEFERRABLE + 1 | 0 | BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE + 7 | 0 | COMMIT WORK + 1 | 1 | SELECT pg_stat_statements_reset() +(8 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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | rows | query +-------+------+------------------------------------------------------------------------------- + 2 | 0 | EXPLAIN (costs off) SELECT 1 + 2 | 0 | EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3 + 1 | 1 | SELECT pg_stat_statements_reset() +(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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | rows | query +-------+------+----------------------------------- + 1 | 0 | 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 | 1 | SELECT pg_stat_statements_reset() +(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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | rows | query +-------+------+------------------------------------------------------------------- + 1 | 1 | 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 | 2 | 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 | 1 | SELECT pg_stat_statements_reset() +(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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | rows | query +-------+------+----------------------------------------------------------------- + 2 | 2 | CREATE TABLE ctas_stats_1 AS SELECT 1 AS a + 2 | 4 | 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 | 0 | DROP TABLE ctas_stats_1 + 2 | 0 | DROP TABLE ctas_stats_2 + 1 | 1 | SELECT pg_stat_statements_reset() +(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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | rows | query +-------+------+----------------------------------------------------------------- + 2 | 2 | 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 | 0 | DROP MATERIALIZED VIEW matview_stats_1 + 1 | 1 | SELECT pg_stat_statements_reset() +(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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | rows | query +-------+------+----------------------------------------------------------------- + 1 | 0 | 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 + 2 | 0 | DROP VIEW view_stats_1 + 1 | 1 | SELECT pg_stat_statements_reset() +(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; +SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | rows | query +-------+------+---------------------------------------------------------------------------- + 1 | 0 | ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1) + 1 | 0 | ALTER DOMAIN domain_stats SET DEFAULT '3' + 1 | 0 | CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0) + 1 | 0 | DROP DOMAIN domain_stats + 1 | 1 | SELECT pg_stat_statements_reset() +(5 rows) + +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +-- SET statements. +-- 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; +-- SET TRANSACTION ISOLATION +BEGIN; +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; +COMMIT; +-- SET SESSION CHARACTERISTICS +SET SESSION SESSION AUTHORIZATION DEFAULT; +RESET SESSION AUTHORIZATION; +BEGIN; +SET LOCAL SESSION AUTHORIZATION DEFAULT; +RESET SESSION AUTHORIZATION; +COMMIT; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+------------------------------------------------------ - 1 | 0 | CREATE INDEX test_b ON stats_util_tab(b) - 1 | 0 | CREATE TEMP TABLE stats_util_tab (a int, b char(20)) - 3 | 0 | DROP TABLE IF EXISTS stats_util_tab - 1 | 0 | DROP TABLE stats_util_tab - 1 | 1 | SELECT $1 + calls | rows | query +-------+------+------------------------------------------------- + 2 | 0 | BEGIN + 2 | 0 | COMMIT + 2 | 0 | RESET SESSION AUTHORIZATION + 1 | 0 | RESET enable_seqscan + 1 | 0 | RESET work_mem 1 | 1 | SELECT pg_stat_statements_reset() -(6 rows) + 1 | 0 | SET LOCAL SESSION AUTHORIZATION DEFAULT + 1 | 0 | SET SESSION SESSION AUTHORIZATION DEFAULT + 1 | 0 | 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 + 2 | 0 | SET work_mem = '1MB' + 1 | 0 | SET work_mem = '2MB' +(15 rows) SELECT pg_stat_statements_reset(); pg_stat_statements_reset diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build index 93f8b0b3ff8..10ccc263000 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', 'level_tracking', 'planning', diff --git a/contrib/pg_stat_statements/sql/cursors.sql b/contrib/pg_stat_statements/sql/cursors.sql new file mode 100644 index 00000000000..cef6dc9e1b8 --- /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 calls, rows, query 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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; +SELECT pg_stat_statements_reset(); diff --git a/contrib/pg_stat_statements/sql/utility.sql b/contrib/pg_stat_statements/sql/utility.sql index 8710b55cf9e..4c58a4c978e 100644 --- a/contrib/pg_stat_statements/sql/utility.sql +++ b/contrib/pg_stat_statements/sql/utility.sql @@ -6,14 +6,232 @@ SET pg_stat_statements.track_utility = TRUE; SELECT pg_stat_statements_reset(); -SELECT 1; -CREATE TEMP TABLE stats_util_tab (a int, b char(20)); -CREATE INDEX test_b ON stats_util_tab(b); -DROP TABLE stats_util_tab \; -DROP TABLE IF EXISTS stats_util_tab; +-- 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 stats_util_tab \; -Drop Table If Exists stats_util_tab; +DROP TABLE IF EXISTS tab_stats \; +Drop Table If Exists tab_stats \; +SELECT calls, rows, query 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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; +SELECT pg_stat_statements_reset(); + +-- Transaction statements +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, same lists. +BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE; +COMMIT; +BEGIN TRANSACTION NOT DEFERRABLE, READ ONLY, READ WRITE, DEFERRABLE; +COMMIT; +SELECT calls, rows, query 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 calls, rows, query 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 calls, rows, query 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 calls, rows, query 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 calls, rows, query 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 calls, rows, query 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 calls, rows, query 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; +SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; +SELECT pg_stat_statements_reset(); + +-- SET statements. +-- 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; +-- SET TRANSACTION ISOLATION +BEGIN; +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; +COMMIT; +-- SET SESSION CHARACTERISTICS +SET SESSION SESSION AUTHORIZATION DEFAULT; +RESET SESSION AUTHORIZATION; +BEGIN; +SET LOCAL SESSION AUTHORIZATION DEFAULT; +RESET SESSION AUTHORIZATION; +COMMIT; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; SELECT pg_stat_statements_reset(); |