Hi, On Mon, Feb 16, 2026 at 04:00:56PM -0500, Andres Freund wrote: > Hi, > > On 2026-02-16 10:10:21 +0000, Bertrand Drouvot wrote: > > Yeah, providing the wait_time would be great. Just to be sure, are you > > suggesting > > to remove all the fields (i.e requests, timeouts, deadlock_timeouts and > > fastpath) > > and just add a wait_time field instead? > > Well, I'd maybe make it waits, wait_time and perhaps fastpath exceeded.
Okay, done that way in the attached. To avoid overhead due to timing as much as possible, the patch simply relies on log_lock_waits and deadlock_timeout. It means that it relies on the existing code, and increments waits and wait_time only if log_lock_waits is on and if the session waited longer than deadlock_timeout. I did not want to dissociate the waits and wait_time increments so that their ratio could still make sense. That sounds like a good compromise, thoughts? > > I think that keeping requests would make sense to be able to get the average > > wait time per request. > > I don't think I'd request for that (as that would require counting in the > normal case), I'd use the number of waits. Yeah, I meant to say waits and not requests. Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
>From b33d6101c912d1d6ce77cf5d95161ba0b6c10874 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Tue, 29 Jul 2025 08:36:35 +0000 Subject: [PATCH v5 1/2] Add lock statistics Adding a new stat kind PGSTAT_KIND_LOCK for the lock statistics. This new statistic kind is a fixed one because its key is the lock type so that we know its size is LOCKTAG_LAST_TYPE + 1. This statistic kind records the following counters: waits wait_time fastpath_exceeded The waits and wait_time counters are incremented if log_lock_waits is on and the session waited longer than deadlock_timeout to acquire the lock. No extra details is added (like the ones, i.e relation oid, database oid, we can find in pg_locks). The idea is to provide an idea on what the locking behaviour looks like. XXX: Bump stat file format --- src/backend/storage/lmgr/lock.c | 58 ++++---- src/backend/storage/lmgr/proc.c | 6 + src/backend/utils/activity/Makefile | 1 + src/backend/utils/activity/meson.build | 1 + src/backend/utils/activity/pgstat.c | 18 +++ src/backend/utils/activity/pgstat_lock.c | 162 +++++++++++++++++++++++ src/include/pgstat.h | 28 ++++ src/include/utils/pgstat_internal.h | 21 +++ src/include/utils/pgstat_kind.h | 5 +- src/tools/pgindent/typedefs.list | 4 + 10 files changed, 276 insertions(+), 28 deletions(-) 28.9% src/backend/storage/lmgr/ 54.5% src/backend/utils/activity/ 7.4% src/include/utils/ 8.2% src/include/ diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c index e1168ad3837..8a0d970b26b 100644 --- a/src/backend/storage/lmgr/lock.c +++ b/src/backend/storage/lmgr/lock.c @@ -39,6 +39,7 @@ #include "access/xlogutils.h" #include "miscadmin.h" #include "pg_trace.h" +#include "pgstat.h" #include "storage/lmgr.h" #include "storage/proc.h" #include "storage/procarray.h" @@ -984,37 +985,42 @@ LockAcquireExtended(const LOCKTAG *locktag, * lock type on a relation we have already locked using the fast-path, but * for now we don't worry about that case either. */ - if (EligibleForRelationFastPath(locktag, lockmode) && - FastPathLocalUseCounts[FAST_PATH_REL_GROUP(locktag->locktag_field2)] < FP_LOCK_SLOTS_PER_GROUP) + if (EligibleForRelationFastPath(locktag, lockmode)) { - uint32 fasthashcode = FastPathStrongLockHashPartition(hashcode); - bool acquired; - - /* - * LWLockAcquire acts as a memory sequencing point, so it's safe to - * assume that any strong locker whose increment to - * FastPathStrongRelationLocks->counts becomes visible after we test - * it has yet to begin to transfer fast-path locks. - */ - LWLockAcquire(&MyProc->fpInfoLock, LW_EXCLUSIVE); - if (FastPathStrongRelationLocks->count[fasthashcode] != 0) - acquired = false; - else - acquired = FastPathGrantRelationLock(locktag->locktag_field2, - lockmode); - LWLockRelease(&MyProc->fpInfoLock); - if (acquired) + if (FastPathLocalUseCounts[FAST_PATH_REL_GROUP(locktag->locktag_field2)] < + FP_LOCK_SLOTS_PER_GROUP) { + uint32 fasthashcode = FastPathStrongLockHashPartition(hashcode); + bool acquired; + /* - * The locallock might contain stale pointers to some old shared - * objects; we MUST reset these to null before considering the - * lock to be acquired via fast-path. + * LWLockAcquire acts as a memory sequencing point, so it's safe + * to assume that any strong locker whose increment to + * FastPathStrongRelationLocks->counts becomes visible after we + * test it has yet to begin to transfer fast-path locks. */ - locallock->lock = NULL; - locallock->proclock = NULL; - GrantLockLocal(locallock, owner); - return LOCKACQUIRE_OK; + LWLockAcquire(&MyProc->fpInfoLock, LW_EXCLUSIVE); + if (FastPathStrongRelationLocks->count[fasthashcode] != 0) + acquired = false; + else + acquired = FastPathGrantRelationLock(locktag->locktag_field2, + lockmode); + LWLockRelease(&MyProc->fpInfoLock); + if (acquired) + { + /* + * The locallock might contain stale pointers to some old + * shared objects; we MUST reset these to null before + * considering the lock to be acquired via fast-path. + */ + locallock->lock = NULL; + locallock->proclock = NULL; + GrantLockLocal(locallock, owner); + return LOCKACQUIRE_OK; + } } + else + pgstat_count_lock_fastpath_exceeded(locallock->tag.lock.locktag_type); } /* diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c index fd8318bdf3d..81d20e7ae76 100644 --- a/src/backend/storage/lmgr/proc.c +++ b/src/backend/storage/lmgr/proc.c @@ -1609,9 +1609,15 @@ ProcSleep(LOCALLOCK *locallock) "Processes holding the lock: %s. Wait queue: %s.", lockHoldersNum, lock_holders_sbuf.data, lock_waiters_sbuf.data)))); else if (myWaitStatus == PROC_WAIT_STATUS_OK) + { + /* Increment the lock statistics counters */ + pgstat_count_lock_waits(locallock->tag.lock.locktag_type); + pgstat_count_lock_wait_time(locallock->tag.lock.locktag_type, msecs); + ereport(LOG, (errmsg("process %d acquired %s on %s after %ld.%03d ms", MyProcPid, modename, buf.data, msecs, usecs))); + } else { Assert(myWaitStatus == PROC_WAIT_STATUS_ERROR); diff --git a/src/backend/utils/activity/Makefile b/src/backend/utils/activity/Makefile index c37bfb350bb..ca3ef89bf59 100644 --- a/src/backend/utils/activity/Makefile +++ b/src/backend/utils/activity/Makefile @@ -26,6 +26,7 @@ OBJS = \ pgstat_database.o \ pgstat_function.o \ pgstat_io.o \ + pgstat_lock.o \ pgstat_relation.o \ pgstat_replslot.o \ pgstat_shmem.o \ diff --git a/src/backend/utils/activity/meson.build b/src/backend/utils/activity/meson.build index 53bd5a246ca..1aa7ece5290 100644 --- a/src/backend/utils/activity/meson.build +++ b/src/backend/utils/activity/meson.build @@ -11,6 +11,7 @@ backend_sources += files( 'pgstat_database.c', 'pgstat_function.c', 'pgstat_io.c', + 'pgstat_lock.c', 'pgstat_relation.c', 'pgstat_replslot.c', 'pgstat_shmem.c', diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index 11bb71cad5a..eb8ccbaa628 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -83,6 +83,7 @@ * - pgstat_database.c * - pgstat_function.c * - pgstat_io.c + * - pgstat_lock.c * - pgstat_relation.c * - pgstat_replslot.c * - pgstat_slru.c @@ -448,6 +449,23 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .snapshot_cb = pgstat_io_snapshot_cb, }, + [PGSTAT_KIND_LOCK] = { + .name = "lock", + + .fixed_amount = true, + .write_to_file = true, + + .snapshot_ctl_off = offsetof(PgStat_Snapshot, lock), + .shared_ctl_off = offsetof(PgStat_ShmemControl, lock), + .shared_data_off = offsetof(PgStatShared_Lock, stats), + .shared_data_len = sizeof(((PgStatShared_Lock *) 0)->stats), + + .flush_static_cb = pgstat_lock_flush_cb, + .init_shmem_cb = pgstat_lock_init_shmem_cb, + .reset_all_cb = pgstat_lock_reset_all_cb, + .snapshot_cb = pgstat_lock_snapshot_cb, + }, + [PGSTAT_KIND_SLRU] = { .name = "slru", diff --git a/src/backend/utils/activity/pgstat_lock.c b/src/backend/utils/activity/pgstat_lock.c new file mode 100644 index 00000000000..7af4bc6d018 --- /dev/null +++ b/src/backend/utils/activity/pgstat_lock.c @@ -0,0 +1,162 @@ +/* ------------------------------------------------------------------------- + * + * pgstat_lock.c + * Implementation of lock statistics. + * + * This file contains the implementation of lock statistics. It is kept separate + * from pgstat.c to enforce the line between the statistics access / storage + * implementation and the details about individual types of statistics. + * + * Copyright (c) 2021-2025, PostgreSQL Global Development Group + * + * IDENTIFICATION + * src/backend/utils/activity/pgstat_lock.c + * ------------------------------------------------------------------------- + */ + +#include "postgres.h" + +#include "utils/pgstat_internal.h" + +static PgStat_PendingLock PendingLockStats; +static bool have_lockstats = false; + +/* + * Simpler wrapper of pgstat_lock_flush_cb() + */ +void +pgstat_lock_flush(bool nowait) +{ + (void) pgstat_lock_flush_cb(nowait); +} + +/* + * Flush out locally pending lock statistics + * + * If no stats have been recorded, this function returns false. + * + * If nowait is true, this function returns true if the lock could not be + * acquired. Otherwise, return false. + */ +bool +pgstat_lock_flush_cb(bool nowait) +{ + LWLock *lcktype_lock; + PgStat_LockEntry *lck_shstats; + bool lock_not_acquired = false; + + if (!have_lockstats) + return false; + + for (int i = 0; i <= LOCKTAG_LAST_TYPE; i++) + { + lcktype_lock = &pgStatLocal.shmem->lock.locks[i]; + lck_shstats = + &pgStatLocal.shmem->lock.stats.stats[i]; + + if (!nowait) + LWLockAcquire(lcktype_lock, LW_EXCLUSIVE); + else if (!LWLockConditionalAcquire(lcktype_lock, LW_EXCLUSIVE)) + { + lock_not_acquired = true; + continue; + } + +#define LOCKSTAT_ACC(fld) \ + (lck_shstats->fld += PendingLockStats.stats[i].fld) + LOCKSTAT_ACC(waits); + LOCKSTAT_ACC(wait_time); + LOCKSTAT_ACC(fastpath_exceeded); +#undef LOCKSTAT_ACC + + LWLockRelease(lcktype_lock); + } + + memset(&PendingLockStats, 0, sizeof(PendingLockStats)); + + have_lockstats = false; + + return lock_not_acquired; +} + + +void +pgstat_lock_init_shmem_cb(void *stats) +{ + PgStatShared_Lock *stat_shmem = (PgStatShared_Lock *) stats; + + for (int i = 0; i <= LOCKTAG_LAST_TYPE; i++) + LWLockInitialize(&stat_shmem->locks[i], LWTRANCHE_PGSTATS_DATA); +} + +void +pgstat_lock_reset_all_cb(TimestampTz ts) +{ + for (int i = 0; i <= LOCKTAG_LAST_TYPE; i++) + { + LWLock *lcktype_lock = &pgStatLocal.shmem->lock.locks[i]; + PgStat_LockEntry *lck_shstats = &pgStatLocal.shmem->lock.stats.stats[i]; + + LWLockAcquire(lcktype_lock, LW_EXCLUSIVE); + + /* + * Use the lock in the first lock type PgStat_LockEntry to protect the + * reset timestamp as well. + */ + if (i == 0) + pgStatLocal.shmem->lock.stats.stat_reset_timestamp = ts; + + memset(lck_shstats, 0, sizeof(*lck_shstats)); + LWLockRelease(lcktype_lock); + } +} + +void +pgstat_lock_snapshot_cb(void) +{ + for (int i = 0; i <= LOCKTAG_LAST_TYPE; i++) + { + LWLock *lcktype_lock = &pgStatLocal.shmem->lock.locks[i]; + PgStat_LockEntry *lck_shstats = &pgStatLocal.shmem->lock.stats.stats[i]; + PgStat_LockEntry *lck_snap = &pgStatLocal.snapshot.lock.stats[i]; + + LWLockAcquire(lcktype_lock, LW_SHARED); + + /* + * Use the lock in the first lock type PgStat_LockEntry to protect the + * reset timestamp as well. + */ + if (i == 0) + pgStatLocal.snapshot.lock.stat_reset_timestamp = + pgStatLocal.shmem->lock.stats.stat_reset_timestamp; + + /* using struct assignment due to better type safety */ + *lck_snap = *lck_shstats; + LWLockRelease(lcktype_lock); + } +} + +#define PGSTAT_COUNT_LOCK_FUNC(stat) \ +void \ +CppConcat(pgstat_count_lock_,stat)(uint8 locktag_type) \ +{ \ + Assert(locktag_type <= LOCKTAG_LAST_TYPE); \ + PendingLockStats.stats[locktag_type].stat++; \ + have_lockstats = true; \ + pgstat_report_fixed = true; \ +} + +/* pgstat_count_lock_waits */ +PGSTAT_COUNT_LOCK_FUNC(waits) + +/* pgstat_count_lock_fastpath_exceeded */ +PGSTAT_COUNT_LOCK_FUNC(fastpath_exceeded) + +void +pgstat_count_lock_wait_time(uint8 locktag_type, long msecs) +{ + Assert(locktag_type <= LOCKTAG_LAST_TYPE); + PendingLockStats.stats[locktag_type].wait_time += (PgStat_Counter) msecs; + have_lockstats = true; + pgstat_report_fixed = true; +} diff --git a/src/include/pgstat.h b/src/include/pgstat.h index fff7ecc2533..dfbead474b1 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -17,6 +17,7 @@ #include "postmaster/pgarch.h" /* for MAX_XFN_CHARS */ #include "replication/conflict.h" #include "replication/worker_internal.h" +#include "storage/lock.h" #include "utils/backend_progress.h" /* for backward compatibility */ /* IWYU pragma: export */ #include "utils/backend_status.h" /* for backward compatibility */ /* IWYU pragma: export */ #include "utils/pgstat_kind.h" @@ -342,6 +343,24 @@ typedef struct PgStat_IO PgStat_BktypeIO stats[BACKEND_NUM_TYPES]; } PgStat_IO; +typedef struct PgStat_LockEntry +{ + PgStat_Counter waits; + PgStat_Counter wait_time; /* time in milliseconds */ + PgStat_Counter fastpath_exceeded; +} PgStat_LockEntry; + +typedef struct PgStat_PendingLock +{ + PgStat_LockEntry stats[LOCKTAG_LAST_TYPE + 1]; +} PgStat_PendingLock; + +typedef struct PgStat_Lock +{ + TimestampTz stat_reset_timestamp; + PgStat_LockEntry stats[LOCKTAG_LAST_TYPE + 1]; +} PgStat_Lock; + typedef struct PgStat_StatDBEntry { PgStat_Counter xact_commit; @@ -614,6 +633,15 @@ extern bool pgstat_tracks_io_op(BackendType bktype, IOObject io_object, IOContext io_context, IOOp io_op); +/* + * Functions in pgstat_lock.c + */ + +extern void pgstat_lock_flush(bool nowait); +extern void pgstat_count_lock_waits(uint8 locktag_type); +extern void pgstat_count_lock_fastpath_exceeded(uint8 locktag_type); +extern void pgstat_count_lock_wait_time(uint8 locktag_type, long msecs); + /* * Functions in pgstat_database.c */ diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index 9b8fbae00ed..97704421a92 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -464,6 +464,16 @@ typedef struct PgStatShared_IO PgStat_IO stats; } PgStatShared_IO; +typedef struct PgStatShared_Lock +{ + /* + * locks[i] protects stats.stats[i]. locks[0] also protects + * stats.stat_reset_timestamp. + */ + LWLock locks[LOCKTAG_LAST_TYPE + 1]; + PgStat_Lock stats; +} PgStatShared_Lock; + typedef struct PgStatShared_SLRU { /* lock protects ->stats */ @@ -570,6 +580,7 @@ typedef struct PgStat_ShmemControl PgStatShared_BgWriter bgwriter; PgStatShared_Checkpointer checkpointer; PgStatShared_IO io; + PgStatShared_Lock lock; PgStatShared_SLRU slru; PgStatShared_Wal wal; @@ -602,6 +613,8 @@ typedef struct PgStat_Snapshot PgStat_IO io; + PgStat_Lock lock; + PgStat_SLRUStats slru[SLRU_NUM_ELEMENTS]; PgStat_WalStats wal; @@ -752,6 +765,14 @@ extern void pgstat_io_init_shmem_cb(void *stats); extern void pgstat_io_reset_all_cb(TimestampTz ts); extern void pgstat_io_snapshot_cb(void); +/* + * Functions in pgstat_lock.c + */ + +extern bool pgstat_lock_flush_cb(bool nowait); +extern void pgstat_lock_init_shmem_cb(void *stats); +extern void pgstat_lock_reset_all_cb(TimestampTz ts); +extern void pgstat_lock_snapshot_cb(void); /* * Functions in pgstat_relation.c diff --git a/src/include/utils/pgstat_kind.h b/src/include/utils/pgstat_kind.h index c30b6235623..2d78a029683 100644 --- a/src/include/utils/pgstat_kind.h +++ b/src/include/utils/pgstat_kind.h @@ -36,8 +36,9 @@ #define PGSTAT_KIND_BGWRITER 8 #define PGSTAT_KIND_CHECKPOINTER 9 #define PGSTAT_KIND_IO 10 -#define PGSTAT_KIND_SLRU 11 -#define PGSTAT_KIND_WAL 12 +#define PGSTAT_KIND_LOCK 11 +#define PGSTAT_KIND_SLRU 12 +#define PGSTAT_KIND_WAL 13 #define PGSTAT_KIND_BUILTIN_MIN PGSTAT_KIND_DATABASE #define PGSTAT_KIND_BUILTIN_MAX PGSTAT_KIND_WAL diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 241945734ec..0432862061b 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2255,6 +2255,7 @@ PgStatShared_Database PgStatShared_Function PgStatShared_HashEntry PgStatShared_IO +PgStatShared_Lock PgStatShared_Relation PgStatShared_ReplSlot PgStatShared_SLRU @@ -2277,8 +2278,11 @@ PgStat_HashKey PgStat_IO PgStat_KindInfo PgStat_LocalState +PgStat_Lock +PgStat_LockEntry PgStat_PendingDroppedStatsItem PgStat_PendingIO +PgStat_PendingLock PgStat_SLRUStats PgStat_ShmemControl PgStat_Snapshot -- 2.34.1
>From 85f170e565302f73e97cbbc5d7e20eeae206c6e9 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Thu, 31 Jul 2025 09:35:31 +0000 Subject: [PATCH v5 2/2] Add the pg_stat_lock view This new view reports lock statistics. Note that it does not omit combinations which do not make sense (as pg_locks does). Also wait_time is reported as bigint as the deadlock_timeout default value is 1s. This commit also adds documentation and a few tests. XXX: Bump catversion --- doc/src/sgml/monitoring.sgml | 113 +++++++++++++++++ src/backend/catalog/system_views.sql | 9 ++ src/backend/utils/activity/pgstat_lock.c | 8 ++ src/backend/utils/adt/pgstatfuncs.c | 39 ++++++ src/include/catalog/pg_proc.dat | 9 ++ src/include/pgstat.h | 1 + src/test/isolation/expected/stats.out | 147 +++++++++++++++++++++++ src/test/isolation/expected/stats_1.out | 147 +++++++++++++++++++++++ src/test/isolation/specs/stats.spec | 76 ++++++++++++ src/test/regress/expected/rules.out | 6 + src/test/regress/expected/stats.out | 42 +++++++ src/test/regress/sql/stats.sql | 30 +++++ 12 files changed, 627 insertions(+) 18.8% doc/src/sgml/ 5.9% src/backend/utils/adt/ 49.4% src/test/isolation/expected/ 11.3% src/test/isolation/specs/ 6.0% src/test/regress/expected/ 4.1% src/test/regress/sql/ 4.3% src/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index b77d189a500..3a196bc305c 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -493,6 +493,15 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser </entry> </row> + <row> + <entry><structname>pg_stat_lock</structname><indexterm><primary>pg_stat_lock</primary></indexterm></entry> + <entry> + One row for each lock type, containing cluster-wide locks statistics. + See <link linkend="monitoring-pg-stat-lock-view"> + <structname>pg_stat_lock</structname></link> for details. + </entry> + </row> + <row> <entry><structname>pg_stat_replication_slots</structname><indexterm><primary>pg_stat_replication_slots</primary></indexterm></entry> <entry>One row per replication slot, showing statistics about the @@ -3124,6 +3133,104 @@ description | Waiting for a newly initialized WAL file to reach durable storage </sect2> + + <sect2 id="monitoring-pg-stat-lock-view"> + <title><structname>pg_stat_lock</structname></title> + + <indexterm> + <primary>pg_stat_lock</primary> + </indexterm> + + <para> + The <structname>pg_stat_lock</structname> view will contain one row for each + lock type, showing cluster-wide locks statistics. + </para> + + <table id="pg-stat-lock-view" xreflabel="pg_stat_lock"> + <title><structname>pg_stat_lock</structname> View</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"> + <para role="column_definition"> + Column Type + </para> + <para> + Description + </para> + </entry> + </row> + </thead> + <tbody> + <row> + <entry role="catalog_table_entry"> + <para role="column_definition"> + <structfield>locktype</structfield> <type>text</type> + </para> + <para> + Type of the lockable object. See <link linkend="view-pg-locks"> + <structname>pg_locks</structname></link> for details. + </para> + </entry> + </row> + + <row> + <entry role="catalog_table_entry"> + <para role="column_definition"> + <structfield>waits</structfield> <type>bigint</type> + </para> + <para> + Number of times a lock of this type had to wait because of a + conflicting lock. Only incremented when <xref linkend="guc-log-lock-waits"/> + is enabled and the lock was successfully acquired after waiting longer + than <xref linkend="guc-deadlock-timeout"/>. + </para> + </entry> + </row> + + <row> + <entry role="catalog_table_entry"> + <para role="column_definition"> + <structfield>wait_time</structfield> <type>bigint</type> + </para> + <para> + Total time spent waiting for locks of this type, in milliseconds. + Only incremented when <xref linkend="guc-log-lock-waits"/> is enabled and + the lock was successfully acquired after waiting longer than + <xref linkend="guc-deadlock-timeout"/>. + </para> + </entry> + </row> + + <row> + <entry role="catalog_table_entry"> + <para role="column_definition"> + <structfield>fastpath_exceeded</structfield> <type>bigint</type> + </para> + <para> + Number of times a lock of this type could not be acquired via fast path + because the fast path slot limit was exceeded. You may want to increase + <xref linkend="guc-max-locks-per-transaction"/> if you feel this counter + is too high. + </para> + </entry> + </row> + + <row> + <entry role="catalog_table_entry"> + <para role="column_definition"> + <structfield>stats_reset</structfield> <type>timestamp with time zone</type> + </para> + <para> + Time at which these statistics were last reset. + </para> + </entry> + </row> + </tbody> + </tgroup> + </table> + </sect2> + <sect2 id="monitoring-pg-stat-bgwriter-view"> <title><structname>pg_stat_bgwriter</structname></title> @@ -5195,6 +5302,12 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structname>pg_stat_io</structname> view. </para> </listitem> + <listitem> + <para> + <literal>lock</literal>: Reset all the counters shown in the + <structname>pg_stat_lock</structname> view. + </para> + </listitem> <listitem> <para> <literal>recovery_prefetch</literal>: Reset all the counters shown in diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 1ea8f1faa9e..22efeb713e4 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -985,6 +985,15 @@ CREATE VIEW pg_stat_slru AS s.stats_reset FROM pg_stat_get_slru() s; +CREATE VIEW pg_stat_lock AS + SELECT + l.locktype, + l.waits, + l.wait_time, + l.fastpath_exceeded, + l.stats_reset + FROM pg_stat_get_lock() l; + CREATE VIEW pg_stat_wal_receiver AS SELECT s.pid, diff --git a/src/backend/utils/activity/pgstat_lock.c b/src/backend/utils/activity/pgstat_lock.c index 7af4bc6d018..cd4399b5ef6 100644 --- a/src/backend/utils/activity/pgstat_lock.c +++ b/src/backend/utils/activity/pgstat_lock.c @@ -21,6 +21,14 @@ static PgStat_PendingLock PendingLockStats; static bool have_lockstats = false; +PgStat_Lock * +pgstat_fetch_stat_lock(void) +{ + pgstat_snapshot_fixed(PGSTAT_KIND_LOCK); + + return &pgStatLocal.snapshot.lock; +} + /* * Simpler wrapper of pgstat_lock_flush_cb() */ diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index b1df96e7b0b..27ca2d8f07c 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1737,6 +1737,42 @@ pg_stat_get_wal(PG_FUNCTION_ARGS) wal_stats->stat_reset_timestamp)); } +Datum +pg_stat_get_lock(PG_FUNCTION_ARGS) +{ +#define PG_STAT_LOCK_COLS 5 + ReturnSetInfo *rsinfo; + PgStat_Lock *lock_stats; + + InitMaterializedSRF(fcinfo, 0); + rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + + lock_stats = pgstat_fetch_stat_lock(); + + for (int lcktype = 0; lcktype <= LOCKTAG_LAST_TYPE; lcktype++) + { + const char *locktypename; + Datum values[PG_STAT_LOCK_COLS] = {0}; + bool nulls[PG_STAT_LOCK_COLS] = {0}; + PgStat_LockEntry *lck_stats = &lock_stats->stats[lcktype]; + int i = 0; + + locktypename = LockTagTypeNames[lcktype]; + + values[i++] = CStringGetTextDatum(locktypename); + values[i++] = Int64GetDatum(lck_stats->waits); + values[i++] = Int64GetDatum(lck_stats->wait_time); + values[i++] = Int64GetDatum(lck_stats->fastpath_exceeded); + values[i] = TimestampTzGetDatum(lock_stats->stat_reset_timestamp); + + Assert(i + 1 == PG_STAT_LOCK_COLS); + + tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls); + } + + return (Datum) 0; +} + /* * Returns statistics of SLRU caches. */ @@ -1921,6 +1957,7 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS) pgstat_reset_of_kind(PGSTAT_KIND_BGWRITER); pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER); pgstat_reset_of_kind(PGSTAT_KIND_IO); + pgstat_reset_of_kind(PGSTAT_KIND_LOCK); XLogPrefetchResetStats(); pgstat_reset_of_kind(PGSTAT_KIND_SLRU); pgstat_reset_of_kind(PGSTAT_KIND_WAL); @@ -1938,6 +1975,8 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS) pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER); else if (strcmp(target, "io") == 0) pgstat_reset_of_kind(PGSTAT_KIND_IO); + else if (strcmp(target, "lock") == 0) + pgstat_reset_of_kind(PGSTAT_KIND_LOCK); else if (strcmp(target, "recovery_prefetch") == 0) XLogPrefetchResetStats(); else if (strcmp(target, "slru") == 0) diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 83f6501df38..c224e842e32 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -6026,6 +6026,15 @@ proargnames => '{backend_type,object,context,reads,read_bytes,read_time,writes,write_bytes,write_time,writebacks,writeback_time,extends,extend_bytes,extend_time,hits,evictions,reuses,fsyncs,fsync_time,stats_reset}', prosrc => 'pg_stat_get_io' }, +{ oid => '9375', descr => 'statistics: per lock type statistics', + proname => 'pg_stat_get_lock', prorows => '10', proretset => 't', + provolatile => 'v', proparallel => 'r', prorettype => 'record', + proargtypes => '', + proallargtypes => '{text,int8,int8,int8,timestamptz}', + proargmodes => '{o,o,o,o,o}', + proargnames => '{locktype,waits,wait_time,fastpath_exceeded,stats_reset}', + prosrc => 'pg_stat_get_lock' }, + { oid => '6386', descr => 'statistics: backend IO statistics', proname => 'pg_stat_get_backend_io', prorows => '5', proretset => 't', provolatile => 'v', proparallel => 'r', prorettype => 'record', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index dfbead474b1..175e431ed81 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -641,6 +641,7 @@ extern void pgstat_lock_flush(bool nowait); extern void pgstat_count_lock_waits(uint8 locktag_type); extern void pgstat_count_lock_fastpath_exceeded(uint8 locktag_type); extern void pgstat_count_lock_wait_time(uint8 locktag_type, long msecs); +extern PgStat_Lock *pgstat_fetch_stat_lock(void); /* * Functions in pgstat_database.c diff --git a/src/test/isolation/expected/stats.out b/src/test/isolation/expected/stats.out index cfad309ccf3..d3cb8715d95 100644 --- a/src/test/isolation/expected/stats.out +++ b/src/test/isolation/expected/stats.out @@ -3751,3 +3751,150 @@ test_stat_func| 1|t |t (1 row) step s1_commit: COMMIT; + +starting permutation: s1_set_deadlock_timeout s1_reset_stat_lock s1_set_log_lock_waits s2_set_deadlock_timeout s2_set_log_lock_waits s1_begin s1_lock_relation s2_begin s2_ff s2_lock_relation s1_sleep s1_commit s2_commit s2_report_stat_lock_relation +pg_stat_force_next_flush +------------------------ + +(1 row) + +step s1_set_deadlock_timeout: SET deadlock_timeout = '10ms'; +step s1_reset_stat_lock: SELECT pg_stat_reset_shared('lock'); +pg_stat_reset_shared +-------------------- + +(1 row) + +step s1_set_log_lock_waits: SET log_lock_waits = on; +step s2_set_deadlock_timeout: SET deadlock_timeout = '10ms'; +step s2_set_log_lock_waits: SET log_lock_waits = on; +step s1_begin: BEGIN; +step s1_lock_relation: LOCK TABLE test_stat_tab; +step s2_begin: BEGIN; +step s2_ff: SELECT pg_stat_force_next_flush(); +pg_stat_force_next_flush +------------------------ + +(1 row) + +step s2_lock_relation: LOCK TABLE test_stat_tab; <waiting ...> +step s1_sleep: SELECT pg_sleep(0.5); +pg_sleep +-------- + +(1 row) + +step s1_commit: COMMIT; +step s2_lock_relation: <... completed> +step s2_commit: COMMIT; +step s2_report_stat_lock_relation: SELECT waits > 0, wait_time > 500 FROM pg_stat_lock WHERE locktype = 'relation'; +?column?|?column? +--------+-------- +t |t +(1 row) + + +starting permutation: s1_set_deadlock_timeout s1_reset_stat_lock s1_set_log_lock_waits s2_set_deadlock_timeout s2_set_log_lock_waits s1_table_insert s1_begin s1_table_update_k1 s2_begin s2_ff s2_table_update_k1 s1_sleep s1_commit s2_commit s2_report_stat_lock_transactionid +pg_stat_force_next_flush +------------------------ + +(1 row) + +step s1_set_deadlock_timeout: SET deadlock_timeout = '10ms'; +step s1_reset_stat_lock: SELECT pg_stat_reset_shared('lock'); +pg_stat_reset_shared +-------------------- + +(1 row) + +step s1_set_log_lock_waits: SET log_lock_waits = on; +step s2_set_deadlock_timeout: SET deadlock_timeout = '10ms'; +step s2_set_log_lock_waits: SET log_lock_waits = on; +step s1_table_insert: INSERT INTO test_stat_tab(key, value) VALUES('k1', 1), ('k2', 1), ('k3', 1); +step s1_begin: BEGIN; +step s1_table_update_k1: UPDATE test_stat_tab SET value = value + 1 WHERE key = 'k1'; +step s2_begin: BEGIN; +step s2_ff: SELECT pg_stat_force_next_flush(); +pg_stat_force_next_flush +------------------------ + +(1 row) + +step s2_table_update_k1: UPDATE test_stat_tab SET value = value + 1 WHERE key = 'k1'; <waiting ...> +step s1_sleep: SELECT pg_sleep(0.5); +pg_sleep +-------- + +(1 row) + +step s1_commit: COMMIT; +step s2_table_update_k1: <... completed> +step s2_commit: COMMIT; +step s2_report_stat_lock_transactionid: SELECT waits > 0, wait_time > 500 FROM pg_stat_lock WHERE locktype = 'transactionid'; +?column?|?column? +--------+-------- +t |t +(1 row) + + +starting permutation: s1_set_deadlock_timeout s1_reset_stat_lock s1_set_log_lock_waits s2_set_deadlock_timeout s2_set_log_lock_waits s1_lock_advisory_lock s2_begin s2_ff s2_lock_advisory_lock s1_sleep s1_lock_advisory_unlock s2_lock_advisory_unlock s2_commit s2_report_stat_lock_advisory +pg_stat_force_next_flush +------------------------ + +(1 row) + +step s1_set_deadlock_timeout: SET deadlock_timeout = '10ms'; +step s1_reset_stat_lock: SELECT pg_stat_reset_shared('lock'); +pg_stat_reset_shared +-------------------- + +(1 row) + +step s1_set_log_lock_waits: SET log_lock_waits = on; +step s2_set_deadlock_timeout: SET deadlock_timeout = '10ms'; +step s2_set_log_lock_waits: SET log_lock_waits = on; +step s1_lock_advisory_lock: SELECT pg_advisory_lock(1); +pg_advisory_lock +---------------- + +(1 row) + +step s2_begin: BEGIN; +step s2_ff: SELECT pg_stat_force_next_flush(); +pg_stat_force_next_flush +------------------------ + +(1 row) + +step s2_lock_advisory_lock: SELECT pg_advisory_lock(1); <waiting ...> +step s1_sleep: SELECT pg_sleep(0.5); +pg_sleep +-------- + +(1 row) + +step s1_lock_advisory_unlock: SELECT pg_advisory_unlock(1); +pg_advisory_unlock +------------------ +t +(1 row) + +step s2_lock_advisory_lock: <... completed> +pg_advisory_lock +---------------- + +(1 row) + +step s2_lock_advisory_unlock: SELECT pg_advisory_unlock(1); +pg_advisory_unlock +------------------ +t +(1 row) + +step s2_commit: COMMIT; +step s2_report_stat_lock_advisory: SELECT waits > 0, wait_time > 500 FROM pg_stat_lock WHERE locktype = 'advisory'; +?column?|?column? +--------+-------- +t |t +(1 row) + diff --git a/src/test/isolation/expected/stats_1.out b/src/test/isolation/expected/stats_1.out index e1d937784cb..89d2e4cc6b4 100644 --- a/src/test/isolation/expected/stats_1.out +++ b/src/test/isolation/expected/stats_1.out @@ -3775,3 +3775,150 @@ test_stat_func| 1|t |t (1 row) step s1_commit: COMMIT; + +starting permutation: s1_set_deadlock_timeout s1_reset_stat_lock s1_set_log_lock_waits s2_set_deadlock_timeout s2_set_log_lock_waits s1_begin s1_lock_relation s2_begin s2_ff s2_lock_relation s1_sleep s1_commit s2_commit s2_report_stat_lock_relation +pg_stat_force_next_flush +------------------------ + +(1 row) + +step s1_set_deadlock_timeout: SET deadlock_timeout = '10ms'; +step s1_reset_stat_lock: SELECT pg_stat_reset_shared('lock'); +pg_stat_reset_shared +-------------------- + +(1 row) + +step s1_set_log_lock_waits: SET log_lock_waits = on; +step s2_set_deadlock_timeout: SET deadlock_timeout = '10ms'; +step s2_set_log_lock_waits: SET log_lock_waits = on; +step s1_begin: BEGIN; +step s1_lock_relation: LOCK TABLE test_stat_tab; +step s2_begin: BEGIN; +step s2_ff: SELECT pg_stat_force_next_flush(); +pg_stat_force_next_flush +------------------------ + +(1 row) + +step s2_lock_relation: LOCK TABLE test_stat_tab; <waiting ...> +step s1_sleep: SELECT pg_sleep(0.5); +pg_sleep +-------- + +(1 row) + +step s1_commit: COMMIT; +step s2_lock_relation: <... completed> +step s2_commit: COMMIT; +step s2_report_stat_lock_relation: SELECT waits > 0, wait_time > 500 FROM pg_stat_lock WHERE locktype = 'relation'; +?column?|?column? +--------+-------- +t |t +(1 row) + + +starting permutation: s1_set_deadlock_timeout s1_reset_stat_lock s1_set_log_lock_waits s2_set_deadlock_timeout s2_set_log_lock_waits s1_table_insert s1_begin s1_table_update_k1 s2_begin s2_ff s2_table_update_k1 s1_sleep s1_commit s2_commit s2_report_stat_lock_transactionid +pg_stat_force_next_flush +------------------------ + +(1 row) + +step s1_set_deadlock_timeout: SET deadlock_timeout = '10ms'; +step s1_reset_stat_lock: SELECT pg_stat_reset_shared('lock'); +pg_stat_reset_shared +-------------------- + +(1 row) + +step s1_set_log_lock_waits: SET log_lock_waits = on; +step s2_set_deadlock_timeout: SET deadlock_timeout = '10ms'; +step s2_set_log_lock_waits: SET log_lock_waits = on; +step s1_table_insert: INSERT INTO test_stat_tab(key, value) VALUES('k1', 1), ('k2', 1), ('k3', 1); +step s1_begin: BEGIN; +step s1_table_update_k1: UPDATE test_stat_tab SET value = value + 1 WHERE key = 'k1'; +step s2_begin: BEGIN; +step s2_ff: SELECT pg_stat_force_next_flush(); +pg_stat_force_next_flush +------------------------ + +(1 row) + +step s2_table_update_k1: UPDATE test_stat_tab SET value = value + 1 WHERE key = 'k1'; <waiting ...> +step s1_sleep: SELECT pg_sleep(0.5); +pg_sleep +-------- + +(1 row) + +step s1_commit: COMMIT; +step s2_table_update_k1: <... completed> +step s2_commit: COMMIT; +step s2_report_stat_lock_transactionid: SELECT waits > 0, wait_time > 500 FROM pg_stat_lock WHERE locktype = 'transactionid'; +?column?|?column? +--------+-------- +t |t +(1 row) + + +starting permutation: s1_set_deadlock_timeout s1_reset_stat_lock s1_set_log_lock_waits s2_set_deadlock_timeout s2_set_log_lock_waits s1_lock_advisory_lock s2_begin s2_ff s2_lock_advisory_lock s1_sleep s1_lock_advisory_unlock s2_lock_advisory_unlock s2_commit s2_report_stat_lock_advisory +pg_stat_force_next_flush +------------------------ + +(1 row) + +step s1_set_deadlock_timeout: SET deadlock_timeout = '10ms'; +step s1_reset_stat_lock: SELECT pg_stat_reset_shared('lock'); +pg_stat_reset_shared +-------------------- + +(1 row) + +step s1_set_log_lock_waits: SET log_lock_waits = on; +step s2_set_deadlock_timeout: SET deadlock_timeout = '10ms'; +step s2_set_log_lock_waits: SET log_lock_waits = on; +step s1_lock_advisory_lock: SELECT pg_advisory_lock(1); +pg_advisory_lock +---------------- + +(1 row) + +step s2_begin: BEGIN; +step s2_ff: SELECT pg_stat_force_next_flush(); +pg_stat_force_next_flush +------------------------ + +(1 row) + +step s2_lock_advisory_lock: SELECT pg_advisory_lock(1); <waiting ...> +step s1_sleep: SELECT pg_sleep(0.5); +pg_sleep +-------- + +(1 row) + +step s1_lock_advisory_unlock: SELECT pg_advisory_unlock(1); +pg_advisory_unlock +------------------ +t +(1 row) + +step s2_lock_advisory_lock: <... completed> +pg_advisory_lock +---------------- + +(1 row) + +step s2_lock_advisory_unlock: SELECT pg_advisory_unlock(1); +pg_advisory_unlock +------------------ +t +(1 row) + +step s2_commit: COMMIT; +step s2_report_stat_lock_advisory: SELECT waits > 0, wait_time > 500 FROM pg_stat_lock WHERE locktype = 'advisory'; +?column?|?column? +--------+-------- +t |t +(1 row) + diff --git a/src/test/isolation/specs/stats.spec b/src/test/isolation/specs/stats.spec index da16710da0f..c600fc11157 100644 --- a/src/test/isolation/specs/stats.spec +++ b/src/test/isolation/specs/stats.spec @@ -130,6 +130,14 @@ step s1_slru_check_stats { WHERE before.stat = 'blks_zeroed'; } +# Lock stats steps +step s1_set_deadlock_timeout { SET deadlock_timeout = '10ms'; } +step s1_set_log_lock_waits { SET log_lock_waits = on; } +step s1_reset_stat_lock { SELECT pg_stat_reset_shared('lock'); } +step s1_sleep { SELECT pg_sleep(0.5); } +step s1_lock_relation { LOCK TABLE test_stat_tab; } +step s1_lock_advisory_lock { SELECT pg_advisory_lock(1); } +step s1_lock_advisory_unlock { SELECT pg_advisory_unlock(1); } session s2 setup { SET stats_fetch_consistency = 'none'; } @@ -164,6 +172,15 @@ step s2_big_notify { SELECT pg_notify('stats_test_use', repeat(i::text, current_setting('block_size')::int / 2)) FROM generate_series(1, 3) g(i); } +# Lock stats steps +step s2_set_deadlock_timeout { SET deadlock_timeout = '10ms'; } +step s2_set_log_lock_waits { SET log_lock_waits = on; } +step s2_report_stat_lock_relation { SELECT waits > 0, wait_time > 500 FROM pg_stat_lock WHERE locktype = 'relation'; } +step s2_report_stat_lock_transactionid { SELECT waits > 0, wait_time > 500 FROM pg_stat_lock WHERE locktype = 'transactionid'; } +step s2_report_stat_lock_advisory { SELECT waits > 0, wait_time > 500 FROM pg_stat_lock WHERE locktype = 'advisory'; } +step s2_lock_relation { LOCK TABLE test_stat_tab; } +step s2_lock_advisory_lock { SELECT pg_advisory_lock(1); } +step s2_lock_advisory_unlock { SELECT pg_advisory_unlock(1); } ###################### # Function stats tests @@ -765,3 +782,62 @@ permutation s1_clear_snapshot s1_func_stats s1_commit + +###################### +# Lock stats tests +###################### + +# relation lock + +permutation + s1_set_deadlock_timeout + s1_reset_stat_lock + s1_set_log_lock_waits + s2_set_deadlock_timeout + s2_set_log_lock_waits + s1_begin + s1_lock_relation + s2_begin + s2_ff + s2_lock_relation + s1_sleep + s1_commit + s2_commit + s2_report_stat_lock_relation + +# transaction lock + +permutation + s1_set_deadlock_timeout + s1_reset_stat_lock + s1_set_log_lock_waits + s2_set_deadlock_timeout + s2_set_log_lock_waits + s1_table_insert + s1_begin + s1_table_update_k1 + s2_begin + s2_ff + s2_table_update_k1 + s1_sleep + s1_commit + s2_commit + s2_report_stat_lock_transactionid + +# advisory lock + +permutation + s1_set_deadlock_timeout + s1_reset_stat_lock + s1_set_log_lock_waits + s2_set_deadlock_timeout + s2_set_log_lock_waits + s1_lock_advisory_lock + s2_begin + s2_ff + s2_lock_advisory_lock + s1_sleep + s1_lock_advisory_unlock + s2_lock_advisory_unlock + s2_commit + s2_report_stat_lock_advisory diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 78a37d9fc8f..c013fbebb88 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1951,6 +1951,12 @@ pg_stat_io| SELECT backend_type, fsync_time, stats_reset FROM pg_stat_get_io() b(backend_type, object, context, reads, read_bytes, read_time, writes, write_bytes, write_time, writebacks, writeback_time, extends, extend_bytes, extend_time, hits, evictions, reuses, fsyncs, fsync_time, stats_reset); +pg_stat_lock| SELECT locktype, + waits, + wait_time, + fastpath_exceeded, + stats_reset + FROM pg_stat_get_lock() l(locktype, waits, wait_time, fastpath_exceeded, stats_reset); pg_stat_progress_analyze| SELECT s.pid, s.datid, d.datname, diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index cd00f35bf7a..ddb016e73d2 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -1910,4 +1910,46 @@ SELECT * FROM check_estimated_rows('SELECT * FROM table_fillfactor'); (1 row) DROP TABLE table_fillfactor; +-- Test fastpath_exceeded stat +CREATE TABLE part_test (id int) PARTITION BY RANGE (id); +SELECT pg_stat_reset_shared('lock'); + pg_stat_reset_shared +---------------------- + +(1 row) + +-- Create 70 partitions (exceeds default number of slots) +DO $$ +BEGIN + FOR i IN 1..70 LOOP + EXECUTE format( + 'CREATE TABLE part_test_%s PARTITION OF part_test + FOR VALUES FROM (%s) TO (%s)', + i, (i-1)*1000, i*1000 + ); + END LOOP; +END; +$$; +SELECT fastpath_exceeded AS fastpath_exceeded_before FROM pg_stat_lock WHERE locktype = 'relation' \gset +-- Needs a lock on each partition +SELECT count(*) FROM part_test; + count +------- + 0 +(1 row) + +-- Ensure pending stats are flushed +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +SELECT fastpath_exceeded > :fastpath_exceeded_before FROM pg_stat_lock WHERE locktype = 'relation'; + ?column? +---------- + t +(1 row) + +DROP TABLE part_test; -- End of Stats Test diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 8768e0f27fd..fac7d734758 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -944,4 +944,34 @@ SELECT * FROM check_estimated_rows('SELECT * FROM table_fillfactor'); DROP TABLE table_fillfactor; +-- Test fastpath_exceeded stat +CREATE TABLE part_test (id int) PARTITION BY RANGE (id); + +SELECT pg_stat_reset_shared('lock'); + +-- Create 70 partitions (exceeds default number of slots) +DO $$ +BEGIN + FOR i IN 1..70 LOOP + EXECUTE format( + 'CREATE TABLE part_test_%s PARTITION OF part_test + FOR VALUES FROM (%s) TO (%s)', + i, (i-1)*1000, i*1000 + ); + END LOOP; +END; +$$; + +SELECT fastpath_exceeded AS fastpath_exceeded_before FROM pg_stat_lock WHERE locktype = 'relation' \gset + +-- Needs a lock on each partition +SELECT count(*) FROM part_test; + +-- Ensure pending stats are flushed +SELECT pg_stat_force_next_flush(); + +SELECT fastpath_exceeded > :fastpath_exceeded_before FROM pg_stat_lock WHERE locktype = 'relation'; + +DROP TABLE part_test; + -- End of Stats Test -- 2.34.1
