Skip to content

Commit d0028e3

Browse files
michaelpqmichaelpq
committed
Refactor more the regression tests of pg_stat_statements
This commit expands more the refactoring of the regression tests of pg_stat_statements, with tests moved out of pg_stat_statements.sql into separate files. The following file structure is now used: - select is mostly the former pg_stat_statements.sql, renamed. - dml for INSERT/UPDATE/DELETE and MERGE - user_activity, to test role-level checks and stat resets. - wal, to check the WAL generation after some queries. Like e8dbdb1, there is no change in terms of code coverage or results, and this finishes the split I was aiming for in these tests. Most of the tests used "test" of "pgss_test" as names for the tables used, these are renamed to less generic names. Reviewed-by: Bertrand Drouvot Discussion: https://postgr.es/m/Y/7Y9U/y/keAW3qH@paquier.xyz
1 parent 98a88bc commit d0028e3

File tree

12 files changed

+1109
-1071
lines changed

12 files changed

+1109
-1071
lines changed

‎contrib/pg_stat_statements/Makefile

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -17,8 +17,8 @@ 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 cursors utility level_tracking planning \
21-
cleanup oldextversions
20+
REGRESS = select dml cursors utility level_tracking planning \
21+
user_activity wal cleanup oldextversions
2222
# Disabled because these tests require "shared_preload_libraries=pg_stat_statements",
2323
# which typical installcheck users do not have (e.g. buildfarm clients).
2424
NO_INSTALLCHECK = 1

‎contrib/pg_stat_statements/expected/dml.out

