Skip to content

Commit e8dbdb1

Browse files
michaelpqmichaelpq
committed
Refactor tests of pg_stat_statements for planning, utility and level tracking
pg_stat_statements.sql acts as the main file for all the core tests of the module, but things have become complicated to follow over the years as some of the sub-scenarios tested in this file rely on assumptions that come from completely different areas of it, like a GUC setup or a relation created previously. For example, row tracking for CTAS/COPY was looking at the number of plans, which was not necessary, or level tracking was mixed with checks on planner counts. This commit refactors the tests of pg_stat_statements, by moving test cases out of pg_stat_statements.sql into their own file, as of: - Planning-related tests in planning.sql, for [re]plan counts and top-level handling. These depend on pg_stat_statements.track_planning. - Utilities in utility.sql (pg_stat_statements.track_utility), that includes now the tests for: -- Row tracking for CTAS, CREATE MATERIALIZED VIEW, COPY. -- Basic utility statements. -- SET statements. - Tracking level, depending on pg_stat_statements.track. This part has been looking at scenarios with DO blocks, PL functions and SQL functions. pg_stat_statements.sql (still named the same for now) still includes some checks for role-level tracking and WAL generation metrics, that ought to become independent in the long term for clarity. While on it, this switches the order of the attributes when querying pg_stat_statements, the query field becoming last. This makes much easier the tracking of changes related to normalization, as queries are the only variable-length attributes queried (unaligned mode would be one extra choice, but that reduces the checks on the other fields). Test scenarios and their results match exactly with what was happening before this commit in terms of calls, number of plans, number of rows, cached data or level tracking, so this has no effect on the coverage in terms of what is produced by the reports in the table pg_stat_statements. A follow-up patch will extend more the tests of pg_stat_statements around utilities, so this split creates a foundation for this purpose, without complicating more pg_stat_statements.sql. Reviewed-by: Bertrand Drouvot Discussion: https://postgr.es/m/Y+MRdEq9W9XVa2AB@paquier.xyz
1 parent e72910f commit e8dbdb1

12 files changed

+623
-570
lines changed

‎contrib/pg_stat_statements/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,7 @@ PGFILEDESC = "pg_stat_statements - execution statistics of SQL statements"
1717
LDFLAGS_SL += $(filter -lm, $(LIBS))
1818

1919
REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
20-
REGRESS = pg_stat_statements oldextversions
20+
REGRESS = pg_stat_statements utility level_tracking planning cleanup oldextversions
2121
# Disabled because these tests require "shared_preload_libraries=pg_stat_statements",
2222
# which typical installcheck users do not have (e.g. buildfarm clients).
2323
NO_INSTALLCHECK = 1

‎contrib/pg_stat_statements/expected/cleanup.out

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
DROP EXTENSION pg_stat_statements;

‎contrib/pg_stat_statements/expected/level_tracking.out

