Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite8dbdb1

Browse files
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 ofthe module, but things have become complicated to follow over the yearsas some of the sub-scenarios tested in this file rely on assumptionsthat come from completely different areas of it, like a GUC setup or arelation created previously. For example, row tracking for CTAS/COPYwas looking at the number of plans, which was not necessary, or leveltracking was mixed with checks on planner counts.This commit refactors the tests of pg_stat_statements, by moving testcases out of pg_stat_statements.sql into their own file, as of:- Planning-related tests in planning.sql, for [re]plan counts andtop-level handling. These depend on pg_stat_statements.track_planning.- Utilities in utility.sql (pg_stat_statements.track_utility), thatincludes 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 hasbeen looking at scenarios with DO blocks, PL functions and SQLfunctions.pg_stat_statements.sql (still named the same for now) still includessome checks for role-level tracking and WAL generation metrics, thatought to become independent in the long term for clarity.While on it, this switches the order of the attributes when queryingpg_stat_statements, the query field becoming last. This makes mucheasier the tracking of changes related to normalization, as queries arethe only variable-length attributes queried (unaligned mode would be oneextra choice, but that reduces the checks on the other fields).Test scenarios and their results match exactly with what was happeningbefore 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 interms of what is produced by the reports in the tablepg_stat_statements. A follow-up patch will extend more the tests ofpg_stat_statements around utilities, so this split creates a foundationfor this purpose, without complicating more pg_stat_statements.sql.Reviewed-by: Bertrand DrouvotDiscussion:https://postgr.es/m/Y+MRdEq9W9XVa2AB@paquier.xyz
1 parente72910f commite8dbdb1

File tree

12 files changed

+623
-570
lines changed

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_statementsutility level_tracking planning cleanupoldextversions
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
Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
DROP EXTENSION pg_stat_statements;
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)
[8]ページ先頭
©2009-2025 Movatter.jp