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

Commit42e41a4

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 parent3da9a2a commit42e41a4

File tree

1 file changed

+187
-40
lines changed

1 file changed

+187
-40
lines changed

‎t/002_pg_stat_statements_aqo.pl‎

Lines changed: 187 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -3,60 +3,207 @@
33

44
use PostgreSQL::Test::Cluster;
55
use PostgreSQL::Test::Utils;
6-
use Test::Moretests=>2;
7-
print"start";
8-
my$node = PostgreSQL::Test::Cluster->new('profiling');
6+
use Test::Moretests=>12;
7+
8+
my$node = PostgreSQL::Test::Cluster->new('test');
99

1010
$node->init;
11-
print"create conf";
1211

1312
$node->append_conf('postgresql.conf',qq{
1413
aqo.mode = 'disabled'
15-
aqo.profile_classes = -1
16-
aqo.profile_enable = 'true'
1714
aqo.force_collect_stat = 'false'
1815
log_statement = 'ddl' # reduce size of logs.
1916
aqo.join_threshold = 0
17+
pg_stat_statements.track = 'none'
2018
});
21-
# Test constants.
22-
my$TRANSACTIONS = 100;
23-
my$CLIENTS = 10;
24-
my$THREADS = 10;
2519
my$query_id;
26-
27-
# General purpose variables.
28-
my$res;
20+
my ($res,$aqo_res);
2921
my$total_classes;
3022
$node->start();
31-
# ERROR: AQO allow to load library only on startup
32-
print"Create extension aqo";
33-
$node->psql('postgres',"CREATE EXTENSION aqo");
34-
$node->psql('postgres',"CREATE EXTENSIONpg_stat_statements");
35-
print"create preload libraries";
36-
$node->append_conf('postgresql.conf',qq{shared_preload_libraries = 'aqo, pg_stat_statements'});
23+
24+
$node->psql('postgres',"CREATE EXTENSION aqo");# Error
25+
$node->append_conf('postgresql.conf',qq{
26+
shared_preload_libraries = 'aqo,pg_stat_statements'
27+
aqo.mode = 'disabled' # disable AQO on schema creation
28+
});
3729
$node->restart();
38-
$node->psql('postgres',"CREATE EXTENSION aqo");
39-
$node->psql('postgres',"CREATE EXTENSION pg_stat_statements");
30+
$node->safe_psql('postgres',"
31+
CREATE EXTENSION aqo;
32+
CREATE EXTENSION pg_stat_statements;
33+
");
34+
35+
# Execute test DDL
36+
$node->psql('postgres',"
37+
CREATE TABLE aqo_test0(a int, b int, c int, d int);
38+
WITH RECURSIVE t(a, b, c, d) AS (
39+
VALUES (0, 0, 0, 0)
40+
UNION ALL
41+
SELECT t.a + 1, t.b + 1, t.c + 1, t.d + 1 FROM t WHERE t.a < 2000
42+
) INSERT INTO aqo_test0 (SELECT * FROM t);
43+
CREATE INDEX aqo_test0_idx_a ON aqo_test0 (a);
44+
ANALYZE aqo_test0;
45+
");
4046
$node->psql('postgres',"
41-
ALTER SYSTEM SET aqo.profile_enable = 'true';
47+
CREATE TABLE trig(
48+
x double precision,
49+
sinx double precision,
50+
cosx double precision);
51+
WITH RECURSIVE t(a, b, c) AS (
52+
VALUES (0.0::double precision, 0.0::double precision, 1.0::double precision)
53+
UNION ALL
54+
SELECT t.a + pi() / 50, sin(t.a + pi() / 50), cos(t.a + pi() / 50)
55+
FROM t WHERE t.a < 2 * pi()
56+
) INSERT INTO trig (SELECT * FROM t);
57+
CREATE INDEX trig_idx_x ON trig (x);
58+
ANALYZE trig;
59+
");
60+
$node->psql('postgres',"
61+
CREATE TABLE department(
62+
DepartmentID INT PRIMARY KEY NOT NULL,
63+
DepartmentName VARCHAR(20)
64+
);
65+
CREATE TABLE employee (
66+
LastName VARCHAR(20),
67+
DepartmentID INT REFERENCES department(DepartmentID)
68+
);
69+
INSERT INTO department
70+
VALUES (31, 'Sales'), (33, 'Engineering'), (34, 'Clerical'),
71+
(35, 'Marketing');
72+
INSERT INTO employee
73+
VALUES ('Rafferty', 31), ('Jones', 33), ('Heisenberg', 33),
74+
('Robinson', 34), ('Smith', 34), ('Williams', NULL);
75+
");
76+
$node->psql('postgres',"
77+
ALTER SYSTEM SET aqo.mode = 'learn';
78+
ALTER SYSTEM SET pg_stat_statements.track = 'all';
4279
SELECT pg_reload_conf();
4380
");
4481

45-
$node->psql('postgres',"CREATE TABLE aqo_test0(a int, b int, c int, d int);
46-
WITH RECURSIVE t(a, b, c, d)
47-
AS (
48-
VALUES (0, 0, 0, 0)
49-
UNION ALL
50-
SELECT t.a + 1, t.b + 1, t.c + 1, t.d + 1 FROM t WHERE t.a < 2000
51-
) INSERT INTO aqo_test0 (SELECT * FROM t);
52-
CREATE INDEX aqo_test0_idx_a ON aqo_test0 (a);
53-
ANALYZE aqo_test0;");
54-
$node->psql('postgres',"
55-
ALTER SYSTEM SET aqo.mode = 'controlled';
56-
");
57-
$res =$node->safe_psql('postgres',"SELECT * FROM aqo_test0");
58-
$res =$node->safe_psql('postgres',"SELECT count(*) FROM pg_stat_statements where query = 'SELECT * FROM aqo_test0'");
59-
is($res, 1);# The same query add in pg_stat_statements
60-
$res =$node->safe_psql('postgres',"SELECT count(*) from aqo_query_texts where query_text = 'SELECT * FROM aqo_test0'");
61-
is($res, 0);# The same query isn't added into aqo_query_texts
62-
$node->stop();
82+
# Trivial query without any clauses/parameters
83+
$node->safe_psql('postgres',"SELECT * FROM aqo_test0");
84+
$res =$node->safe_psql('postgres',"
85+
SELECT query FROM pg_stat_statements
86+
JOIN aqo_queries USING(queryid)
87+
");# Both extensions have the same QueryID for the query above
88+
is($res,"SELECT * FROM aqo_test0");
89+
90+
# Check number of queries which logged in both extensions.
91+
$aqo_res =$node->safe_psql('postgres',"
92+
SELECT count(*) FROM aqo_query_texts
93+
");# 2 - Common fs and trivial select.
94+
$res =$node->safe_psql('postgres',"
95+
SELECT count(*) FROM pg_stat_statements
96+
");# 3 - trivial select and two utility queries above.
97+
is($res -$aqo_res, 1);
98+
99+
$res =$node->safe_psql('postgres',"
100+
SELECT count(*) FROM pg_stat_statements
101+
WHERE queryid NOT IN (SELECT queryid FROM aqo_query_texts)
102+
");# Trivial select and utility query to pg_stat_statements
103+
is($res, 2);
104+
105+
$node->safe_psql('postgres',"
106+
SELECT * FROM trig WHERE sinx < 0.5 and cosx > -0.5
107+
");# Log query with two constants
108+
$node->safe_psql('postgres',"
109+
SELECT count(*) FROM pg_stat_statements
110+
WHERE query = 'SELECT * FROM trig WHERE sinx < 0.5 and cosx > -0.5'
111+
");# The pg_stat_statements utility queries are logged too
112+
$res =$node->safe_psql('postgres',"
113+
SELECT count(*) FROM aqo_query_texts aqt, pg_stat_statements pgss
114+
WHERE aqt.queryid = pgss.queryid
115+
");
116+
is($res, 4);
117+
118+
$res =$node->safe_psql('postgres',"
119+
SELECT count(*) FROM pg_stat_statements
120+
WHERE queryid NOT IN (SELECT queryid FROM aqo_query_texts)
121+
");# pgss logs queries to AQO tables these AQO are skip
122+
is($res, 4);
123+
$res =$node->safe_psql('postgres',"
124+
SELECT count(*) FROM aqo_queries
125+
WHERE queryid NOT IN (SELECT queryid FROM pg_stat_statements)
126+
");# PGSS have logged all queries that AQO logged, expect common fs.
127+
is($res, 1);
128+
129+
# ############################################################################ #
130+
#
131+
# Complex queries with meaningful tables
132+
#
133+
# ############################################################################ #
134+
135+
$node->safe_psql('postgres',"
136+
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
137+
FROM employee
138+
INNER JOIN department ON employee.DepartmentID = department.DepartmentID;
139+
");# Log query with a JOIN and a join clause
140+
$node->safe_psql('postgres',"
141+
EXPLAIN ANALYZE
142+
SELECT ee.LastName, ee.DepartmentID, dpt.DepartmentName
143+
FROM employee ee
144+
INNER JOIN department dpt ON (ee.DepartmentID = dpt.DepartmentID)
145+
WHERE ee.LastName NOT LIKE 'Wi%';
146+
");# Use a table aliases, EXPLAIN ANALYZE mode and WHERE clause.
147+
$node->safe_psql('postgres',"
148+
SELECT ee.LastName, ee.DepartmentID, dpt.DepartmentName
149+
FROM employee ee
150+
INNER JOIN department dpt ON (ee.DepartmentID = dpt.DepartmentID)
151+
WHERE ee.LastName NOT LIKE 'Wi%';
152+
");# Without EXPLAIN ANALYZE option
153+
$node->safe_psql('postgres',"
154+
WITH smth AS (
155+
SELECT a FROM aqo_test0
156+
) SELECT * FROM employee ee, department dpt, smth
157+
WHERE (ee.DepartmentID = dpt.DepartmentID)
158+
AND (ee.LastName NOT LIKE 'Wi%')
159+
AND (ee.DepartmentID < smth.a);
160+
");# Use CTE
161+
$res =$node->safe_psql('postgres',"
162+
SELECT count(*) FROM aqo_query_texts aqt, pg_stat_statements pgss
163+
WHERE aqt.queryid = pgss.queryid
164+
");# Check, both extensions added the query with the same query ID.
165+
is($res, 8);
166+
167+
# Check query texts identity.
168+
# TODO: Maybe AQO should use parameterized query text too?
169+
$res =$node->safe_psql('postgres',"
170+
SELECT count(*)
171+
FROM aqo_query_texts aqt, pg_stat_statements pgss
172+
WHERE aqt.queryid = pgss.queryid AND aqt.query_text != pgss.query
173+
");# PGSS processes a query and generalizes it. So, some queries is diferent
174+
is($res, 6);
175+
$res =$node->safe_psql('postgres',"
176+
SELECT count(*)
177+
FROM aqo_query_texts aqt, pg_stat_statements pgss
178+
WHERE aqt.queryid = pgss.queryid AND aqt.query_text = pgss.query
179+
");# Non-parameterized queries (without constants in a body of query) will have the same query text.
180+
is($res, 2);
181+
182+
# Check queries hasn't logged by another extension
183+
184+
$res =$node->safe_psql('postgres',"
185+
SELECT count(*) FROM pg_stat_statements
186+
WHERE queryid NOT IN (SELECT queryid FROM aqo_queries)
187+
AND query NOT LIKE '%aqo_quer%'
188+
");# PGSS logs all the same except queries with AQO-related objects.
189+
is($res, 1);# allow to find shifts in PGSS logic
190+
191+
# TODO: why queries in EXPLAIN ANALYZE mode have different query ID in AQO
192+
# and PGSS extensions?
193+
194+
$res =$node->safe_psql('postgres',"
195+
SELECT count(*) FROM aqo_queries
196+
WHERE queryid NOT IN (SELECT queryid FROM pg_stat_statements)
197+
");
198+
is($res, 1);
199+
200+
# only first entry in aqo_query_texts has zero hash
201+
$res =$node->safe_psql('postgres',"
202+
SELECT count(*) FROM aqo_query_texts
203+
WHERE queryid = 0
204+
");
205+
is($res, 1);
206+
207+
# TODO: check queries with queries in stored procedures
208+
209+
$node->stop();

0 commit comments

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