2020-09-22 04:58 に Andres Freund さんは書きました:
Hi,
On 2020-09-18 17:55:12 +0900, btnakamichin wrote:
I’m thinking of adding adding a function called
pg_stat_statements_reset_time() that returns the last timestamp when
executed pg_stat_statements_reset(). pg_stat_statements can reset each
SQL
statement. We can record each sql reset timing timestamp but I don’t
feel
the need. This time, I’m thinking of updating the reset timestamp only
when
all stats were reset.
What exactly do you mean by "can reset each SQL statement"? I don't
really see what alternative you're analyzing?
It does make sense to me to have a function returning the time of the
last reset.
Greetings,
Andres Freund
I’m Sorry, I forgot to include pgsql_hackers in the cc, so I resend it
and attach the patch.
Thank you, I appreciate your comment.
I am unfamiliar with this function. I’m sorry if my interpretation is
mistaken.
What exactly do you mean by "can reset each SQL statement"? I don't
really see what alternative you're analyzing?
pg_stat_statements_reset discards statistics gathered so far by
pg_stat_statements corresponding to the specified userid, dbid and
queryid.
If no parameter is specified or all the specified parameters are
0(invalid), it will discard all statistics.
I think that it is important to record timestamp discarding all
statistics so I’d like to add a function for only all stats were reset.
The following is an example of this feature.
postgres=# select * from pg_stat_statements_reset_time();
pg_stat_statements_reset_time
-------------------------------
2020-09-24 13:36:44.87005+09
(1 row)
Best regards,
Naoki Nakamichi
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 081f997d70..3ec627b956 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -6,7 +6,7 @@ OBJS = \
pg_stat_statements.o
EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.4.sql \
+DATA = pg_stat_statements--1.4.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/pg_stat_statements--1.8--1.9.sql b/contrib/pg_stat_statements/pg_stat_statements--1.8--1.9.sql
new file mode 100644
index 0000000000..55cd5a3a63
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.8--1.9.sql
@@ -0,0 +1,12 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.8--1.9.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.9'" to load this file. \quit
+
+--- Define pg_stat_statements_reset_time
+
+CREATE FUNCTION pg_stat_statements_reset_time()
+RETURNS TIMESTAMP WITH TIME ZONE
+AS 'MODULE_PATHNAME'
+LANGUAGE C VOLATILE STRICT;
\ No newline at end of file
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 1eac9edaee..f2e7fbda77 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -81,12 +81,12 @@
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/memutils.h"
+#include "utils/timestamp.h"
PG_MODULE_MAGIC;
/* Location of permanent stats file (valid when database is shut down) */
#define PGSS_DUMP_FILE PGSTAT_STAT_PERMANENT_DIRECTORY "/pg_stat_statements.stat"
-
/*
* Location of external query text file. We don't keep it in the core
* system's stats_temp_directory. The core system can safely use that GUC
@@ -222,6 +222,7 @@ typedef struct pgssSharedState
Size extent; /* current extent of query file */
int n_writers; /* number of active writers to query file */
int gc_count; /* query file garbage collection cycle count */
+ TimestampTz reset_time; /* timestamp with all stats reset */
} pgssSharedState;
/*
@@ -327,6 +328,7 @@ 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);
+PG_FUNCTION_INFO_V1(pg_stat_statements_reset_time);
static void pgss_shmem_startup(void);
static void pgss_shmem_shutdown(int code, Datum arg);
@@ -554,6 +556,7 @@ pgss_shmem_startup(void)
pgss->extent = 0;
pgss->n_writers = 0;
pgss->gc_count = 0;
+ pgss->reset_time = GetCurrentTimestamp();
}
memset(&info, 0, sizeof(info));
@@ -673,6 +676,10 @@ pgss_shmem_startup(void)
entry->counters = temp.counters;
}
+ /* read timestamp when all stats were reseted */
+ if (fread(&pgss->reset_time, sizeof(TimestampTz), 1, file) != 1)
+ goto read_error;
+
pfree(buffer);
FreeFile(file);
FreeFile(qfile);
@@ -794,6 +801,10 @@ pgss_shmem_shutdown(int code, Datum arg)
}
}
+ /* store timestamp when all stats were reseted */
+ if (fwrite(&pgss->reset_time, sizeof(TimestampTz), 1, file) != 1)
+ goto error;
+
free(qbuffer);
qbuffer = NULL;
@@ -1483,6 +1494,17 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+Datum pg_stat_statements_reset_time(PG_FUNCTION_ARGS)
+{
+ TimestampTz reset_ts;
+
+ SpinLockAcquire(&pgss->mutex);
+ reset_ts = pgss->reset_time;
+ SpinLockRelease(&pgss->mutex);
+
+ PG_RETURN_TIMESTAMP(reset_ts);
+};
+
/* Number of output arguments (columns) for various API versions */
#define PG_STAT_STATEMENTS_COLS_V1_0 14
#define PG_STAT_STATEMENTS_COLS_V1_1 18
@@ -2496,6 +2518,10 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
}
else
{
+ SpinLockAcquire(&pgss->mutex);
+ pgss->reset_time = GetCurrentTimestamp();
+ SpinLockRelease(&pgss->mutex);
+
/* Remove all entries. */
hash_seq_init(&hash_seq, pgss_hash);
while ((entry = hash_seq_search(&hash_seq)) != NULL)
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index 65b18b11d2..2f1ce6ed50 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.8'
+default_version = '1.9'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index cf2d25b7b2..3aa2ef3b4c 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -508,6 +508,22 @@
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>
+ <function>pg_stat_statements_reset_time(void) returns time stamp with time zone</function>
+ <indexterm>
+ <primary>pg_stat_statements_reset_time</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ this function shows last reset time only when <function>pg_stat_statements_reset(0,0,0)</function> is called.
+ </para>
+ </listitem>
+
+ </varlistentry>
+
<varlistentry>
<term>
<function>pg_stat_statements(showtext boolean) returns setof record</function>
