Thanks for updating the patch!
+ pgss_info->dealloc = 0;
+ SpinLockInit(&pgss_info->mutex);
+ Assert(pgss_info->dealloc == 0);
Why is this assertion check necessary? It seems not necessary.
+ {
+ Assert(found == found_info);
Having pgssSharedState and pgssInfoCounters separately might make
the code a bit more complicated like the above? If this is true, what
about
including pgssInfoCounters in pgssSharedState?
PGSS_FILE_HEADER needs to be changed since the patch changes
the format of pgss file?
+ /* Read pgss_info */
+ if (feof(file) == 0)
+ if (fread(pgss_info, sizeof(pgssInfoCounters), 1, file) != 1)
+ goto read_error;
Why does feof(file) need to be called here?
+pgss_info_update(void)
+{
+ {
Why is the second "{" necessary? It seems redundant.
+pgss_info_reset(void)
+{
+ {
Same as above.
+pg_stat_statements_info(PG_FUNCTION_ARGS)
+{
+ int64 d_count = 0;
+ {
Same as above.
+ SpinLockAcquire(&c->mutex);
+ d_count = Int64GetDatum(c->dealloc);
+ SpinLockRelease(&c->mutex);
Why does Int64GetDatum() need to be called here? It seems not
necessary.
+ <varlistentry>
+ <term>
+ <function>pg_stat_statements_info() returns bigint</function>
+ <indexterm>
+ <primary>pg_stat_statements_info</primary>
+ </indexterm>
+ </term>
Isn't it better not to expose pg_stat_statements_info() function in the
document because pg_stat_statements_info view is enough and there
seems no use case for the function?
Regards,
Thanks for the comment.
I'll post a fixed patch.
Due to similar fixed, we have also merged the patches discussed in the
following thread.
https://commitfest.postgresql.org/30/2738/
Why is this assertion check necessary? It seems not necessary.
As indicated, it is unnecessary and will be removed.
Having pgssSharedState and pgssInfoCounters separately might make
the code a bit more complicated like the above? If this is true, what
about
including pgssInfoCounters in pgssSharedState?
Fix pgssSharedState to include pgssInfoCounters . The related parts were
also corrected accordingly.
PGSS_FILE_HEADER needs to be changed since the patch changes
the format of pgss file?
The value of PGSS_FILE_HEADER has been updated.
Why does feof(file) need to be called here?
As indicated, it is unnecessary and will be removed.
Why is the second "{" necessary? It seems redundant.
As indicated, it is unnecessary and will be removed.
But I left the {} in pg_stat_statements_info() to make the shared memory
edit part explicit.
Why does Int64GetDatum() need to be called here? It seems not
necessary.
As indicated, it is unnecessary and will be removed.
Isn't it better not to expose pg_stat_statements_info() function in the
document because pg_stat_statements_info view is enough and there
seems no use case for the function?
As indicated, it is unnecessary and will be removed.
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..a1eaedca8e 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 dealloc 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..4edbc03dc1
--- /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_info
+CREATE FUNCTION pg_stat_statements_info (
+ OUT dealloc bigint,
+ OUT reset_exec_time TIMESTAMP WITH TIME ZONE
+)
+RETURNS record
+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..0857c1e2a4 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;
@@ -98,7 +99,7 @@ PG_MODULE_MAGIC;
#define PGSS_TEXT_FILE PG_STAT_TMP_DIR "/pgss_query_texts.stat"
/* Magic number identifying the stats file format */
-static const uint32 PGSS_FILE_HEADER = 0x20171004;
+static const uint32 PGSS_FILE_HEADER = 0x20201116;
/* PostgreSQL major version number, changes in which invalidate all entries */
static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100;
@@ -193,6 +194,17 @@ 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 */
+ TimestampTz reset_exec_time; /* timestamp with all stats reset */
+ bool reset_exec_time_isnull; /* if true last_dealloc is null */
+ slock_t mutex; /* protects the counters only */
+} pgssInfoCounters;
+
/*
* Statistics per statement
*
@@ -222,6 +234,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 */
+ pgssInfoCounters pgss_info; /* Counter for pg_stat_statements_info */
} pgssSharedState;
/*
@@ -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_info);
static void pgss_shmem_startup(void);
static void pgss_shmem_shutdown(int code, Datum arg);
@@ -554,6 +568,9 @@ pgss_shmem_startup(void)
pgss->extent = 0;
pgss->n_writers = 0;
pgss->gc_count = 0;
+ pgss->pgss_info.dealloc = 0;
+ pgss->pgss_info.reset_exec_time = 0;
+ pgss->pgss_info.reset_exec_time_isnull = true;
}
memset(&info, 0, sizeof(info));
@@ -673,6 +690,10 @@ pgss_shmem_startup(void)
entry->counters = temp.counters;
}
+ /* Read pgss_info */
+ if (fread(&pgss->pgss_info, sizeof(pgssInfoCounters), 1, file) != 1)
+ goto read_error;
+
pfree(buffer);
FreeFile(file);
FreeFile(qfile);
@@ -794,6 +815,10 @@ pgss_shmem_shutdown(int code, Datum arg)
}
}
+ /* Dump pgss_info */
+ if (fwrite(&pgss->pgss_info, sizeof(pgssInfoCounters), 1, file) != 1)
+ goto error;
+
free(qbuffer);
qbuffer = NULL;
@@ -1490,6 +1515,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 2
/*
* Retrieve statement statistics.
@@ -1862,6 +1888,42 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
tuplestore_donestoring(tupstore);
}
+Datum
+pg_stat_statements_info(PG_FUNCTION_ARGS)
+{
+ TupleDesc tupdesc;
+ HeapTuple result_tuple;
+ Datum result;
+ Datum values[PG_STAT_STATEMENTS_INFO_COLS];
+ bool nulls[PG_STAT_STATEMENTS_INFO_COLS];
+ bool ts_isnull;
+ int i = 0;
+
+ Assert(PG_STAT_STATEMENTS_INFO_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 pgssSharedState *s = (volatile pgssSharedState *) pgss;
+ SpinLockAcquire(&s->mutex);
+ values[i++] = pgss->pgss_info.dealloc;
+ ts_isnull = pgss->pgss_info.reset_exec_time_isnull;
+ if (!ts_isnull)
+ values[i++] = pgss->pgss_info.reset_exec_time;
+ else
+ nulls[i++] = true;
+ SpinLockRelease(&s->mutex);
+ }
+ result_tuple = heap_form_tuple(tupdesc, values, nulls);
+ result = HeapTupleGetDatum(result_tuple);
+
+ return result;
+}
+
/*
* Estimate shared memory space needed.
*/
@@ -1902,7 +1964,18 @@ 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 pgss_info */
+ {
+ volatile pgssSharedState *s = (volatile pgssSharedState *) pgss;
+ SpinLockAcquire(&s->mutex);
+ s->pgss_info.dealloc += 1; /* increment dealloc count */
+ SpinLockRelease(&s->mutex);
+ }
+ 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);
@@ -2458,6 +2531,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,
@@ -2503,6 +2579,16 @@ entry_reset(Oid userid, Oid dbid, uint64 queryid)
hash_search(pgss_hash, &entry->key, HASH_REMOVE, NULL);
num_remove++;
}
+
+ /* Reset and Update pgss_info */
+ {
+ volatile pgssSharedState *s = (volatile pgssSharedState *) pgss;
+ SpinLockAcquire(&s->mutex);
+ s->pgss_info.dealloc = 0; /* reset dealloc count */
+ s->pgss_info.reset_exec_time = reset_ts; /* reset execution time */
+ s->pgss_info.reset_exec_time_isnull = false;
+ SpinLockRelease(&s->mutex);
+ }
}
/* 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..4016d19553 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 dealloc 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..21f150aabb 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -23,7 +23,8 @@
<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>,
+ 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> and
<function>pg_stat_statements</function>. These are not available globally but
can be enabled for a specific database with
@@ -480,6 +481,55 @@
</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 columns of the view are shown in <xref linkend="pgstatstatementsinfo-columns"/>.
+ </para>
+
+ <table id="pgstatstatementsinfo-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>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>reset_exec_time</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Shows the time at which <function>pg_stat_statements_reset(0,0,0)</function> was last called.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect2>
+
<sect2>
<title>Functions</title>
