On Thu, 5 Nov 2026 07:16:45 +0900 Michael Paquier <[email protected]> wrote:
> On Wed, Nov 04, 2026 at 03:55:31PM -0600, Zsolt Parragi wrote: > > I have only one small comment/question: it seems like the > > PGSTAT_FILE_FORMAT_ID change was dropped between v14 and v15? > > That would be the kind of thing a committer would take care of once/if > committing such a patch. I'd suggest to add a notice to the commit > message of the sent patch instead, to not forget that the counter > needs a bump when/if it gets merged. Thank you for the suggestion. Attached is an updated patch with only the suggested comment added. Regards, Yugo Nagata -- Yugo Nagata <[email protected]>
>From eef5547d7f6d149c19f98c7fff2e76c00782d78e Mon Sep 17 00:00:00 2001 From: Sami Imseih <[email protected]> Date: Fri, 24 Jul 2026 22:45:53 +0900 Subject: [PATCH v20 2/2] Add injection point test for vacuum skip_locked stats Add an isolation test exercising the race window between VACUUM (SKIP_LOCKED) reporting a skipped vacuum and concurrent table drops. Two scenarios are tested: 1. Table dropped (committed) while vacuumer is blocked at the injection point: no orphaned stats entry is created. 2. DROP TABLE rolled back while vacuumer is blocked: skip is still recorded since the table and its stats entry survive. --- src/backend/utils/activity/pgstat_relation.c | 2 + src/test/modules/injection_points/Makefile | 3 +- .../expected/vacuum_skip_lock_stats.out | 93 +++++++++++++++++++ src/test/modules/injection_points/meson.build | 1 + .../specs/vacuum_skip_lock_stats.spec | 68 ++++++++++++++ 5 files changed, 166 insertions(+), 1 deletion(-) create mode 100644 src/test/modules/injection_points/expected/vacuum_skip_lock_stats.out create mode 100644 src/test/modules/injection_points/specs/vacuum_skip_lock_stats.spec diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index e36fa0c4600..4265524bf51 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -21,6 +21,7 @@ #include "access/twophase_rmgr.h" #include "access/xact.h" #include "catalog/catalog.h" +#include "utils/injection_point.h" #include "utils/inval.h" #include "utils/memutils.h" #include "utils/pgstat_internal.h" @@ -448,6 +449,7 @@ pgstat_report_skipped_vacuum_analyze(Oid relid, int flags) isshared = classForm->relisshared; relkind = classForm->relkind; ReleaseSysCache(classTup); + INJECTION_POINT("skipped-vacuum-analyze-before-entry-lock", NULL); /* * Only track skipped vacuum/analyze activity for relkinds exposed by diff --git a/src/test/modules/injection_points/Makefile b/src/test/modules/injection_points/Makefile index 8e2dfaa2384..198921e3d93 100644 --- a/src/test/modules/injection_points/Makefile +++ b/src/test/modules/injection_points/Makefile @@ -37,7 +37,8 @@ ISOLATION = basic \ $(WAIT_CLEANUP_TST) \ heap_lock_update \ on_conflict_probe_window \ - multixact-aborted-updater + multixact-aborted-updater \ + vacuum_skip_lock_stats # some isolation tests require wal_level=replica ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/injection_points/extra.conf diff --git a/src/test/modules/injection_points/expected/vacuum_skip_lock_stats.out b/src/test/modules/injection_points/expected/vacuum_skip_lock_stats.out new file mode 100644 index 00000000000..0efed3db2db --- /dev/null +++ b/src/test/modules/injection_points/expected/vacuum_skip_lock_stats.out @@ -0,0 +1,93 @@ +Parsed test spec with 3 sessions + +starting permutation: lock vacuum unlock drop_table wakeup noop check_stats detach +injection_points_attach +----------------------- + +(1 row) + +step lock: + BEGIN; + LOCK TABLE test_skip IN ACCESS EXCLUSIVE MODE; + +s2: WARNING: skipping vacuum of "test_skip" --- lock not available +step vacuum: VACUUM (SKIP_LOCKED) test_skip; <waiting ...> +step unlock: COMMIT; +step drop_table: DROP TABLE test_skip; +step wakeup: SELECT injection_points_wakeup('skipped-vacuum-analyze-before-entry-lock'); +injection_points_wakeup +----------------------- + +(1 row) + +step vacuum: <... completed> +step noop: +step check_stats: + SELECT pg_stat_force_next_flush(); + SELECT pg_stat_get_lock_skipped_vacuum_count(oid_val) AS skip_count + FROM saved_oid; + +pg_stat_force_next_flush +------------------------ + +(1 row) + +skip_count +---------- + 0 +(1 row) + +step detach: SELECT injection_points_detach('skipped-vacuum-analyze-before-entry-lock'); +injection_points_detach +----------------------- + +(1 row) + + +starting permutation: lock vacuum unlock rollback_drop wakeup noop check_stats detach +injection_points_attach +----------------------- + +(1 row) + +step lock: + BEGIN; + LOCK TABLE test_skip IN ACCESS EXCLUSIVE MODE; + +s2: WARNING: skipping vacuum of "test_skip" --- lock not available +step vacuum: VACUUM (SKIP_LOCKED) test_skip; <waiting ...> +step unlock: COMMIT; +step rollback_drop: + BEGIN; + DROP TABLE test_skip; + ROLLBACK; + +step wakeup: SELECT injection_points_wakeup('skipped-vacuum-analyze-before-entry-lock'); +injection_points_wakeup +----------------------- + +(1 row) + +step vacuum: <... completed> +step noop: +step check_stats: + SELECT pg_stat_force_next_flush(); + SELECT pg_stat_get_lock_skipped_vacuum_count(oid_val) AS skip_count + FROM saved_oid; + +pg_stat_force_next_flush +------------------------ + +(1 row) + +skip_count +---------- + 1 +(1 row) + +step detach: SELECT injection_points_detach('skipped-vacuum-analyze-before-entry-lock'); +injection_points_detach +----------------------- + +(1 row) + diff --git a/src/test/modules/injection_points/meson.build b/src/test/modules/injection_points/meson.build index 97e7a736282..c8c3dc50eb2 100644 --- a/src/test/modules/injection_points/meson.build +++ b/src/test/modules/injection_points/meson.build @@ -45,6 +45,7 @@ injection_points_isolation = [ 'heap_lock_update', 'on_conflict_probe_window', 'multixact-aborted-updater', + 'vacuum_skip_lock_stats', ] # wait_cleanup terminates another backend, whose FATAL message can be lost diff --git a/src/test/modules/injection_points/specs/vacuum_skip_lock_stats.spec b/src/test/modules/injection_points/specs/vacuum_skip_lock_stats.spec new file mode 100644 index 00000000000..b277b1e80f7 --- /dev/null +++ b/src/test/modules/injection_points/specs/vacuum_skip_lock_stats.spec @@ -0,0 +1,68 @@ +# Test for race conditions between VACUUM (SKIP_LOCKED) stats reporting +# and concurrent DROP TABLE. +# +# When VACUUM (SKIP_LOCKED) cannot acquire a lock, it reports skipped +# statistics via pgstat_report_skipped_vacuum_analyze(). An injection +# point after the syscache lookup but before the stats update allows us +# to verify that a concurrent DROP does not leave orphaned stats entries. + +setup +{ + CREATE EXTENSION injection_points; + CREATE TABLE test_skip (id int); + INSERT INTO test_skip VALUES (1); + ANALYZE test_skip; + SELECT pg_stat_force_next_flush(); + CREATE TABLE saved_oid (oid_val oid); + INSERT INTO saved_oid SELECT oid FROM pg_class WHERE relname = 'test_skip'; +} + +teardown +{ + DROP TABLE IF EXISTS test_skip; + DROP TABLE IF EXISTS saved_oid; + DROP EXTENSION injection_points; +} + +# s1: holds the lock so VACUUM skips the table +session s1 +step lock +{ + BEGIN; + LOCK TABLE test_skip IN ACCESS EXCLUSIVE MODE; +} +step unlock { COMMIT; } + +# s2: runs VACUUM (SKIP_LOCKED), blocks at injection point after skip +session s2 +setup +{ + SELECT injection_points_set_local(); + SELECT injection_points_attach('skipped-vacuum-analyze-before-entry-lock', 'wait'); +} +step vacuum { VACUUM (SKIP_LOCKED) test_skip; } +step noop {} +step detach { SELECT injection_points_detach('skipped-vacuum-analyze-before-entry-lock'); } + +# s3: drops table or wakes up the vacuumer +session s3 +step drop_table { DROP TABLE test_skip; } +step rollback_drop +{ + BEGIN; + DROP TABLE test_skip; + ROLLBACK; +} +step wakeup { SELECT injection_points_wakeup('skipped-vacuum-analyze-before-entry-lock'); } +step check_stats +{ + SELECT pg_stat_force_next_flush(); + SELECT pg_stat_get_lock_skipped_vacuum_count(oid_val) AS skip_count + FROM saved_oid; +} + +# Table dropped while vacuumer is blocked: no orphaned stats entry. +permutation lock vacuum(wakeup) unlock drop_table wakeup noop check_stats detach + +# DROP rolled back while vacuumer is blocked: skip is still recorded. +permutation lock vacuum(wakeup) unlock rollback_drop wakeup noop check_stats detach -- 2.43.0
>From 3687e9d07e98cc53093e6033a213509706674202 Mon Sep 17 00:00:00 2001 From: Yugo Nagata <[email protected]> Date: Tue, 24 Mar 2026 13:09:00 +0900 Subject: [PATCH v20 1/2] 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_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. Because this patch adds new built-in statistics functions and extends pg_stat_all_tables with new columns. Catalog version bump required PGSTAT_FILE_FORMAT_ID bump required --- doc/src/sgml/monitoring.sgml | 93 ++++++ src/backend/catalog/system_views.sql | 8 + src/backend/commands/vacuum.c | 45 +++ src/backend/utils/activity/pgstat_relation.c | 111 +++++++ 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 | 296 ++++++++++++++++-- .../isolation/specs/vacuum-skip-locked.spec | 56 +++- src/test/regress/expected/rules.out | 24 ++ 10 files changed, 672 insertions(+), 34 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 445fbc9232c..660c500f771 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -4698,6 +4698,17 @@ 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 using + <literal>SKIP_LOCKED</literal> 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> @@ -4708,6 +4719,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> @@ -4717,6 +4738,17 @@ 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 using + <literal>SKIP_LOCKED</literal> 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> @@ -4727,6 +4759,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 an analyze 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>vacuum_count</structfield> <type>bigint</type> @@ -4737,6 +4779,17 @@ 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 using + <literal>SKIP_LOCKED</literal> 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> @@ -4747,6 +4800,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> @@ -4756,6 +4819,17 @@ 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 using + <literal>SKIP_LOCKED</literal> 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> @@ -4766,6 +4840,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> @@ -4821,6 +4905,15 @@ 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 before its child relations are + processed, 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 6dce9192ba0..7f646cc7320 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 3a5c1efd6ac..026dc153a6e 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -132,6 +132,7 @@ static bool vacuum_rel(Oid relid, RangeVar *relation, VacuumParams params, static double compute_parallel_delay(void); static VacOptValue get_vacoptval_from_boolean(DefElem *def); static bool vac_tid_reaped(ItemPointer itemptr, void *state); +static int vacuum_relation_skip_locked_flags(uint32 options); /* * GUC check function to ensure GUC value specified is within the allowable @@ -154,6 +155,33 @@ check_vacuum_buffer_usage_limit(int *newval, void **extra, return false; } +/* + * Return skipped-stats flags for a lock skip. + */ +static int +vacuum_relation_skip_locked_flags(uint32 options) +{ + int flags = 0; + + 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; + } + + return flags; +} + /* * Primary entry point for manual VACUUM and ANALYZE commands * @@ -818,6 +846,9 @@ vacuum_open_relation(Oid relid, RangeVar *relation, uint32 options, { rel = NULL; rel_lock = false; + + pgstat_report_skipped_vacuum_analyze(relid, + vacuum_relation_skip_locked_flags(options)); } /* if relation is opened, leave */ @@ -963,6 +994,20 @@ 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); + + pgstat_report_skipped_vacuum_analyze(relid, + vacuum_relation_skip_locked_flags(options)); + return vacrels; } diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index 5c70543ba88..e36fa0c4600 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" @@ -420,6 +423,114 @@ pgstat_report_analyze(Relation rel, (void) pgstat_flush_backend(false, PGSTAT_BACKEND_FLUSH_IO); } +/* + * Report that the table was skipped during vacuum and/or 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; + Form_pg_class classForm; + bool isshared; + char relkind; + + if (!pgstat_track_counts || !flags) + return; + + classTup = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(classTup)) + return; /* somebody deleted the rel, forget it */ + classForm = (Form_pg_class) GETSTRUCT(classTup); + isshared = classForm->relisshared; + relkind = classForm->relkind; + ReleaseSysCache(classTup); + + /* + * Only track skipped vacuum/analyze activity for relkinds exposed by + * pg_stat_all_tables. + */ + if (relkind != RELKIND_RELATION && + relkind != RELKIND_MATVIEW && + relkind != RELKIND_TOASTVALUE && + relkind != RELKIND_PARTITIONED_TABLE) + flags &= ~(PGSTAT_REPORT_LOCK_SKIPPED_VACUUM | + PGSTAT_REPORT_LOCK_SKIPPED_AUTOVACUUM); + + if (relkind != RELKIND_RELATION && + relkind != RELKIND_MATVIEW && + relkind != RELKIND_PARTITIONED_TABLE) + flags &= ~(PGSTAT_REPORT_LOCK_SKIPPED_ANALYZE | + PGSTAT_REPORT_LOCK_SKIPPED_AUTOANALYZE); + + if (!flags) + return; + + /* 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); + + 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); + + /* + * 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. This + * has to happen after releasing the stats entry lock, because resolving a + * catcache miss may need to access pg_class and wait for a heavyweight + * lock while still holding that LWLock, which risks deadlock. + */ + AcceptInvalidationMessages(); + classTup = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(classTup)) + { + pgstat_drop_entry(PGSTAT_KIND_RELATION, + isshared ? InvalidOid : MyDatabaseId, relid, true); + return; + } + ReleaseSysCache(classTup); + + /* 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 64b6f60516c..844a023debd 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) @@ -204,6 +216,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 9b78fcad414..fb9ca3286d6 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5675,6 +5675,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 manual 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 manual 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 manual 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 manual analyze 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 analyze 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 9b99062f853..f1b9f9d1801 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -536,6 +536,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; @@ -787,6 +796,14 @@ extern void pgstat_report_analyze(Relation rel, PgStat_Counter livetuples, PgStat_Counter deadtuples, bool resetcounter, TimestampTz starttime); +/* flags for pgstat_report_skipped_vacuum_analyze() */ +#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..3712ea311e4 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; + +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 + +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; + +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 +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; -starting permutation: lock_share vac_full_specified 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| 1| 0 +(3 rows) + + +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; + +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 + +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; + +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 + +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,38 @@ 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_view vac_analyze_view commit check_view_stat +step lock_view: + BEGIN; + LOCK TABLE partv IN ACCESS EXCLUSIVE MODE; + +s2: WARNING: skipping vacuum of "partv" --- lock not available +step vac_analyze_view: VACUUM (ANALYZE, SKIP_LOCKED) partv; +step commit: + COMMIT; + +step check_view_stat: + SELECT pg_stat_get_lock_skipped_vacuum_count('partv'::regclass) AS vac_skip, + pg_stat_get_lock_skipped_analyze_count('partv'::regclass) AS anl_skip; + +vac_skip|anl_skip +--------+-------- + 0| 0 +(1 row) + diff --git a/src/test/isolation/specs/vacuum-skip-locked.spec b/src/test/isolation/specs/vacuum-skip-locked.spec index 3fad6e1c92a..b03ea89c039 100644 --- a/src/test/isolation/specs/vacuum-skip-locked.spec +++ b/src/test/isolation/specs/vacuum-skip-locked.spec @@ -10,10 +10,12 @@ setup ALTER TABLE part1 SET (autovacuum_enabled = false); CREATE TABLE part2 PARTITION OF parted FOR VALUES IN (2); ALTER TABLE part2 SET (autovacuum_enabled = false); + CREATE VIEW partv AS SELECT * FROM part1; } teardown { + DROP VIEW IF EXISTS partv; DROP TABLE IF EXISTS parted; } @@ -28,11 +30,31 @@ step lock_access_exclusive BEGIN; LOCK part1 IN ACCESS EXCLUSIVE MODE; } +step lock_view +{ + BEGIN; + LOCK TABLE partv IN ACCESS EXCLUSIVE MODE; +} 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; +} +step check_view_stat +{ + SELECT pg_stat_get_lock_skipped_vacuum_count('partv'::regclass) AS vac_skip, + pg_stat_get_lock_skipped_analyze_count('partv'::regclass) AS anl_skip; +} + session s2 step vac_specified { VACUUM (SKIP_LOCKED) part1, part2; } step vac_all_parts { VACUUM (SKIP_LOCKED) parted; } @@ -40,22 +62,24 @@ step analyze_specified { ANALYZE (SKIP_LOCKED) part1, part2; } step analyze_all_parts { ANALYZE (SKIP_LOCKED) parted; } step vac_analyze_specified { VACUUM (ANALYZE, SKIP_LOCKED) part1, part2; } step vac_analyze_all_parts { VACUUM (ANALYZE, SKIP_LOCKED) parted; } +step vac_analyze_view { VACUUM (ANALYZE, SKIP_LOCKED) partv; } 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 +permutation lock_view vac_analyze_view commit check_view_stat diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index acbe4667d89..b14b0e42d3d 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, @@ -2364,13 +2372,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, @@ -2419,13 +2435,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
