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

From f746fcc6af12d557189bc4d53e4aa8e3a4f87e31 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <a.rybakina@postgrespro.ru>
Date: Wed, 13 Nov 2024 16:58: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          | 26 ++++-
 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           | 95 +++++++++++++++++++
 src/include/catalog/pg_proc.dat               | 15 ++-
 src/include/pgstat.h                          |  3 +-
 src/test/regress/expected/rules.out           | 16 ++++
 ...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, 309 insertions(+), 14 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 bebf793514a..f337768ea55 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1458,4 +1458,28 @@ FROM
   pg_class rel
   JOIN pg_namespace ns ON ns.oid = rel.relnamespace,
   LATERAL pg_stat_get_vacuum_indexes(rel.oid) stats
-WHERE rel.relkind = 'i';
\ No newline at end of file
+WHERE rel.relkind = 'i';
+
+CREATE VIEW pg_stat_get_vacuum_database AS
+SELECT
+  db.oid as dboid,
+  db.datname AS dbname,
+
+  stats.db_blks_read AS db_blks_read,
+  stats.db_blks_hit AS db_blks_hit,
+  stats.total_blks_dirtied AS total_blks_dirtied,
+  stats.total_blks_written AS total_blks_written,
+
+  stats.wal_records AS wal_records,
+  stats.wal_fpi AS wal_fpi,
+  stats.wal_bytes AS wal_bytes,
+
+  stats.blk_read_time AS blk_read_time,
+  stats.blk_write_time AS blk_write_time,
+
+  stats.delay_time AS delay_time,
+  stats.total_time AS total_time,
+  stats.errors AS errors
+FROM
+  pg_database db,
+  LATERAL pg_stat_get_vacuum_database(db.oid) stats;
\ No newline at end of file
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 551dcfa3198..5b81fbba12a 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -1126,6 +1126,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 7757d2ace74..840d848a752 100644
--- a/src/backend/utils/activity/pgstat_database.c
+++ b/src/backend/utils/activity/pgstat_database.c
@@ -449,6 +449,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 c9238e3f650..853ee510bb5 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -218,6 +218,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;
@@ -231,6 +232,10 @@ pgstat_report_vacuum_error(Oid tableoid, ExtVacReportType m_type)
 	tabentry->vacuum_ext.errors++;
 	tabentry->vacuum_ext.type = m_type;
 	pgstat_unlock_entry(entry_ref);
