Dear Kuroda, > I don't like the idea because such a column has no meaning for the > specific row. > I prefer storing timestamp if GetCurrentTimestamp() is cheap. I agree. New version attached. -- Andrei Zubkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
From ac68636c8a26223854292ee5860f4a5989cb985a Mon Sep 17 00:00:00 2001 From: Andrei Zubkov <zub...@moonset.ru> Date: Tue, 23 Mar 2021 17:03:34 +0300 Subject: [PATCH] pg_stat_statements: Track statement entry timestamp Added first_seen column in pg_stat_statements view. This field is populated with current timestamp when a new statement is added to pg_stat_statements hashtable. This field provides clean information about statistics collection time interval for each statement. Besides it can be used by sampling solutions to detect situations when a statement was evicted and returned back between two samples. Discussion: https://www.postgresql.org/message-id/flat/72e80e7b160a6eb189df9ef6f068cce3765d37f8.camel%40moonset.ru --- .../expected/pg_stat_statements.out | 22 ++++++++ .../pg_stat_statements--1.8--1.9.sql | 54 +++++++++++++++++++ .../pg_stat_statements/pg_stat_statements.c | 31 +++++++++-- .../sql/pg_stat_statements.sql | 8 +++ doc/src/sgml/pgstatstatements.sgml | 9 ++++ 5 files changed, 121 insertions(+), 3 deletions(-) diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out index 16158525ca..ffe08dece5 100644 --- a/contrib/pg_stat_statements/expected/pg_stat_statements.out +++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out @@ -876,4 +876,26 @@ SELECT dealloc FROM pg_stat_statements_info; 0 (1 row) +-- +-- statement timestamps +-- +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +SELECT now() AS start_ts \gset +SELECT 1 AS "STMTTS"; + STMTTS +-------- + 1 +(1 row) + +SELECT count(*) FROM pg_stat_statements WHERE first_seen >= :'start_ts' AND query LIKE '%STMTTS%'; + count +------- + 1 +(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 index 3504ca7eb1..9842d2da49 100644 --- 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 @@ -3,6 +3,60 @@ -- 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 +/* We need to redefine a view and a function */ +/* First we have to remove them from the extension */ +ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements; +ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean); + +/* Then we can drop them */ +DROP VIEW pg_stat_statements; +DROP FUNCTION pg_stat_statements(boolean); + +/* Now redefine */ +CREATE FUNCTION pg_stat_statements(IN showtext boolean, + OUT userid oid, + OUT dbid oid, + OUT queryid bigint, + OUT query text, + OUT plans int8, + OUT total_plan_time float8, + OUT min_plan_time float8, + OUT max_plan_time float8, + OUT mean_plan_time float8, + OUT stddev_plan_time float8, + OUT calls int8, + OUT total_exec_time float8, + OUT min_exec_time float8, + OUT max_exec_time float8, + OUT mean_exec_time float8, + OUT stddev_exec_time float8, + OUT rows int8, + OUT shared_blks_hit int8, + OUT shared_blks_read int8, + OUT shared_blks_dirtied int8, + OUT shared_blks_written int8, + OUT local_blks_hit int8, + OUT local_blks_read int8, + OUT local_blks_dirtied int8, + OUT local_blks_written int8, + OUT temp_blks_read int8, + OUT temp_blks_written int8, + OUT blk_read_time float8, + OUT blk_write_time float8, + OUT wal_records int8, + OUT wal_fpi int8, + OUT wal_bytes numeric, + OUT first_seen timestamp with time zone +) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'pg_stat_statements_1_9' +LANGUAGE C STRICT VOLATILE PARALLEL SAFE; + +CREATE VIEW pg_stat_statements AS + SELECT * FROM pg_stat_statements(true); + +GRANT SELECT ON pg_stat_statements TO PUBLIC; + --- Define pg_stat_statements_info CREATE FUNCTION pg_stat_statements_info( OUT dealloc bigint, diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 62cccbfa44..090889e21b 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -99,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 = 0x20201218; +static const uint32 PGSS_FILE_HEADER = 0x20210322; /* PostgreSQL major version number, changes in which invalidate all entries */ static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100; @@ -125,7 +125,8 @@ typedef enum pgssVersion PGSS_V1_1, PGSS_V1_2, PGSS_V1_3, - PGSS_V1_8 + PGSS_V1_8, + PGSS_V1_9 } pgssVersion; typedef enum pgssStoreKind @@ -217,6 +218,7 @@ typedef struct pgssEntry Size query_offset; /* query text offset in external file */ int query_len; /* # of valid bytes in query string, or -1 */ int encoding; /* query text encoding */ + TimestampTz first_seen; /* timestamp of entry allocation moment */ slock_t mutex; /* protects the counters only */ } pgssEntry; @@ -337,6 +339,7 @@ PG_FUNCTION_INFO_V1(pg_stat_statements_reset_1_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_1_9); PG_FUNCTION_INFO_V1(pg_stat_statements); PG_FUNCTION_INFO_V1(pg_stat_statements_info); @@ -684,6 +687,7 @@ pgss_shmem_startup(void) /* copy in the actual stats */ entry->counters = temp.counters; + entry->first_seen = temp.first_seen; } /* Read global statistics for pg_stat_statements */ @@ -1511,7 +1515,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS) #define PG_STAT_STATEMENTS_COLS_V1_2 19 #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_COLS_V1_9 33 +#define PG_STAT_STATEMENTS_COLS 33 /* maximum of above */ /* * Retrieve statement statistics. @@ -1523,6 +1528,16 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS) * expected API version is identified by embedding it in the C name of the * function. Unfortunately we weren't bright enough to do that for 1.1. */ +Datum +pg_stat_statements_1_9(PG_FUNCTION_ARGS) +{ + bool showtext = PG_GETARG_BOOL(0); + + pg_stat_statements_internal(fcinfo, PGSS_V1_9, showtext); + + return (Datum) 0; +} + Datum pg_stat_statements_1_8(PG_FUNCTION_ARGS) { @@ -1642,6 +1657,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, if (api_version != PGSS_V1_8) elog(ERROR, "incorrect number of output arguments"); break; + case PG_STAT_STATEMENTS_COLS_V1_9: + if (api_version != PGSS_V1_9) + elog(ERROR, "incorrect number of output arguments"); + break; default: elog(ERROR, "incorrect number of output arguments"); } @@ -1727,6 +1746,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, Counters tmp; double stddev; int64 queryid = entry->key.queryid; + TimestampTz first_seen; memset(values, 0, sizeof(values)); memset(nulls, 0, sizeof(nulls)); @@ -1793,6 +1813,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, SpinLockAcquire(&e->mutex); tmp = e->counters; + first_seen = e->first_seen; SpinLockRelease(&e->mutex); } @@ -1864,12 +1885,15 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, Int32GetDatum(-1)); values[i++] = wal_bytes; } + if (api_version >= PGSS_V1_9) + values[i++] = TimestampTzGetDatum(first_seen); Assert(i == (api_version == PGSS_V1_0 ? PG_STAT_STATEMENTS_COLS_V1_0 : api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 : api_version == PGSS_V1_2 ? PG_STAT_STATEMENTS_COLS_V1_2 : api_version == PGSS_V1_3 ? PG_STAT_STATEMENTS_COLS_V1_3 : api_version == PGSS_V1_8 ? PG_STAT_STATEMENTS_COLS_V1_8 : + api_version == PGSS_V1_9 ? PG_STAT_STATEMENTS_COLS_V1_9 : -1 /* fail if you forget to update this assert */ )); tuplestore_putvalues(tupstore, tupdesc, values, nulls); @@ -1985,6 +2009,7 @@ entry_alloc(pgssHashKey *key, Size query_offset, int query_len, int encoding, entry->query_offset = query_offset; entry->query_len = query_len; entry->encoding = encoding; + entry->first_seen = GetCurrentTimestamp(); } return entry; diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql index 6f58d9d0f6..590798d133 100644 --- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql +++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql @@ -364,4 +364,12 @@ SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE SELECT pg_stat_statements_reset(); SELECT dealloc FROM pg_stat_statements_info; +-- +-- statement timestamps +-- +SELECT pg_stat_statements_reset(); +SELECT now() AS start_ts \gset +SELECT 1 AS "STMTTS"; +SELECT count(*) FROM pg_stat_statements WHERE first_seen >= :'start_ts' AND query LIKE '%STMTTS%'; + DROP EXTENSION pg_stat_statements; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index 464bf0e5ae..54df2ea73a 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -363,6 +363,15 @@ Total amount of WAL generated by the statement in bytes </para></entry> </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>first_seen</structfield> <type>timestamp with time zone</type> + </para> + <para> + Timestamp of statistics gathering start for the statement + </para></entry> + </row> </tbody> </tgroup> </table> -- 2.26.3
