However, let me confirm the following.
Is this information really useful?
If there is no valid use case for this, I'd like to drop it.
Thought?
I thought it would be easy for users to see at a glance that if there
is a case I assumed,
if the last modified date and time is old, there is no need to adjust
at all, and if the
last modified date and time is recent, it would be easy for users to
understand that the
parameters need to be adjusted.
What do you think?
Even without the last deallocation timestamp, you can presume
when the deallocation of entries happened by periodically monitoring
the total number of deallocations and seeing those history. Or IMO
it's better to log whenever the deallocation happens as proposed
upthread.
Then you can easily check the history of occurrences of deallocations
from the log file.
Regards,
+1.I think you should output the deallocation history to the log as
well.
In light of the above, I've posted a patch that reflects the points
made.
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/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index e0edb134f3..85e78c7f46 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -859,4 +859,19 @@ SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE
SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 | 0 | 0
(6 rows)
+--
+-- Checking the execution of the pg_stat_statements_info view
+--
+SELECT pg_stat_statements_reset(0,0,0);
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SELECT * FROM pg_stat_statements_info;
+ dealloc
+---------
+ 0
+(1 row)
+
DROP EXTENSION pg_stat_statements;
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..5c491f242a
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.8--1.9.sql
@@ -0,0 +1,17 @@
+/* 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_info
+CREATE FUNCTION pg_stat_statements_info (
+ OUT dealloc bigint
+)
+RETURNS bigint
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
+
+CREATE VIEW pg_stat_statements_info AS
+ SELECT * FROM pg_stat_statements_info();
+
+GRANT SELECT ON pg_stat_statements_info TO PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 1eac9edaee..65cf4fb0a7 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -193,6 +193,15 @@ typedef struct Counters
uint64 wal_bytes; /* total amount of WAL bytes generated */
} Counters;
+/*
+ * Counter for pg_stat_statements_info
+ */
+typedef struct pgssInfoCounters
+{
+ int64 dealloc; /* # of deallocation */
+ slock_t mutex; /* protects the counters only */
+} pgssInfoCounters;
+
/*
* Statistics per statement
*
@@ -279,6 +288,7 @@ static ProcessUtility_hook_type prev_ProcessUtility = NULL;
/* Links to shared memory state */
static pgssSharedState *pgss = NULL;
static HTAB *pgss_hash = NULL;
+static pgssInfoCounters *pgss_info = NULL;
/*---- GUC variables ----*/
@@ -327,6 +337,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_info);
static void pgss_shmem_startup(void);
static void pgss_shmem_shutdown(int code, Datum arg);
@@ -380,6 +391,8 @@ static char *generate_normalized_query(pgssJumbleState *jstate, const char *quer
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 void pgss_info_update(void);
+static void pgss_info_reset(void);
/*
@@ -518,6 +531,7 @@ static void
pgss_shmem_startup(void)
{
bool found;
+ bool found_info;
HASHCTL info;
FILE *file = NULL;
FILE *qfile = NULL;
@@ -534,6 +548,7 @@ pgss_shmem_startup(void)
/* reset in case this is a restart within the postmaster */
pgss = NULL;
pgss_hash = NULL;
+ pgss_info = NULL;
/*
* Create or attach to the shared memory state, including hash table
@@ -556,6 +571,16 @@ pgss_shmem_startup(void)
pgss->gc_count = 0;
}
+ pgss_info = ShmemInitStruct("pg_stat_statements_info",
+ sizeof(pgssInfoCounters),
+ &found_info);
+ if (!found_info)
+ {
+ pgss_info->dealloc = 0;
+ SpinLockInit(&pgss_info->mutex);
+ Assert(pgss_info->dealloc == 0);
+ }
+
memset(&info, 0, sizeof(info));
info.keysize = sizeof(pgssHashKey);
info.entrysize = sizeof(pgssEntry);
@@ -577,7 +602,10 @@ pgss_shmem_startup(void)
* Done if some other process already completed our initialization.
*/
if (found)
+ {
+ Assert(found == found_info);
return;
+ }
/*
* Note: we don't bother with locks here, because there should be no other
@@ -673,6 +701,11 @@ pgss_shmem_startup(void)
entry->counters = temp.counters;
}
+ /* Read pgss_info */
+ if (feof(file) == 0)
+ if (fread(pgss_info, sizeof(pgssInfoCounters), 1, file) != 1)
+ goto read_error;
+
pfree(buffer);
FreeFile(file);
FreeFile(qfile);
@@ -748,7 +781,7 @@ pgss_shmem_shutdown(int code, Datum arg)
return;
/* Safety check ... shouldn't get here unless shmem is set up. */
- if (!pgss || !pgss_hash)
+ if (!pgss || !pgss_hash || !pgss_info)
return;
/* Don't dump if told not to. */
@@ -794,6 +827,10 @@ pgss_shmem_shutdown(int code, Datum arg)
}
}
+ /* Dump pgss_info */
+ if (fwrite(pgss_info, sizeof(pgssInfoCounters), 1, file) != 1)
+ goto error;
+
free(qbuffer);
qbuffer = NULL;
@@ -1453,6 +1490,28 @@ done:
pfree(norm_query);
}
+static void
+pgss_info_update(void)
+{
+ {
+ volatile pgssInfoCounters *c = (volatile pgssInfoCounters *) pgss_info;
+ SpinLockAcquire(&c->mutex);
+ c->dealloc += 1; /* increment dealloc count */
+ SpinLockRelease(&c->mutex);
+ }
+}
+
+static void
+pgss_info_reset(void)
+{
+ {
+ volatile pgssInfoCounters *c = (volatile pgssInfoCounters *) pgss_info;
+ SpinLockAcquire(&c->mutex);
+ c->dealloc = 0; /* reset dealloc count */
+ SpinLockRelease(&c->mutex);
+ }
+}
+
/*
* Reset statement statistics corresponding to userid, dbid, and queryid.
*/
@@ -1490,6 +1549,7 @@ 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 32 /* maximum of above */
+#define PG_STAT_STATEMENTS_INFO_COLS 1
/*
* Retrieve statement statistics.
@@ -1862,6 +1922,19 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
tuplestore_donestoring(tupstore);
}
+Datum
+pg_stat_statements_info(PG_FUNCTION_ARGS)
+{
+ int64 d_count = 0;
+ {
+ volatile pgssInfoCounters *c = (volatile pgssInfoCounters *) pgss_info;
+ SpinLockAcquire(&c->mutex);
+ d_count = Int64GetDatum(c->dealloc);
+ SpinLockRelease(&c->mutex);
+ }
+ PG_RETURN_INT64(d_count);
+}
+
/*
* Estimate shared memory space needed.
*/
@@ -1872,6 +1945,7 @@ pgss_memsize(void)
size = MAXALIGN(sizeof(pgssSharedState));
size = add_size(size, hash_estimate_size(pgss_max, sizeof(pgssEntry)));
+ size = add_size(size, MAXALIGN(sizeof(pgssInfoCounters)));
return size;
}
@@ -1902,7 +1976,12 @@ entry_alloc(pgssHashKey *key, Size query_offset, int query_len, int encoding,
/* Make space if needed */
while (hash_get_num_entries(pgss_hash) >= pgss_max)
+ {
entry_dealloc();
+ pgss_info_update();
+ ereport(LOG,
+ (errmsg("The information in pg_stat_statements has been deallocated.")));
+ }
/* Find or create an entry with desired hash code */
entry = (pgssEntry *) hash_search(pgss_hash, key, HASH_ENTER, &found);
@@ -2503,6 +2582,9 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
hash_search(pgss_hash, &entry->key, HASH_REMOVE, NULL);
num_remove++;
}
+
+ /* Reset pgss_info */
+ pgss_info_reset();
}
/* All entries are removed? */
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/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index 996a24a293..e3cb0930d9 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -357,4 +357,10 @@ SELECT 42;
SELECT 42;
SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+--
+-- Checking the execution of the pg_stat_statements_info view
+--
+SELECT pg_stat_statements_reset(0,0,0);
+SELECT * FROM pg_stat_statements_info;
+
DROP EXTENSION pg_stat_statements;
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index cf2d25b7b2..b3c53dd39f 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -23,10 +23,11 @@
<para>
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
+ these statistics, the module provides views, <structname>pg_stat_statements</structname>
+ and <structname>pg_stat_statements_info</structname>,
+ and the utility functions <function>pg_stat_statements_reset</function>,
+ <function>pg_stat_statements</function>, and <function>pg_stat_statements_info</function>.
+ These are not available globally but can be enabled for a specific database with
<command>CREATE EXTENSION pg_stat_statements</command>.
</para>
@@ -480,6 +481,48 @@
</para>
</sect2>
+ <sect2>
+ <title>The <structname>pg_stat_statements_info</structname> View</title>
+
+ <para>
+ This module tracks statistics of <filename>pg_stat_statements</filename>
+ itself for detailed performance analysis.
+ The statistics of pg_stat_statements itself are made available via a
+ view named <structname>pg_stat_statements_info</structname>.
+ This view contains only a single row.
+ The row is reset only when <function>pg_stat_statements_reset(0,0,0)</function>
+ is called. The columns of the view are
+ shown in <xref linkend="pgstatstatementsctl-columns"/>.
+ </para>
+
+ <table id="pgstatstatementsctl-columns">
+ <title><structname>pg_stat_statements_info</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>dealloc</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Total number of deallocations of pg_stat_statements view entry
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect2>
+
<sect2>
<title>Functions</title>
@@ -537,6 +580,24 @@
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>pg_stat_statements_info() returns record</function>
+ <indexterm>
+ <primary>pg_stat_statements_info</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ The <structname>pg_stat_statements_info</structname> view is defined
+ in terms of a function also named <function>pg_stat_statements_info</function>.
+ It is possible for clients to call the
+ <function>pg_stat_statements_info</function> directly.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</sect2>
