agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Yugo Nagata <[email protected]>
Subject: [PATCH v12 1/2] Track skipped vacuum and analyze activity per relation
Date: Tue, 24 Mar 2026 13:09:00 +0900
This commit adds eight fields to the relation statistics that track
the last time vacuum or analyze has been attempted but skipped due to
lock unavailability, along with their counts:
- last_lock_skipped_vacuum
- last_lock_skipped_autovacuum
- last_lock_skipped_analyze
- last_lock_skipped_autoanalyze
- lock_skipped_vacuum_count
- lock_skipped_autovacuum_count
- lock_skipped_analyze_count
- lock_skipped_autoanalyze_count
The field names make explicit that the skip is due to lock
unavailability, distinguishing it from other reasons a vacuum or
analyze might not run on a relation.
These fields can help users confirm that autovacuum is actively attempting
to run on a table that has not been vacuumed or analyzed for a long time,
and that the lack of progress is due to repeated skips rather than inactivity.
---
doc/src/sgml/monitoring.sgml | 88 ++++++
src/backend/catalog/system_views.sql | 8 +
src/backend/commands/vacuum.c | 38 +++
src/backend/utils/activity/pgstat_relation.c | 85 ++++++
src/backend/utils/adt/pgstatfuncs.c | 24 ++
src/include/catalog/pg_proc.dat | 32 ++
src/include/pgstat.h | 19 +-
.../isolation/expected/vacuum-skip-locked.out | 276 ++++++++++++++++--
.../isolation/specs/vacuum-skip-locked.spec | 42 ++-
src/test/regress/expected/rules.out | 24 ++
10 files changed, 601 insertions(+), 35 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 1ce0ef00799..e4d6dda1166 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4678,6 +4678,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_lock_skipped_vacuum</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Last time a manual vacuum on this table was attempted but skipped due to
+ lock unavailability (not counting <command>VACUUM FULL</command>)
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>last_autovacuum</structfield> <type>timestamp with time zone</type>
@@ -4688,6 +4698,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_lock_skipped_autovacuum</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Last time a vacuum on this table by the autovacuum daemon was attempted
+ but skipped due to lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>last_analyze</structfield> <type>timestamp with time zone</type>
@@ -4697,6 +4717,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_lock_skipped_analyze</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Last time a manual analyze on this table was attempted but skipped due to
+ lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>last_autoanalyze</structfield> <type>timestamp with time zone</type>
@@ -4707,6 +4737,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_lock_skipped_autoanalyze</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Last time at which an analyze on this table by the autovacuum was
+ attempted but skipped due to lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>vacuum_count</structfield> <type>bigint</type>
@@ -4717,6 +4757,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>lock_skipped_vacuum_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times manual vacuums on this table have been attempted but skipped
+ due to lock unavailability (not counting <command>VACUUM FULL</command>)
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>autovacuum_count</structfield> <type>bigint</type>
@@ -4727,6 +4777,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>lock_skipped_autovacuum_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times vacuums on this table by the autovacuum daemon have been
+ attempted but skipped due to lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>analyze_count</structfield> <type>bigint</type>
@@ -4736,6 +4796,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>lock_skipped_analyze_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times manual analyzes on this table have been attempted but
+ skipped due to lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>autoanalyze_count</structfield> <type>bigint</type>
@@ -4746,6 +4816,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>lock_skipped_autoanalyze_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times analyzes on this table by the autovacuum daemon have
+ been attempted but skipped due to lock unavailability
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>total_vacuum_time</structfield> <type>double precision</type>
@@ -4801,6 +4881,14 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</tgroup>
</table>
+ <note>
+ <para>
+ When a manual vacuum or analyze on a parent table in an inheritance or
+ partitioning hierarchy is skipped, the statistics are recorded only for
+ the parent table, not for its children.
+ </para>
+ </note>
+
</sect2>
<sect2 id="monitoring-pg-stat-autovacuum-scores-view">
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 090281a03dd..d5e127f432d 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -736,13 +736,21 @@ CREATE VIEW pg_stat_all_tables AS
pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze,
pg_stat_get_ins_since_vacuum(C.oid) AS n_ins_since_vacuum,
pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,
+ pg_stat_get_last_lock_skipped_vacuum_time(C.oid) as last_lock_skipped_vacuum,
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
+ pg_stat_get_last_lock_skipped_autovacuum_time(C.oid) as last_lock_skipped_autovacuum,
pg_stat_get_last_analyze_time(C.oid) as last_analyze,
+ pg_stat_get_last_lock_skipped_analyze_time(C.oid) as last_lock_skipped_analyze,
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
+ pg_stat_get_last_lock_skipped_autoanalyze_time(C.oid) as last_lock_skipped_autoanalyze,
pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
+ pg_stat_get_lock_skipped_vacuum_count(C.oid) AS lock_skipped_vacuum_count,
pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
+ pg_stat_get_lock_skipped_autovacuum_count(C.oid) AS lock_skipped_autovacuum_count,
pg_stat_get_analyze_count(C.oid) AS analyze_count,
+ pg_stat_get_lock_skipped_analyze_count(C.oid) AS lock_skipped_analyze_count,
pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
+ pg_stat_get_lock_skipped_autoanalyze_count(C.oid) AS lock_skipped_autoanalyze_count,
pg_stat_get_total_vacuum_time(C.oid) AS total_vacuum_time,
pg_stat_get_total_autovacuum_time(C.oid) AS total_autovacuum_time,
pg_stat_get_total_analyze_time(C.oid) AS total_analyze_time,
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 38539a6fd3d..613ff24d4f5 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -793,8 +793,26 @@ vacuum_open_relation(Oid relid, RangeVar *relation, uint32 options,
rel = try_relation_open(relid, NoLock);
else
{
+ int flags = 0;
+
rel = NULL;
rel_lock = false;
+
+ if ((options & VACOPT_VACUUM) != 0 && (options & VACOPT_FULL) == 0)
+ {
+ if (AmAutoVacuumWorkerProcess())
+ flags |= PGSTAT_REPORT_LOCK_SKIPPED_AUTOVACUUM;
+ else
+ flags |= PGSTAT_REPORT_LOCK_SKIPPED_VACUUM;
+ }
+ if ((options & VACOPT_ANALYZE) != 0)
+ {
+ if (AmAutoVacuumWorkerProcess())
+ flags |= PGSTAT_REPORT_LOCK_SKIPPED_AUTOANALYZE;
+ else
+ flags |= PGSTAT_REPORT_LOCK_SKIPPED_ANALYZE;
+ }
+ pgstat_report_skipped_vacuum_analyze(relid, flags);
}
/* if relation is opened, leave */
@@ -930,6 +948,8 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
*/
if (!OidIsValid(relid))
{
+ int flags = 0;
+
if (options & VACOPT_VACUUM)
ereport(WARNING,
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
@@ -940,6 +960,24 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
errmsg("skipping analyze of \"%s\" --- lock not available",
vrel->relation->relname)));
+
+ /*
+ * Get relid for statistics reporting.
+ *
+ * Since we failed to acquire the lock, use NoLock here. Although
+ * a concurrent DDL may have dropped or renamed the relation,
+ * RangeVarGetRelid() with NoLock does not check for invalidation
+ * messages.
+ */
+ relid = RangeVarGetRelid(vrel->relation, NoLock, true);
+
+ if ((options & VACOPT_VACUUM) != 0 && (options & VACOPT_FULL) == 0)
+ flags |= PGSTAT_REPORT_LOCK_SKIPPED_VACUUM;
+ if ((options & VACOPT_ANALYZE) != 0)
+ flags |= PGSTAT_REPORT_LOCK_SKIPPED_ANALYZE;
+
+ pgstat_report_skipped_vacuum_analyze(relid, flags);
+
return vacrels;
}
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 04f2eb21d0b..d8259823925 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -17,12 +17,15 @@
#include "postgres.h"
+#include "access/htup_details.h"
#include "access/twophase_rmgr.h"
#include "access/xact.h"
#include "catalog/catalog.h"
+#include "utils/inval.h"
#include "utils/memutils.h"
#include "utils/pgstat_internal.h"
#include "utils/rel.h"
+#include "utils/syscache.h"
#include "utils/timestamp.h"
@@ -367,6 +370,88 @@ pgstat_report_analyze(Relation rel,
(void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO);
}
+/*
+ * Report that the table was skipped during vacuum or/and analyze.
+ */
+void
+pgstat_report_skipped_vacuum_analyze(Oid relid, int flags)
+{
+ PgStat_EntryRef *entry_ref;
+ PgStatShared_Relation *shtabentry;
+ PgStat_StatTabEntry *tabentry;
+ TimestampTz ts;
+ HeapTuple classTup;
+ bool isshared;
+
+ if (!pgstat_track_counts || !flags)
+ return;
+
+ classTup = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(classTup))
+ return; /* somebody deleted the rel, forget it */
+ isshared = ((Form_pg_class) GETSTRUCT(classTup))->relisshared;
+ ReleaseSysCache(classTup);
+
+ /* Store the data in the table's hash table entry. */
+ ts = GetCurrentTimestamp();
+
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
+ isshared ? InvalidOid : MyDatabaseId,
+ relid, false);
+
+ /*
+ * Re-check whether the relation still exists. Unlike the normal stats
+ * reporting path, we don't hold a relation lock here (the whole point of
+ * SKIP_LOCKED is that we failed to acquire one). Without a lock, a
+ * concurrent DROP TABLE could commit between our earlier syscache lookup
+ * and the stats entry creation above, leaving an orphaned stats entry.
+ *
+ * Accept cache invalidation messages first, so that a DROP that committed
+ * after our earlier check is visible to the syscache lookup below.
+ */
+ AcceptInvalidationMessages();
+ classTup = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(classTup))
+ {
+ pgstat_unlock_entry(entry_ref);
+ pgstat_drop_entry(PGSTAT_KIND_RELATION,
+ isshared ? InvalidOid : MyDatabaseId, relid, true);
+ return;
+ }
+ ReleaseSysCache(classTup);
+
+ shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats;
+ tabentry = &shtabentry->stats;
+
+ if (flags & PGSTAT_REPORT_LOCK_SKIPPED_VACUUM)
+ {
+ tabentry->last_lock_skipped_vacuum_time = ts;
+ tabentry->lock_skipped_vacuum_count++;
+ }
+ else if (flags & PGSTAT_REPORT_LOCK_SKIPPED_AUTOVACUUM)
+ {
+ tabentry->last_lock_skipped_autovacuum_time = ts;
+ tabentry->lock_skipped_autovacuum_count++;
+ }
+
+ if (flags & PGSTAT_REPORT_LOCK_SKIPPED_ANALYZE)
+ {
+ tabentry->last_lock_skipped_analyze_time = ts;
+ tabentry->lock_skipped_analyze_count++;
+ }
+ else if (flags & PGSTAT_REPORT_LOCK_SKIPPED_AUTOANALYZE)
+ {
+ tabentry->last_lock_skipped_autoanalyze_time = ts;
+ tabentry->lock_skipped_autoanalyze_count++;
+ }
+
+ pgstat_unlock_entry(entry_ref);
+
+ /* see pgstat_report_vacuum() */
+ pgstat_flush_io(false);
+ (void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO);
+}
+
/*
* count a tuple insertion of n tuples
*/
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 565d0e70768..6a05c3660e2 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -84,6 +84,18 @@ PG_STAT_GET_RELENTRY_INT64(mod_since_analyze)
/* pg_stat_get_numscans */
PG_STAT_GET_RELENTRY_INT64(numscans)
+/* pg_stat_get_lock_skipped_analyze_count */
+PG_STAT_GET_RELENTRY_INT64(lock_skipped_analyze_count)
+
+/* pg_stat_get_lock_skipped_autoanalyze_count */
+PG_STAT_GET_RELENTRY_INT64(lock_skipped_autoanalyze_count)
+
+/* pg_stat_get_lock_skipped_autovacuum_count */
+PG_STAT_GET_RELENTRY_INT64(lock_skipped_autovacuum_count)
+
+/* pg_stat_get_lock_skipped_vacuum_count */
+PG_STAT_GET_RELENTRY_INT64(lock_skipped_vacuum_count)
+
/* pg_stat_get_tuples_deleted */
PG_STAT_GET_RELENTRY_INT64(tuples_deleted)
@@ -170,6 +182,18 @@ PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_vacuum_time)
/* pg_stat_get_lastscan */
PG_STAT_GET_RELENTRY_TIMESTAMPTZ(lastscan)
+/* pg_stat_get_last_lock_skipped_analyze_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_lock_skipped_analyze_time)
+
+/* pg_stat_get_last_lock_skipped_autoanalyze_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_lock_skipped_autoanalyze_time)
+
+/* pg_stat_get_last_lock_skipped_autovacuum_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_lock_skipped_autovacuum_time)
+
+/* pg_stat_get_last_lock_skipped_vacuum_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_lock_skipped_vacuum_time)
+
/* pg_stat_get_stat_reset_time */
PG_STAT_GET_RELENTRY_TIMESTAMPTZ(stat_reset_time)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index f8a021987b5..d5484465af2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5678,6 +5678,38 @@
proargmodes => '{o,o,o,o,o,o,o,o,o,o}',
proargnames => '{oid,score,xid_score,mxid_score,vacuum_score,vacuum_insert_score,analyze_score,do_vacuum,do_analyze,for_wraparound}',
prosrc => 'pg_stat_get_autovacuum_scores' },
+{ oid => '8142', descr => 'statistics: last vacuum skip time due to lock unavailability for a table',
+ proname => 'pg_stat_get_last_lock_skipped_vacuum_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_lock_skipped_vacuum_time' },
+{ oid => '8143', descr => 'statistics: last auto vacuum skip time due to lock unavailability for a table',
+ proname => 'pg_stat_get_last_lock_skipped_autovacuum_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_lock_skipped_autovacuum_time' },
+{ oid => '8144', descr => 'statistics: last analyze skip time due to lock unavailability for a table',
+ proname => 'pg_stat_get_last_lock_skipped_analyze_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_lock_skipped_analyze_time' },
+{ oid => '8145', descr => 'statistics: last auto analyze skip time due to lock unavailability for a table',
+ proname => 'pg_stat_get_last_lock_skipped_autoanalyze_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_lock_skipped_autoanalyze_time' },
+{ oid => '8146', descr => 'statistics: number of vacuum skips due to lock unavailability for a table',
+ proname => 'pg_stat_get_lock_skipped_vacuum_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_lock_skipped_vacuum_count' },
+{ oid => '8147', descr => 'statistics: number of auto vacuum skips due to lock unavailability for a table',
+ proname => 'pg_stat_get_lock_skipped_autovacuum_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_lock_skipped_autovacuum_count' },
+{ oid => '8148', descr => 'statistics: number of analyzes skips due to lock unavailability for a table',
+ proname => 'pg_stat_get_lock_skipped_analyze_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_lock_skipped_analyze_count' },
+{ oid => '8149', descr => 'statistics: number of auto analyzes skips due to lock unavailability for a table',
+ proname => 'pg_stat_get_lock_skipped_autoanalyze_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_lock_skipped_autoanalyze_count' },
{ oid => '1936', descr => 'statistics: currently active backend IDs',
proname => 'pg_stat_get_backend_idset', prorows => '100', proretset => 't',
provolatile => 's', proparallel => 'r', prorettype => 'int4',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 58a44857f13..5146d7489a7 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -218,7 +218,7 @@ typedef struct PgStat_TableXactStatus
* ------------------------------------------------------------
*/
-#define PGSTAT_FILE_FORMAT_ID 0x01A5BCBC
+#define PGSTAT_FILE_FORMAT_ID 0x01A5BCBD
typedef struct PgStat_ArchiverStats
{
@@ -479,6 +479,15 @@ typedef struct PgStat_StatTabEntry
TimestampTz last_autoanalyze_time; /* autovacuum initiated */
PgStat_Counter autoanalyze_count;
+ TimestampTz last_lock_skipped_vacuum_time; /* user initiated vacuum */
+ PgStat_Counter lock_skipped_vacuum_count;
+ TimestampTz last_lock_skipped_autovacuum_time; /* autovacuum initiated */
+ PgStat_Counter lock_skipped_autovacuum_count;
+ TimestampTz last_lock_skipped_analyze_time; /* user initiated */
+ PgStat_Counter lock_skipped_analyze_count;
+ TimestampTz last_lock_skipped_autoanalyze_time; /* autovacuum initiated */
+ PgStat_Counter lock_skipped_autoanalyze_count;
+
PgStat_Counter total_vacuum_time; /* times in milliseconds */
PgStat_Counter total_autovacuum_time;
PgStat_Counter total_analyze_time;
@@ -716,6 +725,14 @@ extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
bool resetcounter, TimestampTz starttime);
+/* flags for pgstat_flush_backend() */
+#define PGSTAT_REPORT_LOCK_SKIPPED_VACUUM (1 << 0) /* vacuum is skipped */
+#define PGSTAT_REPORT_LOCK_SKIPPED_ANALYZE (1 << 1) /* analyze is skipped */
+#define PGSTAT_REPORT_LOCK_SKIPPED_AUTOVACUUM (1 << 2) /* autovacuum is skipped */
+#define PGSTAT_REPORT_LOCK_SKIPPED_AUTOANALYZE (1 << 3) /* autoanalyze is
+ * skipped */
+extern void pgstat_report_skipped_vacuum_analyze(Oid relid, int flags);
+
/*
* If stats are enabled, but pending data hasn't been prepared yet, call
* pgstat_assoc_relation() to do so. See its comment for why this is done
diff --git a/src/test/isolation/expected/vacuum-skip-locked.out b/src/test/isolation/expected/vacuum-skip-locked.out
index 99db281a159..b1b25036163 100644
--- a/src/test/isolation/expected/vacuum-skip-locked.out
+++ b/src/test/isolation/expected/vacuum-skip-locked.out
@@ -1,6 +1,6 @@
Parsed test spec with 2 sessions
-starting permutation: lock_share vac_specified commit
+starting permutation: lock_share vac_specified commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -10,8 +10,23 @@ step vac_specified: VACUUM (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
-starting permutation: lock_share vac_all_parts commit
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 0| 1| 0| 0
+part2 | 1| 0| 0| 0
+parted | 0| 0| 0| 0
+(3 rows)
+
+
+starting permutation: lock_share vac_all_parts commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -20,8 +35,23 @@ step vac_all_parts: VACUUM (SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
+
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 0| 1| 0| 0
+part2 | 1| 0| 0| 0
+parted | 0| 0| 0| 0
+(3 rows)
+
-starting permutation: lock_share analyze_specified commit
+starting permutation: lock_share analyze_specified commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -31,8 +61,23 @@ step analyze_specified: ANALYZE (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
-starting permutation: lock_share analyze_all_parts commit
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 0| 0| 0| 1
+part2 | 0| 0| 1| 0
+parted | 0| 0| 0| 0
+(3 rows)
+
+
+starting permutation: lock_share analyze_all_parts commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -41,8 +86,23 @@ step analyze_all_parts: ANALYZE (SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
+
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 0| 0| 0| 1
+part2 | 0| 0| 1| 0
+parted | 0| 0| 1| 0
+(3 rows)
+
-starting permutation: lock_share vac_analyze_specified commit
+starting permutation: lock_share vac_analyze_specified commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -52,8 +112,23 @@ step vac_analyze_specified: VACUUM (ANALYZE, SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
-starting permutation: lock_share vac_analyze_all_parts commit
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 0| 1| 0| 1
+part2 | 1| 0| 1| 0
+parted | 0| 0| 0| 0
+(3 rows)
+
+
+starting permutation: lock_share vac_analyze_all_parts commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -62,8 +137,23 @@ step vac_analyze_all_parts: VACUUM (ANALYZE, SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
+
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 0| 1| 0| 1
+part2 | 1| 0| 1| 0
+parted | 0| 0| 1| 0
+(3 rows)
+
-starting permutation: lock_share vac_full_specified commit
+starting permutation: lock_share vac_full_specified commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -73,8 +163,23 @@ step vac_full_specified: VACUUM (SKIP_LOCKED, FULL) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
-starting permutation: lock_share vac_full_all_parts commit
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 0| 0| 0| 0
+part2 | 0| 0| 0| 0
+parted | 0| 0| 0| 0
+(3 rows)
+
+
+starting permutation: lock_share vac_full_all_parts commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -83,8 +188,23 @@ step vac_full_all_parts: VACUUM (SKIP_LOCKED, FULL) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
+
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 0| 0| 0| 0
+part2 | 0| 0| 0| 0
+parted | 0| 0| 0| 0
+(3 rows)
-starting permutation: lock_access_exclusive vac_specified commit
+
+starting permutation: lock_access_exclusive vac_specified commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -94,8 +214,23 @@ step vac_specified: VACUUM (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
+
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 0| 1| 0| 0
+part2 | 1| 0| 0| 0
+parted | 0| 0| 0| 0
+(3 rows)
-starting permutation: lock_access_exclusive vac_all_parts commit
+
+starting permutation: lock_access_exclusive vac_all_parts commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -104,8 +239,23 @@ step vac_all_parts: VACUUM (SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
+
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 0| 1| 0| 0
+part2 | 1| 0| 0| 0
+parted | 0| 0| 0| 0
+(3 rows)
-starting permutation: lock_access_exclusive analyze_specified commit
+
+starting permutation: lock_access_exclusive analyze_specified commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -115,8 +265,23 @@ step analyze_specified: ANALYZE (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
+
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 0| 0| 0| 1
+part2 | 0| 0| 1| 0
+parted | 0| 0| 0| 0
+(3 rows)
+
-starting permutation: lock_access_exclusive analyze_all_parts commit
+starting permutation: lock_access_exclusive analyze_all_parts commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -126,8 +291,23 @@ step commit:
COMMIT;
step analyze_all_parts: <... completed>
-
-starting permutation: lock_access_exclusive vac_analyze_specified commit
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
+
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 0| 0| 1| 0
+part2 | 0| 0| 1| 0
+parted | 0| 0| 1| 0
+(3 rows)
+
+
+starting permutation: lock_access_exclusive vac_analyze_specified commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -137,8 +317,23 @@ step vac_analyze_specified: VACUUM (ANALYZE, SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
+
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 0| 1| 0| 1
+part2 | 1| 0| 1| 0
+parted | 0| 0| 0| 0
+(3 rows)
-starting permutation: lock_access_exclusive vac_analyze_all_parts commit
+
+starting permutation: lock_access_exclusive vac_analyze_all_parts commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -148,8 +343,23 @@ step commit:
COMMIT;
step vac_analyze_all_parts: <... completed>
-
-starting permutation: lock_access_exclusive vac_full_specified commit
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
+
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 1| 0| 1| 0
+part2 | 1| 0| 1| 0
+parted | 0| 0| 1| 0
+(3 rows)
+
+
+starting permutation: lock_access_exclusive vac_full_specified commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -159,8 +369,23 @@ step vac_full_specified: VACUUM (SKIP_LOCKED, FULL) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
-starting permutation: lock_access_exclusive vac_full_all_parts commit
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 0| 0| 0| 0
+part2 | 0| 0| 0| 0
+parted | 0| 0| 0| 0
+(3 rows)
+
+
+starting permutation: lock_access_exclusive vac_full_all_parts commit check_stat
step lock_access_exclusive:
BEGIN;
LOCK part1 IN ACCESS EXCLUSIVE MODE;
@@ -169,3 +394,18 @@ step vac_full_all_parts: VACUUM (SKIP_LOCKED, FULL) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
+
+relname|vacuum_count|lock_skipped_vacuum_count|analyze_count|lock_skipped_analyze_count
+-------+------------+-------------------------+-------------+--------------------------
+part1 | 0| 0| 0| 0
+part2 | 0| 0| 0| 0
+parted | 0| 0| 0| 0
+(3 rows)
+
diff --git a/src/test/isolation/specs/vacuum-skip-locked.spec b/src/test/isolation/specs/vacuum-skip-locked.spec
index 3fad6e1c92a..e6cd1cf6f4f 100644
--- a/src/test/isolation/specs/vacuum-skip-locked.spec
+++ b/src/test/isolation/specs/vacuum-skip-locked.spec
@@ -33,6 +33,16 @@ step commit
COMMIT;
}
+step check_stat
+{
+ SELECT relname,
+ vacuum_count, lock_skipped_vacuum_count,
+ analyze_count, lock_skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2')
+ ORDER BY relname;
+}
+
session s2
step vac_specified { VACUUM (SKIP_LOCKED) part1, part2; }
step vac_all_parts { VACUUM (SKIP_LOCKED) parted; }
@@ -43,19 +53,19 @@ step vac_analyze_all_parts { VACUUM (ANALYZE, SKIP_LOCKED) parted; }
step vac_full_specified { VACUUM (SKIP_LOCKED, FULL) part1, part2; }
step vac_full_all_parts { VACUUM (SKIP_LOCKED, FULL) parted; }
-permutation lock_share vac_specified commit
-permutation lock_share vac_all_parts commit
-permutation lock_share analyze_specified commit
-permutation lock_share analyze_all_parts commit
-permutation lock_share vac_analyze_specified commit
-permutation lock_share vac_analyze_all_parts commit
-permutation lock_share vac_full_specified commit
-permutation lock_share vac_full_all_parts commit
-permutation lock_access_exclusive vac_specified commit
-permutation lock_access_exclusive vac_all_parts commit
-permutation lock_access_exclusive analyze_specified commit
-permutation lock_access_exclusive analyze_all_parts commit
-permutation lock_access_exclusive vac_analyze_specified commit
-permutation lock_access_exclusive vac_analyze_all_parts commit
-permutation lock_access_exclusive vac_full_specified commit
-permutation lock_access_exclusive vac_full_all_parts commit
+permutation lock_share vac_specified commit check_stat
+permutation lock_share vac_all_parts commit check_stat
+permutation lock_share analyze_specified commit check_stat
+permutation lock_share analyze_all_parts commit check_stat
+permutation lock_share vac_analyze_specified commit check_stat
+permutation lock_share vac_analyze_all_parts commit check_stat
+permutation lock_share vac_full_specified commit check_stat
+permutation lock_share vac_full_all_parts commit check_stat
+permutation lock_access_exclusive vac_specified commit check_stat
+permutation lock_access_exclusive vac_all_parts commit check_stat
+permutation lock_access_exclusive analyze_specified commit check_stat
+permutation lock_access_exclusive analyze_all_parts commit check_stat
+permutation lock_access_exclusive vac_analyze_specified commit check_stat
+permutation lock_access_exclusive vac_analyze_all_parts commit check_stat
+permutation lock_access_exclusive vac_full_specified commit check_stat
+permutation lock_access_exclusive vac_full_all_parts commit check_stat
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 6a3341356da..3fcee4dc097 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1835,13 +1835,21 @@ pg_stat_all_tables| SELECT c.oid AS relid,
pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze,
pg_stat_get_ins_since_vacuum(c.oid) AS n_ins_since_vacuum,
pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum,
+ pg_stat_get_last_lock_skipped_vacuum_time(c.oid) AS last_lock_skipped_vacuum,
pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum,
+ pg_stat_get_last_lock_skipped_autovacuum_time(c.oid) AS last_lock_skipped_autovacuum,
pg_stat_get_last_analyze_time(c.oid) AS last_analyze,
+ pg_stat_get_last_lock_skipped_analyze_time(c.oid) AS last_lock_skipped_analyze,
pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze,
+ pg_stat_get_last_lock_skipped_autoanalyze_time(c.oid) AS last_lock_skipped_autoanalyze,
pg_stat_get_vacuum_count(c.oid) AS vacuum_count,
+ pg_stat_get_lock_skipped_vacuum_count(c.oid) AS lock_skipped_vacuum_count,
pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count,
+ pg_stat_get_lock_skipped_autovacuum_count(c.oid) AS lock_skipped_autovacuum_count,
pg_stat_get_analyze_count(c.oid) AS analyze_count,
+ pg_stat_get_lock_skipped_analyze_count(c.oid) AS lock_skipped_analyze_count,
pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count,
+ pg_stat_get_lock_skipped_autoanalyze_count(c.oid) AS lock_skipped_autoanalyze_count,
pg_stat_get_total_vacuum_time(c.oid) AS total_vacuum_time,
pg_stat_get_total_autovacuum_time(c.oid) AS total_autovacuum_time,
pg_stat_get_total_analyze_time(c.oid) AS total_analyze_time,
@@ -2357,13 +2365,21 @@ pg_stat_sys_tables| SELECT relid,
n_mod_since_analyze,
n_ins_since_vacuum,
last_vacuum,
+ last_lock_skipped_vacuum,
last_autovacuum,
+ last_lock_skipped_autovacuum,
last_analyze,
+ last_lock_skipped_analyze,
last_autoanalyze,
+ last_lock_skipped_autoanalyze,
vacuum_count,
+ lock_skipped_vacuum_count,
autovacuum_count,
+ lock_skipped_autovacuum_count,
analyze_count,
+ lock_skipped_analyze_count,
autoanalyze_count,
+ lock_skipped_autoanalyze_count,
total_vacuum_time,
total_autovacuum_time,
total_analyze_time,
@@ -2412,13 +2428,21 @@ pg_stat_user_tables| SELECT relid,
n_mod_since_analyze,
n_ins_since_vacuum,
last_vacuum,
+ last_lock_skipped_vacuum,
last_autovacuum,
+ last_lock_skipped_autovacuum,
last_analyze,
+ last_lock_skipped_analyze,
last_autoanalyze,
+ last_lock_skipped_autoanalyze,
vacuum_count,
+ lock_skipped_vacuum_count,
autovacuum_count,
+ lock_skipped_autovacuum_count,
analyze_count,
+ lock_skipped_analyze_count,
autoanalyze_count,
+ lock_skipped_autoanalyze_count,
total_vacuum_time,
total_autovacuum_time,
total_analyze_time,
--
2.43.0
--Multipart=_Sat__25_Jul_2026_02_51_59_+0900_.UJmWUNxW9qIrIIE--
view thread (690+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v12 1/2] Track skipped vacuum and analyze activity per relation
In-Reply-To: <no-message-id-959109@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox