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

Commitd0028e3

Browse files
committed
Refactor more the regression tests of pg_stat_statements
This commit expands more the refactoring of the regression tests ofpg_stat_statements, with tests moved out of pg_stat_statements.sql intoseparate 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.Likee8dbdb1, there is no change in terms of code coverage or results,and this finishes the split I was aiming for in these tests. Most ofthe tests used "test" of "pgss_test" as names for the tables used, theseare renamed to less generic names.Reviewed-by: Bertrand DrouvotDiscussion:https://postgr.es/m/Y/7Y9U/y/keAW3qH@paquier.xyz
1 parent98a88bc commitd0028e3

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 walcleanup 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
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)
[8]ページ先頭
©2009-2025 Movatter.jp