Thank you for pointing that out. I'll post a fixed patch.
+ SpinLockAcquire(&pgss->mutex);
You might noticed, but there a purpose of using the following
idiom. Without that, compiler might optimize out the comparison
assuming *pgss won't change.
volatile pgssSharedState *s = (volatile pgssSharedState *) pgss; \
SpinLockAcquire(&s->mutex); \
+ SpinLockAcquire(&pgss->mutex);
+ pgss->reset_time = GetCurrentTimestamp();
Fix the use of this idiom when modifying *pgss.
We should avoid (possiblly) time-cosuming thing like
GetCurrentTimestamp();
I understood your point to be "It's better not to execute
GetCurrentTimestamp()
while spinlock is running.
Fix to store the result of GetCurrentTimestamp() in a local variable
before
running spinlock. This value is stored in reset_time.
+ this function shows last reset time only when
<function>pg_stat_statements_reset(0,0,0)</function> is called.
This reads as "この関数はpg_statstatement_reset(0,0,0) が呼び出された
ときにだけ最終リセット時刻を表示します。", which I think is different
from what is intentended.
And the wording is not alike with the documentation for similar
functions.
https://www.postgresql.org/docs/13/functions-datetime.html
current_timestamp → timestamp with time zone
Current date and time (start of current transaction); see Section
9.9.4
https://www.postgresql.org/docs/13/monitoring-stats.html
pg_stat_archiver view
stats_reset timestamp with time zone
Time at which these statistics were last reset
So something like the following?
Time at which pg_stat_statements_reset(0,0,0) was last called.
or
Time at which statistics are last discarded by calling
pg_stat_statements_reset(0,0,0).
I have made the following corrections.
this function shows last reset time only when
<function>pg_stat_statements_reset(0,0,0)</function> is called.
→this function shows the time at which
<function>pg_stat_statements_reset(0,0,0)</function> was last called.
<function>pg_stat_statements_reset_time(void) returns time stamp with
time zone</function>
→<function>pg_stat_statements_reset_time(void) returns timestamp with
time zone</function>
Regards.
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..3d9768b158
--- /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;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 1eac9edaee..8639923f5b 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -81,6 +81,7 @@
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/memutils.h"
+#include "utils/timestamp.h"
PG_MODULE_MAGIC;
@@ -222,6 +223,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 +329,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 +557,7 @@ pgss_shmem_startup(void)
pgss->extent = 0;
pgss->n_writers = 0;
pgss->gc_count = 0;
+ pgss->reset_time = 0;
}
memset(&info, 0, sizeof(info));
@@ -673,6 +677,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 +802,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 +1495,18 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+Datum pg_stat_statements_reset_time(PG_FUNCTION_ARGS)
+{
+ TimestampTz reset_ts;
+ {
+ volatile pgssSharedState *s = (volatile pgssSharedState *) pgss;
+ SpinLockAcquire(&s->mutex);
+ reset_ts = s->reset_time;
+ SpinLockRelease(&s->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
@@ -2458,6 +2482,9 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
long num_entries;
long num_remove = 0;
pgssHashKey key;
+ TimestampTz reset_ts;
+
+ reset_ts = GetCurrentTimestamp();
if (!pgss || !pgss_hash)
ereport(ERROR,
@@ -2496,6 +2523,13 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
}
else
{
+ {
+ volatile pgssSharedState *s = (volatile pgssSharedState *) pgss;
+ SpinLockAcquire(&s->mutex);
+ s->reset_time = reset_ts;
+ SpinLockRelease(&s->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..d5a573dd70 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -24,9 +24,9 @@
When <filename>pg_stat_statements</filename> is loaded, it tracks
statistics across all databases of the server. To access and manipulate
these statistics, the module provides a view, <structname>pg_stat_statements</structname>,
- and the utility functions <function>pg_stat_statements_reset</function> and
- <function>pg_stat_statements</function>. These are not available globally but
- can be enabled for a specific database with
+ and the utility functions <function>pg_stat_statements_reset</function>,
+ <function>pg_stat_statements</function>, and <function>pg_stat_statements_reset_time</function>.
+ These are not available globally but can be enabled for a specific database with
<command>CREATE EXTENSION pg_stat_statements</command>.
</para>
@@ -508,6 +508,22 @@
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>
+ <function>pg_stat_statements_reset_time(void) returns timestamp with time zone</function>
+ <indexterm>
+ <primary>pg_stat_statements_reset_time</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ this function shows the time at which <function>pg_stat_statements_reset(0,0,0)</function> was last called.
+ </para>
+ </listitem>
+
+ </varlistentry>
+
<varlistentry>
<term>
<function>pg_stat_statements(showtext boolean) returns setof record</function>