Lines changed: 147 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,147 @@
1+
--
2+
-- DMLs on test table
3+
--
4+
SET pg_stat_statements.track_utility = FALSE;
5+
CREATE TEMP TABLE pgss_dml_tab (a int, b char(20));
6+
INSERT INTO pgss_dml_tab VALUES(generate_series(1, 10), 'aaa');
7+
UPDATE pgss_dml_tab SET b = 'bbb' WHERE a > 7;
8+
DELETE FROM pgss_dml_tab WHERE a > 9;
9+
-- explicit transaction
10+
BEGIN;
11+
UPDATE pgss_dml_tab SET b = '111' WHERE a = 1 ;
12+
COMMIT;
13+
BEGIN \;
14+
UPDATE pgss_dml_tab SET b = '222' WHERE a = 2 \;
15+
COMMIT ;
16+
UPDATE pgss_dml_tab SET b = '333' WHERE a = 3 \;
17+
UPDATE pgss_dml_tab SET b = '444' WHERE a = 4 ;
18+
BEGIN \;
19+
UPDATE pgss_dml_tab SET b = '555' WHERE a = 5 \;
20+
UPDATE pgss_dml_tab SET b = '666' WHERE a = 6 \;
21+
COMMIT ;
22+
-- many INSERT values
23+
INSERT INTO pgss_dml_tab (a, b) VALUES (1, 'a'), (2, 'b'), (3, 'c');
24+
-- SELECT with constants
25+
SELECT * FROM pgss_dml_tab WHERE a > 5 ORDER BY a ;
26+
a | b
27+
---+----------------------
28+
6 | 666
29+
7 | aaa
30+
8 | bbb
31+
9 | bbb
32+
(4 rows)
33+
34+
SELECT *
35+
FROM pgss_dml_tab
36+
WHERE a > 9
37+
ORDER BY a ;
38+
a | b
39+
---+---
40+
(0 rows)
41+
42+
-- these two need to be done on a different table
43+
-- SELECT without constants
44+
SELECT * FROM pgss_dml_tab ORDER BY a;
45+
a | b
46+
---+----------------------
47+
1 | a
48+
1 | 111
49+
2 | b
50+
2 | 222
51+
3 | c
52+
3 | 333
53+
4 | 444
54+
5 | 555
55+
6 | 666
56+
7 | aaa
57+
8 | bbb
58+
9 | bbb
59+
(12 rows)
60+
61+
-- SELECT with IN clause
62+
SELECT * FROM pgss_dml_tab WHERE a IN (1, 2, 3, 4, 5);
63+
a | b
64+
---+----------------------
65+
1 | 111
66+
2 | 222
67+
3 | 333
68+
4 | 444
69+
5 | 555
70+
1 | a
71+
2 | b
72+
3 | c
73+
(8 rows)
74+
75+
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
76+
calls | rows | query
77+
-------+------+---------------------------------------------------------------------
78+
1 | 1 | DELETE FROM pgss_dml_tab WHERE a > $1
79+
1 | 3 | INSERT INTO pgss_dml_tab (a, b) VALUES ($1, $2), ($3, $4), ($5, $6)
80+
1 | 10 | INSERT INTO pgss_dml_tab VALUES(generate_series($1, $2), $3)
81+
1 | 12 | SELECT * FROM pgss_dml_tab ORDER BY a
82+
2 | 4 | SELECT * FROM pgss_dml_tab WHERE a > $1 ORDER BY a
83+
1 | 8 | SELECT * FROM pgss_dml_tab WHERE a IN ($1, $2, $3, $4, $5)
84+
1 | 1 | SELECT pg_stat_statements_reset()
85+
1 | 0 | SET pg_stat_statements.track_utility = FALSE
86+
6 | 6 | UPDATE pgss_dml_tab SET b = $1 WHERE a = $2
87+
1 | 3 | UPDATE pgss_dml_tab SET b = $1 WHERE a > $2
88+
(10 rows)
89+
90+
SELECT pg_stat_statements_reset();
91+
pg_stat_statements_reset
92+
--------------------------
93+
94+
(1 row)
95+
96+
-- MERGE
97+
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
98+
WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text;
99+
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
100+
WHEN MATCHED THEN UPDATE SET b = pgss_dml_tab.b || st.a::text;
101+
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
102+
WHEN MATCHED AND length(st.b) > 1 THEN UPDATE SET b = pgss_dml_tab.b || st.a::text;
103+
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
104+
WHEN NOT MATCHED THEN INSERT (a, b) VALUES (0, NULL);
105+
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
106+
WHEN NOT MATCHED THEN INSERT VALUES (0, NULL); -- same as above
107+
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
108+
WHEN NOT MATCHED THEN INSERT (b, a) VALUES (NULL, 0);
109+
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a)
110+
WHEN NOT MATCHED THEN INSERT (a) VALUES (0);
111+
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
112+
WHEN MATCHED THEN DELETE;
113+
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
114+
WHEN MATCHED THEN DO NOTHING;
115+
MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= 4)
116+
WHEN NOT MATCHED THEN DO NOTHING;
117+
DROP TABLE pgss_dml_tab;
118+
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
119+
calls | rows | query
120+
-------+------+-----------------------------------------------------------------------------------------
121+
1 | 6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
122+
| | WHEN MATCHED AND length(st.b) > $2 THEN UPDATE SET b = pgss_dml_tab.b || st.a::text
123+
1 | 6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
124+
| | WHEN MATCHED THEN DELETE
125+
1 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
126+
| | WHEN MATCHED THEN DO NOTHING
127+
1 | 6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
128+
| | WHEN MATCHED THEN UPDATE SET b = pgss_dml_tab.b || st.a::text
129+
1 | 6 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
130+
| | WHEN MATCHED THEN UPDATE SET b = st.b || st.a::text
131+
1 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a AND st.a >= $1)+
132+
| | WHEN NOT MATCHED THEN DO NOTHING
133+
1 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a) +
134+
| | WHEN NOT MATCHED THEN INSERT (a) VALUES ($1)
135+
2 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a) +
136+
| | WHEN NOT MATCHED THEN INSERT (a, b) VALUES ($1, $2)
137+
1 | 0 | MERGE INTO pgss_dml_tab USING pgss_dml_tab st ON (st.a = pgss_dml_tab.a) +
138+
| | WHEN NOT MATCHED THEN INSERT (b, a) VALUES ($1, $2)
139+
1 | 1 | SELECT pg_stat_statements_reset()
140+
(10 rows)
141+
142+
SELECT pg_stat_statements_reset();
143+
pg_stat_statements_reset
144+
--------------------------
145+
146+
(1 row)
147+

0 commit comments

Comments
 (0)