diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out index 12d0dcf61f..5187767c42 100644 --- a/contrib/pg_stat_statements/expected/pg_stat_statements.out +++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out @@ -96,24 +96,24 @@ EXECUTE pgss_test(1); DEALLOCATE pgss_test; SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls | rows -----------------------------------------------+-------+------ - PREPARE pgss_test (int) AS SELECT $1 LIMIT ? | 1 | 1 - SELECT ? +| 4 | 4 - +| | - AS "text" | | - SELECT ? + ? | 2 | 2 - SELECT ? + ? + ? AS "add" | 3 | 3 - SELECT ? AS "float" | 1 | 1 - SELECT ? AS "int" | 2 | 2 - SELECT ? AS i UNION SELECT ? ORDER BY i | 1 | 2 - SELECT ? || ? | 1 | 1 - SELECT pg_stat_statements_reset() | 1 | 1 - WITH t(f) AS ( +| 1 | 2 - VALUES (?), (?) +| | - ) +| | - SELECT f FROM t ORDER BY f | | - select ?::jsonb ? ? | 1 | 1 + query | calls | rows +-----------------------------------------------+-------+------ + PREPARE pgss_test (int) AS SELECT $1 LIMIT $0 | 1 | 1 + SELECT $0 +| 4 | 4 + +| | + AS "text" | | + SELECT $0 + $0 | 2 | 2 + SELECT $0 + $0 + $0 AS "add" | 3 | 3 + SELECT $0 AS "float" | 1 | 1 + SELECT $0 AS "int" | 2 | 2 + SELECT $0 AS i UNION SELECT $0 ORDER BY i | 1 | 2 + SELECT $0 || $0 | 1 | 1 + SELECT pg_stat_statements_reset() | 1 | 1 + WITH t(f) AS ( +| 1 | 2 + VALUES ($0), ($0) +| | + ) +| | + SELECT f FROM t ORDER BY f | | + select $0::jsonb ? $0 | 1 | 1 (11 rows) -- @@ -196,17 +196,17 @@ SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5); (8 rows) SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls | rows --------------------------------------------------------+-------+------ - DELETE FROM test WHERE a > ? | 1 | 1 - INSERT INTO test (a, b) VALUES (?, ?), (?, ?), (?, ?) | 1 | 3 - INSERT INTO test VALUES(generate_series(?, ?), ?) | 1 | 10 - SELECT * FROM test ORDER BY a | 1 | 12 - SELECT * FROM test WHERE a > ? ORDER BY a | 2 | 4 - SELECT * FROM test WHERE a IN (?, ?, ?, ?, ?) | 1 | 8 - SELECT pg_stat_statements_reset() | 1 | 1 - UPDATE test SET b = ? WHERE a = ? | 6 | 6 - UPDATE test SET b = ? WHERE a > ? | 1 | 3 + query | calls | rows +-------------------------------------------------------------+-------+------ + DELETE FROM test WHERE a > $0 | 1 | 1 + INSERT INTO test (a, b) VALUES ($0, $0), ($0, $0), ($0, $0) | 1 | 3 + INSERT INTO test VALUES(generate_series($0, $0), $0) | 1 | 10 + SELECT * FROM test ORDER BY a | 1 | 12 + SELECT * FROM test WHERE a > $0 ORDER BY a | 2 | 4 + SELECT * FROM test WHERE a IN ($0, $0, $0, $0, $0) | 1 | 8 + SELECT pg_stat_statements_reset() | 1 | 1 + UPDATE test SET b = $0 WHERE a = $0 | 6 | 6 + UPDATE test SET b = $0 WHERE a > $0 | 1 | 3 (9 rows) -- @@ -290,9 +290,9 @@ SELECT PLUS_ONE(10); SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls | rows -----------------------------------+-------+------ - SELECT ?::TEXT | 1 | 1 - SELECT PLUS_ONE(?) | 2 | 2 - SELECT PLUS_TWO(?) | 2 | 2 + SELECT $0::TEXT | 1 | 1 + SELECT PLUS_ONE($0) | 2 | 2 + SELECT PLUS_TWO($0) | 2 | 2 SELECT pg_stat_statements_reset() | 1 | 1 (4 rows) @@ -347,10 +347,10 @@ SELECT PLUS_ONE(1); SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls | rows -----------------------------------+-------+------ - SELECT (i + ? + ?)::INTEGER | 2 | 2 - SELECT (i + ?)::INTEGER LIMIT ? | 2 | 2 - SELECT PLUS_ONE(?) | 2 | 2 - SELECT PLUS_TWO(?) | 2 | 2 + SELECT (i + $0 + $0)::INTEGER | 2 | 2 + SELECT (i + $0)::INTEGER LIMIT $0 | 2 | 2 + SELECT PLUS_ONE($0) | 2 | 2 + SELECT PLUS_TWO($0) | 2 | 2 SELECT pg_stat_statements_reset() | 1 | 1 (5 rows) @@ -391,7 +391,7 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C"; DROP FUNCTION PLUS_TWO(INTEGER) | 1 | 0 DROP TABLE IF EXISTS test | 3 | 0 DROP TABLE test | 1 | 0 - SELECT ? | 1 | 1 + SELECT $0 | 1 | 1 SELECT pg_stat_statements_reset() | 1 | 1 (8 rows) diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 62dec8768a..6d08403c42 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -27,7 +27,7 @@ * to blame query costs on the proper queryId. * * To facilitate presenting entries to users, we create "representative" query - * strings in which constants are replaced with '?' characters, to make it + * strings in which constants are replaced with '$0' characters, to make it * clearer what a normalized entry can represent. To save on shared memory, * and to avoid having to truncate oversized query strings, we store these * strings in a temporary external query-texts file. Offsets into this @@ -2940,7 +2940,8 @@ generate_normalized_query(pgssJumbleState *jstate, const char *query, quer_loc = 0, /* Source query byte location */ n_quer_loc = 0, /* Normalized query byte location */ last_off = 0, /* Offset from start for previous tok */ - last_tok_len = 0; /* Length (in bytes) of that tok */ + last_tok_len = 0, /* Length (in bytes) of that tok */ + norm_query_buflen = 0; /* Size of the normalized query buffer */ /* * Get constants' lengths (core system only gives us locations). Note @@ -2948,8 +2949,11 @@ generate_normalized_query(pgssJumbleState *jstate, const char *query, */ fill_in_constant_lengths(jstate, query, query_loc); + /* Accomodate the additional query replacement characters */ + norm_query_buflen = query_len + jstate->clocations_count; + /* Allocate result buffer */ - norm_query = palloc(query_len + 1); + norm_query = palloc(norm_query_buflen + 1); for (i = 0; i < jstate->clocations_count; i++) { @@ -2973,8 +2977,9 @@ generate_normalized_query(pgssJumbleState *jstate, const char *query, memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; - /* And insert a '?' in place of the constant token */ - norm_query[n_quer_loc++] = '?'; + /* And insert a '$0' in place of the constant token */ + norm_query[n_quer_loc++] = '$'; + norm_query[n_quer_loc++] = '0'; quer_loc = off + tok_len; last_off = off; @@ -2991,7 +2996,7 @@ generate_normalized_query(pgssJumbleState *jstate, const char *query, memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; - Assert(n_quer_loc <= query_len); + Assert(n_quer_loc <= norm_query_buflen); norm_query[n_quer_loc] = '\0'; *query_len_p = n_quer_loc; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index d4f09fc2a3..17fbd3bccc 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -245,7 +245,7 @@ When a constant's value has been ignored for purposes of matching the - query to other queries, the constant is replaced by ? + query to other queries, the constant is replaced by $0 in the pg_stat_statements display. The rest of the query text is that of the first query that had the particular queryid hash value associated with the @@ -481,13 +481,13 @@ bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5; -[ RECORD 1 ]--------------------------------------------------------------------- -query | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?; +query | UPDATE pgbench_branches SET bbalance = bbalance + $0 WHERE bid = $0; calls | 3000 total_time | 9609.00100000002 rows | 2836 hit_percent | 99.9778970000200936 -[ RECORD 2 ]--------------------------------------------------------------------- -query | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?; +query | UPDATE pgbench_tellers SET tbalance = tbalance + $0 WHERE tid = $0; calls | 3000 total_time | 8015.156 rows | 2990 @@ -499,7 +499,7 @@ total_time | 310.624 rows | 100000 hit_percent | 0.30395136778115501520 -[ RECORD 4 ]--------------------------------------------------------------------- -query | UPDATE pgbench_accounts SET abalance = abalance + ? WHERE aid = ?; +query | UPDATE pgbench_accounts SET abalance = abalance + $0 WHERE aid = $0; calls | 3000 total_time | 271.741999999997 rows | 3000