Lines changed: 204 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,204 @@
1+
--
2+
-- Statement level tracking
3+
--
4+
SET pg_stat_statements.track_utility = TRUE;
5+
SELECT pg_stat_statements_reset();
6+
pg_stat_statements_reset
7+
--------------------------
8+
9+
(1 row)
10+
11+
-- DO block - top-level tracking.
12+
CREATE TABLE stats_track_tab (x int);
13+
SET pg_stat_statements.track = 'top';
14+
DELETE FROM stats_track_tab;
15+
DO $$
16+
BEGIN
17+
DELETE FROM stats_track_tab;
18+
END;
19+
$$ LANGUAGE plpgsql;
20+
SELECT toplevel, calls, query FROM pg_stat_statements
21+
WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
22+
toplevel | calls | query
23+
----------+-------+--------------------------------
24+
t | 1 | DELETE FROM stats_track_tab
25+
t | 1 | DO $$ +
26+
| | BEGIN +
27+
| | DELETE FROM stats_track_tab;+
28+
| | END; +
29+
| | $$ LANGUAGE plpgsql
30+
(2 rows)
31+
32+
SELECT pg_stat_statements_reset();
33+
pg_stat_statements_reset
34+
--------------------------
35+
36+
(1 row)
37+
38+
-- DO block - all-level tracking.
39+
SET pg_stat_statements.track = 'all';
40+
DELETE FROM stats_track_tab;
41+
DO $$
42+
BEGIN
43+
DELETE FROM stats_track_tab;
44+
END; $$;
45+
DO LANGUAGE plpgsql $$
46+
BEGIN
47+
-- this is a SELECT
48+
PERFORM 'hello world'::TEXT;
49+
END; $$;
50+
SELECT toplevel, calls, query FROM pg_stat_statements
51+
ORDER BY query COLLATE "C", toplevel;
52+
toplevel | calls | query
53+
----------+-------+--------------------------------------
54+
f | 1 | DELETE FROM stats_track_tab
55+
t | 1 | DELETE FROM stats_track_tab
56+
t | 1 | DO $$ +
57+
| | BEGIN +
58+
| | DELETE FROM stats_track_tab; +
59+
| | END; $$
60+
t | 1 | DO LANGUAGE plpgsql $$ +
61+
| | BEGIN +
62+
| | -- this is a SELECT +
63+
| | PERFORM 'hello world'::TEXT; +
64+
| | END; $$
65+
f | 1 | SELECT $1::TEXT
66+
t | 1 | SELECT pg_stat_statements_reset()
67+
t | 1 | SET pg_stat_statements.track = 'all'
68+
(7 rows)
69+
70+
-- PL/pgSQL function - top-level tracking.
71+
SET pg_stat_statements.track = 'top';
72+
SET pg_stat_statements.track_utility = FALSE;
73+
SELECT pg_stat_statements_reset();
74+
pg_stat_statements_reset
75+
--------------------------
76+
77+
(1 row)
78+
79+
CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
80+
DECLARE
81+
r INTEGER;
82+
BEGIN
83+
SELECT (i + 1 + 1.0)::INTEGER INTO r;
84+
RETURN r;
85+
END; $$ LANGUAGE plpgsql;
86+
SELECT PLUS_TWO(3);
87+
plus_two
88+
----------
89+
5
90+
(1 row)
91+
92+
SELECT PLUS_TWO(7);
93+
plus_two
94+
----------
95+
9
96+
(1 row)
97+
98+
-- SQL function --- use LIMIT to keep it from being inlined
99+
CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
100+
$$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
101+
SELECT PLUS_ONE(8);
102+
plus_one
103+
----------
104+
9
105+
(1 row)
106+
107+
SELECT PLUS_ONE(10);
108+
plus_one
109+
----------
110+
11
111+
(1 row)
112+
113+
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
114+
calls | rows | query
115+
-------+------+-----------------------------------
116+
2 | 2 | SELECT PLUS_ONE($1)
117+
2 | 2 | SELECT PLUS_TWO($1)
118+
1 | 1 | SELECT pg_stat_statements_reset()
119+
(3 rows)
120+
121+
-- PL/pgSQL function - all-level tracking.
122+
SET pg_stat_statements.track = 'all';
123+
SELECT pg_stat_statements_reset();
124+
pg_stat_statements_reset
125+
--------------------------
126+
127+
(1 row)
128+
129+
-- we drop and recreate the functions to avoid any caching funnies
130+
DROP FUNCTION PLUS_ONE(INTEGER);
131+
DROP FUNCTION PLUS_TWO(INTEGER);
132+
-- PL/pgSQL function
133+
CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
134+
DECLARE
135+
r INTEGER;
136+
BEGIN
137+
SELECT (i + 1 + 1.0)::INTEGER INTO r;
138+
RETURN r;
139+
END; $$ LANGUAGE plpgsql;
140+
SELECT PLUS_TWO(-1);
141+
plus_two
142+
----------
143+
1
144+
(1 row)
145+
146+
SELECT PLUS_TWO(2);
147+
plus_two
148+
----------
149+
4
150+
(1 row)
151+
152+
-- SQL function --- use LIMIT to keep it from being inlined
153+
CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
154+
$$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
155+
SELECT PLUS_ONE(3);
156+
plus_one
157+
----------
158+
4
159+
(1 row)
160+
161+
SELECT PLUS_ONE(1);
162+
plus_one
163+
----------
164+
2
165+
(1 row)
166+
167+
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
168+
calls | rows | query
169+
-------+------+-----------------------------------
170+
2 | 2 | SELECT (i + $2 + $3)::INTEGER
171+
2 | 2 | SELECT (i + $2)::INTEGER LIMIT $3
172+
2 | 2 | SELECT PLUS_ONE($1)
173+
2 | 2 | SELECT PLUS_TWO($1)
174+
1 | 1 | SELECT pg_stat_statements_reset()
175+
(5 rows)
176+
177+
DROP FUNCTION PLUS_ONE(INTEGER);
178+
--
179+
-- pg_stat_statements.track = none
180+
--
181+
SET pg_stat_statements.track = 'none';
182+
SELECT pg_stat_statements_reset();
183+
pg_stat_statements_reset
184+
--------------------------
185+
186+
(1 row)
187+
188+
SELECT 1 AS "one";
189+
one
190+
-----
191+
1
192+
(1 row)
193+
194+
SELECT 1 + 1 AS "two";
195+
two
196+
-----
197+
2
198+
(1 row)
199+
200+
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
201+
calls | rows | query
202+
-------+------+-------
203+
(0 rows)
204+

0 commit comments

Comments
 (0)