Often when looking through pg_stat_statements, it would be nice to have
some sample values for the constants and parameters. This patch
implements that by taking the values from the first execution of the
normalized query.
To keep things reasonable, there is a limit on how big the parameters
can be.
This patch is based off of 5303ffe71b.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 39b368b70e..fae5c29cef 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -4,9 +4,13 @@ MODULE_big = pg_stat_statements
OBJS = pg_stat_statements.o $(WIN32RES)
EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.4--1.5.sql \
- pg_stat_statements--1.3--1.4.sql pg_stat_statements--1.2--1.3.sql \
- pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.0--1.1.sql \
+DATA = pg_stat_statements--1.6.sql \
+ pg_stat_statements--1.5--1.6.sql \
+ pg_stat_statements--1.4--1.5.sql \
+ pg_stat_statements--1.3--1.4.sql \
+ pg_stat_statements--1.2--1.3.sql \
+ pg_stat_statements--1.1--1.2.sql \
+ pg_stat_statements--1.0--1.1.sql \
pg_stat_statements--unpackaged--1.0.sql
PGFILEDESC = "pg_stat_statements - execution statistics of SQL statements"
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 5318c3550c..f032db98c2 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -95,25 +95,25 @@ EXECUTE pgss_test(1);
(1 row)
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, $2 LIMIT $3 | 1 | 1
- SELECT $1 +| 4 | 4
- +| |
- AS "text" | |
- SELECT $1 + $2 | 2 | 2
- SELECT $1 + $2 + $3 AS "add" | 3 | 3
- SELECT $1 AS "float" | 1 | 1
- SELECT $1 AS "int" | 2 | 2
- SELECT $1 AS i UNION SELECT $2 ORDER BY i | 1 | 2
- SELECT $1 || $2 | 1 | 1
- SELECT pg_stat_statements_reset() | 1 | 1
- WITH t(f) AS ( +| 1 | 2
- VALUES ($1), ($2) +| |
- ) +| |
- SELECT f FROM t ORDER BY f | |
- select $1::jsonb ? $2 | 1 | 1
+SELECT query, calls, rows, consts, params, param_types FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows | consts | params | param_types
+---------------------------------------------------+-------+------+------------------------------+--------+-------------
+ PREPARE pgss_test (int) AS SELECT $1, $2 LIMIT $3 | 1 | 1 | [2:3]={'test',1} | {1} | {integer}
+ SELECT $1 +| 4 | 4 | {'hello'} | |
+ +| | | | |
+ AS "text" | | | | |
+ SELECT $1 + $2 | 2 | 2 | {3,3} | |
+ SELECT $1 + $2 + $3 AS "add" | 3 | 3 | {1,1,1} | |
+ SELECT $1 AS "float" | 1 | 1 | {2.0} | |
+ SELECT $1 AS "int" | 2 | 2 | {1} | |
+ SELECT $1 AS i UNION SELECT $2 ORDER BY i | 1 | 2 | {1,2} | |
+ SELECT $1 || $2 | 1 | 1 | {"' '","' !'"} | |
+ SELECT pg_stat_statements_reset() | 1 | 1 | | |
+ WITH t(f) AS ( +| 1 | 2 | {1.0,2.0} | |
+ VALUES ($1), ($2) +| | | | |
+ ) +| | | | |
+ SELECT f FROM t ORDER BY f | | | | |
+ select $1::jsonb ? $2 | 1 | 1 | {"'{\"a\":1, \"b\":2}'",'b'} | |
(11 rows)
--
@@ -395,4 +395,38 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
SELECT pg_stat_statements_reset() | 1 | 1
(8 rows)
+-- test the constants
+SELECT 42 + 6 AS fortyeight, 'hello ' || 'world' as helloworld;
+ fortyeight | helloworld
+------------+-------------
+ 48 | hello world
+(1 row)
+
+SELECT query, consts
+FROM pg_stat_statements
+WHERE query ~ 'fortyeight'
+ AND query !~ 'pg_stat_statements'
+ AND dbid = (SELECT oid FROM pg_database WHERE datname = current_catalog);
+ query | consts
+------------------------------------------------------+---------------------------
+ SELECT $1 + $2 AS fortyeight, $3 || $4 as helloworld | {42,6,"'hello '",'world'}
+(1 row)
+
+-- test the parameters
+PREPARE foo AS SELECT relname FROM pg_class WHERE oid = $1 AND relpages > $2;
+EXECUTE foo (0, 42);
+ relname
+---------
+(0 rows)
+
+SELECT query, params, param_types
+FROM pg_stat_statements
+WHERE query ~ 'foo'
+ AND query !~ 'pg_stat_statements'
+ AND dbid = (SELECT oid FROM pg_database WHERE datname = current_catalog);
+ query | params | param_types
+------------------------------------------------------------------------------+--------+---------------
+ PREPARE foo AS SELECT relname FROM pg_class WHERE oid = $1 AND relpages > $2 | {0,42} | {oid,integer}
+(1 row)
+
DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.5--1.6.sql b/contrib/pg_stat_statements/pg_stat_statements--1.5--1.6.sql
new file mode 100644
index 0000000000..353a786c8a
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.5--1.6.sql
@@ -0,0 +1,50 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.5--1.6.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.6'" to load this file. \quit
+
+/* First we have to remove them from the extension */
+ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
+ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean);
+
+/* Then we can drop them */
+DROP VIEW pg_stat_statements;
+DROP FUNCTION pg_stat_statements(boolean);
+
+/* Now redefine */
+CREATE FUNCTION pg_stat_statements(IN showtext boolean,
+ OUT userid oid,
+ OUT dbid oid,
+ OUT queryid bigint,
+ OUT query text,
+ OUT consts text[],
+ OUT params text[],
+ OUT param_types regtype[],
+ OUT calls int8,
+ OUT total_time float8,
+ OUT min_time float8,
+ OUT max_time float8,
+ OUT mean_time float8,
+ OUT stddev_time float8,
+ OUT rows int8,
+ OUT shared_blks_hit int8,
+ OUT shared_blks_read int8,
+ OUT shared_blks_dirtied int8,
+ OUT shared_blks_written int8,
+ OUT local_blks_hit int8,
+ OUT local_blks_read int8,
+ OUT local_blks_dirtied int8,
+ OUT local_blks_written int8,
+ OUT temp_blks_read int8,
+ OUT temp_blks_written int8,
+ OUT blk_read_time float8,
+ OUT blk_write_time float8
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_stat_statements_1_6'
+LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
+
+CREATE VIEW pg_stat_statements AS
+ SELECT * FROM pg_stat_statements(true);
+
+GRANT SELECT ON pg_stat_statements TO PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.4.sql b/contrib/pg_stat_statements/pg_stat_statements--1.6.sql
similarity index 87%
rename from contrib/pg_stat_statements/pg_stat_statements--1.4.sql
rename to contrib/pg_stat_statements/pg_stat_statements--1.6.sql
index 58cdf600fc..a8f040a6a1 100644
--- a/contrib/pg_stat_statements/pg_stat_statements--1.4.sql
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.6.sql
@@ -1,4 +1,4 @@
-/* contrib/pg_stat_statements/pg_stat_statements--1.4.sql */
+/* contrib/pg_stat_statements/pg_stat_statements--1.6.sql */
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pg_stat_statements" to load this file. \quit
@@ -14,6 +14,9 @@ CREATE FUNCTION pg_stat_statements(IN showtext boolean,
OUT dbid oid,
OUT queryid bigint,
OUT query text,
+ OUT consts text[],
+ OUT params text[],
+ OUT param_types regtype[],
OUT calls int8,
OUT total_time float8,
OUT min_time float8,
@@ -35,7 +38,7 @@ CREATE FUNCTION pg_stat_statements(IN showtext boolean,
OUT blk_write_time float8
)
RETURNS SETOF record
-AS 'MODULE_PATHNAME', 'pg_stat_statements_1_3'
+AS 'MODULE_PATHNAME', 'pg_stat_statements_1_6'
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
-- Register a view on the function for ease of use.
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 3de8333be2..ede4fad216 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -62,7 +62,9 @@
#include <unistd.h>
#include "access/hash.h"
+#include "access/xact.h"
#include "catalog/pg_authid.h"
+#include "catalog/pg_type.h"
#include "executor/instrument.h"
#include "funcapi.h"
#include "mb/pg_wchar.h"
@@ -78,6 +80,7 @@
#include "tcop/utility.h"
#include "utils/builtins.h"
#include "utils/memutils.h"
+#include "utils/lsyscache.h"
PG_MODULE_MAGIC;
@@ -119,7 +122,10 @@ typedef enum pgssVersion
PGSS_V1_0 = 0,
PGSS_V1_1,
PGSS_V1_2,
- PGSS_V1_3
+ PGSS_V1_3,
+ /* V1_4 only changed parallel safety */
+ /* V1_5 only added privilege management */
+ PGSS_V1_6
} pgssVersion;
/*
@@ -180,6 +186,14 @@ typedef struct pgssEntry
int query_len; /* # of valid bytes in query string, or -1 */
int encoding; /* query text encoding */
slock_t mutex; /* protects the counters only */
+
+ /* offsets for the constants and parameters, and their types */
+ Size consts_offset;
+ int consts_len;
+ Size params_offset;
+ int params_len;
+ Size param_types_offset;
+ int param_types_len;
} pgssEntry;
/*
@@ -220,6 +234,9 @@ typedef struct pgssJumbleState
/* Array of locations of constants that should be removed */
pgssLocationLen *clocations;
+ /* Array of Oids for constants types */
+ Oid *consts_types;
+
/* Allocated length of clocations array */
int clocations_buf_size;
@@ -269,6 +286,9 @@ static int pgss_max; /* max # statements to track */
static int pgss_track; /* tracking level */
static bool pgss_track_utility; /* whether to track utility commands */
static bool pgss_save; /* whether to save stats across shutdown */
+static bool pgss_collect_consts; /* whether to collect constant values */
+static bool pgss_collect_params; /* whether to collect parameter values */
+static int pgss_width_threshold; /* how wide the consts and params can be */
#define pgss_enabled() \
@@ -291,6 +311,7 @@ void _PG_fini(void);
PG_FUNCTION_INFO_V1(pg_stat_statements_reset);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_2);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_3);
+PG_FUNCTION_INFO_V1(pg_stat_statements_1_6);
PG_FUNCTION_INFO_V1(pg_stat_statements);
static void pgss_shmem_startup(void);
@@ -311,12 +332,17 @@ static void pgss_store(const char *query, uint64 queryId,
int query_location, int query_len,
double total_time, uint64 rows,
const BufferUsage *bufusage,
- pgssJumbleState *jstate);
+ pgssJumbleState *jstate,
+ const ParamListInfo qparams);
static void pg_stat_statements_internal(FunctionCallInfo fcinfo,
pgssVersion api_version,
bool showtext);
static Size pgss_memsize(void);
-static pgssEntry *entry_alloc(pgssHashKey *key, Size query_offset, int query_len,
+static pgssEntry *entry_alloc(pgssHashKey *key,
+ Size query_offset, int query_len,
+ Size consts_offset, int consts_len,
+ Size params_offset, int params_len,
+ Size param_types_offset, int param_types_len,
int encoding, bool sticky);
static void entry_dealloc(void);
static bool qtext_store(const char *query, int query_len,
@@ -332,12 +358,15 @@ static void AppendJumble(pgssJumbleState *jstate,
static void JumbleQuery(pgssJumbleState *jstate, Query *query);
static void JumbleRangeTable(pgssJumbleState *jstate, List *rtable);
static void JumbleExpr(pgssJumbleState *jstate, Node *node);
-static void RecordConstLocation(pgssJumbleState *jstate, int location);
+static void RecordConstLocationAndType(pgssJumbleState *jstate, int location, Oid type);
static char *generate_normalized_query(pgssJumbleState *jstate, const char *query,
- int query_loc, int *query_len_p, int encoding);
+ int query_loc, int *query_len_p,
+ char **consts, int *consts_len,
+ int encoding);
static void fill_in_constant_lengths(pgssJumbleState *jstate, const char *query,
int query_loc);
static int comp_location(const void *a, const void *b);
+static Datum CStringGetArrayDatum(Oid arraytype, char *cstring);
/*
@@ -407,6 +436,41 @@ _PG_init(void)
NULL,
NULL);
+ DefineCustomBoolVariable("pg_stat_statements.collect_consts",
+ "Collect the values of constants into the consts field",
+ NULL,
+ &pgss_collect_consts,
+ true,
+ PGC_SIGHUP,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomBoolVariable("pg_stat_statements.collect_params",
+ "Collect the values of parameters and their types into the params and param_types fields",
+ NULL,
+ &pgss_collect_params,
+ true,
+ PGC_SIGHUP,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomIntVariable("pg_stat_statements.width_threshold",
+ "Defines the maximum length of the consts and params fields",
+ NULL,
+ &pgss_width_threshold,
+ 1024,
+ -1,
+ INT_MAX,
+ PGC_SIGHUP,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
EmitWarningsOnPlaceholders("pg_stat_statements");
/*
@@ -452,6 +516,32 @@ _PG_fini(void)
ProcessUtility_hook = prev_ProcessUtility;
}
+/* This is used for some repetitive code in pgss_shmem_startup */
+#define DESERIALIZE_TEXT(xlen, xoffset) do { \
+ if (xlen < 0) \
+ xoffset = 0; \
+ else \
+ { \
+ /* Resize buffer as needed */ \
+ if (xlen >= buffer_size) \
+ { \
+ buffer_size = Max(buffer_size * 2, xlen + 1); \
+ buffer = repalloc(buffer, buffer_size); \
+ } \
+\
+ if (fread(buffer, 1, xlen + 1, file) != xlen + 1) \
+ goto read_error; \
+\
+ /* Should have a trailing null, but let's make sure */ \
+ buffer[xlen] = '\0'; \
+\
+ xoffset = pgss->extent; \
+ if (fwrite(buffer, 1, xlen + 1, qfile) != xlen + 1) \
+ goto write_error; \
+ pgss->extent += xlen + 1; \
+ } \
+} while(0)
+
/*
* shmem_startup hook: allocate or attach to shared memory,
* then load any pre-existing statistics from file.
@@ -576,7 +666,7 @@ pgss_shmem_startup(void)
{
pgssEntry temp;
pgssEntry *entry;
- Size query_offset;
+ Size query_offset, consts_offset, params_offset, param_types_offset;
if (fread(&temp, sizeof(pgssEntry), 1, file) != 1)
goto read_error;
@@ -602,16 +692,24 @@ pgss_shmem_startup(void)
if (temp.counters.calls == 0)
continue;
- /* Store the query text */
+ /* Store the query text, consts, and params */
query_offset = pgss->extent;
if (fwrite(buffer, 1, temp.query_len + 1, qfile) != temp.query_len + 1)
goto write_error;
pgss->extent += temp.query_len + 1;
+ /* consts, params, and param_types */
+ DESERIALIZE_TEXT(temp.consts_len, consts_offset);
+ DESERIALIZE_TEXT(temp.params_len, params_offset);
+ DESERIALIZE_TEXT(temp.param_types_len, param_types_offset);
+
/* make the hashtable entry (discards old entries if too many) */
- entry = entry_alloc(&temp.key, query_offset, temp.query_len,
- temp.encoding,
- false);
+ entry = entry_alloc(&temp.key,
+ query_offset, temp.query_len,
+ consts_offset, temp.consts_len,
+ params_offset, temp.params_len,
+ param_types_offset, temp.param_types_len,
+ temp.encoding, false);
/* copy in the actual stats */
entry->counters = temp.counters;
@@ -671,6 +769,18 @@ fail:
*/
}
+/* This is used for some repetitive code in pgss_shmem_shutdown */
+#define SERIALIZE_TEXT(xlen, xoffset) do { \
+ len = (xlen); \
+ qstr = qtext_fetch((xoffset), len, qbuffer, qbuffer_size); \
+ if (qstr && fwrite(qstr, 1, len + 1, file) != len + 1) \
+ { \
+ /* note: we assume hash_seq_term won't change errno */ \
+ hash_seq_term(&hash_seq); \
+ goto error; \
+ } \
+} while(0)
+
/*
* shmem_shutdown hook: Dump statistics into file.
*
@@ -726,6 +836,7 @@ pgss_shmem_shutdown(int code, Datum arg)
char *qstr = qtext_fetch(entry->query_offset, len,
qbuffer, qbuffer_size);
+ /* query */
if (qstr == NULL)
continue; /* Ignore any entries with bogus texts */
@@ -736,6 +847,11 @@ pgss_shmem_shutdown(int code, Datum arg)
hash_seq_term(&hash_seq);
goto error;
}
+
+ /* write out consts, params, and param_types */
+ SERIALIZE_TEXT(entry->consts_len, entry->consts_offset);
+ SERIALIZE_TEXT(entry->params_len, entry->params_offset);
+ SERIALIZE_TEXT(entry->param_types_len, entry->param_types_offset);
}
free(qbuffer);
@@ -809,6 +925,7 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query)
jstate.clocations = (pgssLocationLen *)
palloc(jstate.clocations_buf_size * sizeof(pgssLocationLen));
jstate.clocations_count = 0;
+ jstate.consts_types = (Oid *) palloc(jstate.clocations_buf_size * sizeof(Oid));
jstate.highest_extern_param_id = 0;
/* Compute query ID and mark the Query node with it */
@@ -838,7 +955,8 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query)
0,
0,
NULL,
- &jstate);
+ &jstate,
+ NULL);
}
/*
@@ -945,7 +1063,8 @@ pgss_ExecutorEnd(QueryDesc *queryDesc)
queryDesc->totaltime->total * 1000.0, /* convert to msec */
queryDesc->estate->es_processed,
&queryDesc->totaltime->bufusage,
- NULL);
+ NULL,
+ queryDesc->params);
}
if (prev_ExecutorEnd)
@@ -1056,6 +1175,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
INSTR_TIME_GET_MILLISEC(duration),
rows,
&bufusage,
+ NULL,
NULL);
}
else
@@ -1083,6 +1203,92 @@ pgss_hash_string(const char *str, int len)
len, 0));
}
+static void
+CollectParams(ParamListInfo qparams, char **params, char **param_types)
+{
+ Datum *params_arr, *ptypes_arr;
+ bool *param_nulls;
+ int paramno;
+ ArrayType *sql_params_array, *sql_param_types_array;
+ /* these are used for the params, but then reused for the arrays */
+ Oid typoutput;
+ bool typisvarlena;
+ int16 typlen;
+ bool typbyval;
+ char typalign;
+ uint64 totalsize;
+
+ /* If we don't have any params or if the transaction is aborted, quit */
+ if (!pgss_collect_params ||
+ !qparams || qparams->numParams == 0 ||
+ IsAbortedTransactionBlockState())
+ {
+ *params = NULL;
+ *param_types = NULL;
+ return;
+ }
+
+ params_arr = (Datum *) palloc(sizeof(Datum) * qparams->numParams);
+ param_nulls = (bool *) palloc(sizeof(bool) * qparams->numParams);
+ ptypes_arr = (Datum *) palloc(sizeof(Datum) * qparams->numParams);
+
+ totalsize = 0;
+ for (paramno = 0; paramno < qparams->numParams; paramno++)
+ {
+ ParamExternData *prm = &qparams->params[paramno];
+
+ /* Add the param value to the list */
+ if (!prm->isnull && OidIsValid(prm->ptype))
+ {
+ char *pstring;
+
+ getTypeOutputInfo(prm->ptype, &typoutput, &typisvarlena);
+ pstring = OidOutputFunctionCall(typoutput, prm->value);
+ params_arr[paramno] = PointerGetDatum(cstring_to_text(pstring));
+ param_nulls[paramno] = false;
+
+ /* Make sure we're not getting too long; abort if so */
+ totalsize += strlen(pstring);
+ if (totalsize > pgss_width_threshold && pgss_width_threshold >= 0)
+ {
+ *params = NULL;
+ *param_types = NULL;
+ return;
+ }
+ }
+ else
+ {
+ params_arr[paramno] = 0;
+ param_nulls[paramno] = true;
+ }
+
+ /* Add the param type to the list */
+ ptypes_arr[paramno] = ObjectIdGetDatum(prm->ptype);
+ }
+
+ /* Make the params array */
+ {
+ int dims[1];
+ int lbs[1];
+
+ dims[0] = qparams->numParams;
+ lbs[0] = 1;
+
+ get_typlenbyvalalign(TEXTOID, &typlen, &typbyval, &typalign);
+ sql_params_array = construct_md_array(params_arr, param_nulls, 1, dims, lbs,
+ TEXTOID, typlen, typbyval, typalign);
+ getTypeOutputInfo(TEXTARRAYOID, &typoutput, &typisvarlena);
+ *params = OidOutputFunctionCall(typoutput, PointerGetDatum(sql_params_array));
+ }
+
+ /* Make the param_types array */
+ get_typlenbyvalalign(OIDOID, &typlen, &typbyval, &typalign);
+ sql_param_types_array = construct_array(ptypes_arr, qparams->numParams,
+ OIDOID, typlen, typbyval, typalign);
+ getTypeOutputInfo(OIDARRAYOID, &typoutput, &typisvarlena);
+ *param_types = OidOutputFunctionCall(typoutput, PointerGetDatum(sql_param_types_array));
+}
+
/*
* Store some statistics for a statement.
*
@@ -1098,12 +1304,15 @@ pgss_store(const char *query, uint64 queryId,
int query_location, int query_len,
double total_time, uint64 rows,
const BufferUsage *bufusage,
- pgssJumbleState *jstate)
+ pgssJumbleState *jstate,
+ const ParamListInfo qparams)
{
pgssHashKey key;
pgssEntry *entry;
char *norm_query = NULL;
int encoding = GetDatabaseEncoding();
+ char *consts = NULL;
+ bool need_params = false;
Assert(query != NULL);
@@ -1159,10 +1368,23 @@ pgss_store(const char *query, uint64 queryId,
entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_FIND, NULL);
+ /*
+ * If we had an entry without params, and now we have params, remove the
+ * entry and create a new one.
+ */
+ if (pgss_collect_params &&
+ entry && entry->params_len == -1 &&
+ qparams && qparams->numParams > 0)
+ {
+ need_params = true;
+ }
+
/* Create new entry, if not present */
- if (!entry)
+ if (!entry || need_params)
{
- Size query_offset;
+ char *params = NULL, *param_types = NULL;
+ Size query_offset, consts_offset, params_offset, param_types_offset;
+ int consts_len = -1, params_len = -1, param_types_len = -1;
int gc_count;
bool stored;
bool do_gc;
@@ -1180,47 +1402,101 @@ pgss_store(const char *query, uint64 queryId,
norm_query = generate_normalized_query(jstate, query,
query_location,
&query_len,
+ &consts, &consts_len,
encoding);
+
LWLockAcquire(pgss->lock, LW_SHARED);
}
- /* Append new query text to file with only shared lock held */
- stored = qtext_store(norm_query ? norm_query : query, query_len,
- &query_offset, &gc_count);
-
/*
- * Determine whether we need to garbage collect external query texts
- * while the shared lock is still held. This micro-optimization
- * avoids taking the time to decide this while holding exclusive lock.
+ * If we are given some params, collect them. We don't need to hold
+ * the lock while doing so. It's possible that we just acquired the
+ * lock after normalizing the query only to release it again here, but
+ * so what.
*/
- do_gc = need_gc_qtexts();
+ if (pgss_collect_params && qparams && qparams->numParams > 0)
+ {
+ LWLockRelease(pgss->lock);
- /* Need exclusive lock to make a new hashtable entry - promote */
- LWLockRelease(pgss->lock);
- LWLockAcquire(pgss->lock, LW_EXCLUSIVE);
+ CollectParams(qparams, ¶ms, ¶m_types);
+ /* We should always have both params and param_types, or neither */
+ Assert((params == NULL) == (param_types == NULL));
- /*
- * A garbage collection may have occurred while we weren't holding the
- * lock. In the unlikely event that this happens, the query text we
- * stored above will have been garbage collected, so write it again.
- * This should be infrequent enough that doing it while holding
- * exclusive lock isn't a performance problem.
- */
- if (!stored || pgss->gc_count != gc_count)
+ if (params)
+ params_len = strlen(params);
+ if (param_types)
+ param_types_len = strlen(param_types);
+
+ LWLockAcquire(pgss->lock, LW_SHARED);
+
+ if (entry)
+ {
+ stored = qtext_store(params, params_len, ¶ms_offset, NULL);
+ stored &= qtext_store(param_types, param_types_len, ¶m_types_offset, NULL);
+
+ /* If we failed to write to the text file, give up */
+ if (!stored)
+ goto done;
+
+ entry->params_len = params_len;
+ entry->params_offset = params_offset;
+ entry->param_types_len = param_types_len;
+ entry->param_types_offset = param_types_offset;
+ }
+ }
+
+ if (!entry)
+ {
+ /* Append new query text to file with only shared lock held */
stored = qtext_store(norm_query ? norm_query : query, query_len,
- &query_offset, NULL);
+ &query_offset, &gc_count);
+ stored &= qtext_store(consts, consts_len, &consts_offset, NULL);
+ stored &= qtext_store(params, params_len, ¶ms_offset, NULL);
+ stored &= qtext_store(param_types, param_types_len, ¶m_types_offset, NULL);
- /* If we failed to write to the text file, give up */
- if (!stored)
- goto done;
+ /*
+ * Determine whether we need to garbage collect external query texts
+ * while the shared lock is still held. This micro-optimization
+ * avoids taking the time to decide this while holding exclusive lock.
+ */
+ do_gc = need_gc_qtexts();
- /* OK to create a new hashtable entry */
- entry = entry_alloc(&key, query_offset, query_len, encoding,
- jstate != NULL);
+ /* Need exclusive lock to make a new hashtable entry - promote */
+ LWLockRelease(pgss->lock);
+ LWLockAcquire(pgss->lock, LW_EXCLUSIVE);
- /* If needed, perform garbage collection while exclusive lock held */
- if (do_gc)
- gc_qtexts();
+ /*
+ * A garbage collection may have occurred while we weren't holding the
+ * lock. In the unlikely event that this happens, the query text we
+ * stored above will have been garbage collected, so write it again.
+ * This should be infrequent enough that doing it while holding
+ * exclusive lock isn't a performance problem.
+ */
+ if (!stored || pgss->gc_count != gc_count)
+ {
+ stored = qtext_store(norm_query ? norm_query : query, query_len,
+ &query_offset, NULL);
+ stored &= qtext_store(consts, consts_len, &consts_offset, NULL);
+ stored &= qtext_store(params, strlen(params), ¶ms_offset, NULL);
+ stored &= qtext_store(param_types, strlen(param_types), ¶m_types_offset, NULL);
+ }
+
+ /* If we failed to write to the text file, give up */
+ if (!stored)
+ goto done;
+
+ /* OK to create a new hashtable entry */
+ entry = entry_alloc(&key,
+ query_offset, query_len,
+ consts_offset, consts_len,
+ params_offset, params_len,
+ param_types_offset, param_types_len,
+ encoding, jstate != NULL);
+
+ /* If needed, perform garbage collection while exclusive lock held */
+ if (do_gc)
+ gc_qtexts();
+ }
}
/* Increment the counts, except when jstate is not NULL */
@@ -1310,7 +1586,10 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
#define PG_STAT_STATEMENTS_COLS_V1_1 18
#define PG_STAT_STATEMENTS_COLS_V1_2 19
#define PG_STAT_STATEMENTS_COLS_V1_3 23
-#define PG_STAT_STATEMENTS_COLS 23 /* maximum of above */
+/* V1_4 only changed parallel safety */
+/* V1_5 only added privilege management */
+#define PG_STAT_STATEMENTS_COLS_V1_6 26
+#define PG_STAT_STATEMENTS_COLS 26 /* maximum of above */
/*
* Retrieve statement statistics.
@@ -1322,6 +1601,16 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
* expected API version is identified by embedding it in the C name of the
* function. Unfortunately we weren't bright enough to do that for 1.1.
*/
+Datum
+pg_stat_statements_1_6(PG_FUNCTION_ARGS)
+{
+ bool showtext = PG_GETARG_BOOL(0);
+
+ pg_stat_statements_internal(fcinfo, PGSS_V1_6, showtext);
+
+ return (Datum) 0;
+}
+
Datum
pg_stat_statements_1_3(PG_FUNCTION_ARGS)
{
@@ -1355,6 +1644,15 @@ pg_stat_statements(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+static Datum
+CStringGetArrayDatum(Oid arraytype, char *cstring)
+{
+ Oid typInput, typIOParam;
+
+ getTypeInputInfo(arraytype, &typInput, &typIOParam);
+ return OidInputFunctionCall(typInput, cstring, typIOParam, -1);
+}
+
/* Common code for all versions of pg_stat_statements() */
static void
pg_stat_statements_internal(FunctionCallInfo fcinfo,
@@ -1428,6 +1726,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
if (api_version != PGSS_V1_3)
elog(ERROR, "incorrect number of output arguments");
break;
+ case PG_STAT_STATEMENTS_COLS_V1_6:
+ if (api_version != PGSS_V1_6)
+ elog(ERROR, "incorrect number of output arguments");
+ break;
default:
elog(ERROR, "incorrect number of output arguments");
}
@@ -1544,17 +1846,69 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
if (enc != qstr)
pfree(enc);
+
+ if (api_version >= PGSS_V1_6)
+ {
+ /* consts */
+ char *cstr, *pstr, *ptstr;
+
+ cstr = qtext_fetch(entry->consts_offset,
+ entry->consts_len,
+ qbuffer,
+ qbuffer_size);
+
+ if (cstr && (entry->consts_len >= 0))
+ values[i++] = CStringGetArrayDatum(TEXTARRAYOID, cstr);
+ else
+ nulls[i++] = true;
+
+ /* params */
+ pstr = qtext_fetch(entry->params_offset,
+ entry->params_len,
+ qbuffer,
+ qbuffer_size);
+
+ if (pstr && (entry->params_len >= 0))
+ values[i++] = CStringGetArrayDatum(TEXTARRAYOID, pstr);
+ else
+ nulls[i++] = true;
+
+ /* param types */
+ ptstr = qtext_fetch(entry->param_types_offset,
+ entry->param_types_len,
+ qbuffer,
+ qbuffer_size);
+
+ if (ptstr && (entry->param_types_len >= 0))
+ values[i++] = CStringGetArrayDatum(REGTYPEARRAYOID, ptstr);
+ else
+ nulls[i++] = true;
+ }
}
else
{
- /* Just return a null if we fail to find the text */
+ /* Just return nulls if we fail to find the text */
nulls[i++] = true;
+ if (api_version >= PGSS_V1_6)
+ {
+ /* consts, params, and param_types */
+ nulls[i++] = true;
+ nulls[i++] = true;
+ nulls[i++] = true;
+ }
}
}
else
{
/* Query text not requested */
nulls[i++] = true;
+ if (api_version >= PGSS_V1_6)
+ {
+ /* consts, params, and param_types */
+ nulls[i++] = true;
+ nulls[i++] = true;
+ nulls[i++] = true;
+ }
}
}
else
@@ -1571,6 +1925,17 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
values[i++] = CStringGetTextDatum("<insufficient privilege>");
else
nulls[i++] = true;
+
+ /*
+ * Just return nulls in this case for constants, parameters, and
+ * parameter types
+ */
+ if (api_version >= PGSS_V1_6)
+ {
+ nulls[i++] = true;
+ nulls[i++] = true;
+ nulls[i++] = true;
+ }
}
/* copy counters to a local variable to keep locking time short */
@@ -1629,6 +1994,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 :
api_version == PGSS_V1_2 ? PG_STAT_STATEMENTS_COLS_V1_2 :
api_version == PGSS_V1_3 ? PG_STAT_STATEMENTS_COLS_V1_3 :
+ api_version == PGSS_V1_6 ? PG_STAT_STATEMENTS_COLS_V1_6 :
-1 /* fail if you forget to update this assert */ ));
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
@@ -1675,8 +2041,12 @@ pgss_memsize(void)
* have made the entry while we waited to get exclusive lock.
*/
static pgssEntry *
-entry_alloc(pgssHashKey *key, Size query_offset, int query_len, int encoding,
- bool sticky)
+entry_alloc(pgssHashKey *key,
+ Size query_offset, int query_len,
+ Size consts_offset, int consts_len,
+ Size params_offset, int params_len,
+ Size param_types_offset, int param_types_len,
+ int encoding, bool sticky)
{
pgssEntry *entry;
bool found;
@@ -1703,6 +2073,12 @@ entry_alloc(pgssHashKey *key, Size query_offset, int query_len, int encoding,
entry->query_offset = query_offset;
entry->query_len = query_len;
entry->encoding = encoding;
+ entry->consts_offset = consts_offset;
+ entry->consts_len = consts_len;
+ entry->params_offset = params_offset;
+ entry->params_len = params_len;
+ entry->param_types_offset = param_types_offset;
+ entry->param_types_len = param_types_len;
}
return entry;
@@ -1771,7 +2147,7 @@ entry_dealloc(void)
/* In the mean length computation, ignore dropped texts. */
if (entry->query_len >= 0)
{
- tottextlen += entry->query_len + 1;
+ tottextlen += entry->query_len + entry->consts_len + entry->params_len + entry->param_types_len + 1;
nvalidtexts++;
}
}
@@ -2030,6 +2406,23 @@ need_gc_qtexts(void)
return true;
}
+/* This is used for some repetitive code in gc_qtexts */
+#define WRITE_TEXT(xlen, xoffset) do { \
+ int len = (xlen); \
+ char *txt = qtext_fetch((xoffset), len, qbuffer, qbuffer_size); \
+ if (txt && fwrite(txt, 1, len + 1, qfile) != len + 1) \
+ { \
+ ereport(LOG, \
+ (errcode_for_file_access(), \
+ errmsg("could not write pg_stat_statement file \"%s\": %m", \
+ PGSS_TEXT_FILE))); \
+ hash_seq_term(&hash_seq); \
+ goto gc_fail; \
+ } \
+ (xoffset) = extent; \
+ extent += len + 1; \
+} while(0)
+
/*
* Garbage-collect orphaned query texts in external file.
*
@@ -2109,22 +2502,21 @@ gc_qtexts(void)
/* Trouble ... drop the text */
entry->query_offset = 0;
entry->query_len = -1;
+ entry->consts_offset = 0;
+ entry->consts_len = -1;
+ entry->params_offset = 0;
+ entry->params_len = -1;
+ entry->param_types_offset = 0;
+ entry->param_types_len = -1;
/* entry will not be counted in mean query length computation */
continue;
}
- if (fwrite(qry, 1, query_len + 1, qfile) != query_len + 1)
- {
- ereport(LOG,
- (errcode_for_file_access(),
- errmsg("could not write pg_stat_statement file \"%s\": %m",
- PGSS_TEXT_FILE)));
- hash_seq_term(&hash_seq);
- goto gc_fail;
- }
+ WRITE_TEXT(entry->query_len, entry->query_offset);
+ WRITE_TEXT(entry->consts_len, entry->consts_offset);
+ WRITE_TEXT(entry->params_len, entry->params_offset);
+ WRITE_TEXT(entry->param_types_len, entry->param_types_offset);
- entry->query_offset = extent;
- extent += query_len + 1;
nentries++;
}
@@ -2458,8 +2850,8 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
/* We jumble only the constant's type, not its value */
APP_JUMB(c->consttype);
- /* Also, record its parse location for query normalization */
- RecordConstLocation(jstate, c->location);
+ /* Also, record its parse location and type for query normalization */
+ RecordConstLocationAndType(jstate, c->location, c->consttype);
}
break;
case T_Param:
@@ -2903,7 +3295,7 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
* that is currently being walked.
*/
static void
-RecordConstLocation(pgssJumbleState *jstate, int location)
+RecordConstLocationAndType(pgssJumbleState *jstate, int location, Oid type)
{
/* -1 indicates unknown or undefined location */
if (location >= 0)
@@ -2916,10 +3308,20 @@ RecordConstLocation(pgssJumbleState *jstate, int location)
repalloc(jstate->clocations,
jstate->clocations_buf_size *
sizeof(pgssLocationLen));
+
+ /* the consts_types array piggybacks on this */
+ jstate->consts_types = (Oid *)
+ repalloc(jstate->consts_types,
+ jstate->clocations_buf_size *
+ sizeof(Oid));
}
jstate->clocations[jstate->clocations_count].location = location;
/* initialize lengths to -1 to simplify fill_in_constant_lengths */
jstate->clocations[jstate->clocations_count].length = -1;
+
+ /* piggyback the consts_types array */
+ jstate->consts_types[jstate->clocations_count] = type;
+
jstate->clocations_count++;
}
}
@@ -2945,7 +3347,9 @@ RecordConstLocation(pgssJumbleState *jstate, int location)
*/
static char *
generate_normalized_query(pgssJumbleState *jstate, const char *query,
- int query_loc, int *query_len_p, int encoding)
+ int query_loc, int *query_len_p,
+ char **consts, int *consts_len,
+ int encoding)
{
char *norm_query;
int query_len = *query_len_p;
@@ -2956,6 +3360,9 @@ generate_normalized_query(pgssJumbleState *jstate, const char *query,
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 */
+ Datum *consts_arr; /* A Datum C-array used to make the result */
+ int consts_count; /* The exact number of constants collected */
+ uint64 totalsize; /* Ensure that consts doesn't get too big */
/*
* Get constants' lengths (core system only gives us locations). Note
@@ -2974,6 +3381,10 @@ generate_normalized_query(pgssJumbleState *jstate, const char *query,
/* Allocate result buffer */
norm_query = palloc(norm_query_buflen + 1);
+ /* and the consts array */
+ consts_arr = (Datum *) palloc(sizeof(Datum) * jstate->clocations_count);
+ consts_count = 0;
+ totalsize = 0;
for (i = 0; i < jstate->clocations_count; i++)
{
@@ -2989,6 +3400,15 @@ generate_normalized_query(pgssJumbleState *jstate, const char *query,
if (tok_len < 0)
continue; /* ignore any duplicates */
+ totalsize += tok_len;
+ if (pgss_collect_consts &&
+ (totalsize <= pgss_width_threshold || pgss_width_threshold < 0))
+ {
+ /* Collect the constant */
+ consts_arr[consts_count++] = PointerGetDatum(
+ cstring_to_text_with_len(query + off, tok_len));
+ }
+
/* Copy next chunk (what precedes the next constant) */
len_to_wrt = off - last_off;
len_to_wrt -= last_tok_len;
@@ -3012,6 +3432,32 @@ generate_normalized_query(pgssJumbleState *jstate, const char *query,
*/
len_to_wrt = query_len - quer_loc;
+ /* Turn the Datum C-array into a text SQL-array, unless it was too big */
+ if (!pgss_collect_consts || consts_count == 0 ||
+ (totalsize > pgss_width_threshold && pgss_width_threshold >= 0))
+ {
+ /* Use -1 to indicate that consts is null */
+ *consts = NULL;
+ *consts_len = -1;
+ }
+ else
+ {
+ ArrayType *sql_array;
+ int dims[1];
+ int lbs[1];
+ bool isvarlena;
+ Oid typOutput;
+
+ dims[0] = consts_count;
+ lbs[0] = 1 + jstate->highest_extern_param_id;
+
+ sql_array = construct_md_array(consts_arr, NULL, 1, dims, lbs,
+ TEXTOID, -1, false, 'i');
+ getTypeOutputInfo(TEXTARRAYOID, &typOutput, &isvarlena);
+ *consts = OidOutputFunctionCall(typOutput, PointerGetDatum(sql_array));
+ *consts_len = strlen(*consts);
+ }
+
Assert(len_to_wrt >= 0);
memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt);
n_quer_loc += len_to_wrt;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index 193fcdfafa..617038b4c0 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.control
+++ b/contrib/pg_stat_statements/pg_stat_statements.control
@@ -1,5 +1,5 @@
# pg_stat_statements extension
comment = 'track execution statistics of all SQL statements executed'
-default_version = '1.5'
+default_version = '1.6'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index a8361fd1bf..13923ede5d 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -51,7 +51,7 @@ PREPARE pgss_test (int) AS SELECT $1, 'test' LIMIT 1;
EXECUTE pgss_test(1);
DEALLOCATE pgss_test;
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT query, calls, rows, consts, params, param_types FROM pg_stat_statements ORDER BY query COLLATE "C";
--
-- CRUD: INSERT SELECT UPDATE DELETE on test table
@@ -195,4 +195,23 @@ DROP FUNCTION PLUS_TWO(INTEGER);
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+-- test the constants
+SELECT 42 + 6 AS fortyeight, 'hello ' || 'world' as helloworld;
+
+SELECT query, consts
+FROM pg_stat_statements
+WHERE query ~ 'fortyeight'
+ AND query !~ 'pg_stat_statements'
+ AND dbid = (SELECT oid FROM pg_database WHERE datname = current_catalog);
+
+-- test the parameters
+PREPARE foo AS SELECT relname FROM pg_class WHERE oid = $1 AND relpages > $2;
+EXECUTE foo (0, 42);
+
+SELECT query, params, param_types
+FROM pg_stat_statements
+WHERE query ~ 'foo'
+ AND query !~ 'pg_stat_statements'
+ AND dbid = (SELECT oid FROM pg_database WHERE datname = current_catalog);
+
DROP EXTENSION pg_stat_statements;
