public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sami Imseih <[email protected]>
To: Yugo Nagata <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Pgsql Hackers <[email protected]>
Subject: Re: Track skipped tables during autovacuum and autoanalyze
Date: Mon, 4 May 2026 15:44:57 -0500
Message-ID: <CAA5RZ0vwgXZ5kF2GvYBR+Ma1LPSbDjE9pjANzSiUw3wmpv51PQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAA5RZ0snnePNW1NKGKh+NyJ1CY26T5F_6-tTq+BHWM2kj1fN1g@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAA5RZ0tNh03LLgJVMA=PXSdY8YVoui4_GyfbfTrYK5cka3Q9Rw@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAA5RZ0tUHU-r=Tc47P2DZyytF7x5h5OwBiRABe_dZt+zWNqe9g@mail.gmail.com>
<[email protected]>
Thanks for the update!
> >
> > 1/
> >
> > + relid = RangeVarGetRelid(vrel->relation, NoLock, false);
> >
> > Should this be called with "true" as the 3rd (missing_ok) argument, otherwise
> > we end up with an error instead of a "--- relation no longer exists" log. right?
>
> No, it should be false. If missing_ok is true, VACUUM (SKIP_LOCKED) on a not-existing
> table would emit a "skipping vacuum of ... --- relation no longer exists" message, but
> it should be "relation ... does not exist".
Yeah you are right.
But, after looking more into this, I still think the
expand_vacuum_rel() changes can be
improved. The branching
- */
- if (!OidIsValid(relid))
+ if (!(options & VACOPT_SKIP_LOCKED))
{
- if (options & VACOPT_VACUUM)
- ereport(WARNING,
-
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
- errmsg("skipping
vacuum of \"%s\" --- lock not available",
-
vrel->relation->relname)));
- else
- ereport(WARNING,
-
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
- errmsg("skipping
analyze of \"%s\" --- lock not available",
+ relid = RangeVarGetRelidExtended(vrel->relation,
+
AccessShareLock,
+
0, NULL, NULL);
+ if (!OidIsValid(relid))
+ return vacrels;
+ }
+ else
+ {
+ /* Get relid for reporting before taking a lock */
+ relid = RangeVarGetRelid(vrel->relation, NoLock, false);
+
+ if (!ConditionalLockRelationOid(relid, AccessShareLock))
is not needed. We can continue just using RangeVarGetRelidExtended()
with the rvr_opts and an AccessExclusiveLock, and once we need to
report that we cannot obtain the lock, RangeVarGetRelid() can be
called at that point for the purpose of calling
pgstat_report_skipped_vacuum_analyze(). This is safer than calling
ConditionalLockRelationOid() on a relid obtained with NoLock. See
attached v6.
>> 2/
>>
>> Can the isolation tests
>> src/test/isolation/specs/vacuum-skip-locked.spec be updated
>> to check pg_stat_user_tables as well?
> Yes, we can. I've attached an updated patch including that test.
> While working on the test, I noticed that skipped FULL VACUUM was counted
> in the previous patch, so I fixed it not to avoid counting those cases.
The tests looks good to me.
> The names of the new fields are still open. The current pattern is
> "last_skipped_..." and "skipped_..._count". Alternatively, we could use
> "..._last_skip" and "..._skip_count", which would be consistent with
> slotsync_skip_count and slosync_last_skip.
> Which do you think is better?
I think last_skipped_* is better since we use last_vacuum, last_autovacuum, etc.
--
Sami
Attachments:
[application/octet-stream] v6-0001-Track-skipped-vacuum-and-analyze-activity-per-rel.patch (39.6K, 2-v6-0001-Track-skipped-vacuum-and-analyze-activity-per-rel.patch)
download | inline diff:
From 13e2d6b8390ea48cb4fd3e604a46bde02b750bde Mon Sep 17 00:00:00 2001
From: Yugo Nagata <[email protected]>
Date: Tue, 24 Mar 2026 13:09:00 +0900
Subject: [PATCH v6 1/1] Track skipped vacuum and analyze activity per relation
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_skipped_vacuum
- last_skipped_autovacuum
- last_skipped_analyze
- last_skipped_autoanalyze
- skipped_vacuum_count
- skipped_autovacuum_count
- skipped_analyze_count
- skipped_autoanalyze_count
These field 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 | 31 +++
src/backend/utils/activity/pgstat_relation.c | 64 +++++
src/backend/utils/adt/pgstatfuncs.c | 24 ++
src/include/catalog/pg_proc.dat | 32 +++
src/include/pgstat.h | 17 ++
.../isolation/expected/vacuum-skip-locked.out | 260 ++++++++++++++++--
.../isolation/specs/vacuum-skip-locked.spec | 41 +--
src/test/regress/expected/rules.out | 24 ++
10 files changed, 555 insertions(+), 34 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 08d5b824552..a9b579d87a9 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4387,6 +4387,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_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>
@@ -4397,6 +4407,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_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>
@@ -4406,6 +4426,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_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>
@@ -4416,6 +4446,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_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>
@@ -4426,6 +4466,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>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>
@@ -4436,6 +4486,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>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>
@@ -4445,6 +4505,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>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>
@@ -4455,6 +4525,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>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>
@@ -4510,6 +4590,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 73a1c1c4670..f509fc7876b 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_skipped_vacuum_time(C.oid) as last_skipped_vacuum,
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
+ pg_stat_get_last_skipped_autovacuum_time(C.oid) as last_skipped_autovacuum,
pg_stat_get_last_analyze_time(C.oid) as last_analyze,
+ pg_stat_get_last_skipped_analyze_time(C.oid) as last_skipped_analyze,
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
+ pg_stat_get_last_skipped_autoanalyze_time(C.oid) as last_skipped_autoanalyze,
pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
+ pg_stat_get_skipped_vacuum_count(C.oid) AS skipped_vacuum_count,
pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
+ pg_stat_get_skipped_autovacuum_count(C.oid) AS skipped_autovacuum_count,
pg_stat_get_analyze_count(C.oid) AS analyze_count,
+ pg_stat_get_skipped_analyze_count(C.oid) AS skipped_analyze_count,
pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
+ pg_stat_get_skipped_autoanalyze_count(C.oid) AS 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 99d0db82ed7..51c5fc2fdaf 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_SKIPPED_AUTOVACUUM;
+ else
+ flags |= PGSTAT_REPORT_SKIPPED_VACUUM;
+ }
+ if ((options & VACOPT_ANALYZE) != 0)
+ {
+ if (AmAutoVacuumWorkerProcess())
+ flags |= PGSTAT_REPORT_SKIPPED_AUTOANALYZE;
+ else
+ flags |= PGSTAT_REPORT_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,17 @@ 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 */
+ relid = RangeVarGetRelid(vrel->relation, NoLock, true);
+
+ if ((options & VACOPT_VACUUM) != 0 && (options & VACOPT_FULL) == 0)
+ flags |= PGSTAT_REPORT_SKIPPED_VACUUM;
+ if ((options & VACOPT_ANALYZE) != 0)
+ flags |= PGSTAT_REPORT_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 b2ca28f83ba..21d1b382ba0 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -17,12 +17,14 @@
#include "postgres.h"
+#include "access/htup_details.h"
#include "access/twophase_rmgr.h"
#include "access/xact.h"
#include "catalog/catalog.h"
#include "utils/memutils.h"
#include "utils/pgstat_internal.h"
#include "utils/rel.h"
+#include "utils/syscache.h"
#include "utils/timestamp.h"
@@ -367,6 +369,68 @@ 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();
+
+ /* block acquiring lock for the same reason as pgstat_report_autovac() */
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
+ isshared ? InvalidOid : MyDatabaseId,
+ relid, false);
+
+ shtabentry = (PgStatShared_Relation *) entry_ref->shared_stats;
+ tabentry = &shtabentry->stats;
+
+ if (flags & PGSTAT_REPORT_SKIPPED_VACUUM)
+ {
+ tabentry->last_skipped_vacuum_time = ts;
+ tabentry->skipped_vacuum_count++;
+ }
+ else if (flags & PGSTAT_REPORT_SKIPPED_AUTOVACUUM)
+ {
+ tabentry->last_skipped_autovacuum_time = ts;
+ tabentry->skipped_autovacuum_count++;
+ }
+
+ if (flags & PGSTAT_REPORT_SKIPPED_ANALYZE)
+ {
+ tabentry->last_skipped_analyze_time = ts;
+ tabentry->skipped_analyze_count++;
+ }
+ else if (flags & PGSTAT_REPORT_SKIPPED_AUTOANALYZE)
+ {
+ tabentry->last_skipped_autoanalyze_time = ts;
+ tabentry->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 7a9dfa9ba3b..aef291217b6 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_skipped_analyze_count */
+PG_STAT_GET_RELENTRY_INT64(skipped_analyze_count)
+
+/* pg_stat_get_skipped_autoanalyze_count */
+PG_STAT_GET_RELENTRY_INT64(skipped_autoanalyze_count)
+
+/* pg_stat_get_skipped_autovacuum_count */
+PG_STAT_GET_RELENTRY_INT64(skipped_autovacuum_count)
+
+/* pg_stat_get_skipped_vacuum_count */
+PG_STAT_GET_RELENTRY_INT64(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_skipped_analyze_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_skipped_analyze_time)
+
+/* pg_stat_get_last_skipped_autoanalyze_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_skipped_autoanalyze_time)
+
+/* pg_stat_get_last_skipped_autovacuum_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_skipped_autovacuum_time)
+
+/* pg_stat_get_last_skipped_vacuum_time */
+PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_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 fa9ae79082b..32debb34863 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5680,6 +5680,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 skipped vacuum time for a table',
+ proname => 'pg_stat_get_last_skipped_vacuum_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_skipped_vacuum_time' },
+{ oid => '8143', descr => 'statistics: last skipped auto vacuum time for a table',
+ proname => 'pg_stat_get_last_skipped_autovacuum_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_skipped_autovacuum_time' },
+{ oid => '8144', descr => 'statistics: last skipped analyze time for a table',
+ proname => 'pg_stat_get_last_skipped_analyze_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_skipped_analyze_time' },
+{ oid => '8145', descr => 'statistics: last skipped auto analyze time for a table',
+ proname => 'pg_stat_get_last_skipped_autoanalyze_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_last_skipped_autoanalyze_time' },
+{ oid => '8146', descr => 'statistics: number of skipped vacuum for a table',
+ proname => 'pg_stat_get_skipped_vacuum_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_skipped_vacuum_count' },
+{ oid => '8147', descr => 'statistics: number of skipped auto vacuum for a table',
+ proname => 'pg_stat_get_skipped_autovacuum_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_skipped_autovacuum_count' },
+{ oid => '8148', descr => 'statistics: number of skipped analyzes for a table',
+ proname => 'pg_stat_get_skipped_analyze_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_skipped_analyze_count' },
+{ oid => '8149', descr => 'statistics: number of skipped auto analyzes for a table',
+ proname => 'pg_stat_get_skipped_autoanalyze_count', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+ prosrc => 'pg_stat_get_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 dfa2e837638..54b9fc60bc3 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -479,6 +479,15 @@ typedef struct PgStat_StatTabEntry
TimestampTz last_autoanalyze_time; /* autovacuum initiated */
PgStat_Counter autoanalyze_count;
+ TimestampTz last_skipped_vacuum_time; /* user initiated vacuum */
+ PgStat_Counter skipped_vacuum_count;
+ TimestampTz last_skipped_autovacuum_time; /* autovacuum initiated */
+ PgStat_Counter skipped_autovacuum_count;
+ TimestampTz last_skipped_analyze_time; /* user initiated */
+ PgStat_Counter skipped_analyze_count;
+ TimestampTz last_skipped_autoanalyze_time; /* autovacuum initiated */
+ PgStat_Counter skipped_autoanalyze_count;
+
PgStat_Counter total_vacuum_time; /* times in milliseconds */
PgStat_Counter total_autovacuum_time;
PgStat_Counter total_analyze_time;
@@ -703,6 +712,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_SKIPPED_VACUUM (1 << 0) /* vacuum is skipped */
+#define PGSTAT_REPORT_SKIPPED_ANALYZE (1 << 1) /* analyze is skipped */
+#define PGSTAT_REPORT_SKIPPED_AUTOVACUUM (1 << 2) /* autovacuum is skipped */
+#define PGSTAT_REPORT_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..e2cff175b7c 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,22 @@ step vac_specified: VACUUM (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
-starting permutation: lock_share vac_all_parts commit
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 0
+part2 | 1| 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 +34,22 @@ step vac_all_parts: VACUUM (SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 0
+part2 | 1| 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 +59,22 @@ step analyze_specified: ANALYZE (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
-starting permutation: lock_share analyze_all_parts commit
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 1
+part2 | 0| 0| 1| 0
+(3 rows)
+
+
+starting permutation: lock_share analyze_all_parts commit check_stat
step lock_share:
BEGIN;
LOCK part1 IN SHARE MODE;
@@ -41,8 +83,22 @@ step analyze_all_parts: ANALYZE (SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 1| 0
+part1 | 0| 0| 0| 1
+part2 | 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 +108,22 @@ step vac_analyze_specified: VACUUM (ANALYZE, SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
-starting permutation: lock_share vac_analyze_all_parts commit
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 1
+part2 | 1| 0| 1| 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 +132,22 @@ step vac_analyze_all_parts: VACUUM (ANALYZE, SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 1| 0
+part1 | 0| 1| 0| 1
+part2 | 1| 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 +157,22 @@ step vac_full_specified: VACUUM (SKIP_LOCKED, FULL) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
-starting permutation: lock_share vac_full_all_parts commit
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 0
+part2 | 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 +181,22 @@ step vac_full_all_parts: VACUUM (SKIP_LOCKED, FULL) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 0
+part2 | 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 +206,22 @@ step vac_specified: VACUUM (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 0
+part2 | 1| 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 +230,22 @@ step vac_all_parts: VACUUM (SKIP_LOCKED) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 0
+part2 | 1| 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 +255,22 @@ step analyze_specified: ANALYZE (SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 1
+part2 | 0| 0| 1| 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 +280,22 @@ step commit:
COMMIT;
step analyze_all_parts: <... completed>
-
-starting permutation: lock_access_exclusive vac_analyze_specified commit
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 1| 0
+part1 | 0| 0| 1| 0
+part2 | 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 +305,22 @@ step vac_analyze_specified: VACUUM (ANALYZE, SKIP_LOCKED) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 1| 0| 1
+part2 | 1| 0| 1| 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 +330,22 @@ 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, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 1| 0
+part1 | 1| 0| 1| 0
+part2 | 1| 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 +355,22 @@ step vac_full_specified: VACUUM (SKIP_LOCKED, FULL) part1, part2;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
-starting permutation: lock_access_exclusive vac_full_all_parts commit
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 0
+part2 | 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 +379,17 @@ step vac_full_all_parts: VACUUM (SKIP_LOCKED, FULL) parted;
step commit:
COMMIT;
+step check_stat:
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+
+relname|vacuum_count|skipped_vacuum_count|analyze_count|skipped_analyze_count
+-------+------------+--------------------+-------------+---------------------
+parted | 0| 0| 0| 0
+part1 | 0| 0| 0| 0
+part2 | 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..b0da75d4b6d 100644
--- a/src/test/isolation/specs/vacuum-skip-locked.spec
+++ b/src/test/isolation/specs/vacuum-skip-locked.spec
@@ -33,6 +33,15 @@ step commit
COMMIT;
}
+step check_stat
+{
+ SELECT relname,
+ vacuum_count, skipped_vacuum_count,
+ analyze_count, skipped_analyze_count
+ FROM pg_stat_all_tables
+ WHERE relname IN ('parted', 'part1', 'part2');
+}
+
session s2
step vac_specified { VACUUM (SKIP_LOCKED) part1, part2; }
step vac_all_parts { VACUUM (SKIP_LOCKED) parted; }
@@ -43,19 +52,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 a65a5bf0c4f..9b2075d3373 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_skipped_vacuum_time(c.oid) AS last_skipped_vacuum,
pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum,
+ pg_stat_get_last_skipped_autovacuum_time(c.oid) AS last_skipped_autovacuum,
pg_stat_get_last_analyze_time(c.oid) AS last_analyze,
+ pg_stat_get_last_skipped_analyze_time(c.oid) AS last_skipped_analyze,
pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze,
+ pg_stat_get_last_skipped_autoanalyze_time(c.oid) AS last_skipped_autoanalyze,
pg_stat_get_vacuum_count(c.oid) AS vacuum_count,
+ pg_stat_get_skipped_vacuum_count(c.oid) AS skipped_vacuum_count,
pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count,
+ pg_stat_get_skipped_autovacuum_count(c.oid) AS skipped_autovacuum_count,
pg_stat_get_analyze_count(c.oid) AS analyze_count,
+ pg_stat_get_skipped_analyze_count(c.oid) AS skipped_analyze_count,
pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count,
+ pg_stat_get_skipped_autoanalyze_count(c.oid) AS 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,
@@ -2346,13 +2354,21 @@ pg_stat_sys_tables| SELECT relid,
n_mod_since_analyze,
n_ins_since_vacuum,
last_vacuum,
+ last_skipped_vacuum,
last_autovacuum,
+ last_skipped_autovacuum,
last_analyze,
+ last_skipped_analyze,
last_autoanalyze,
+ last_skipped_autoanalyze,
vacuum_count,
+ skipped_vacuum_count,
autovacuum_count,
+ skipped_autovacuum_count,
analyze_count,
+ skipped_analyze_count,
autoanalyze_count,
+ skipped_autoanalyze_count,
total_vacuum_time,
total_autovacuum_time,
total_analyze_time,
@@ -2401,13 +2417,21 @@ pg_stat_user_tables| SELECT relid,
n_mod_since_analyze,
n_ins_since_vacuum,
last_vacuum,
+ last_skipped_vacuum,
last_autovacuum,
+ last_skipped_autovacuum,
last_analyze,
+ last_skipped_analyze,
last_autoanalyze,
+ last_skipped_autoanalyze,
vacuum_count,
+ skipped_vacuum_count,
autovacuum_count,
+ skipped_autovacuum_count,
analyze_count,
+ skipped_analyze_count,
autoanalyze_count,
+ skipped_autoanalyze_count,
total_vacuum_time,
total_autovacuum_time,
total_analyze_time,
--
2.50.1 (Apple Git-155)
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], [email protected], [email protected]
Subject: Re: Track skipped tables during autovacuum and autoanalyze
In-Reply-To: <CAA5RZ0vwgXZ5kF2GvYBR+Ma1LPSbDjE9pjANzSiUw3wmpv51PQ@mail.gmail.com>
* 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