From 1576634d66956d232a7b980353409d81521d24c3 Mon Sep 17 00:00:00 2001 From: Shinya Kato Date: Sun, 19 Oct 2025 15:34:25 +0900 Subject: [PATCH v1 3/3] pg_stat_statements: Add wal_fpi_bytes_[un]compressed --- contrib/pg_stat_statements/Makefile | 1 + .../expected/oldextversions.out | 69 ++++++++++++++++ contrib/pg_stat_statements/meson.build | 1 + .../pg_stat_statements--1.13--1.14.sql | 80 +++++++++++++++++++ .../pg_stat_statements/pg_stat_statements.c | 52 +++++++++++- .../pg_stat_statements.control | 2 +- .../pg_stat_statements/sql/oldextversions.sql | 5 ++ doc/src/sgml/pgstatstatements.sgml | 20 +++++ 8 files changed, 227 insertions(+), 3 deletions(-) create mode 100644 contrib/pg_stat_statements/pg_stat_statements--1.13--1.14.sql diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile index fe0478ac552..ee41ee0311e 100644 --- a/contrib/pg_stat_statements/Makefile +++ b/contrib/pg_stat_statements/Makefile @@ -7,6 +7,7 @@ OBJS = \ EXTENSION = pg_stat_statements DATA = pg_stat_statements--1.4.sql \ + pg_stat_statements--1.13--1.14.sql \ pg_stat_statements--1.12--1.13.sql \ pg_stat_statements--1.11--1.12.sql pg_stat_statements--1.10--1.11.sql \ pg_stat_statements--1.9--1.10.sql pg_stat_statements--1.8--1.9.sql \ diff --git a/contrib/pg_stat_statements/expected/oldextversions.out b/contrib/pg_stat_statements/expected/oldextversions.out index 726383a99d7..257c474d42d 100644 --- a/contrib/pg_stat_statements/expected/oldextversions.out +++ b/contrib/pg_stat_statements/expected/oldextversions.out @@ -474,4 +474,73 @@ SELECT count(*) > 0 AS has_data FROM pg_stat_statements; t (1 row) +-- New functions and views for pg_stat_statements in 1.14 +AlTER EXTENSION pg_stat_statements UPDATE TO '1.14'; +\d pg_stat_statements + View "public.pg_stat_statements" + Column | Type | Collation | Nullable | Default +----------------------------+--------------------------+-----------+----------+--------- + userid | oid | | | + dbid | oid | | | + toplevel | boolean | | | + queryid | bigint | | | + query | text | | | + plans | bigint | | | + total_plan_time | double precision | | | + min_plan_time | double precision | | | + max_plan_time | double precision | | | + mean_plan_time | double precision | | | + stddev_plan_time | double precision | | | + calls | bigint | | | + total_exec_time | double precision | | | + min_exec_time | double precision | | | + max_exec_time | double precision | | | + mean_exec_time | double precision | | | + stddev_exec_time | double precision | | | + rows | bigint | | | + shared_blks_hit | bigint | | | + shared_blks_read | bigint | | | + shared_blks_dirtied | bigint | | | + shared_blks_written | bigint | | | + local_blks_hit | bigint | | | + local_blks_read | bigint | | | + local_blks_dirtied | bigint | | | + local_blks_written | bigint | | | + temp_blks_read | bigint | | | + temp_blks_written | bigint | | | + shared_blk_read_time | double precision | | | + shared_blk_write_time | double precision | | | + local_blk_read_time | double precision | | | + local_blk_write_time | double precision | | | + temp_blk_read_time | double precision | | | + temp_blk_write_time | double precision | | | + wal_records | bigint | | | + wal_fpi | bigint | | | + wal_bytes | numeric | | | + wal_buffers_full | bigint | | | + wal_fpi_bytes_uncompressed | numeric | | | + wal_fpi_bytes_compressed | numeric | | | + jit_functions | bigint | | | + jit_generation_time | double precision | | | + jit_inlining_count | bigint | | | + jit_inlining_time | double precision | | | + jit_optimization_count | bigint | | | + jit_optimization_time | double precision | | | + jit_emission_count | bigint | | | + jit_emission_time | double precision | | | + jit_deform_count | bigint | | | + jit_deform_time | double precision | | | + parallel_workers_to_launch | bigint | | | + parallel_workers_launched | bigint | | | + generic_plan_calls | bigint | | | + custom_plan_calls | bigint | | | + stats_since | timestamp with time zone | | | + minmax_stats_since | timestamp with time zone | | | + +SELECT count(*) > 0 AS has_data FROM pg_stat_statements; + has_data +---------- + t +(1 row) + DROP EXTENSION pg_stat_statements; diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build index 7b8bfbb1de7..378d363eef8 100644 --- a/contrib/pg_stat_statements/meson.build +++ b/contrib/pg_stat_statements/meson.build @@ -21,6 +21,7 @@ contrib_targets += pg_stat_statements install_data( 'pg_stat_statements.control', 'pg_stat_statements--1.4.sql', + 'pg_stat_statements--1.13--1.14.sql', 'pg_stat_statements--1.12--1.13.sql', 'pg_stat_statements--1.11--1.12.sql', 'pg_stat_statements--1.10--1.11.sql', diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.13--1.14.sql b/contrib/pg_stat_statements/pg_stat_statements--1.13--1.14.sql new file mode 100644 index 00000000000..c76a66fe4e2 --- /dev/null +++ b/contrib/pg_stat_statements/pg_stat_statements--1.13--1.14.sql @@ -0,0 +1,80 @@ +/* contrib/pg_stat_statements/pg_stat_statements--1.13--1.14.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.14'" to load this file. \quit + +/* 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 toplevel bool, + 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 shared_blk_read_time float8, + OUT shared_blk_write_time float8, + OUT local_blk_read_time float8, + OUT local_blk_write_time float8, + OUT temp_blk_read_time float8, + OUT temp_blk_write_time float8, + OUT wal_records int8, + OUT wal_fpi int8, + OUT wal_bytes numeric, + OUT wal_buffers_full int8, + OUT wal_fpi_bytes_uncompressed numeric, + OUT wal_fpi_bytes_compressed numeric, + OUT jit_functions int8, + OUT jit_generation_time float8, + OUT jit_inlining_count int8, + OUT jit_inlining_time float8, + OUT jit_optimization_count int8, + OUT jit_optimization_time float8, + OUT jit_emission_count int8, + OUT jit_emission_time float8, + OUT jit_deform_count int8, + OUT jit_deform_time float8, + OUT parallel_workers_to_launch int8, + OUT parallel_workers_launched int8, + OUT generic_plan_calls int8, + OUT custom_plan_calls int8, + OUT stats_since timestamp with time zone, + OUT minmax_stats_since timestamp with time zone +) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'pg_stat_statements_1_14' +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; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index f2187167c5c..0f658f2122e 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -86,7 +86,7 @@ PG_MODULE_MAGIC_EXT( #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 = 0x20250731; +static const uint32 PGSS_FILE_HEADER = 0x20251020; /* PostgreSQL major version number, changes in which invalidate all entries */ static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100; @@ -116,6 +116,7 @@ typedef enum pgssVersion PGSS_V1_11, PGSS_V1_12, PGSS_V1_13, + PGSS_V1_14, } pgssVersion; typedef enum pgssStoreKind @@ -191,6 +192,12 @@ typedef struct Counters int64 wal_records; /* # of WAL records generated */ int64 wal_fpi; /* # of WAL full page images generated */ uint64 wal_bytes; /* total amount of WAL generated in bytes */ + uint64 wal_fpi_bytes_uncompressed; /* total amount of WAL full page + * images generated in bytes before + * compression */ + uint64 wal_fpi_bytes_compressed; /* total amount of WAL full page + * images generated in bytes after + * compression */ int64 wal_buffers_full; /* # of times the WAL buffers became full */ int64 jit_functions; /* total number of JIT functions emitted */ double jit_generation_time; /* total time to generate jit code */ @@ -327,6 +334,7 @@ PG_FUNCTION_INFO_V1(pg_stat_statements_1_10); PG_FUNCTION_INFO_V1(pg_stat_statements_1_11); PG_FUNCTION_INFO_V1(pg_stat_statements_1_12); PG_FUNCTION_INFO_V1(pg_stat_statements_1_13); +PG_FUNCTION_INFO_V1(pg_stat_statements_1_14); PG_FUNCTION_INFO_V1(pg_stat_statements); PG_FUNCTION_INFO_V1(pg_stat_statements_info); @@ -1481,6 +1489,8 @@ pgss_store(const char *query, int64 queryId, entry->counters.wal_fpi += walusage->wal_fpi; entry->counters.wal_bytes += walusage->wal_bytes; entry->counters.wal_buffers_full += walusage->wal_buffers_full; + entry->counters.wal_fpi_bytes_uncompressed += walusage->wal_fpi_bytes_uncompressed; + entry->counters.wal_fpi_bytes_compressed += walusage->wal_fpi_bytes_compressed; if (jitusage) { entry->counters.jit_functions += jitusage->created_functions; @@ -1581,7 +1591,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS) #define PG_STAT_STATEMENTS_COLS_V1_11 49 #define PG_STAT_STATEMENTS_COLS_V1_12 52 #define PG_STAT_STATEMENTS_COLS_V1_13 54 -#define PG_STAT_STATEMENTS_COLS 54 /* maximum of above */ +#define PG_STAT_STATEMENTS_COLS_V1_14 56 +#define PG_STAT_STATEMENTS_COLS 56 /* maximum of above */ /* * Retrieve statement statistics. @@ -1593,6 +1604,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_14(PG_FUNCTION_ARGS) +{ + bool showtext = PG_GETARG_BOOL(0); + + pg_stat_statements_internal(fcinfo, PGSS_V1_14, showtext); + + return (Datum) 0; +} + Datum pg_stat_statements_1_13(PG_FUNCTION_ARGS) { @@ -1765,6 +1786,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, if (api_version != PGSS_V1_13) elog(ERROR, "incorrect number of output arguments"); break; + case PG_STAT_STATEMENTS_COLS_V1_14: + if (api_version != PGSS_V1_14) + elog(ERROR, "incorrect number of output arguments"); + break; default: elog(ERROR, "incorrect number of output arguments"); } @@ -1996,6 +2021,28 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, { values[i++] = Int64GetDatumFast(tmp.wal_buffers_full); } + if (api_version >= PGSS_V1_14) + { + char buf[256]; + Datum wal_fpi_bytes_uncompressed; + Datum wal_fpi_bytes_compressed; + + snprintf(buf, sizeof buf, UINT64_FORMAT, + tmp.wal_fpi_bytes_uncompressed); + wal_fpi_bytes_uncompressed = DirectFunctionCall3(numeric_in, + CStringGetDatum(buf), + ObjectIdGetDatum(0), + Int32GetDatum(-1)); + values[i++] = wal_fpi_bytes_uncompressed; + + snprintf(buf, sizeof buf, UINT64_FORMAT, + tmp.wal_fpi_bytes_compressed); + wal_fpi_bytes_compressed = DirectFunctionCall3(numeric_in, + CStringGetDatum(buf), + ObjectIdGetDatum(0), + Int32GetDatum(-1)); + values[i++] = wal_fpi_bytes_compressed; + } if (api_version >= PGSS_V1_10) { values[i++] = Int64GetDatumFast(tmp.jit_functions); @@ -2038,6 +2085,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, api_version == PGSS_V1_11 ? PG_STAT_STATEMENTS_COLS_V1_11 : api_version == PGSS_V1_12 ? PG_STAT_STATEMENTS_COLS_V1_12 : api_version == PGSS_V1_13 ? PG_STAT_STATEMENTS_COLS_V1_13 : + api_version == PGSS_V1_14 ? PG_STAT_STATEMENTS_COLS_V1_14 : -1 /* fail if you forget to update this assert */ )); tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls); diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control index 2eee0ceffa8..61ae41efc14 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.13' +default_version = '1.14' module_pathname = '$libdir/pg_stat_statements' relocatable = true diff --git a/contrib/pg_stat_statements/sql/oldextversions.sql b/contrib/pg_stat_statements/sql/oldextversions.sql index e416efe9ffb..a43fc740906 100644 --- a/contrib/pg_stat_statements/sql/oldextversions.sql +++ b/contrib/pg_stat_statements/sql/oldextversions.sql @@ -68,4 +68,9 @@ AlTER EXTENSION pg_stat_statements UPDATE TO '1.13'; \d pg_stat_statements SELECT count(*) > 0 AS has_data FROM pg_stat_statements; +-- New functions and views for pg_stat_statements in 1.14 +AlTER EXTENSION pg_stat_statements UPDATE TO '1.14'; +\d pg_stat_statements +SELECT count(*) > 0 AS has_data FROM pg_stat_statements; + DROP EXTENSION pg_stat_statements; diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index d753de5836e..a968b428d7a 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -445,6 +445,26 @@ + + + wal_fpi_bytes_uncompressed numeric + + + Total amount of WAL full page images by the statement in bytes before + compression + + + + + + wal_fpi_bytes_compressed numeric + + + Total amount of WAL full page images by the statement in bytes after + compression + + + jit_functions bigint -- 2.47.3