Skip to content

Commit daa8365

Browse files
michaelpqmichaelpq
committed
Reflect normalization of query strings for utilities in pg_stat_statements
Applying normalization changes how the following query strings are reflected in pg_stat_statements, by showing Const nodes with a dollar-signed parameter as this is how such queries are structured internally once parsed: - DECLARE - EXPLAIN - CREATE MATERIALIZED VIEW - CREATE TABLE AS More normalization could be done in the future depending on the parts where query jumbling is applied (like A_Const nodes?), the changes being reflected in the regression tests in majority created in de2aca2. This just allows the basics to work for utility queries using Const nodes. Reviewed-by: Bertrand Drouvot Discussion: https://postgr.es/m/Y+MRdEq9W9XVa2AB@paquier.xyz
1 parent be504a3 commit daa8365

File tree

3 files changed

+29
-27
lines changed

3 files changed

+29
-27
lines changed

‎contrib/pg_stat_statements/expected/cursors.out

Lines changed: 7 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -16,10 +16,10 @@ CLOSE cursor_stats_1;
1616
DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2;
1717
CLOSE cursor_stats_1;
1818
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
19-
calls | rows | query
20-
-------+------+------------------------------------------------------
19+
calls | rows | query
20+
-------+------+-------------------------------------------------------
2121
2 | 0 | CLOSE cursor_stats_1
22-
2 | 0 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1
22+
2 | 0 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $1
2323
1 | 1 | SELECT pg_stat_statements_reset()
2424
(3 rows)
2525

@@ -49,14 +49,14 @@ CLOSE cursor_stats_1;
4949
CLOSE cursor_stats_2;
5050
COMMIT;
5151
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
52-
calls | rows | query
53-
-------+------+------------------------------------------------------
52+
calls | rows | query
53+
-------+------+-------------------------------------------------------
5454
1 | 0 | BEGIN
5555
1 | 0 | CLOSE cursor_stats_1
5656
1 | 0 | CLOSE cursor_stats_2
5757
1 | 0 | COMMIT
58-
1 | 0 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2
59-
1 | 0 | DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT 3
58+
1 | 0 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $1
59+
1 | 0 | DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT $1
6060
1 | 1 | FETCH 1 IN cursor_stats_1
6161
1 | 1 | FETCH 1 IN cursor_stats_2
6262
1 | 1 | SELECT pg_stat_statements_reset()

‎contrib/pg_stat_statements/expected/utility.out

Lines changed: 19 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -226,10 +226,10 @@ EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 7;
226226
(2 rows)
227227

228228
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
229-
calls | rows | query
230-
-------+------+-------------------------------------------------------------------------------
231-
2 | 0 | EXPLAIN (costs off) SELECT 1
232-
2 | 0 | EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3
229+
calls | rows | query
230+
-------+------+---------------------------------------------------------------------------------
231+
2 | 0 | EXPLAIN (costs off) SELECT $1
232+
2 | 0 | EXPLAIN (costs off) SELECT a FROM generate_series($1,$2) AS tab(a) WHERE a = $3
233233
1 | 1 | SELECT pg_stat_statements_reset()
234234
(3 rows)
235235

@@ -326,12 +326,12 @@ CREATE TABLE ctas_stats_2 AS
326326
FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 1;
327327
DROP TABLE ctas_stats_2;
328328
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
329-
calls | rows | query
330-
-------+------+-----------------------------------------------------------------
331-
2 | 2 | CREATE TABLE ctas_stats_1 AS SELECT 1 AS a
332-
2 | 4 | CREATE TABLE ctas_stats_2 AS +
333-
| | SELECT a AS col1, 2::int AS col2 +
334-
| | FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2
329+
calls | rows | query
330+
-------+------+--------------------------------------------------------------------
331+
2 | 2 | CREATE TABLE ctas_stats_1 AS SELECT $1 AS a
332+
2 | 4 | CREATE TABLE ctas_stats_2 AS +
333+
| | SELECT a AS col1, $1::int AS col2 +
334+
| | FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AND a > $5
335335
2 | 0 | DROP TABLE ctas_stats_1
336336
2 | 0 | DROP TABLE ctas_stats_2
337337
1 | 1 | SELECT pg_stat_statements_reset()
@@ -354,11 +354,11 @@ CREATE MATERIALIZED VIEW matview_stats_1 AS
354354
FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
355355
DROP MATERIALIZED VIEW matview_stats_1;
356356
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
357-
calls | rows | query
358-
-------+------+-----------------------------------------------------------------
359-
2 | 2 | CREATE MATERIALIZED VIEW matview_stats_1 AS +
360-
| | SELECT a AS col1, 2::int AS col2 +
361-
| | FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2
357+
calls | rows | query
358+
-------+------+--------------------------------------------------------------------
359+
2 | 2 | CREATE MATERIALIZED VIEW matview_stats_1 AS +
360+
| | SELECT a AS col1, $1::int AS col2 +
361+
| | FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AND a > $5
362362
2 | 0 | DROP MATERIALIZED VIEW matview_stats_1
363363
1 | 1 | SELECT pg_stat_statements_reset()
364364
(3 rows)
@@ -508,19 +508,19 @@ FETCH FORWARD ALL pgss_cursor;
508508

509509
COMMIT;
510510
SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
511-
calls | rows | query
512-
-------+------+----------------------------------------------------------------------------
511+
calls | rows | query
512+
-------+------+-------------------------------------------------------------------------
513513
1 | 0 | BEGIN
514514
1 | 0 | COMMIT
515515
1 | 3 | COPY pgss_ctas (a, b) FROM STDIN
516516
1 | 13 | CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas
517-
1 | 10 | CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a
517+
1 | 10 | CREATE TABLE pgss_ctas AS SELECT a, $1 b FROM generate_series($2, $3) a
518518
1 | 0 | DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv
519519
1 | 5 | FETCH FORWARD 5 pgss_cursor
520520
1 | 7 | FETCH FORWARD ALL pgss_cursor
521521
1 | 1 | FETCH NEXT pgss_cursor
522522
1 | 13 | REFRESH MATERIALIZED VIEW pgss_matv
523-
1 | 10 | SELECT generate_series(1, 10) c INTO pgss_select_into
523+
1 | 10 | SELECT generate_series($1, $2) c INTO pgss_select_into
524524
1 | 1 | SELECT pg_stat_statements_reset()
525525
(12 rows)
526526

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -836,8 +836,10 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query, JumbleState *jstate)
836836
if (query->utilityStmt)
837837
{
838838
if (pgss_track_utility && !PGSS_HANDLED_UTILITY(query->utilityStmt))
839+
{
839840
query->queryId = UINT64CONST(0);
840-
return;
841+
return;
842+
}
841843
}
842844

843845
/*

0 commit comments

Comments
 (0)