+
+	dbentry = pgstat_prep_database_pending(dboid);
+	dbentry->vacuum_ext.errors++;
+	dbentry->vacuum_ext.type = m_type;
 }
 
 /*
@@ -244,6 +249,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;
 
@@ -297,6 +303,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 f797cf2d7f3..32e38d43062 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2311,6 +2311,101 @@ pg_stat_get_vacuum_indexes(PG_FUNCTION_ARGS)
 
 	Assert(i == PG_STAT_GET_VACUUM_INDEX_STATS_COLS);
 
+	/* Returns the record as Datum */
+	PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
+}
+
+Datum
+pg_stat_get_vacuum_database(PG_FUNCTION_ARGS)
+{
+	#define PG_STAT_GET_VACUUM_DATABASE_STATS_COLS	13
+
+	Oid						 dbid = PG_GETARG_OID(0);
+	PgStat_StatDBEntry 		*dbentry;
+	ExtVacReport 			*extvacuum;
+	TupleDesc				 tupdesc;
+	Datum					 values[PG_STAT_GET_VACUUM_DATABASE_STATS_COLS] = {0};
+	bool					 nulls[PG_STAT_GET_VACUUM_DATABASE_STATS_COLS] = {0};
+	char					 buf[256];
+	int						 i = 0;
+	ExtVacReport allzero;
+
+	/* Initialise attributes information in the tuple descriptor */
+	tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_VACUUM_DATABASE_STATS_COLS);
+
+	TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "dbid",
+					   INT4OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_ blks_read",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_hit",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_dirtied",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_blks_written",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_records",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_fpi",
+					   INT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "wal_bytes",
+					   NUMERICOID, -1, 0);
+
+	TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_read_time",
+					   FLOAT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "blk_write_time",
+					   FLOAT8OID, -1, 0);
+
+	TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "delay_time",
+					   FLOAT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "total_time",
+					   FLOAT8OID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) ++i, "errors",
+					   INT4OID, -1, 0);
+
+	Assert(i == PG_STAT_GET_VACUUM_DATABASE_STATS_COLS);
+
+	BlessTupleDesc(tupdesc);
+
+	dbentry = pgstat_fetch_stat_dbentry(dbid);
+
+	if (dbentry == NULL)
+	{
+		/* If the subscription is not found, initialise its stats */
+		memset(&allzero, 0, sizeof(ExtVacReport));
+		extvacuum = &allzero;
+	}
+	else
+	{
+		extvacuum = &(dbentry->vacuum_ext);
+	}
+
+	i = 0;
+
+	values[i++] = ObjectIdGetDatum(dbid);
+
+	values[i++] = Int64GetDatum(extvacuum->total_blks_read);
+	values[i++] = Int64GetDatum(extvacuum->total_blks_hit);
+	values[i++] = Int64GetDatum(extvacuum->total_blks_dirtied);
+	values[i++] = Int64GetDatum(extvacuum->total_blks_written);
+
+	values[i++] = Int64GetDatum(extvacuum->wal_records);
+	values[i++] = Int64GetDatum(extvacuum->wal_fpi);
+
+	/* Convert to numeric, like pg_stat_statements */
+	snprintf(buf, sizeof buf, UINT64_FORMAT, extvacuum->wal_bytes);
+	values[i++] = DirectFunctionCall3(numeric_in,
+									  CStringGetDatum(buf),
+									  ObjectIdGetDatum(0),
+									  Int32GetDatum(-1));
+
+	values[i++] = Float8GetDatum(extvacuum->blk_read_time);
+	values[i++] = Float8GetDatum(extvacuum->blk_write_time);
+	values[i++] = Float8GetDatum(extvacuum->delay_time);
+	values[i++] = Float8GetDatum(extvacuum->total_time);
+	values[i++] = Float8GetDatum(extvacuum->errors);
+
+	Assert(i == PG_STAT_GET_VACUUM_DATABASE_STATS_COLS);
+
 	/* Returns the record as Datum */
 	PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
 }
\ No newline at end of file
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index be8a10533bb..e12d77f1e3b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12403,7 +12403,7 @@
   prosrc => 'gist_stratnum_identity' },
 
 { oid => '8001',
-  descr => 'pg_stat_get_vacuum_tables return stats values',
+  descr => 'pg_stat_get_vacuum_tables returns vacuum stats values for table',
   proname => 'pg_stat_get_vacuum_tables', prorows => 1000, provolatile => 's', prorettype => 'record',proisstrict => 'f',
   proretset => 't',
   proargtypes => 'oid',
@@ -12421,12 +12421,21 @@
   proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
   prosrc => 'pg_stat_get_rev_all_frozen_pages' },
 { oid => '8004',
-  descr => 'pg_stat_get_vacuum_indexes return stats values',
+  descr => 'pg_stat_get_vacuum_indexes returns vacuum stats values for index',
   proname => 'pg_stat_get_vacuum_indexes', prorows => 1000, provolatile => 's', prorettype => 'record',proisstrict => 'f',
   proretset => 't',
   proargtypes => 'oid',
   proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,numeric,float8,float8,float8,float8}',
   proargmodes => '{i,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,total_time}',
