[Katsuragi Yuta <btkatsuragiyu@oss.nttdata.com>]

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 Yuta

Attachments:

  [text/x-diff] pg_stat_statements_ctl_v1.patch (12.1K, ../0d9f1107772cf5c3f954e985464c7298@oss.nttdata.com/2-pg_stat_statements_ctl_v1.patch)
  download | inline diff:
diff --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>