agora inbox for pgsql-hackers@postgresql.orghelp / color / mirror / Atom feed
[PATCH v13 1/2] Track skipped vacuum and analyze activity per relation 1+ messages / 1 participants [nested] [flat]
* [PATCH v13 1/2] Track skipped vacuum and analyze activity per relation @ 2026-03-24 04:09 Yugo Nagata <nagata@sraoss.co.jp> 0 siblings, 0 replies; 1+ messages in thread From: Yugo Nagata @ 2026-03-24 04:09 UTC (permalink / raw) 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=_Mon__27_Jul_2026_13_03_41_+0900_q9gi0JK8Af.Ckleb-- ^ permalink raw reply [nested|flat] 1+ messages in thread
only message in thread Thread overview: 1+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-03-24 04:09 [PATCH v13 1/2] Track skipped vacuum and analyze activity per relation Yugo Nagata <nagata@sraoss.co.jp>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox