This is a proposal to add some features to pg_stat_statements.
Attached is the patch of this.
pg_stat_statements uses a hash table to hold statistics,
and the maximum number of its entries can be configured through
pg_stat_statements.max.
When the number of entries exceeds the pg_stat_statements.max,
pg_stat_statements deallocate existing entries.
Currently, it is impossible to know how many times/when this
deallocation happened.
But, with this information, more detailed performance analysis would be
possible.
So, this patch provides access to this information.
This patch provides a view (pg_stat_statements_ctl) and
a function (pg_stat_statements_ctl).
The pg_stat_statements_ctl view is defined
in terms of a function also named pg_stat_statements_ctl.
The entry of pg_stat_statements_ctl view is removed
when pg_stat_statements_reset(0,0,0) is called.
Maybe, it is convenient to provide a function named
pg_stat_statements_ctl_reset
that removes only the entry of pg_stat_statements_ctl.
The following is an example of this feature.
Here, a deallocation is shown with the INFO message.
pg_stat_statements_ctl tracks the number of deallocations
and timestamp of last deallocation.
testdb=# select * from pg_stat_statements_ctl;
dealloc | last_dealloc
---------+-------------------------------
2 | 2020-09-18 16:55:31.128256+09
(1 row)
testdb=# select count(*) from test1;
2020-09-18 16:59:20.745 JST [3920] INFO: deallocate
2020-09-18 16:59:20.745 JST [3920] STATEMENT: select count(*) from
test1;
INFO: deallocate
count
-------
90
(1 row)
testdb=# select * from pg_stat_statements_ctl;
dealloc | last_dealloc
---------+-------------------------------
3 | 2020-09-18 16:59:20.745652+09
(1 row)
Regards,
Katsuragi Yutadiff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 081f997d70..63fd4874b1 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..43c7966946
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.8--1.9.sql
@@ -0,0 +1,18 @@
+/* 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_ctl
+CREATE FUNCTION pg_stat_statements_ctl (
+ OUT dealloc integer,
+ OUT last_dealloc TIMESTAMP WITH TIME ZONE
+)
+RETURNS record
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
+
+CREATE VIEW pg_stat_statements_ctl AS
+ SELECT * FROM pg_stat_statements_ctl();
+
+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 1eac9edaee..aa116db498 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;
@@ -193,6 +194,17 @@ typedef struct Counters
uint64 wal_bytes; /* total amount of WAL bytes generated */
} Counters;
+/*
+ * Counter for pg_stat_statements_ctl
+ */
+typedef struct pgssCtlCounter
+{
+ int64 dealloc; /* # of deallocation */
+ TimestampTz last_dealloc; /* timestamp of last deallocation */
+ bool ts_isnull; /* if true last_dealloc is null */
+ slock_t mutex;
+} pgssCtlCounter;
+
/*
* Statistics per statement
*
@@ -279,6 +291,7 @@ static ProcessUtility_hook_type prev_ProcessUtility = NULL;
/* Links to shared memory state */
static pgssSharedState *pgss = NULL;
static HTAB *pgss_hash = NULL;
+static pgssCtlCounter *pgss_ctl = NULL;
/*---- GUC variables ----*/
@@ -327,6 +340,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_ctl);
static void pgss_shmem_startup(void);
static void pgss_shmem_shutdown(int code, Datum arg);
@@ -380,6 +394,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 update_ctl(void);
+static void reset_ctl(void);
/*
@@ -518,6 +534,7 @@ static void
pgss_shmem_startup(void)
{
bool found;
+ bool found_ctl;
HASHCTL info;
FILE *file = NULL;
FILE *qfile = NULL;
@@ -534,6 +551,7 @@ pgss_shmem_startup(void)
/* reset in case this is a restart within the postmaster */
pgss = NULL;
pgss_hash = NULL;
+ pgss_ctl = NULL;
/*
* Create or attach to the shared memory state, including hash table
@@ -556,6 +574,17 @@ pgss_shmem_startup(void)
pgss->gc_count = 0;
}
+ pgss_ctl = ShmemInitStruct("pg_stat_statements ctl",
+ sizeof(pgssCtlCounter),
+ &found_ctl);
+ if (!found_ctl)
+ {
+ pgss_ctl->dealloc = 0;
+ pgss_ctl->ts_isnull = true;
+ SpinLockInit(&pgss_ctl->mutex);
+ Assert(pgss_ctl->dealloc == 0);
+ }
+
memset(&info, 0, sizeof(info));
info.keysize = sizeof(pgssHashKey);
info.entrysize = sizeof(pgssEntry);
@@ -577,7 +606,10 @@ pgss_shmem_startup(void)
* Done if some other process already completed our initialization.
*/
if (found)
+ {
+ Assert(found == found_ctl);
return;
+ }
/*
* Note: we don't bother with locks here, because there should be no other
@@ -672,6 +704,12 @@ pgss_shmem_startup(void)
/* copy in the actual stats */
entry->counters = temp.counters;
}
+ // Assert(feof(file) != 0); // EOF
+
+ /* Read pgss_ctl */
+ if (feof(file) == 0)
+ if (fread(pgss_ctl, sizeof(pgssCtlCounter), 1, file) != 1)
+ goto read_error;
pfree(buffer);
FreeFile(file);
@@ -748,7 +786,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_ctl)
return;
/* Don't dump if told not to. */
@@ -794,6 +832,10 @@ pgss_shmem_shutdown(int code, Datum arg)
}
}
+ /* Dump pgss_ctl */
+ if (fwrite(pgss_ctl, sizeof(pgssCtlCounter), 1, file) != 1)
+ goto error;
+
free(qbuffer);
qbuffer = NULL;
@@ -818,6 +860,7 @@ error:
(errcode_for_file_access(),
errmsg("could not write file \"%s\": %m",
PGSS_DUMP_FILE ".tmp")));
+
if (qbuffer)
free(qbuffer);
if (file)
@@ -1453,6 +1496,33 @@ done:
pfree(norm_query);
}
+static void
+update_ctl(void)
+{
+ TimestampTz current_ts = GetCurrentTimestamp();
+
+ {
+ volatile pgssCtlCounter *c = (volatile pgssCtlCounter *) pgss_ctl;
+ SpinLockAcquire(&c->mutex);
+ pgss_ctl->dealloc += 1; /* increment dealloc count */
+ pgss_ctl->last_dealloc = current_ts;
+ pgss_ctl->ts_isnull = false;
+ SpinLockRelease(&c->mutex);
+ }
+}
+
+static void
+reset_ctl(void)
+{
+ {
+ volatile pgssCtlCounter *c = (volatile pgssCtlCounter *) pgss_ctl;
+ SpinLockAcquire(&c->mutex);
+ pgss_ctl->dealloc = 0; /* reset dealloc count */
+ pgss_ctl->ts_isnull = true;
+ SpinLockRelease(&c->mutex);
+ }
+}
+
/*
* Reset statement statistics corresponding to userid, dbid, and queryid.
*/
@@ -1490,6 +1560,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_CTL_COLS 2
/*
* Retrieve statement statistics.
@@ -1862,6 +1933,44 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
tuplestore_donestoring(tupstore);
}
+Datum
+pg_stat_statements_ctl(PG_FUNCTION_ARGS)
+{
+ TupleDesc tupdesc;
+ HeapTuple result_tuple;
+ Datum result;
+ Datum values[PG_STAT_STATEMENTS_CTL_COLS];
+ bool nulls[PG_STAT_STATEMENTS_CTL_COLS];
+ bool ts_isnull;
+ int i = 0;
+
+ Assert(PG_STAT_STATEMENTS_CTL_COLS == 2);
+
+ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+ elog(ERROR, "return type must be a row type");
+
+ tupdesc = BlessTupleDesc(tupdesc);
+
+ memset(nulls, 0, sizeof(nulls));
+
+ {
+ volatile pgssCtlCounter *c = (volatile pgssCtlCounter *) pgss_ctl;
+ SpinLockAcquire(&c->mutex);
+ values[i++] = Int64GetDatum(pgss_ctl->dealloc);
+ ts_isnull = pgss_ctl->ts_isnull;
+ if (!ts_isnull)
+ values[i++] = TimestampTzGetDatum(pgss_ctl->last_dealloc);
+ else
+ nulls[i++] = true;
+ SpinLockRelease(&c->mutex);
+ }
+
+ result_tuple = heap_form_tuple(tupdesc, values, nulls);
+ result = HeapTupleGetDatum(result_tuple);
+
+ return result;
+}
+
/*
* Estimate shared memory space needed.
*/
@@ -1872,6 +1981,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(pgssCtlCounter)));
return size;
}
@@ -1902,7 +2012,10 @@ 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();
+ update_ctl();
+ }
/* Find or create an entry with desired hash code */
entry = (pgssEntry *) hash_search(pgss_hash, key, HASH_ENTER, &found);
@@ -2503,6 +2616,9 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
hash_search(pgss_hash, &entry->key, HASH_REMOVE, NULL);
num_remove++;
}
+
+ /* Reset pgss_ctl */
+ reset_ctl();
}
/* 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/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index cf2d25b7b2..605795fe80 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_ctl</structname>,
+ and the utility functions <function>pg_stat_statements_reset</function>,
+ <function>pg_stat_statements</function>, and <function>pg_stat_statements_ctl</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,57 @@
</para>
</sect2>
+ <sect2>
+ <title>The <structname>pg_stat_statements_ctl</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_ctl</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_ctl</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>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_dealloc</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ The timestamp of last deallocation
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect2>
+
<sect2>
<title>Functions</title>
@@ -537,6 +589,24 @@
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>pg_stat_statements_ctl() returns record</function>
+ <indexterm>
+ <primary>pg_stat_statements_ctl</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ The <structname>pg_stat_statements_ctl</structname> view is defined
+ in terms of a function also named <function>pg_stat_statements_ctl</function>.
+ It is possible for clients to call the
+ <function>pg_stat_statements_ctl</function> directly.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</sect2>
