Hi Julien!
Thank you for such detailed review!
On Wed, 2022-03-30 at 17:31 +0800, Julien Rouhaud wrote:
> Feature wise, I'm happy with the patch. I just have a few comments.
>
> Tests:
>
> - it's missing some test in sql/oldextversions.sql to validate that the
> code
> works with the extension in version 1.9
Yes, I've just added some tests there, but it seems they are not quite
suficient. Maybe we should try to do some queries to views and
functions in old versions? A least when new C function version
appears...
During tests developing I've noted that current test of
pg_stat_statements_info view actually tests only view access. However
we can test at least functionality of stats_reset field like this:
SELECT now() AS ref_ts \gset
SELECT dealloc, stats_reset >= :'ref_ts' FROM pg_stat_statements_info;
SELECT pg_stat_statements_reset();
SELECT dealloc, stats_reset >= :'ref_ts' FROM pg_stat_statements_info;
Does it seems reasonable?
> - the last test removed the minmax_plan_zero field, why?
My thaught was as follows... Reexecution of the same query will
definitely cause execution. However, most likely it wouldn't be
planned, but if it would (maybe this is possible, or maybe it will be
possible in the future in some cases) the test shouldn't fail. Checking
of only execution stats seems enough to me - in most cases we can't
check planning stats with such test anyway.
What do you think about it?
>
> Code:
>
> + TimestampTz stats_since; /* timestamp of entry
> allocation moment */
>
> I think "timestamp of entry allocation" is enough?
Yes
>
> + * Calculate min and max time. min = 0 and max
> = 0
> + * means that min/max statistics reset was
> happen
>
> maybe "means that the min/max statistics were reset"
Agreed
>
> +/*
> + * Reset min/max values of specified entries
> + */
> +static void
> +entry_minmax_reset(Oid userid, Oid dbid, uint64 queryid)
> +{
> [...]
>
> There's a lot of duplicated logic with entry_reset().
> Would it be possible to merge at least the C reset function to handle
> either
> all-metrics or minmax-only?
Great point! I've merged minmax reset functionality in the entry_reset
function.
> Also, maybe it would be better to have a single SQL
> reset function, something like:
>
> pg_stat_statements_reset(IN userid Oid DEFAULT 0,
> IN dbid Oid DEFAULT 0,
> IN queryid bigint DEFAULT 0,
> IN minmax_only DEFAULT false
> )
Of course!
>
> Doc:
>
> + <structfield>stats_since</structfield> <type>timestamp with
> time zone</type>
> + </para>
> + <para>
> + Timestamp of statistics gathering start for the statement
>
> The description is a bit weird. Maybe like "Time at which statistics
> gathering
> started for this statement"? Same for the minmax version.
Agreed.
I've attached 7th patch version with fixes mentioned above.
--
Best regards, Andrei Zubkov
From 41e56237b2c4b68ff101590e6f7c4cdc79b53816 Mon Sep 17 00:00:00 2001
From: Andrei Zubkov <zub...@moonset.ru>
Date: Thu, 31 Mar 2022 12:37:17 +0300
Subject: [PATCH] pg_stat_statements: Track statement entry timestamp
This patch adds stats_since column to the pg_stat_statements view. This column
is populated with the current timestamp when a new statement is added to the
pg_stat_statements hashtable. It provides clean information about statistics
collection time interval for each statement. Besides it can be used
by sampling solutions to detect situations when a statement was evicted and
returned back between samples.
Such sampling solution could derive any pg_stat_statements statistic value for
an interval between two samples with except of all min/max statistics. To
address this issue this patch adds the ability to reset min/max
statistics independently of statement reset using the new minmax_only parameter
of the pg_stat_statements_reset(userid oid, dbid oid, queryid bigint,
minmax_only boolean) function.
Timestamp of such reset is stored in the minmax_stats_since field for
each statement.
Discussion:
https://www.postgresql.org/message-id/flat/72e80e7b160a6eb189df9ef6f068cce3765d37f8.camel%40moonset.ru
---
contrib/pg_stat_statements/Makefile | 3 +-
.../expected/oldextversions.out | 61 ++++++
.../expected/pg_stat_statements.out | 140 ++++++++++++
.../pg_stat_statements--1.9--1.10.sql | 108 ++++++++++
.../pg_stat_statements/pg_stat_statements.c | 200 +++++++++++++++---
.../pg_stat_statements.control | 2 +-
.../pg_stat_statements/sql/oldextversions.sql | 8 +
.../sql/pg_stat_statements.sql | 92 ++++++++
doc/src/sgml/pgstatstatements.sgml | 52 ++++-
9 files changed, 625 insertions(+), 41 deletions(-)
create mode 100644 contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 7fabd96f38d..edc40c8bbfb 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -6,7 +6,8 @@ OBJS = \
pg_stat_statements.o
EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.8--1.9.sql \
+DATA = pg_stat_statements--1.4.sql \
+ pg_stat_statements--1.9--1.10.sql pg_stat_statements--1.8--1.9.sql \
pg_stat_statements--1.7--1.8.sql pg_stat_statements--1.6--1.7.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 \
diff --git a/contrib/pg_stat_statements/expected/oldextversions.out b/contrib/pg_stat_statements/expected/oldextversions.out
index f18c08838f5..70877948491 100644
--- a/contrib/pg_stat_statements/expected/oldextversions.out
+++ b/contrib/pg_stat_statements/expected/oldextversions.out
@@ -136,4 +136,65 @@ SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
(1 row)
+ALTER EXTENSION pg_stat_statements UPDATE TO '1.9';
+\d pg_stat_statements
+ View "public.pg_stat_statements"
+ Column | Type | Collation | Nullable | Default
+---------------------+------------------+-----------+----------+---------
+ userid | oid | | |
+ dbid | oid | | |
+ toplevel | boolean | | |
+ queryid | bigint | | |
+ query | text | | |
+ plans | bigint | | |
+ total_plan_time | double precision | | |
+ min_plan_time | double precision | | |
+ max_plan_time | double precision | | |
+ mean_plan_time | double precision | | |
+ stddev_plan_time | double precision | | |
+ calls | bigint | | |
+ total_exec_time | double precision | | |
+ min_exec_time | double precision | | |
+ max_exec_time | double precision | | |
+ mean_exec_time | double precision | | |
+ stddev_exec_time | double precision | | |
+ rows | bigint | | |
+ shared_blks_hit | bigint | | |
+ shared_blks_read | bigint | | |
+ shared_blks_dirtied | bigint | | |
+ shared_blks_written | bigint | | |
+ local_blks_hit | bigint | | |
+ local_blks_read | bigint | | |
+ local_blks_dirtied | bigint | | |
+ local_blks_written | bigint | | |
+ temp_blks_read | bigint | | |
+ temp_blks_written | bigint | | |
+ blk_read_time | double precision | | |
+ blk_write_time | double precision | | |
+ wal_records | bigint | | |
+ wal_fpi | bigint | | |
+ wal_bytes | numeric | | |
+
+\d pg_stat_statements_info
+ View "public.pg_stat_statements_info"
+ Column | Type | Collation | Nullable | Default
+-------------+--------------------------+-----------+----------+---------
+ dealloc | bigint | | |
+ stats_reset | timestamp with time zone | | |
+
+SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+ pg_get_functiondef
+--------------------------------------------------------------------------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION public.pg_stat_statements_reset(userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0)+
+ RETURNS void +
+ LANGUAGE c +
+ PARALLEL SAFE STRICT +
+ AS '$libdir/pg_stat_statements', $function$pg_stat_statements_reset_1_7$function$ +
+
+(1 row)
+
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+ERROR: permission denied for function pg_stat_statements_reset
+RESET SESSION AUTHORIZATION;
DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index e0abe34bb6a..5d0285853f6 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -1077,4 +1077,144 @@ SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
2
(1 row)
+--
+-- statement timestamps
+--
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SELECT 1 AS "STMTTS1";
+ STMTTS1
+---------
+ 1
+(1 row)
+
+SELECT now() AS ref_ts \gset
+SELECT 1,2 AS "STMTTS2";
+ ?column? | STMTTS2
+----------+---------
+ 1 | 2
+(1 row)
+
+SELECT stats_since >= :'ref_ts', count(*) FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%'
+GROUP BY stats_since >= :'ref_ts'
+ORDER BY stats_since >= :'ref_ts';
+ ?column? | count
+----------+-------
+ f | 1
+ t | 1
+(2 rows)
+
+SELECT now() AS ref_ts \gset
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_plan_time + max_plan_time = 0
+ ) as minmax_plan_zero,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_stats_since_after_ref,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+ total | minmax_plan_zero | minmax_exec_zero | minmax_stats_since_after_ref | stats_since_after_ref
+-------+------------------+------------------+------------------------------+-----------------------
+ 2 | 0 | 0 | 0 | 0
+(1 row)
+
+-- Perform single min/max reset
+SELECT pg_stat_statements_reset(0, 0, queryid, true)
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS1%';
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+-- check
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_plan_time + max_plan_time = 0
+ ) as minmax_plan_zero,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_stats_since_after_ref,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+ total | minmax_plan_zero | minmax_exec_zero | minmax_stats_since_after_ref | stats_since_after_ref
+-------+------------------+------------------+------------------------------+-----------------------
+ 2 | 1 | 1 | 1 | 0
+(1 row)
+
+-- Perform common min/max reset
+SELECT pg_stat_statements_reset(0, 0, 0, true);
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+-- check again
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_plan_time + max_plan_time = 0
+ ) as minmax_plan_zero,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_stats_since_after_ref,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+ total | minmax_plan_zero | minmax_exec_zero | minmax_stats_since_after_ref | stats_since_after_ref
+-------+------------------+------------------+------------------------------+-----------------------
+ 2 | 2 | 2 | 2 | 0
+(1 row)
+
+-- Execute first query once more to check stats update
+SELECT 1 AS "STMTTS1";
+ STMTTS1
+---------
+ 1
+(1 row)
+
+-- check
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_stats_since_after_ref,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+ total | minmax_exec_zero | minmax_stats_since_after_ref | stats_since_after_ref
+-------+------------------+------------------------------+-----------------------
+ 2 | 1 | 2 | 0
+(1 row)
+
DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql b/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql
new file mode 100644
index 00000000000..2f9cc0944e7
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql
@@ -0,0 +1,108 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.10'" to load this file. \quit
+
+/* We need to redefine a view and a function */
+/* 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);
+ALTER EXTENSION pg_stat_statements DROP FUNCTION
+ pg_stat_statements_reset(Oid, Oid, bigint);
+
+/* Then we can drop them */
+DROP VIEW pg_stat_statements;
+DROP FUNCTION pg_stat_statements(boolean);
+DROP FUNCTION pg_stat_statements_reset(Oid, Oid, bigint);
+
+/* Now redefine */
+CREATE FUNCTION pg_stat_statements(IN showtext boolean,
+ OUT userid oid,
+ OUT dbid oid,
+ OUT toplevel bool,
+ OUT queryid bigint,
+ OUT query text,
+ OUT plans int8,
+ OUT total_plan_time float8,
+ OUT min_plan_time float8,
+ OUT max_plan_time float8,
+ OUT mean_plan_time float8,
+ OUT stddev_plan_time float8,
+ OUT calls int8,
+ OUT total_exec_time float8,
+ OUT min_exec_time float8,
+ OUT max_exec_time float8,
+ OUT mean_exec_time float8,
+ OUT stddev_exec_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,
+ OUT wal_records int8,
+ OUT wal_fpi int8,
+ OUT wal_bytes numeric,
+ OUT stats_since timestamp with time zone,
+ OUT minmax_stats_since timestamp with time zone
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_stat_statements_1_10'
+LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
+
+CREATE VIEW pg_stat_statements AS
+ SELECT
+ userid,
+ dbid,
+ toplevel,
+ queryid,
+ query,
+ plans,
+ total_plan_time,
+ min_plan_time,
+ max_plan_time,
+ mean_plan_time,
+ stddev_plan_time,
+ calls,
+ total_exec_time,
+ min_exec_time,
+ max_exec_time,
+ mean_exec_time,
+ stddev_exec_time,
+ rows,
+ shared_blks_hit,
+ shared_blks_read,
+ shared_blks_dirtied,
+ shared_blks_written,
+ local_blks_hit,
+ local_blks_read,
+ local_blks_dirtied,
+ local_blks_written,
+ temp_blks_read,
+ temp_blks_written,
+ blk_read_time,
+ blk_write_time,
+ wal_records,
+ wal_fpi,
+ wal_bytes,
+ stats_since,
+ minmax_stats_since
+ FROM pg_stat_statements(true);
+
+CREATE FUNCTION pg_stat_statements_reset(IN userid Oid DEFAULT 0,
+ IN dbid Oid DEFAULT 0,
+ IN queryid bigint DEFAULT 0,
+ IN minmax_only boolean DEFAULT false
+)
+RETURNS void
+AS 'MODULE_PATHNAME', 'pg_stat_statements_reset_1_10'
+LANGUAGE C STRICT PARALLEL SAFE;
+
+GRANT SELECT ON pg_stat_statements TO PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 55786ae84f2..879f220066c 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -88,7 +88,7 @@ PG_MODULE_MAGIC;
#define PGSS_TEXT_FILE PG_STAT_TMP_DIR "/pgss_query_texts.stat"
/* Magic number identifying the stats file format */
-static const uint32 PGSS_FILE_HEADER = 0x20201227;
+static const uint32 PGSS_FILE_HEADER = 0x20210322;
/* PostgreSQL major version number, changes in which invalidate all entries */
static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100;
@@ -121,7 +121,8 @@ typedef enum pgssVersion
PGSS_V1_2,
PGSS_V1_3,
PGSS_V1_8,
- PGSS_V1_9
+ PGSS_V1_9,
+ PGSS_V1_10
} pgssVersion;
typedef enum pgssStoreKind
@@ -165,9 +166,9 @@ typedef struct Counters
double total_time[PGSS_NUMKIND]; /* total planning/execution time,
* in msec */
double min_time[PGSS_NUMKIND]; /* minimum planning/execution time in
- * msec */
+ * msec since min/max reset */
double max_time[PGSS_NUMKIND]; /* maximum planning/execution time in
- * msec */
+ * msec since min/max reset */
double mean_time[PGSS_NUMKIND]; /* mean planning/execution time in
* msec */
double sum_var_time[PGSS_NUMKIND]; /* sum of variances in
@@ -209,12 +210,14 @@ typedef struct pgssGlobalStats
*/
typedef struct pgssEntry
{
- pgssHashKey key; /* hash key of entry - MUST BE FIRST */
- Counters counters; /* the statistics for this query */
- Size query_offset; /* query text offset in external file */
- int query_len; /* # of valid bytes in query string, or -1 */
- int encoding; /* query text encoding */
- slock_t mutex; /* protects the counters only */
+ pgssHashKey key; /* hash key of entry - MUST BE FIRST */
+ Counters counters; /* the statistics for this query */
+ Size query_offset; /* query text offset in external file */
+ int query_len; /* # of valid bytes in query string, or -1 */
+ int encoding; /* query text encoding */
+ TimestampTz stats_since; /* timestamp of entry allocation */
+ TimestampTz minmax_stats_since; /* timestamp of last min/max values reset */
+ slock_t mutex; /* protects the counters only */
} pgssEntry;
/*
@@ -298,10 +301,12 @@ void _PG_fini(void);
PG_FUNCTION_INFO_V1(pg_stat_statements_reset);
PG_FUNCTION_INFO_V1(pg_stat_statements_reset_1_7);
+PG_FUNCTION_INFO_V1(pg_stat_statements_reset_1_10);
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_8);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_9);
+PG_FUNCTION_INFO_V1(pg_stat_statements_1_10);
PG_FUNCTION_INFO_V1(pg_stat_statements);
PG_FUNCTION_INFO_V1(pg_stat_statements_info);
@@ -345,7 +350,7 @@ static char *qtext_fetch(Size query_offset, int query_len,
char *buffer, Size buffer_size);
static bool need_gc_qtexts(void);
static void gc_qtexts(void);
-static void entry_reset(Oid userid, Oid dbid, uint64 queryid);
+static void entry_reset(Oid userid, Oid dbid, uint64 queryid, bool minmax_only);
static char *generate_normalized_query(JumbleState *jstate, const char *query,
int query_loc, int *query_len_p);
static void fill_in_constant_lengths(JumbleState *jstate, const char *query,
@@ -649,6 +654,8 @@ pgss_shmem_startup(void)
/* copy in the actual stats */
entry->counters = temp.counters;
+ entry->stats_since = temp.stats_since;
+ entry->minmax_stats_since = temp.minmax_stats_since;
}
/* Read global statistics for pg_stat_statements */
@@ -1350,11 +1357,23 @@ pgss_store(const char *query, uint64 queryId,
e->counters.sum_var_time[kind] +=
(total_time - old_mean) * (total_time - e->counters.mean_time[kind]);
- /* calculate min and max time */
- if (e->counters.min_time[kind] > total_time)
+ /*
+ * Calculate min and max time. min = 0 and max = 0
+ * means that the min/max statistics were reset
+ */
+ if (e->counters.min_time[kind] == 0
+ && e->counters.max_time[kind] == 0)
+ {
e->counters.min_time[kind] = total_time;
- if (e->counters.max_time[kind] < total_time)
e->counters.max_time[kind] = total_time;
+ }
+ else
+ {
+ if (e->counters.min_time[kind] > total_time)
+ e->counters.min_time[kind] = total_time;
+ if (e->counters.max_time[kind] < total_time)
+ e->counters.max_time[kind] = total_time;
+ }
}
e->counters.rows += rows;
e->counters.shared_blks_hit += bufusage->shared_blks_hit;
@@ -1399,7 +1418,25 @@ pg_stat_statements_reset_1_7(PG_FUNCTION_ARGS)
dbid = PG_GETARG_OID(1);
queryid = (uint64) PG_GETARG_INT64(2);
- entry_reset(userid, dbid, queryid);
+ entry_reset(userid, dbid, queryid, false);
+
+ PG_RETURN_VOID();
+}
+
+Datum
+pg_stat_statements_reset_1_10(PG_FUNCTION_ARGS)
+{
+ Oid userid;
+ Oid dbid;
+ uint64 queryid;
+ bool minmax_only;
+
+ userid = PG_GETARG_OID(0);
+ dbid = PG_GETARG_OID(1);
+ queryid = (uint64) PG_GETARG_INT64(2);
+ minmax_only = PG_GETARG_BOOL(3);
+
+ entry_reset(userid, dbid, queryid, minmax_only);
PG_RETURN_VOID();
}
@@ -1410,7 +1447,7 @@ pg_stat_statements_reset_1_7(PG_FUNCTION_ARGS)
Datum
pg_stat_statements_reset(PG_FUNCTION_ARGS)
{
- entry_reset(0, 0, 0);
+ entry_reset(0, 0, 0, false);
PG_RETURN_VOID();
}
@@ -1422,7 +1459,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
#define PG_STAT_STATEMENTS_COLS_V1_3 23
#define PG_STAT_STATEMENTS_COLS_V1_8 32
#define PG_STAT_STATEMENTS_COLS_V1_9 33
-#define PG_STAT_STATEMENTS_COLS 33 /* maximum of above */
+#define PG_STAT_STATEMENTS_COLS_V1_10 35
+#define PG_STAT_STATEMENTS_COLS 35 /* maximum of above */
/*
* Retrieve statement statistics.
@@ -1434,6 +1472,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_10(PG_FUNCTION_ARGS)
+{
+ bool showtext = PG_GETARG_BOOL(0);
+
+ pg_stat_statements_internal(fcinfo, PGSS_V1_10, showtext);
+
+ return (Datum) 0;
+}
+
Datum
pg_stat_statements_1_9(PG_FUNCTION_ARGS)
{
@@ -1547,6 +1595,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
if (api_version != PGSS_V1_9)
elog(ERROR, "incorrect number of output arguments");
break;
+ case PG_STAT_STATEMENTS_COLS_V1_10:
+ if (api_version != PGSS_V1_10)
+ elog(ERROR, "incorrect number of output arguments");
+ break;
default:
elog(ERROR, "incorrect number of output arguments");
}
@@ -1625,6 +1677,8 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
Counters tmp;
double stddev;
int64 queryid = entry->key.queryid;
+ TimestampTz stats_since;
+ TimestampTz minmax_stats_since;
memset(values, 0, sizeof(values));
memset(nulls, 0, sizeof(nulls));
@@ -1693,6 +1747,8 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
SpinLockAcquire(&e->mutex);
tmp = e->counters;
+ stats_since = e->stats_since;
+ minmax_stats_since = e->minmax_stats_since;
SpinLockRelease(&e->mutex);
}
@@ -1764,6 +1820,11 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
Int32GetDatum(-1));
values[i++] = wal_bytes;
}
+ if (api_version >= PGSS_V1_10)
+ {
+ values[i++] = TimestampTzGetDatum(stats_since);
+ values[i++] = TimestampTzGetDatum(minmax_stats_since);
+ }
Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 :
api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 :
@@ -1771,6 +1832,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
api_version == PGSS_V1_3 ? PG_STAT_STATEMENTS_COLS_V1_3 :
api_version == PGSS_V1_8 ? PG_STAT_STATEMENTS_COLS_V1_8 :
api_version == PGSS_V1_9 ? PG_STAT_STATEMENTS_COLS_V1_9 :
+ api_version == PGSS_V1_10 ? PG_STAT_STATEMENTS_COLS_V1_10 :
-1 /* fail if you forget to update this assert */ ));
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
@@ -1884,6 +1946,8 @@ 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->stats_since = GetCurrentTimestamp();
+ entry->minmax_stats_since = entry->stats_since;
}
return entry;
@@ -2431,17 +2495,19 @@ gc_fail:
}
/*
- * Release entries corresponding to parameters passed.
+ * Reset entries corresponding to parameters passed.
*/
static void
-entry_reset(Oid userid, Oid dbid, uint64 queryid)
+entry_reset(Oid userid, Oid dbid, uint64 queryid, bool minmax_only)
{
HASH_SEQ_STATUS hash_seq;
pgssEntry *entry;
+ Counters *entry_counters;
FILE *qfile;
long num_entries;
long num_remove = 0;
pgssHashKey key;
+ TimestampTz minmax_stats_reset;
if (!pgss || !pgss_hash)
ereport(ERROR,
@@ -2451,6 +2517,10 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
LWLockAcquire(pgss->lock, LW_EXCLUSIVE);
num_entries = hash_get_num_entries(pgss_hash);
+ if (minmax_only) {
+ minmax_stats_reset = GetCurrentTimestamp();
+ }
+
if (userid != 0 && dbid != 0 && queryid != UINT64CONST(0))
{
/* If all the parameters are available, use the fast path. */
@@ -2459,23 +2529,57 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
key.dbid = dbid;
key.queryid = queryid;
- /* Remove the key if it exists, starting with the top-level entry */
+ /* Reset is started from nested-level */
key.toplevel = false;
- entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_REMOVE, NULL);
- if (entry) /* found */
- num_remove++;
+ entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_FIND, NULL);
+
+ if (entry) {
+ /* Found */
+ if (minmax_only) {
+ /* When requested reset only min/max statistics of an entry */
+ entry_counters = &entry->counters;
+ for (int kind = 0; kind < PGSS_NUMKIND; kind++)
+ {
+ entry_counters->max_time[kind] = 0;
+ entry_counters->min_time[kind] = 0;
+ }
+ entry->minmax_stats_since = minmax_stats_reset;
+ }
+ else
+ {
+ /* Remove the key otherwise */
+ hash_search(pgss_hash, &entry->key, HASH_REMOVE, NULL);
+ num_remove++;
+ }
+ }
- /* Also remove entries for top level statements */
+ /* Reset entries for top level statements */
key.toplevel = true;
-
- /* Remove the key if exists */
- entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_REMOVE, NULL);
- if (entry) /* found */
- num_remove++;
+ entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_FIND, NULL);
+
+ if (entry) {
+ /* Found */
+ if (minmax_only) {
+ /* When requested reset only min/max statistics of an entry */
+ entry_counters = &entry->counters;
+ for (int kind = 0; kind < PGSS_NUMKIND; kind++)
+ {
+ entry_counters->max_time[kind] = 0;
+ entry_counters->min_time[kind] = 0;
+ }
+ entry->minmax_stats_since = minmax_stats_reset;
+ }
+ else
+ {
+ /* Remove the key otherwise */
+ hash_search(pgss_hash, &entry->key, HASH_REMOVE, NULL);
+ num_remove++;
+ }
+ }
}
else if (userid != 0 || dbid != 0 || queryid != UINT64CONST(0))
{
- /* Remove entries corresponding to valid parameters. */
+ /* Reset entries corresponding to valid parameters. */
hash_seq_init(&hash_seq, pgss_hash);
while ((entry = hash_seq_search(&hash_seq)) != NULL)
{
@@ -2483,8 +2587,22 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
(!dbid || entry->key.dbid == dbid) &&
(!queryid || entry->key.queryid == queryid))
{
- hash_search(pgss_hash, &entry->key, HASH_REMOVE, NULL);
- num_remove++;
+ if (minmax_only) {
+ /* When requested reset only min/max statistics of an entry */
+ entry_counters = &entry->counters;
+ for (int kind = 0; kind < PGSS_NUMKIND; kind++)
+ {
+ entry_counters->max_time[kind] = 0;
+ entry_counters->min_time[kind] = 0;
+ }
+ entry->minmax_stats_since = minmax_stats_reset;
+ }
+ else
+ {
+ /* Remove the key otherwise */
+ hash_search(pgss_hash, &entry->key, HASH_REMOVE, NULL);
+ num_remove++;
+ }
}
}
}
@@ -2494,8 +2612,22 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
hash_seq_init(&hash_seq, pgss_hash);
while ((entry = hash_seq_search(&hash_seq)) != NULL)
{
- hash_search(pgss_hash, &entry->key, HASH_REMOVE, NULL);
- num_remove++;
+ if (minmax_only) {
+ /* When requested reset only min/max statistics of an entry */
+ entry_counters = &entry->counters;
+ for (int kind = 0; kind < PGSS_NUMKIND; kind++)
+ {
+ entry_counters->max_time[kind] = 0;
+ entry_counters->min_time[kind] = 0;
+ }
+ entry->minmax_stats_since = minmax_stats_reset;
+ }
+ else
+ {
+ /* Remove entry otherwise */
+ hash_search(pgss_hash, &entry->key, HASH_REMOVE, NULL);
+ num_remove++;
+ }
}
}
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index 2f1ce6ed507..0747e481383 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 planning and execution statistics of all SQL statements executed'
-default_version = '1.9'
+default_version = '1.10'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
diff --git a/contrib/pg_stat_statements/sql/oldextversions.sql b/contrib/pg_stat_statements/sql/oldextversions.sql
index f2e822acd3e..c2af29866ba 100644
--- a/contrib/pg_stat_statements/sql/oldextversions.sql
+++ b/contrib/pg_stat_statements/sql/oldextversions.sql
@@ -36,4 +36,12 @@ AlTER EXTENSION pg_stat_statements UPDATE TO '1.8';
\d pg_stat_statements
SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+ALTER EXTENSION pg_stat_statements UPDATE TO '1.9';
+\d pg_stat_statements
+\d pg_stat_statements_info
+SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
+SET SESSION AUTHORIZATION pg_read_all_stats;
+SELECT pg_stat_statements_reset();
+RESET SESSION AUTHORIZATION;
+
DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index dffd2c8c187..af41243d4e3 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -442,4 +442,96 @@ SELECT (
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
+--
+-- statement timestamps
+--
+SELECT pg_stat_statements_reset();
+SELECT 1 AS "STMTTS1";
+SELECT now() AS ref_ts \gset
+SELECT 1,2 AS "STMTTS2";
+SELECT stats_since >= :'ref_ts', count(*) FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%'
+GROUP BY stats_since >= :'ref_ts'
+ORDER BY stats_since >= :'ref_ts';
+
+SELECT now() AS ref_ts \gset
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_plan_time + max_plan_time = 0
+ ) as minmax_plan_zero,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_stats_since_after_ref,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+
+-- Perform single min/max reset
+SELECT pg_stat_statements_reset(0, 0, queryid, true)
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS1%';
+
+-- check
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_plan_time + max_plan_time = 0
+ ) as minmax_plan_zero,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_stats_since_after_ref,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+
+-- Perform common min/max reset
+SELECT pg_stat_statements_reset(0, 0, 0, true);
+
+-- check again
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_plan_time + max_plan_time = 0
+ ) as minmax_plan_zero,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_stats_since_after_ref,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+
+-- Execute first query once more to check stats update
+SELECT 1 AS "STMTTS1";
+
+-- check
+SELECT
+ count(*) as total,
+ count(*) FILTER (
+ WHERE min_exec_time + max_exec_time = 0
+ ) as minmax_exec_zero,
+ count(*) FILTER (
+ WHERE minmax_stats_since >= :'ref_ts'
+ ) as minmax_stats_since_after_ref,
+ count(*) FILTER (
+ WHERE stats_since >= :'ref_ts'
+ ) as stats_since_after_ref
+FROM pg_stat_statements
+WHERE query LIKE '%STMTTS%';
+
DROP EXTENSION pg_stat_statements;
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index 3a7e36bd13c..bad50031fb5 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -142,7 +142,10 @@
<para>
Minimum time spent planning the statement, in milliseconds
(if <varname>pg_stat_statements.track_planning</varname> is enabled,
- otherwise zero)
+ otherwise zero), this field will contain zero until this statement
+ is planned fist time after reset performed by the
+ <function>pg_stat_statements_reset</function> function with the
+ <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
</para></entry>
</row>
@@ -153,7 +156,10 @@
<para>
Maximum time spent planning the statement, in milliseconds
(if <varname>pg_stat_statements.track_planning</varname> is enabled,
- otherwise zero)
+ otherwise zero), this field will contain zero until this statement
+ is planned fist time after reset performed by the
+ <function>pg_stat_statements_reset</function> function with the
+ <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
</para></entry>
</row>
@@ -203,7 +209,11 @@
<structfield>min_exec_time</structfield> <type>double precision</type>
</para>
<para>
- Minimum time spent executing the statement, in milliseconds
+ Minimum time spent executing the statement, in milliseconds,
+ this field will contain zero until this statement
+ is executed fist time after reset performed by the
+ <function>pg_stat_statements_reset</function> function with the
+ <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
</para></entry>
</row>
@@ -212,7 +222,11 @@
<structfield>max_exec_time</structfield> <type>double precision</type>
</para>
<para>
- Maximum time spent executing the statement, in milliseconds
+ Maximum time spent executing the statement, in milliseconds,
+ this field will contain zero until this statement
+ is executed fist time after reset performed by the
+ <function>pg_stat_statements_reset</function> function with the
+ <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
</para></entry>
</row>
@@ -379,6 +393,25 @@
Total amount of WAL generated by the statement in bytes
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>stats_since</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Time at which statistics gathering started for this statement
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>minmax_stats_since</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Time at which min/max statistics gathering started for this
+ statement
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -570,7 +603,8 @@
<variablelist>
<varlistentry>
<term>
- <function>pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void</function>
+ <function>pg_stat_statements_reset(userid Oid, dbid Oid, queryid
+ bigint, minmax_only boolean) returns void</function>
<indexterm>
<primary>pg_stat_statements_reset</primary>
</indexterm>
@@ -589,6 +623,14 @@
If all statistics in the <filename>pg_stat_statements</filename>
view are discarded, it will also reset the statistics in the
<structname>pg_stat_statements_info</structname> view.
+ When <structfield>minmax_only</structfield> is <literal>true</literal> only the
+ values of minimun and maximum execution and planning time will be reset (i.e.
+ <structfield>min_plan_time</structfield>, <structfield>max_plan_time</structfield>,
+ <structfield>min_exec_time</structfield> and <structfield>max_exec_time</structfield>
+ fields). The default value for <structfield>minmax_only</structfield> parameter is
+ <literal>false</literal>. Time of last min/max reset performed is shown in
+ <structfield>minmax_stats_since</structfield> field of the
+ <structname>pg_stat_statements</structname> view.
By default, this function can only be executed by superusers.
Access may be granted to others using <command>GRANT</command>.
</para>
--
2.31.1
