v4-0002-pg_stat_statements-Track-I-O-timing-for-temp-bloc.patch

From be47febf4d96d307f90cc5dbb5d5548ae39119c2 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Tue, 1 Mar 2022 10:42:59 +0900
Subject: [PATCH v4 2/2] pg_stat_statements: Track I/O timing for temp blocks.

This commit adds new columsn temp_blk_read_time and
temp_blk_write_time to pg_stat_statements function, showing the time
spent on disk I/O for temp blocks.

Bumped pg_stat_statements version to 1.10 for newly added columns.

XXX: need to bump PGSS_FILE_HEADER.
---
 contrib/pg_stat_statements/Makefile           |  3 +-
 .../expected/oldextversions.out               | 94 +++++++++++++++++++
 .../pg_stat_statements--1.9--1.10.sql         | 59 ++++++++++++
 .../pg_stat_statements/pg_stat_statements.c   | 35 ++++++-
 .../pg_stat_statements.control                |  2 +-
 .../pg_stat_statements/sql/oldextversions.sql | 10 ++
 doc/src/sgml/pgstatstatements.sgml            | 24 ++++-
 7 files changed, 219 insertions(+), 8 deletions(-)
 create mode 100644 contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql

diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 7fabd96f38..edc40c8bbf 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -6,7 +6,8 @@ OBJS = \
 	pg_stat_statements.o
 
 EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.8--1.9.sql \
+DATA = pg_stat_statements--1.4.sql \
+	pg_stat_statements--1.9--1.10.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/oldextversions.out b/contrib/pg_stat_statements/expected/oldextversions.out
index f18c08838f..fbd60380ee 100644
--- a/contrib/pg_stat_statements/expected/oldextversions.out
+++ b/contrib/pg_stat_statements/expected/oldextversions.out
@@ -136,4 +136,98 @@ SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
  
 (1 row)
 
+-- New function pg_stat_statement_info, and new function
+-- and view for pg_stat_statements introduced in 1.9
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.9';
+SELECT pg_get_functiondef('pg_stat_statements_info'::regproc);
+                                                   pg_get_functiondef                                                    
+-------------------------------------------------------------------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION public.pg_stat_statements_info(OUT dealloc bigint, OUT stats_reset timestamp with time zone)+
+  RETURNS record                                                                                                        +
+  LANGUAGE c                                                                                                            +
+  PARALLEL SAFE STRICT                                                                                                  +
+ AS '$libdir/pg_stat_statements', $function$pg_stat_statements_info$function$                                           +
+ 
+(1 row)
+
+\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           |           |          | 
+ blk_read_time       | double precision |           |          | 
+ blk_write_time      | double precision |           |          | 
+ wal_records         | bigint           |           |          | 
+ wal_fpi             | bigint           |           |          | 
+ wal_bytes           | numeric          |           |          | 
+
+-- New functions and views for pg_stat_statements in 1.10
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.10';
+\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           |           |          | 
+ blk_read_time       | double precision |           |          | 
+ 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          |           |          | 
+
 DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql b/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql
new file mode 100644
index 0000000000..ca777f14e8
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql
@@ -0,0 +1,59 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.10'" 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 blk_read_time float8,
+    OUT 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
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_stat_statements_1_10'
+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 55786ae84f..f01aaca2cc 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -121,7 +121,8 @@ typedef enum pgssVersion
 	PGSS_V1_2,
 	PGSS_V1_3,
 	PGSS_V1_8,
-	PGSS_V1_9
+	PGSS_V1_9,
+	PGSS_V1_10
 } pgssVersion;
 
 typedef enum pgssStoreKind
@@ -183,8 +184,10 @@ typedef struct Counters
 	int64		local_blks_written; /* # of local disk blocks written */
 	int64		temp_blks_read; /* # of temp blocks read */
 	int64		temp_blks_written;	/* # of temp blocks written */
-	double		blk_read_time;	/* time spent reading, in msec */
-	double		blk_write_time; /* time spent writing, in msec */
+	double		blk_read_time;	/* time spent reading blocks, in msec */
+	double		blk_write_time; /* time spent writing blocks, in msec */
+	double		temp_blk_read_time;	/* time spent reading temp blocks, in msec */
+	double		temp_blk_write_time; /* time spent writing temp blocks, in msec */
 	double		usage;			/* usage factor */
 	int64		wal_records;	/* # of WAL records generated */
 	int64		wal_fpi;		/* # of WAL full page images generated */