-  prosrc => 'pg_stat_get_vacuum_indexes' }
+  prosrc => 'pg_stat_get_vacuum_indexes' },
+{ oid => '8005',
+  descr => 'pg_stat_get_vacuum_database returns vacuum stats values for database',
+  proname => 'pg_stat_get_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,int4}',
+  proargmodes => '{i,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,total_time,errors}',
+  prosrc => 'pg_stat_get_vacuum_database' },
 ]
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index a47debdc351..6271d96b415 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 bede88721c0..05be0949871 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1883,6 +1883,22 @@ pg_stat_database_conflicts| SELECT oid AS datid,
     pg_stat_get_db_conflict_startup_deadlock(oid) AS confl_deadlock,
     pg_stat_get_db_conflict_logicalslot(oid) AS confl_active_logicalslot
    FROM pg_database d;
+pg_stat_get_vacuum_database| SELECT db.oid AS dboid,
+    db.datname AS dbname,
+    stats.db_blks_read,
+    stats.db_blks_hit,
+    stats.total_blks_dirtied,
+    stats.total_blks_written,
+    stats.wal_records,
+    stats.wal_fpi,
+    stats.wal_bytes,
+    stats.blk_read_time,
+    stats.blk_write_time,
+    stats.delay_time,
+    stats.total_time,
+    stats.errors
+   FROM pg_database db,
+    LATERAL pg_stat_get_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, total_time, errors);
 pg_stat_get_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 eee468a50d5..8468f7edaeb 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 
@@ -183,7 +186,7 @@ FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relna
 
 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, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid \gset
-UPDATE vestat SET x = x + 1001;
+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, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
@@ -193,10 +196,7 @@ FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relna
 (1 row)
 
 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 pg_stat_vacuum_tables.relname = 'vestat' \gset
-ERROR:  column "rev_all_frozen_pages" does not exist
-LINE 1: ...LECT pages_frozen AS pf, pages_all_visible AS pv, rev_all_fr...
-                                                             ^
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid \gset
 VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
 -- vacuum freezed pages
 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
@@ -206,4 +206,75 @@ FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relna
  t            | t                 | t                    | t
 (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,
+       total_time > 0 AS total_time
+FROM
+pg_stat_get_vacuum_database
+WHERE dbname = current_database();
+             dbname             | db_blks_hit | total_blks_dirtied | total_blks_written | wal_records | wal_fpi | wal_bytes | total_time 
+--------------------------------+-------------+--------------------+--------------------+-------------+---------+-----------+------------
+ regression_statistic_vacuum_db | 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,
+       total_time > 0 AS total_time
+FROM
+pg_stat_get_vacuum_database
+WHERE dbname = 'regression_statistic_vacuum_db';
+             dbname             | db_blks_hit | total_blks_dirtied | total_blks_written | wal_records | wal_fpi | wal_bytes | total_time 
+--------------------------------+-------------+--------------------+--------------------+-------------+---------+-----------+------------
+ regression_statistic_vacuum_db | 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_get_vacuum_tables(0)
+WHERE oid = 0; -- must be 0
+ count 
+-------
+     0
+(1 row)
+
+SELECT count(*)
+FROM pg_database d
+CROSS JOIN pg_stat_get_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 ee74bb3a958..47df1056ad8 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...
@@ -143,16 +147,72 @@ FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relna
 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, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid \gset
 
-UPDATE vestat SET x = x + 1001;
+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, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
 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 pg_stat_vacuum_tables.relname = 'vestat' \gset
+FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid \gset
 
 VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
 -- vacuum freezed pages
 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, pg_stat_all_tables WHERE pg_stat_vacuum_tables.relname = 'vestat' and pg_stat_vacuum_tables.relid = pg_stat_all_tables.relid;
 
-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,
+       total_time > 0 AS total_time
+FROM
+pg_stat_get_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,
+       total_time > 0 AS total_time
+FROM
+pg_stat_get_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_get_vacuum_tables(0)
+WHERE oid = 0; -- must be 0
+
+SELECT count(*)
+FROM pg_database d
+CROSS JOIN pg_stat_get_vacuum_database(0)
+WHERE oid = 0; -- must be 0
+
+\c postgres
+DROP DATABASE regression_statistic_vacuum_db1;
+DROP DATABASE regression_statistic_vacuum_db;
\ No newline at end of file
-- 
2.34.1