v10-0003-Machinery-for-grabbing-an-extended-vacuum-statistics.patch

From e8560d296f9440558ae92e8c77518137a3dc9e58 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <a.rybakina@postgrespro.ru>
Date: Tue, 22 Oct 2024 21:02:16 +0300
Subject: [PATCH 3/3] Machinery for grabbing an extended vacuum statistics on
 databases. It transmits vacuum statistical information about each table and
 accumulates it for the database which the table belonged.

---
 src/backend/catalog/system_views.sql          | 29 ++++++-
 src/backend/utils/activity/pgstat.c           |  2 +
 src/backend/utils/activity/pgstat_database.c  |  1 +
 src/backend/utils/activity/pgstat_relation.c  | 16 ++++
 src/backend/utils/adt/pgstatfuncs.c           | 77 +++++++++++++++++-
 src/include/catalog/pg_proc.dat               | 11 ++-
 src/include/pgstat.h                          |  3 +-
 src/test/regress/expected/rules.out           | 18 +++++
 ...ut => vacuum_tables_and_db_statistics.out} | 81 ++++++++++++++++++-
 src/test/regress/parallel_schedule            |  2 +-
 ...ql => vacuum_tables_and_db_statistics.sql} | 66 ++++++++++++++-
 11 files changed, 291 insertions(+), 15 deletions(-)
 rename src/test/regress/expected/{vacuum_tables_statistics.out => vacuum_tables_and_db_statistics.out} (77%)
 rename src/test/regress/sql/{vacuum_tables_statistics.sql => vacuum_tables_and_db_statistics.sql} (79%)

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 4da271dae10..b68e0f00abd 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1424,7 +1424,6 @@ SELECT
   COALESCE(stats.user_time, 0) AS user_time,
   COALESCE(stats.total_time, 0) AS total_time,
   COALESCE(stats.interrupts, 0) AS interrupts
-
 FROM pg_class rel
   JOIN pg_namespace ns ON ns.oid = rel.relnamespace
   LEFT JOIN pg_stat_vacuum_tables(rel.oid) stats ON true
@@ -1463,4 +1462,30 @@ FROM
   pg_class rel
   JOIN pg_namespace ns ON ns.oid = rel.relnamespace
   LEFT JOIN pg_stat_vacuum_indexes(rel.oid) stats ON true
-WHERE rel.relkind = 'i';
\ No newline at end of file
+WHERE rel.relkind = 'i';
+
+CREATE VIEW pg_stat_vacuum_database AS
+SELECT
+  db.oid as dboid,
+  db.datname AS dbname,
+
+  COALESCE(stats.db_blks_read, 0) AS db_blks_read,
+  COALESCE(stats.db_blks_hit, 0) AS db_blks_hit,
+  COALESCE(stats.total_blks_dirtied, 0) AS total_blks_dirtied,
+  COALESCE(stats.total_blks_written, 0) AS total_blks_written,
+
+  COALESCE(stats.wal_records, 0) AS wal_records,
+  COALESCE(stats.wal_fpi, 0) AS wal_fpi,
+  COALESCE(stats.wal_bytes, 0) AS wal_bytes,
+
+  COALESCE(stats.blk_read_time, 0) AS blk_read_time,
+  COALESCE(stats.blk_write_time, 0) AS blk_write_time,
+
+  COALESCE(stats.delay_time, 0) AS delay_time,
+  COALESCE(stats.system_time, 0) AS system_time,
+  COALESCE(stats.user_time, 0) AS user_time,
+  COALESCE(stats.total_time, 0) AS total_time,
+  COALESCE(stats.interrupts, 0) AS interrupts
+FROM
+  pg_database db
+  LEFT JOIN pg_stat_vacuum_database(db.oid) stats ON true;
\ No newline at end of file
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 843617eba25..21b29804620 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -1124,6 +1124,8 @@ pgstat_update_snapshot(PgStat_Kind kind)
 		pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_SNAPSHOT;
 		if (kind == PGSTAT_KIND_RELATION)
 			pgstat_build_snapshot(PGSTAT_KIND_RELATION);
+		else if (kind == PGSTAT_KIND_DATABASE)
+			pgstat_build_snapshot(PGSTAT_KIND_DATABASE);
 	}
 	PG_FINALLY();
 	{
diff --git a/src/backend/utils/activity/pgstat_database.c b/src/backend/utils/activity/pgstat_database.c
index 29bc0909748..a060d1a4042 100644
--- a/src/backend/utils/activity/pgstat_database.c
+++ b/src/backend/utils/activity/pgstat_database.c
@@ -430,6 +430,7 @@ pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
 	pgstat_unlock_entry(entry_ref);
 
 	memset(pendingent, 0, sizeof(*pendingent));
+	memset(&(pendingent)->vacuum_ext, 0, sizeof(ExtVacReport));
 
 	return true;
 }
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 5c95363c04a..725e26423f2 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -219,6 +219,7 @@ pgstat_report_vacuum_error(Oid tableoid, ExtVacReportType m_type)
 	PgStatShared_Relation *shtabentry;
 	PgStat_StatTabEntry *tabentry;
 	Oid			dboid =  MyDatabaseId;
+	PgStat_StatDBEntry *dbentry;	/* pending database entry */
 
 	if (!pgstat_track_counts)
 		return;
@@ -232,6 +233,10 @@ pgstat_report_vacuum_error(Oid tableoid, ExtVacReportType m_type)
 	tabentry->vacuum_ext.interrupts++;
 	tabentry->vacuum_ext.type = m_type;
 	pgstat_unlock_entry(entry_ref);
+
+	dbentry = pgstat_prep_database_pending(dboid);
+	dbentry->vacuum_ext.interrupts++;
+	dbentry->vacuum_ext.type = m_type;
 }
 
 /*
@@ -245,6 +250,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
 	PgStat_EntryRef *entry_ref;
 	PgStatShared_Relation *shtabentry;
 	PgStat_StatTabEntry *tabentry;
+	PgStatShared_Database *dbentry;
 	Oid			dboid = (shared ? InvalidOid : MyDatabaseId);
 	TimestampTz ts;
 
@@ -298,6 +304,16 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
 	 * VACUUM command has processed all tables and committed.
 	 */
 	pgstat_flush_io(false);
+	if (dboid != InvalidOid)
+	{
+		entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_DATABASE,
+											dboid, InvalidOid, false);
+		dbentry = (PgStatShared_Database *) entry_ref->shared_stats;
+
+		pgstat_accumulate_extvac_stats(&dbentry->stats.vacuum_ext, params, false);
+		pgstat_unlock_entry(entry_ref);
+	}
+
 }
 
 /*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 8f5a17e7375..cac34fbe64f 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2102,8 +2102,49 @@ pg_stat_have_stats(PG_FUNCTION_ARGS)
 
 #define EXTVACHEAPSTAT_COLUMNS	27
 #define EXTVACIDXSTAT_COLUMNS	19
+#define EXTVACDBSTAT_COLUMNS	15
 #define EXTVACSTAT_COLUMNS Max(EXTVACHEAPSTAT_COLUMNS, EXTVACIDXSTAT_COLUMNS)
 
+static void
+tuplestore_put_for_database(Oid dbid, ReturnSetInfo *rsinfo,
+							PgStat_StatDBEntry *dbentry)
+{
+	Datum		values[EXTVACDBSTAT_COLUMNS];
+	bool		nulls[EXTVACDBSTAT_COLUMNS];
+	char		buf[256];
+	int			i = 0;
+
+	memset(nulls, 0, EXTVACDBSTAT_COLUMNS * sizeof(bool));
+
+	values[i++] = ObjectIdGetDatum(dbid);
+
+	values[i++] = Int64GetDatum(dbentry->vacuum_ext.total_blks_read);
+	values[i++] = Int64GetDatum(dbentry->vacuum_ext.total_blks_hit);
+	values[i++] = Int64GetDatum(dbentry->vacuum_ext.total_blks_dirtied);
+	values[i++] = Int64GetDatum(dbentry->vacuum_ext.total_blks_written);
+
+	values[i++] = Int64GetDatum(dbentry->vacuum_ext.wal_records);
+	values[i++] = Int64GetDatum(dbentry->vacuum_ext.wal_fpi);
+
+	/* Convert to numeric, like pg_stat_statements */
+	snprintf(buf, sizeof buf, UINT64_FORMAT, dbentry->vacuum_ext.wal_bytes);
+	values[i++] = DirectFunctionCall3(numeric_in,
+									  CStringGetDatum(buf),
+									  ObjectIdGetDatum(0),
+									  Int32GetDatum(-1));
+
+	values[i++] = Float8GetDatum(dbentry->vacuum_ext.blk_read_time);
+	values[i++] = Float8GetDatum(dbentry->vacuum_ext.blk_write_time);
+	values[i++] = Float8GetDatum(dbentry->vacuum_ext.delay_time);
+	values[i++] = Float8GetDatum(dbentry->vacuum_ext.system_time);
+	values[i++] = Float8GetDatum(dbentry->vacuum_ext.user_time);
+	values[i++] = Float8GetDatum(dbentry->vacuum_ext.total_time);
+	values[i++] = Int32GetDatum(dbentry->vacuum_ext.interrupts);
+
+	Assert(i == rsinfo->setDesc->natts);
+	tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+}
+
 static void
 tuplestore_put_for_relation(Oid relid, ReturnSetInfo *rsinfo,
 							PgStat_StatTabEntry *tabentry)
@@ -2195,8 +2236,9 @@ pg_stats_vacuum(FunctionCallInfo fcinfo, ExtVacReportType type, int ncolumns)
 		if (OidIsValid(relid))
 		{
 			tabentry = pgstat_fetch_stat_tabentry(relid);
-			if (tabentry == NULL || tabentry->vacuum_ext.type != type)
-				/* Table don't exists or isn't an heap relation. */
+
+			if ((tabentry == NULL || tabentry->vacuum_ext.type != type))
+				/* Table don't exists or isn't a heap or index relation. */
 				return;
 
 			tuplestore_put_for_relation(relid, rsinfo, tabentry);
@@ -2204,7 +2246,7 @@ pg_stats_vacuum(FunctionCallInfo fcinfo, ExtVacReportType type, int ncolumns)
 		else
 		{
 			SnapshotIterator		hashiter;
-			PgStat_SnapshotEntry   *entry;
+			PgStat_SnapshotEntry    *entry;
 
 			pgstat_update_snapshot(PGSTAT_KIND_RELATION);
 
@@ -2222,6 +2264,22 @@ pg_stats_vacuum(FunctionCallInfo fcinfo, ExtVacReportType type, int ncolumns)
 			}
 		}
 	}
+	else if (type == PGSTAT_EXTVAC_DB)
+	{
+		PgStat_StatDBEntry	    *dbentry;
+		Oid						dbid = PG_GETARG_OID(0);
+
+		if (OidIsValid(dbid))
+		{
+			dbentry = pgstat_fetch_stat_dbentry(dbid);
+
+			if (dbentry == NULL)
+				/* Database doesn't exist */
+				return;
+
+			tuplestore_put_for_database(dbid, rsinfo, dbentry);
+		}
+	}
 }
 
 /*
@@ -2244,4 +2302,15 @@ pg_stat_vacuum_indexes(PG_FUNCTION_ARGS)
 	pg_stats_vacuum(fcinfo, PGSTAT_EXTVAC_INDEX, EXTVACIDXSTAT_COLUMNS);
 
 	PG_RETURN_VOID();
-}
\ No newline at end of file
+}
+
+/*
+ * Get the vacuum statistics for the database.
+ */
+Datum
+pg_stat_vacuum_database(PG_FUNCTION_ARGS)
+{
+	pg_stats_vacuum(fcinfo, PGSTAT_EXTVAC_DB, EXTVACDBSTAT_COLUMNS);
+
+	PG_RETURN_VOID();
+ }
\ No newline at end of file
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 856d04b986f..f5f97a80cf5 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12380,5 +12380,14 @@
   proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,numeric,float8,float8,float8,float8,float8,float8,int4}',
   proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
   proargnames => '{reloid,relid,total_blks_read,total_blks_hit,total_blks_dirtied,total_blks_written,rel_blks_read,rel_blks_hit,pages_deleted,tuples_deleted,wal_records,wal_fpi,wal_bytes,blk_read_time,blk_write_time,delay_time,system_time,user_time,total_time,interrupts}',
-  prosrc => 'pg_stat_vacuum_indexes' }
+  prosrc => 'pg_stat_vacuum_indexes' },
+{ oid => '8003',
+  descr => 'pg_stat_vacuum_database return stats values',
+  proname => 'pg_stat_vacuum_database', prorows => 1000, provolatile => 's', prorettype => 'record',proisstrict => 'f',
+  proretset => 't',
+  proargtypes => 'oid',
+  proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,numeric,float8,float8,float8,float8,float8,float8,int4}',
+  proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
+  proargnames => '{dbid,dboid,db_blks_read,db_blks_hit,total_blks_dirtied,total_blks_written,wal_records,wal_fpi,wal_bytes,blk_read_time,blk_write_time,delay_time,system_time,user_time,total_time,interrupts}',
+  prosrc => 'pg_stat_vacuum_database' },
 ]
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index b784bcc3efe..c6d663c1c48 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -174,7 +174,8 @@ typedef enum ExtVacReportType
 {
 	PGSTAT_EXTVAC_INVALID = 0,
 	PGSTAT_EXTVAC_HEAP = 1,
-	PGSTAT_EXTVAC_INDEX = 2
+	PGSTAT_EXTVAC_INDEX = 2,
+	PGSTAT_EXTVAC_DB = 3,
 } ExtVacReportType;
 
 /* ----------
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index b2611386211..f8112d54f52 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2237,6 +2237,24 @@ pg_stat_user_tables| SELECT relid,
     autoanalyze_count
    FROM pg_stat_all_tables
   WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
+pg_stat_vacuum_database| SELECT db.oid AS dboid,
+    db.datname AS dbname,
+    COALESCE(stats.db_blks_read, (0)::bigint) AS db_blks_read,
+    COALESCE(stats.db_blks_hit, (0)::bigint) AS db_blks_hit,
+    COALESCE(stats.total_blks_dirtied, (0)::bigint) AS total_blks_dirtied,
+    COALESCE(stats.total_blks_written, (0)::bigint) AS total_blks_written,
+    COALESCE(stats.wal_records, (0)::bigint) AS wal_records,
+    COALESCE(stats.wal_fpi, (0)::bigint) AS wal_fpi,
+    COALESCE(stats.wal_bytes, (0)::numeric) AS wal_bytes,
+    COALESCE(stats.blk_read_time, (0)::double precision) AS blk_read_time,
+    COALESCE(stats.blk_write_time, (0)::double precision) AS blk_write_time,
+    COALESCE(stats.delay_time, (0)::double precision) AS delay_time,
+    COALESCE(stats.system_time, (0)::double precision) AS system_time,
+    COALESCE(stats.user_time, (0)::double precision) AS user_time,
+    COALESCE(stats.total_time, (0)::double precision) AS total_time,
+    COALESCE(stats.interrupts, 0) AS interrupts
+   FROM (pg_database db
+     LEFT JOIN LATERAL pg_stat_vacuum_database(db.oid) stats(dboid, db_blks_read, db_blks_hit, total_blks_dirtied, total_blks_written, wal_records, wal_fpi, wal_bytes, blk_read_time, blk_write_time, delay_time, system_time, user_time, total_time, interrupts) ON (true));
 pg_stat_vacuum_indexes| SELECT rel.oid AS relid,
     ns.nspname AS schema,
     rel.relname,
diff --git a/src/test/regress/expected/vacuum_tables_statistics.out b/src/test/regress/expected/vacuum_tables_and_db_statistics.out
similarity index 77%
rename from src/test/regress/expected/vacuum_tables_statistics.out
rename to src/test/regress/expected/vacuum_tables_and_db_statistics.out
index 8bf706fdf86..7a0b3ba96e1 100644
--- a/src/test/regress/expected/vacuum_tables_statistics.out
+++ b/src/test/regress/expected/vacuum_tables_and_db_statistics.out
@@ -6,6 +6,9 @@
 -- number of frozen and visible pages removed by backend.
 -- Statistic wal_fpi is not displayed in this test because its behavior is unstable.
 --
+CREATE DATABASE regression_statistic_vacuum_db;
+CREATE DATABASE regression_statistic_vacuum_db1;
+\c regression_statistic_vacuum_db;
 -- conditio sine qua non
 SHOW track_counts;  -- must be on
  track_counts 
@@ -175,10 +178,7 @@ FROM pg_stat_vacuum_tables WHERE relname = 'vestat';
 
 SELECT pages_frozen AS pf, pages_all_visible AS pv, rev_all_frozen_pages AS hafp,rev_all_visible_pages AS havp
 FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
-UPDATE vestat SET x = x1001;
-ERROR:  column "x1001" does not exist
-LINE 1: UPDATE vestat SET x = x1001;
-                              ^
+UPDATE vestat SET x = x+1001;
 VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
 SELECT pages_frozen > :pf AS pages_frozen,pages_all_visible > :pv AS pages_all_visible,rev_all_frozen_pages > :hafp AS rev_all_frozen_pages,rev_all_visible_pages > :havp AS rev_all_visible_pages
 FROM pg_stat_vacuum_tables WHERE relname = 'vestat';
@@ -204,4 +204,77 @@ SELECT min(relid) FROM pg_stat_vacuum_tables(0) where relid > 0;
  1213
 (1 row)
 
+-- Now check vacuum statistics for current database
+SELECT dbname,
+       db_blks_hit > 0 AS db_blks_hit,
+       total_blks_dirtied > 0 AS total_blks_dirtied,
+       total_blks_written > 0 AS total_blks_written,
+       wal_records > 0 AS wal_records,
+       wal_fpi > 0 AS wal_fpi,
+       wal_bytes > 0 AS wal_bytes,
+       user_time > 0 AS user_time,
+       total_time > 0 AS total_time
+FROM
+pg_stat_vacuum_database
+WHERE dbname = current_database();
+             dbname             | db_blks_hit | total_blks_dirtied | total_blks_written | wal_records | wal_fpi | wal_bytes | user_time | total_time 
+--------------------------------+-------------+--------------------+--------------------+-------------+---------+-----------+-----------+------------
+ regression_statistic_vacuum_db | t           | t                  | t                  | t           | t       | t         | t         | t
+(1 row)
+
+DROP TABLE vestat CASCADE;
+-- ensure pending stats are flushed
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush 
+--------------------------
+ 
+(1 row)
+
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+UPDATE vestat SET x = 10001;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+\c regression_statistic_vacuum_db1;
+-- Now check vacuum statistics for postgres database from another database
+SELECT dbname,
+       db_blks_hit > 0 AS db_blks_hit,
+       total_blks_dirtied > 0 AS total_blks_dirtied,
+       total_blks_written > 0 AS total_blks_written,
+       wal_records > 0 AS wal_records,
+       wal_fpi > 0 AS wal_fpi,
+       wal_bytes > 0 AS wal_bytes,
+       user_time > 0 AS user_time,
+       total_time > 0 AS total_time
+FROM
+pg_stat_vacuum_database
+WHERE dbname = 'regression_statistic_vacuum_db';
+             dbname             | db_blks_hit | total_blks_dirtied | total_blks_written | wal_records | wal_fpi | wal_bytes | user_time | total_time 
+--------------------------------+-------------+--------------------+--------------------+-------------+---------+-----------+-----------+------------
+ regression_statistic_vacuum_db | t           | t                  | t                  | t           | t       | t         | t         | t
+(1 row)
+
+\c regression_statistic_vacuum_db
 DROP TABLE vestat CASCADE;
+\c regression_statistic_vacuum_db1;
+SELECT count(*)
+FROM pg_database d
+CROSS JOIN pg_stat_vacuum_tables(0)
+WHERE oid = 0; -- must be 0
+ count 
+-------
+     0
+(1 row)
+
+SELECT count(*)
+FROM pg_database d
+CROSS JOIN pg_stat_vacuum_database(0)
+WHERE oid = 0; -- must be 0
+ count 
+-------
+     0
+(1 row)
+
+\c postgres
+DROP DATABASE regression_statistic_vacuum_db1;
+DROP DATABASE regression_statistic_vacuum_db;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 9847a330ed1..1ba32b87cf5 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -141,4 +141,4 @@ test: tablespace
 # Check vacuum statistics
 # ----------
 test: vacuum_index_statistics
-test: vacuum_tables_statistics
\ No newline at end of file
+test: vacuum_tables_and_db_statistics
\ No newline at end of file
diff --git a/src/test/regress/sql/vacuum_tables_statistics.sql b/src/test/regress/sql/vacuum_tables_and_db_statistics.sql
similarity index 79%
rename from src/test/regress/sql/vacuum_tables_statistics.sql
rename to src/test/regress/sql/vacuum_tables_and_db_statistics.sql
index ed4352566ee..a3ddc9419de 100644
--- a/src/test/regress/sql/vacuum_tables_statistics.sql
+++ b/src/test/regress/sql/vacuum_tables_and_db_statistics.sql
@@ -7,6 +7,10 @@
 -- Statistic wal_fpi is not displayed in this test because its behavior is unstable.
 --
 
+CREATE DATABASE regression_statistic_vacuum_db;
+CREATE DATABASE regression_statistic_vacuum_db1;
+\c regression_statistic_vacuum_db;
+
 -- conditio sine qua non
 SHOW track_counts;  -- must be on
 -- not enabled by default, but we want to test it...
@@ -140,7 +144,7 @@ FROM pg_stat_vacuum_tables WHERE relname = 'vestat';
 SELECT pages_frozen AS pf, pages_all_visible AS pv, rev_all_frozen_pages AS hafp,rev_all_visible_pages AS havp
 FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
 
-UPDATE vestat SET x = x1001;
+UPDATE vestat SET x = x+1001;
 VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
 
 SELECT pages_frozen > :pf AS pages_frozen,pages_all_visible > :pv AS pages_all_visible,rev_all_frozen_pages > :hafp AS rev_all_frozen_pages,rev_all_visible_pages > :havp AS rev_all_visible_pages
@@ -156,4 +160,62 @@ FROM pg_stat_vacuum_tables WHERE relname = 'vestat';
 
 SELECT min(relid) FROM pg_stat_vacuum_tables(0) where relid > 0;
 
-DROP TABLE vestat CASCADE;
\ No newline at end of file
+-- Now check vacuum statistics for current database
+SELECT dbname,
+       db_blks_hit > 0 AS db_blks_hit,
+       total_blks_dirtied > 0 AS total_blks_dirtied,
+       total_blks_written > 0 AS total_blks_written,
+       wal_records > 0 AS wal_records,
+       wal_fpi > 0 AS wal_fpi,
+       wal_bytes > 0 AS wal_bytes,
+       user_time > 0 AS user_time,
+       total_time > 0 AS total_time
+FROM
+pg_stat_vacuum_database
+WHERE dbname = current_database();
+
+DROP TABLE vestat CASCADE;
+
+-- ensure pending stats are flushed
+SELECT pg_stat_force_next_flush();
+
+CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
+ANALYZE vestat;
+UPDATE vestat SET x = 10001;
+VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
+
+\c regression_statistic_vacuum_db1;
+
+-- Now check vacuum statistics for postgres database from another database
+SELECT dbname,
+       db_blks_hit > 0 AS db_blks_hit,
+       total_blks_dirtied > 0 AS total_blks_dirtied,
+       total_blks_written > 0 AS total_blks_written,
+       wal_records > 0 AS wal_records,
+       wal_fpi > 0 AS wal_fpi,
+       wal_bytes > 0 AS wal_bytes,
+       user_time > 0 AS user_time,
+       total_time > 0 AS total_time
+FROM
+pg_stat_vacuum_database
+WHERE dbname = 'regression_statistic_vacuum_db';
+
+\c regression_statistic_vacuum_db
+
+DROP TABLE vestat CASCADE;
+
+\c regression_statistic_vacuum_db1;
+SELECT count(*)
+FROM pg_database d
+CROSS JOIN pg_stat_vacuum_tables(0)
+WHERE oid = 0; -- must be 0
+
+SELECT count(*)
+FROM pg_database d
+CROSS JOIN pg_stat_vacuum_database(0)
+WHERE oid = 0; -- must be 0
+
+\c postgres
+DROP DATABASE regression_statistic_vacuum_db1;
+DROP DATABASE regression_statistic_vacuum_db;
-- 
2.34.1