@@ -302,6 +305,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_1_10);
 PG_FUNCTION_INFO_V1(pg_stat_statements);
 PG_FUNCTION_INFO_V1(pg_stat_statements_info);
 
@@ -1369,6 +1373,8 @@ pgss_store(const char *query, uint64 queryId,
 		e->counters.temp_blks_written += bufusage->temp_blks_written;
 		e->counters.blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage->blk_read_time);
 		e->counters.blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage->blk_write_time);
+		e->counters.temp_blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage->temp_blk_read_time);
+		e->counters.temp_blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage->temp_blk_write_time);
 		e->counters.usage += USAGE_EXEC(total_time);
 		e->counters.wal_records += walusage->wal_records;
 		e->counters.wal_fpi += walusage->wal_fpi;
@@ -1422,7 +1428,8 @@ 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_V1_9	33
-#define PG_STAT_STATEMENTS_COLS			33	/* maximum of above */
+#define PG_STAT_STATEMENTS_COLS_V1_10	35
+#define PG_STAT_STATEMENTS_COLS			35	/* maximum of above */
 
 /*
  * Retrieve statement statistics.
@@ -1434,6 +1441,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_10(PG_FUNCTION_ARGS)
+{
+	bool		showtext = PG_GETARG_BOOL(0);
+
+	pg_stat_statements_internal(fcinfo, PGSS_V1_10, showtext);
+
+	return (Datum) 0;
+}
+
 Datum
 pg_stat_statements_1_9(PG_FUNCTION_ARGS)
 {
@@ -1547,6 +1564,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
 			if (api_version != PGSS_V1_9)
 				elog(ERROR, "incorrect number of output arguments");
 			break;
+		case PG_STAT_STATEMENTS_COLS_V1_10:
+			if (api_version != PGSS_V1_10)
+				elog(ERROR, "incorrect number of output arguments");
+			break;
 		default:
 			elog(ERROR, "incorrect number of output arguments");
 	}
@@ -1747,6 +1768,11 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
 			values[i++] = Float8GetDatumFast(tmp.blk_read_time);
 			values[i++] = Float8GetDatumFast(tmp.blk_write_time);
 		}
+		if (api_version >= PGSS_V1_10)
+		{
+			values[i++] = Float8GetDatumFast(tmp.temp_blk_read_time);
+			values[i++] = Float8GetDatumFast(tmp.temp_blk_write_time);
+		}
 		if (api_version >= PGSS_V1_8)
 		{
 			char		buf[256];
@@ -1771,6 +1797,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
 					 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 :
+					 api_version == PGSS_V1_10 ? PG_STAT_STATEMENTS_COLS_V1_10 :
 					 -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 2f1ce6ed50..0747e48138 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.9'
+default_version = '1.10'
 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 f2e822acd3..ea0f7c143d 100644
--- a/contrib/pg_stat_statements/sql/oldextversions.sql
+++ b/contrib/pg_stat_statements/sql/oldextversions.sql
@@ -36,4 +36,14 @@ AlTER EXTENSION pg_stat_statements UPDATE TO '1.8';
 \d pg_stat_statements
 SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
 
+-- New function pg_stat_statement_info, and new function
+-- and view for pg_stat_statements introduced in 1.9
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.9';
+SELECT pg_get_functiondef('pg_stat_statements_info'::regproc);
+\d pg_stat_statements
+
+-- New functions and views for pg_stat_statements in 1.10
+AlTER EXTENSION pg_stat_statements UPDATE TO '1.10';
+\d pg_stat_statements
+
 DROP EXTENSION pg_stat_statements;
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index 3a7e36bd13..62b15dd19e 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -338,7 +338,7 @@
        <structfield>blk_read_time</structfield> <type>double precision</type>
       </para>
       <para>
-       Total time the statement spent reading blocks, in milliseconds
+       Total time the statement spent reading data file blocks, in milliseconds
        (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
       </para></entry>
      </row>
@@ -348,7 +348,27 @@
        <structfield>blk_write_time</structfield> <type>double precision</type>
       </para>
       <para>
-       Total time the statement spent writing blocks, in milliseconds
+       Total time the statement spent writing data file blocks, in milliseconds
+       (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>temp_blk_read_time</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Total time the statement spent reading temp file blocks, in milliseconds
+       (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>temp_blk_write_time</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Total time the statement spent writing temp file blocks, in milliseconds
        (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
       </para></entry>
      </row>
-- 
2.24.3 (Apple Git-128)