On 04.02.2025 18:22, Alena Rybakina wrote:
Also, should pgstat_track_vacuum_statistics also affect
per database statistics?
According to my original idea, I thought that we could collect
extended statistics on relationships depending on whether the hook is
enabled, and always on databases. This will help us to constantly
collect statistics on the vacuum and notice when something is wrong
and at the same time not very expensive: there are much fewer
databases compared to the same relationships and there are much fewer
statistics there. You can introduce an additional hook that disables
all collection of vacuum statistics. This patch can be seen here for
the 17th version of the patch [0], I have not yet started adding it,
since we did not come to an agreement.
I added this version as
"v19-0003.2-extended-vacuum-statistics.patch.no-cbot"
--
Regards,
Alena Rybakina
Postgres Professional
From af474098037c8292d9c82c711c0fe78aa1580395 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <a.rybak...@postgrespro.ru>
Date: Tue, 4 Feb 2025 18:39:35 +0300
Subject: [PATCH] Machinery for grabbing an extended vacuum statistics on
databases.
Database vacuum statistics information is the collected general
vacuum statistics indexes and tables owned by the databases, which
they belong to.
By default, vacuum database statistics are always collected, and
the track_vacuum_statistics guc enables the ability to collect
extended statistics for a given database's relations. This is done
to achieve a balance between the allocated memory for storing statistics
and having the necessary monitoring at hand to track the state of
vacuum operation.
In addition to the fact that there are far fewer databases in a system
than relations, vacuum statistics for a database contain fewer statistics
than relations, but they are enough to indicate that something may be
wrong in the system and prompt the administrator to enable extended
monitoring for relations.
So, buffer, wal, statistics of I/O time of read and writen blocks
statistics will be observed because they are collected for both
tables, indexes. In addition, we show the number of errors caught
during operation of the vacuum only for the error level.
wraparound_failsafe_count is a number of times when the vacuum starts
urgent cleanup to prevent wraparound problem which is critical for
the database.
Authors: Alena Rybakina <lena.riback...@yandex.ru>,
Andrei Lepikhov <a.lepik...@postgrespro.ru>,
Andrei Zubkov <a.zub...@postgrespro.ru>
Reviewed-by: Dilip Kumar <dilipbal...@gmail.com>, Masahiko Sawada
<sawada.m...@gmail.com>,
Ilia Evdokimov <ilya.evdoki...@tantorlabs.com>, jian he
<jian.universal...@gmail.com>,
Kirill Reshke <reshkekir...@gmail.com>, Alexander Korotkov
<aekorot...@gmail.com>,
Jim Nasby <jna...@upgrade.com>, Sami Imseih <samims...@gmail.com>
---
src/backend/access/heap/vacuumlazy.c | 75 +++++--------
src/backend/catalog/system_views.sql | 27 ++++-
src/backend/utils/activity/pgstat.c | 2 +-
src/backend/utils/activity/pgstat_database.c | 1 +
src/backend/utils/activity/pgstat_relation.c | 52 ++++++++-
src/backend/utils/adt/pgstatfuncs.c | 100 +++++++++++++++++-
src/backend/utils/misc/guc_tables.c | 2 +-
src/include/catalog/pg_proc.dat | 13 ++-
src/include/pgstat.h | 5 +-
.../vacuum-extending-in-repetable-read.spec | 6 ++
src/test/regress/expected/rules.out | 17 +++
.../expected/vacuum_index_statistics.out | 16 +--
...ut => vacuum_tables_and_db_statistics.out} | 89 ++++++++++++++--
src/test/regress/parallel_schedule | 2 +-
src/test/regress/regression.diffs | 12 +++
.../regress/sql/vacuum_index_statistics.sql | 6 +-
...ql => vacuum_tables_and_db_statistics.sql} | 69 +++++++++++-
17 files changed, 408 insertions(+), 86 deletions(-)
rename src/test/regress/expected/{vacuum_tables_statistics.out =>
vacuum_tables_and_db_statistics.out} (82%)
create mode 100644 src/test/regress/regression.diffs
rename src/test/regress/sql/{vacuum_tables_statistics.sql =>
vacuum_tables_and_db_statistics.sql} (81%)
diff --git a/src/backend/access/heap/vacuumlazy.c
b/src/backend/access/heap/vacuumlazy.c
index 991fb831726..f1b3be70785 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -363,9 +363,6 @@ extvac_stats_start(Relation rel, LVExtStatCounters
*counters)
{
TimestampTz starttime;
- if(!pgstat_track_vacuum_statistics)
- return;
-
memset(counters, 0, sizeof(LVExtStatCounters));
starttime = GetCurrentTimestamp();
@@ -404,9 +401,6 @@ extvac_stats_end(Relation rel, LVExtStatCounters *counters,
long secs;
int usecs;
- if(!pgstat_track_vacuum_statistics)
- return;
-
/* Calculate diffs of global stat parameters on WAL and buffer usage. */
memset(&walusage, 0, sizeof(WalUsage));
WalUsageAccumDiff(&walusage, &pgWalUsage, &counters->walusage);
@@ -454,9 +448,6 @@ void
extvac_stats_start_idx(Relation rel, IndexBulkDeleteResult *stats,
LVExtStatCountersIdx *counters)
{
- if(!pgstat_track_vacuum_statistics)
- return;
-
/* Set initial values for common heap and index statistics*/
extvac_stats_start(rel, &counters->common);
counters->pages_deleted = counters->tuples_removed = 0;
@@ -531,7 +522,7 @@ accumulate_heap_vacuum_statistics(LVExtStatCounters
*extVacCounters, LVRelState
vacrel->extVacReport.table.missed_dead_tuples +=
vacrel->missed_dead_tuples;
vacrel->extVacReport.table.missed_dead_pages +=
vacrel->missed_dead_pages;
vacrel->extVacReport.table.index_vacuum_count +=
vacrel->num_index_scans;
- vacrel->extVacReport.table.wraparound_failsafe_count +=
vacrel->wraparound_failsafe_count;
+ vacrel->extVacReport.wraparound_failsafe_count +=
vacrel->wraparound_failsafe_count;
}
/*
@@ -850,15 +841,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
*
* We are ready to send vacuum statistics information for heap
relations.
*/
- if(pgstat_track_vacuum_statistics)
- {
- /* Make generic extended vacuum stats report and
- * fill heap-specific extended stats fields.
- */
- extvac_stats_end(vacrel->rel, &extVacCounters,
&(vacrel->extVacReport));
- accumulate_heap_vacuum_statistics(&extVacCounters, vacrel);
-
- pgstat_report_vacuum(RelationGetRelid(rel),
+ accumulate_heap_vacuum_statistics(&extVacCounters, vacrel);
+ pgstat_report_vacuum(RelationGetRelid(rel),
rel->rd_rel->relisshared,
Max(vacrel->new_live_tuples,
0),
vacrel->recently_dead_tuples +
@@ -866,18 +850,6 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
starttime,
&(vacrel->extVacReport));
- }
- else
- {
- pgstat_report_vacuum(RelationGetRelid(rel),
-
rel->rd_rel->relisshared,
-
Max(vacrel->new_live_tuples, 0),
-
vacrel->recently_dead_tuples +
-
vacrel->missed_dead_tuples,
- starttime,
- NULL);
- }
-
pgstat_progress_end_command();
if (instrument)
@@ -2898,14 +2870,11 @@ lazy_vacuum_one_index(Relation indrel,
IndexBulkDeleteResult *istat,
istat = vac_bulkdel_one_index(&ivinfo, istat, vacrel->dead_items,
vacrel->dead_items_info);
- if(pgstat_track_vacuum_statistics)
- {
- /* Make extended vacuum stats report for index */
- extvac_stats_end_idx(indrel, istat, &extVacCounters,
&extVacReport);
- pgstat_report_vacuum(RelationGetRelid(indrel),
-
indrel->rd_rel->relisshared,
- 0, 0, 0,
&extVacReport);
- }
+ /* Make extended vacuum stats report for index */
+ extvac_stats_end_idx(indrel, istat, &extVacCounters, &extVacReport);
+ pgstat_report_vacuum(RelationGetRelid(indrel),
+
indrel->rd_rel->relisshared,
+ 0, 0, 0, &extVacReport);
/* Revert to the previous phase information for error traceback */
restore_vacuum_error_info(vacrel, &saved_err_info);
@@ -2962,14 +2931,11 @@ lazy_cleanup_one_index(Relation indrel,
IndexBulkDeleteResult *istat,
istat = vac_cleanup_one_index(&ivinfo, istat);
- if(pgstat_track_vacuum_statistics)
- {
- /* Make extended vacuum stats report for index */
- extvac_stats_end_idx(indrel, istat, &extVacCounters,
&extVacReport);
- pgstat_report_vacuum(RelationGetRelid(indrel),
-
indrel->rd_rel->relisshared,
- 0, 0, 0,
&extVacReport);
- }
+ /* Make extended vacuum stats report for index */
+ extvac_stats_end_idx(indrel, istat, &extVacCounters, &extVacReport);
+ pgstat_report_vacuum(RelationGetRelid(indrel),
+
indrel->rd_rel->relisshared,
+ 0, 0, 0, &extVacReport);
/* Revert to the previous phase information for error traceback */
restore_vacuum_error_info(vacrel, &saved_err_info);
@@ -3583,6 +3549,9 @@ vacuum_error_callback(void *arg)
switch (errinfo->phase)
{
case VACUUM_ERRCB_PHASE_SCAN_HEAP:
+ if(geterrelevel() == ERROR)
+
pgstat_report_vacuum_error(errinfo->reloid, PGSTAT_EXTVAC_TABLE);
+
if (BlockNumberIsValid(errinfo->blkno))
{
if (OffsetNumberIsValid(errinfo->offnum))
@@ -3598,6 +3567,9 @@ vacuum_error_callback(void *arg)
break;
case VACUUM_ERRCB_PHASE_VACUUM_HEAP:
+ if(geterrelevel() == ERROR)
+ pgstat_report_vacuum_error(errinfo->reloid,
PGSTAT_EXTVAC_TABLE);
+
if (BlockNumberIsValid(errinfo->blkno))
{
if (OffsetNumberIsValid(errinfo->offnum))
@@ -3613,16 +3585,25 @@ vacuum_error_callback(void *arg)
break;
case VACUUM_ERRCB_PHASE_VACUUM_INDEX:
+ if(geterrelevel() == ERROR)
+ pgstat_report_vacuum_error(errinfo->indoid,
PGSTAT_EXTVAC_INDEX);
+
errcontext("while vacuuming index \"%s\" of relation
\"%s.%s\"",
errinfo->indname,
errinfo->relnamespace, errinfo->relname);
break;
case VACUUM_ERRCB_PHASE_INDEX_CLEANUP:
+ if(geterrelevel() == ERROR)
+ pgstat_report_vacuum_error(errinfo->indoid,
PGSTAT_EXTVAC_INDEX);
+
errcontext("while cleaning up index \"%s\" of relation
\"%s.%s\"",
errinfo->indname,
errinfo->relnamespace, errinfo->relname);
break;
case VACUUM_ERRCB_PHASE_TRUNCATE:
+ if(geterrelevel() == ERROR)
+ pgstat_report_vacuum_error(errinfo->reloid,
PGSTAT_EXTVAC_TABLE);
+
if (BlockNumberIsValid(errinfo->blkno))
errcontext("while truncating relation \"%s.%s\"
to %u blocks",
errinfo->relnamespace,
errinfo->relname, errinfo->blkno);
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index da9079afa21..a72d01102bb 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1473,4 +1473,29 @@ 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_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.wraparound_failsafe AS wraparound_failsafe,
+ 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 363cbf2bb04..4cc1d09d96f 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -204,7 +204,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind);
bool pgstat_track_counts = false;
int pgstat_fetch_consistency =
PGSTAT_FETCH_CONSISTENCY_CACHE;
-bool pgstat_track_vacuum_statistics = true;
+bool pgstat_track_vacuum_statistics = false;
/* ----------
* state shared with pgstat_*.c
diff --git a/src/backend/utils/activity/pgstat_database.c
b/src/backend/utils/activity/pgstat_database.c
index 05a8ccfdb75..d5c1e2a2cf5 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 cd4ffb50bca..6d45db6d3d2 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -205,6 +205,38 @@ pgstat_drop_relation(Relation rel)
}
}
+/* ---------
+ * pgstat_report_vacuum_error() -
+ *
+ * Tell the collector about an (auto)vacuum interruption.
+ * ---------
+ */
+void
+pgstat_report_vacuum_error(Oid tableoid, ExtVacReportType m_type)
+{
+ PgStat_EntryRef *entry_ref;
+ PgStatShared_Relation *shtabentry;
+ PgStat_StatTabEntry *tabentry;
+ Oid dboid = MyDatabaseId;
+ PgStat_StatDBEntry *dbentry; /* pending database entry */
+
+ if (!pgstat_track_counts)
+ return;
+
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
+
dboid, tableoid, false);
+
+ shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats;
+ tabentry = &shtabentry->stats;
+
+ 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;
+}
+
/*
* Report that the table was just vacuumed and flush IO statistics.
*/
@@ -216,6 +248,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;
PgStat_Counter elapsedtime;
@@ -237,7 +270,8 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
tabentry->live_tuples = livetuples;
tabentry->dead_tuples = deadtuples;
- pgstat_accumulate_extvac_stats(&tabentry->vacuum_ext, params, true);
+ if(pgstat_track_vacuum_statistics)
+ pgstat_accumulate_extvac_stats(&tabentry->vacuum_ext, params,
true);
/*
* It is quite possible that a non-aggressive VACUUM ended up skipping
@@ -274,6 +308,16 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
*/
pgstat_flush_io(false);
(void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO);
+
+ 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);
+ }
}
/*
@@ -1007,9 +1051,6 @@ static void
pgstat_accumulate_extvac_stats(ExtVacReport *dst, ExtVacReport *src,
bool
accumulate_reltype_specific_info)
{
- if(!pgstat_track_vacuum_statistics)
- return;
-
dst->total_blks_read += src->total_blks_read;
dst->total_blks_hit += src->total_blks_hit;
dst->total_blks_dirtied += src->total_blks_dirtied;
@@ -1021,6 +1062,8 @@ pgstat_accumulate_extvac_stats(ExtVacReport *dst,
ExtVacReport *src,
dst->blk_write_time += src->blk_write_time;
dst->delay_time += src->delay_time;
dst->total_time += src->total_time;
+ dst->wraparound_failsafe_count += src->wraparound_failsafe_count;
+ dst->errors += src->errors;
if (!accumulate_reltype_specific_info)
return;
@@ -1048,7 +1091,6 @@ pgstat_accumulate_extvac_stats(ExtVacReport *dst,
ExtVacReport *src,
dst->table.index_vacuum_count +=
src->table.index_vacuum_count;
dst->table.missed_dead_pages +=
src->table.missed_dead_pages;
dst->table.missed_dead_tuples +=
src->table.missed_dead_tuples;
- dst->table.wraparound_failsafe_count +=
src->table.wraparound_failsafe_count;
}
else if (dst->type == PGSTAT_EXTVAC_INDEX)
{
diff --git a/src/backend/utils/adt/pgstatfuncs.c
b/src/backend/utils/adt/pgstatfuncs.c
index 80e867d773f..59eb528b20c 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2369,7 +2369,7 @@ pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
values[i++] = Int64GetDatum(extvacuum->table.recently_dead_tuples);
values[i++] = Int64GetDatum(extvacuum->table.missed_dead_tuples);
- values[i++] = Int32GetDatum(extvacuum->table.wraparound_failsafe_count);
+ values[i++] = Int32GetDatum(extvacuum->wraparound_failsafe_count);
values[i++] = Int64GetDatum(extvacuum->table.index_vacuum_count);
values[i++] = Int64GetDatum(extvacuum->wal_records);
@@ -2499,6 +2499,104 @@ 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 14
+
+ 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,
"wraparound_failsafe_count",
+ INT4OID, -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++] = Int32GetDatum(extvacuum->wraparound_failsafe_count);
+ values[i++] = Int32GetDatum(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/backend/utils/misc/guc_tables.c
b/src/backend/utils/misc/guc_tables.c
index 3f7750ad6a4..7315be9b7bc 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1485,7 +1485,7 @@ struct config_bool ConfigureNamesBool[] =
NULL
},
&pgstat_track_vacuum_statistics,
- true,
+ false,
NULL, NULL, NULL
},
{
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index c983e069eef..df2981abd82 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12473,12 +12473,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,int4}',
+ proargmodes => '{i,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,total_time,wraparound_failsafe,errors}',
+ prosrc => 'pg_stat_get_vacuum_database' },
]
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index a305836f237..dcf8cb763a4 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -155,6 +155,9 @@ typedef struct ExtVacReport
int64 tuples_deleted; /* tuples deleted by vacuum */
+ int32 errors;
+ int32 wraparound_failsafe_count; /* the number of times
to prevent wraparound problem */
+
ExtVacReportType type; /* heap, index, etc. */
/* ----------
@@ -184,7 +187,6 @@ typedef struct ExtVacReport
int64 missed_dead_tuples; /*
tuples not pruned by vacuum due to failure to get a cleanup lock */
int64 missed_dead_pages; /*
pages with missed dead tuples */
int64 index_vacuum_count; /* number of
index vacuumings */
- int32 wraparound_failsafe_count; /* the
number of times to prevent workaround problem */
} table;
struct
{
@@ -759,6 +761,7 @@ extern void pgstat_report_vacuum(Oid tableoid, bool shared,
extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
bool
resetcounter, TimestampTz starttime);
+extern void pgstat_report_vacuum_error(Oid tableoid, ExtVacReportType m_type);
/*
* If stats are enabled, but pending data hasn't been prepared yet, call
diff --git a/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
b/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
index 5893d89573d..cfec3159580 100644
--- a/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
+++ b/src/test/isolation/specs/vacuum-extending-in-repetable-read.spec
@@ -18,6 +18,9 @@ teardown
}
session s1
+setup {
+ SET track_vacuum_statistics TO 'on';
+ }
step s1_begin_repeatable_read {
BEGIN transaction ISOLATION LEVEL REPEATABLE READ;
select count(ival) from test_vacuum_stat_isolation where id>900;
@@ -25,6 +28,9 @@ step s1_begin_repeatable_read {
step s1_commit { COMMIT; }
session s2
+setup {
+ SET track_vacuum_statistics TO 'on';
+ }
step s2_insert { INSERT INTO test_vacuum_stat_isolation(id,
ival) SELECT ival, ival%10 FROM generate_series(1,1000) As ival; }
step s2_update { UPDATE test_vacuum_stat_isolation SET ival =
ival + 2 where id > 900; }
step s2_delete { DELETE FROM test_vacuum_stat_isolation where
id > 900; }
diff --git a/src/test/regress/expected/rules.out
b/src/test/regress/expected/rules.out
index 88b0e13ed58..1741753b52d 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2259,6 +2259,23 @@ pg_stat_user_tables| SELECT relid,
rev_all_visible_pages
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,
+ 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.wraparound_failsafe,
+ 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,
wraparound_failsafe, errors);
pg_stat_vacuum_indexes| SELECT rel.oid AS relid,
ns.nspname AS schemaname,
rel.relname,
diff --git a/src/test/regress/expected/vacuum_index_statistics.out
b/src/test/regress/expected/vacuum_index_statistics.out
index e00a0fc683c..9e5d33342c9 100644
--- a/src/test/regress/expected/vacuum_index_statistics.out
+++ b/src/test/regress/expected/vacuum_index_statistics.out
@@ -16,8 +16,12 @@ SHOW track_counts; -- must be on
\set sample_size 10000
-- not enabled by default, but we want to test it...
SET track_functions TO 'all';
--- Test that vacuum statistics will be empty when parameter is off.
-SET track_vacuum_statistics TO 'off';
+SHOW track_vacuum_statistics; -- must be off
+ track_vacuum_statistics
+-------------------------
+ off
+(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;
@@ -33,12 +37,7 @@ WHERE vt.relname = 'vestat';
RESET track_vacuum_statistics;
DROP TABLE vestat CASCADE;
-SHOW track_vacuum_statistics; -- must be on
- track_vacuum_statistics
--------------------------
- on
-(1 row)
-
+SET track_vacuum_statistics TO 'on';
-- ensure pending stats are flushed
SELECT pg_stat_force_next_flush();
pg_stat_force_next_flush
@@ -181,3 +180,4 @@ WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid;
(1 row)
DROP TABLE vestat;
+RESET track_vacuum_statistics;
diff --git a/src/test/regress/expected/vacuum_tables_statistics.out
b/src/test/regress/expected/vacuum_tables_and_db_statistics.out
similarity index 82%
rename from src/test/regress/expected/vacuum_tables_statistics.out
rename to src/test/regress/expected/vacuum_tables_and_db_statistics.out
index b5ea9c9ab1e..f79ae58fb38 100644
--- a/src/test/regress/expected/vacuum_tables_statistics.out
+++ b/src/test/regress/expected/vacuum_tables_and_db_statistics.out
@@ -6,7 +6,6 @@
-- number of frozen and visible pages removed by backend.
-- Statistic wal_fpi is not displayed in this test because its behavior is
unstable.
--
--- conditio sine qua non
SHOW track_counts; -- must be on
track_counts
--------------
@@ -16,8 +15,12 @@ SHOW track_counts; -- must be on
\set sample_size 10000
-- not enabled by default, but we want to test it...
SET track_functions TO 'all';
--- Test that vacuum statistics will be empty when parameter is off.
-SET track_vacuum_statistics TO 'off';
+SHOW track_vacuum_statistics; -- must be off
+ track_vacuum_statistics
+-------------------------
+ off
+(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;
@@ -37,12 +40,12 @@ WHERE vt.relname = 'vestat';
RESET track_vacuum_statistics;
DROP TABLE vestat CASCADE;
-SHOW track_vacuum_statistics; -- must be on
- track_vacuum_statistics
--------------------------
- on
-(1 row)
-
+CREATE DATABASE regression_statistic_vacuum_db;
+CREATE DATABASE regression_statistic_vacuum_db1;
+\c regression_statistic_vacuum_db;
+SET track_vacuum_statistics TO on;
+-- not enabled by default, but we want to test it...
+SET track_functions TO 'all';
-- ensure pending stats are flushed
SELECT pg_stat_force_next_flush();
pg_stat_force_next_flush
@@ -165,7 +168,7 @@ FROM pg_stat_vacuum_tables WHERE relname = 'vestat' \gset
SELECT :dwr3>0 AS dWR, :dfpi3=0 AS dFPI, :dwb3>0 AS dWB;
dwr | dfpi | dwb
-----+------+-----
- t | t | t
+ f | t | f
(1 row)
--
@@ -225,3 +228,69 @@ FROM pg_stat_vacuum_tables, pg_stat_all_tables WHERE
pg_stat_vacuum_tables.relna
(1 row)
DROP TABLE vestat CASCADE;
+-- 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_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)
+
+-- 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;
+SET track_vacuum_statistics TO on;
+-- 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_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
+SET track_vacuum_statistics TO on;
+DROP TABLE vestat CASCADE;
+\c regression_statistic_vacuum_db1;
+SET track_vacuum_statistics TO on;
+SELECT count(*)
+FROM pg_database d
+CROSS JOIN pg_stat_get_vacuum_tables(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;
+RESET track_vacuum_statistics;
diff --git a/src/test/regress/parallel_schedule
b/src/test/regress/parallel_schedule
index 977a87a5b1f..19c76b96830 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/regression.diffs
b/src/test/regress/regression.diffs
new file mode 100644
index 00000000000..d4c24f41b40
--- /dev/null
+++ b/src/test/regress/regression.diffs
@@ -0,0 +1,12 @@
+diff -U3
/home/alena/postgrespro__copy61/src/test/regress/expected/vacuum_tables_and_db_statistics.out
/home/alena/postgrespro__copy61/src/test/regress/results/vacuum_tables_and_db_statistics.out
+---
/home/alena/postgrespro__copy61/src/test/regress/expected/vacuum_tables_and_db_statistics.out
2025-02-04 18:03:12.645127559 +0300
++++
/home/alena/postgrespro__copy61/src/test/regress/results/vacuum_tables_and_db_statistics.out
2025-02-04 18:36:10.238349991 +0300
+@@ -168,7 +168,7 @@
+ SELECT :dwr3>0 AS dWR, :dfpi3=0 AS dFPI, :dwb3>0 AS dWB;
+ dwr | dfpi | dwb
+ -----+------+-----
+- t | t | t
++ f | t | f
+ (1 row)
+
+ --
diff --git a/src/test/regress/sql/vacuum_index_statistics.sql
b/src/test/regress/sql/vacuum_index_statistics.sql
index ae146e1d23f..9b7e645187d 100644
--- a/src/test/regress/sql/vacuum_index_statistics.sql
+++ b/src/test/regress/sql/vacuum_index_statistics.sql
@@ -14,8 +14,7 @@ SHOW track_counts; -- must be on
-- not enabled by default, but we want to test it...
SET track_functions TO 'all';
--- Test that vacuum statistics will be empty when parameter is off.
-SET track_vacuum_statistics TO 'off';
+SHOW track_vacuum_statistics; -- must be off
CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
@@ -33,7 +32,7 @@ WHERE vt.relname = 'vestat';
RESET track_vacuum_statistics;
DROP TABLE vestat CASCADE;
-SHOW track_vacuum_statistics; -- must be on
+SET track_vacuum_statistics TO 'on';
-- ensure pending stats are flushed
SELECT pg_stat_force_next_flush();
@@ -149,3 +148,4 @@ FROM pg_stat_vacuum_indexes vt, pg_class c
WHERE vt.relname = 'vestat_pkey' AND vt.relid = c.oid;
DROP TABLE vestat;
+RESET track_vacuum_statistics;
diff --git a/src/test/regress/sql/vacuum_tables_statistics.sql
b/src/test/regress/sql/vacuum_tables_and_db_statistics.sql
similarity index 81%
rename from src/test/regress/sql/vacuum_tables_statistics.sql
rename to src/test/regress/sql/vacuum_tables_and_db_statistics.sql
index 5bc34bec64b..ca7dbde9387 100644
--- a/src/test/regress/sql/vacuum_tables_statistics.sql
+++ b/src/test/regress/sql/vacuum_tables_and_db_statistics.sql
@@ -7,15 +7,13 @@
-- Statistic wal_fpi is not displayed in this test because its behavior is
unstable.
--
--- conditio sine qua non
SHOW track_counts; -- must be on
\set sample_size 10000
-- not enabled by default, but we want to test it...
SET track_functions TO 'all';
--- Test that vacuum statistics will be empty when parameter is off.
-SET track_vacuum_statistics TO 'off';
+SHOW track_vacuum_statistics; -- must be off
CREATE TABLE vestat (x int) WITH (autovacuum_enabled = off, fillfactor = 10);
INSERT INTO vestat SELECT x FROM generate_series(1,:sample_size) as x;
@@ -36,7 +34,13 @@ WHERE vt.relname = 'vestat';
RESET track_vacuum_statistics;
DROP TABLE vestat CASCADE;
-SHOW track_vacuum_statistics; -- must be on
+CREATE DATABASE regression_statistic_vacuum_db;
+CREATE DATABASE regression_statistic_vacuum_db1;
+\c regression_statistic_vacuum_db;
+SET track_vacuum_statistics TO on;
+
+-- not enabled by default, but we want to test it...
+SET track_functions TO 'all';
-- ensure pending stats are flushed
SELECT pg_stat_force_next_flush();
@@ -180,4 +184,59 @@ VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) vestat;
SELECT vm_new_frozen_pages = :pf AS vm_new_frozen_pages,vm_new_visible_pages =
:pv AS vm_new_visible_pages,vm_new_visible_frozen_pages = :pvf AS
vm_new_visible_frozen_pages, 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
+DROP TABLE vestat CASCADE;
+
+-- 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_vacuum_database
+WHERE dbname = current_database();
+
+-- 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;
+SET track_vacuum_statistics TO on;
+
+-- 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_vacuum_database
+WHERE dbname = 'regression_statistic_vacuum_db';
+
+\c regression_statistic_vacuum_db
+SET track_vacuum_statistics TO on;
+
+DROP TABLE vestat CASCADE;
+
+\c regression_statistic_vacuum_db1;
+SET track_vacuum_statistics TO on;
+SELECT count(*)
+FROM pg_database d
+CROSS JOIN pg_stat_get_vacuum_tables(0)
+WHERE oid = 0; -- must be 0
+
+\c postgres
+DROP DATABASE regression_statistic_vacuum_db1;
+DROP DATABASE regression_statistic_vacuum_db;
+RESET track_vacuum_statistics;
\ No newline at end of file
--
2.34.1
