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

Commitf455a0d

Browse files
committed
Add more TAP tests on joint usage of query_id machinery by AQO and PGSS
extensions. Some minor inconsistencies were detected (see issue#71).Authors: A.Kazarinov, A.Lepikhov
1 parentdcda61d commitf455a0d

File tree

1 file changed

+165
-51
lines changed

1 file changed

+165
-51
lines changed

‎t/002_pg_stat_statements_aqo.pl

Lines changed: 165 additions & 51 deletions
Original file line numberDiff line numberDiff line change
@@ -2,92 +2,206 @@
22
use warnings;
33
use PostgreSQL::Test::Cluster;
44
use PostgreSQL::Test::Utils;
5-
use Test::Moretests=>4;
6-
print"start";
7-
my$node = PostgreSQL::Test::Cluster->new('profiling');
5+
use Test::Moretests=>12;
6+
7+
my$node = PostgreSQL::Test::Cluster->new('test');
88
$node->init;
9-
print"create conf";
109

1110
$node->append_conf('postgresql.conf',qq{
1211
aqo.mode = 'disabled'
13-
aqo.profile_classes = -1
14-
aqo.profile_enable = 'true'
1512
aqo.force_collect_stat = 'false'
1613
log_statement = 'ddl' # reduce size of logs.
14+
pg_stat_statements.track = 'none'
1715
});
18-
# Test constants.
19-
my$TRANSACTIONS = 100;
20-
my$CLIENTS = 10;
21-
my$THREADS = 10;
2216
my$query_id;
23-
24-
# General purpose variables.
25-
my$res;
17+
my ($res,$aqo_res);
2618
my$total_classes;
2719

2820
# Check: allow to load the libraries only on startup
2921
$node->start();
30-
$node->psql('postgres',"CREATE EXTENSION aqo");
31-
$node->psql('postgres',"CREATE EXTENSION pg_stat_statements");
32-
22+
$node->psql('postgres',"CREATE EXTENSION aqo");# Error
3323
$node->append_conf('postgresql.conf',qq{
3424
shared_preload_libraries = 'aqo, pg_stat_statements'
35-
aqo.mode = 'learn' #unconditional learning
25+
aqo.mode = 'disabled' #disable AQO on schema creation
3626
});
3727
$node->restart();
38-
$node->psql('postgres',"CREATE EXTENSION aqo");
39-
$node->psql('postgres',"CREATE EXTENSION pg_stat_statements");
40-
41-
$node->psql('postgres',"CREATE TABLE aqo_test0(a int, b int, c int, d int);
42-
WITH RECURSIVE t(a, b, c, d)
43-
AS (
44-
VALUES (0, 0, 0, 0)
45-
UNION ALL
46-
SELECT t.a + 1, t.b + 1, t.c + 1, t.d + 1 FROM t WHERE t.a < 2000
47-
) INSERT INTO aqo_test0 (SELECT * FROM t);
48-
CREATE INDEX aqo_test0_idx_a ON aqo_test0 (a);
49-
ANALYZE aqo_test0;");
50-
51-
$res =$node->safe_psql('postgres',"SELECT * FROM aqo_test0");
52-
$res =$node->safe_psql('postgres',"SELECT count(*) FROM pg_stat_statements where query = 'SELECT * FROM aqo_test0'");
28+
$node->safe_psql('postgres',"
29+
CREATE EXTENSION aqo;
30+
CREATE EXTENSION pg_stat_statements;
31+
");
32+
33+
# Execute test DDL
34+
$node->psql('postgres',"
35+
CREATE TABLE aqo_test0(a int, b int, c int, d int);
36+
WITH RECURSIVE t(a, b, c, d) AS (
37+
VALUES (0, 0, 0, 0)
38+
UNION ALL
39+
SELECT t.a + 1, t.b + 1, t.c + 1, t.d + 1 FROM t WHERE t.a < 2000
40+
) INSERT INTO aqo_test0 (SELECT * FROM t);
41+
CREATE INDEX aqo_test0_idx_a ON aqo_test0 (a);
42+
ANALYZE aqo_test0;
43+
");
44+
$node->psql('postgres',"
45+
CREATE TABLE trig(
46+
x double precision,
47+
sinx double precision,
48+
cosx double precision);
49+
WITH RECURSIVE t(a, b, c) AS (
50+
VALUES (0.0::double precision, 0.0::double precision, 1.0::double precision)
51+
UNION ALL
52+
SELECT t.a + pi() / 50, sin(t.a + pi() / 50), cos(t.a + pi() / 50)
53+
FROM t WHERE t.a < 2 * pi()
54+
) INSERT INTO trig (SELECT * FROM t);
55+
CREATE INDEX trig_idx_x ON trig (x);
56+
ANALYZE trig;
57+
");
58+
$node->psql('postgres',"
59+
CREATE TABLE department(
60+
DepartmentID INT PRIMARY KEY NOT NULL,
61+
DepartmentName VARCHAR(20)
62+
);
63+
CREATE TABLE employee (
64+
LastName VARCHAR(20),
65+
DepartmentID INT REFERENCES department(DepartmentID)
66+
);
67+
INSERT INTO department
68+
VALUES (31, 'Sales'), (33, 'Engineering'), (34, 'Clerical'),
69+
(35, 'Marketing');
70+
INSERT INTO employee
71+
VALUES ('Rafferty', 31), ('Jones', 33), ('Heisenberg', 33),
72+
('Robinson', 34), ('Smith', 34), ('Williams', NULL);
73+
");
74+
$node->psql('postgres',"
75+
ALTER SYSTEM SET aqo.mode = 'learn';
76+
ALTER SYSTEM SET pg_stat_statements.track = 'all';
77+
SELECT pg_reload_conf();
78+
");
79+
80+
# Trivial query without any clauses/parameters
81+
$node->safe_psql('postgres',"SELECT * FROM aqo_test0");
82+
$res =$node->safe_psql('postgres',"
83+
SELECT query FROM pg_stat_statements
84+
JOIN aqo_queries ON queryid = query_hash
85+
");# Both extensions have the same QueryID for the query above
86+
is($res,"SELECT * FROM aqo_test0");
5387

5488
# Check number of queries which logged in both extensions.
89+
$aqo_res =$node->safe_psql('postgres',"
90+
SELECT count(*) FROM aqo_query_texts
91+
");# 2 - Common fs and trivial select.
5592
$res =$node->safe_psql('postgres',"
56-
SELECT count(*) FROM aqo_query_texts aqt, pg_stat_statements pgss
57-
WHERE aqt.query_hash = pgss.queryid
58-
");
59-
is($res, 3);
93+
SELECT count(*) FROM pg_stat_statements
94+
");# 3 - trivial select and two utility queries above.
95+
is($res -$aqo_res, 1);
6096

61-
# TODO: Maybe AQO should parameterize query text too?
97+
$res =$node->safe_psql('postgres',"
98+
SELECT count(*) FROM pg_stat_statements
99+
WHERE queryid NOT IN (SELECT query_hash FROM aqo_query_texts)
100+
");# Trivial select and utility query to pg_stat_statements
101+
is($res, 2);
102+
103+
$node->safe_psql('postgres',"
104+
SELECT * FROM trig WHERE sinx < 0.5 and cosx > -0.5
105+
");# Log query with two constants
106+
$node->safe_psql('postgres',"
107+
SELECT count(*) FROM pg_stat_statements
108+
WHERE query = 'SELECT * FROM trig WHERE sinx < 0.5 and cosx > -0.5'
109+
");# The pg_stat_statements utility queries are logged too
62110
$res =$node->safe_psql('postgres',"
63111
SELECT count(*) FROM aqo_query_texts aqt, pg_stat_statements pgss
64-
WHERE aqt.query_hash = pgss.queryid AND aqt.query_text = pgss.query
112+
WHERE aqt.query_hash = pgss.queryid
65113
");
66-
is($res,1);
114+
is($res,4);
67115

68-
# Just fix a number of differences
69116
$res =$node->safe_psql('postgres',"
70-
SELECT count(*) FROM aqo_query_texts
117+
SELECT count(*) FROM pg_stat_statements
118+
WHERE queryid NOT IN (SELECT query_hash FROM aqo_query_texts)
119+
");# pgss logs queries to AQO tables these AQO are skip
120+
is($res, 4);
121+
$res =$node->safe_psql('postgres',"
122+
SELECT count(*) FROM aqo_queries
71123
WHERE query_hash NOT IN (SELECT queryid FROM pg_stat_statements)
72-
");
124+
");# PGSS have logged all queries that AQO logged, expect common fs.
73125
is($res, 1);
74126

127+
# ############################################################################ #
128+
#
129+
# Complex queries with meaningful tables
130+
#
131+
# ############################################################################ #
132+
133+
$node->safe_psql('postgres',"
134+
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
135+
FROM employee
136+
INNER JOIN department ON employee.DepartmentID = department.DepartmentID;
137+
");# Log query with a JOIN and a join clause
138+
$node->safe_psql('postgres',"
139+
EXPLAIN ANALYZE
140+
SELECT ee.LastName, ee.DepartmentID, dpt.DepartmentName
141+
FROM employee ee
142+
INNER JOIN department dpt ON (ee.DepartmentID = dpt.DepartmentID)
143+
WHERE ee.LastName NOT LIKE 'Wi%';
144+
");# Use a table aliases, EXPLAIN ANALYZE mode and WHERE clause.
145+
$node->safe_psql('postgres',"
146+
SELECT ee.LastName, ee.DepartmentID, dpt.DepartmentName
147+
FROM employee ee
148+
INNER JOIN department dpt ON (ee.DepartmentID = dpt.DepartmentID)
149+
WHERE ee.LastName NOT LIKE 'Wi%';
150+
");# Without EXPLAIN ANALYZE option
151+
$node->safe_psql('postgres',"
152+
WITH smth AS (
153+
SELECT a FROM aqo_test0
154+
) SELECT * FROM employee ee, department dpt, smth
155+
WHERE (ee.DepartmentID = dpt.DepartmentID)
156+
AND (ee.LastName NOT LIKE 'Wi%')
157+
AND (ee.DepartmentID < smth.a);
158+
");# Use CTE
75159
$res =$node->safe_psql('postgres',"
76-
SELECT query_text FROM aqo_query_texts
77-
WHERE query_hash NOT IN (SELECT queryid FROM pg_stat_statements)
78-
");
79-
note($res);# Just see differences
160+
SELECT count(*) FROM aqo_query_texts aqt, pg_stat_statements pgss
161+
WHERE aqt.query_hash = pgss.queryid
162+
");# Check, both extensions added the query with the same query ID.
163+
is($res, 8);
164+
165+
# Check query texts identity.
166+
# TODO: Maybe AQO should use parameterized query text too?
167+
$res =$node->safe_psql('postgres',"
168+
SELECT count(*)
169+
FROM aqo_query_texts aqt, pg_stat_statements pgss
170+
WHERE aqt.query_hash = pgss.queryid AND aqt.query_text != pgss.query
171+
");# PGSS processes a query and generalizes it. So, some queries is diferent
172+
is($res, 6);
173+
$res =$node->safe_psql('postgres',"
174+
SELECT count(*)
175+
FROM aqo_query_texts aqt, pg_stat_statements pgss
176+
WHERE aqt.query_hash = pgss.queryid AND aqt.query_text = pgss.query
177+
");# Non-parameterized queries (without constants in a body of query) will have the same query text.
178+
is($res, 2);
179+
180+
# Check queries hasn't logged by another extension
80181

81182
$res =$node->safe_psql('postgres',"
82183
SELECT count(*) FROM pg_stat_statements
83-
WHERE queryid NOT IN (SELECT query_hash FROM aqo_query_texts)
184+
WHERE queryid NOT IN (SELECT query_hash FROM aqo_queries)
185+
AND query NOT LIKE '%aqo_quer%'
186+
");# PGSS logs all the same except queries with AQO-related objects.
187+
is($res, 1);# allow to find shifts in PGSS logic
188+
189+
# TODO: why queries in EXPLAIN ANALYZE mode have different query ID in AQO
190+
# and PGSS extensions?
191+
192+
$res =$node->safe_psql('postgres',"
193+
SELECT count(*) FROM aqo_queries
194+
WHERE query_hash NOT IN (SELECT queryid FROM pg_stat_statements)
84195
");
85-
is($res,8);
196+
is($res,1);
86197

198+
# only first entry in aqo_query_texts has zero hash
87199
$res =$node->safe_psql('postgres',"
88-
SELECTquery FROMpg_stat_statements
89-
WHEREqueryid NOT IN (SELECTquery_hashFROM aqo_query_texts)
200+
SELECTcount(*) FROMaqo_query_texts
201+
WHERE query_hash= 0
90202
");
91-
note($res);# Just see differences
203+
is($res, 1);
204+
205+
# TODO: check queries with queries in stored procedures
92206

93207
$node->stop();

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp