Hi, On Fri, Feb 13, 2026 at 04:36:57PM +0900, Michael Paquier wrote: > On Tue, Feb 10, 2026 at 07:30:50AM +0000, Bertrand Drouvot wrote: > > New rebase due to 73d60ac385a. > > I have been looking at this patch, and can get behind the data > gathered here in terms of being able to tune things
Thanks! > > So my suggestion for the moment would be to be more frugal (yeah I > know, sorry..) and limit ourselves to four fields: deadlock_timeout, > requests, fastpath and timeouts. Three fields to compare with > requests, one for each GUC. That's fine by me. We could still add the others in the future if we feel the need. Done that way in the attached. > Regarding the implementation, you are right to use a fixed-sized stats > kind for the job. I can see a lot of code has been copy-pasted from > pgstat_io.c, then slightly adjusted to fit into the picture. That's > fine here, it makes the implementation straight-forward to read. Yeah, no need to reinvent the wheel. > Regarding the documentation, listing all the values for locktype is a > recipe for rot. I'd suggest to remove the list instead, with only a > link referring to pg_locks to avoid the duplication. Makes sense, done that way. Makes me think that we could do the same for pg_locks and just link it to the Wait Events of Type Lock? (Table 27.11.) Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
>From 7e7f2cecfedce71c5aa68ac6843a003540ee33e2 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Tue, 29 Jul 2025 08:36:35 +0000 Subject: [PATCH v4 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: requests timeouts deadlock_timeouts fastpath These counters enable tuning of lock related GUCs (max_locks_per_transaction, lock_timeout, and deadlock_timeout). 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 | 12 ++ src/backend/storage/lmgr/proc.c | 2 + src/backend/tcop/postgres.c | 8 ++ 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 | 160 +++++++++++++++++++++++ src/include/pgstat.h | 30 +++++ src/include/utils/pgstat_internal.h | 21 +++ src/include/utils/pgstat_kind.h | 5 +- src/tools/pgindent/typedefs.list | 4 + 11 files changed, 260 insertions(+), 2 deletions(-) 9.7% src/backend/storage/lmgr/ 3.0% src/backend/tcop/ 66.2% src/backend/utils/activity/ 9.1% src/include/utils/ 10.9% src/include/ diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c index e1168ad3837..30537ccfade 100644 --- a/src/backend/storage/lmgr/lock.c +++ b/src/backend/storage/lmgr/lock.c @@ -45,6 +45,7 @@ #include "storage/spin.h" #include "storage/standby.h" #include "utils/memutils.h" +#include "utils/pgstat_internal.h" #include "utils/ps_status.h" #include "utils/resowner.h" @@ -871,6 +872,9 @@ LockAcquireExtended(const LOCKTAG *locktag, lockMethodTable->lockModeNames[lockmode]), errhint("Only RowExclusiveLock or less can be acquired on database objects during recovery."))); + /* Increment the lock statistics requests counter */ + pgstat_count_lock_requests(locktag->locktag_type); + #ifdef LOCK_DEBUG if (LOCK_DEBUG_ENABLED(locktag)) elog(LOG, "LockAcquire: lock [%u,%u] %s", @@ -2799,6 +2803,8 @@ FastPathGrantRelationLock(Oid relid, LOCKMODE lockmode) { Assert(!FAST_PATH_CHECK_LOCKMODE(MyProc, f, lockmode)); FAST_PATH_SET_LOCKMODE(MyProc, f, lockmode); + /* Increment the lock statistics fastpath counter */ + pgstat_count_lock_fastpath(LOCKTAG_RELATION); return true; } } @@ -2808,6 +2814,8 @@ FastPathGrantRelationLock(Oid relid, LOCKMODE lockmode) { MyProc->fpRelId[unused_slot] = relid; FAST_PATH_SET_LOCKMODE(MyProc, unused_slot, lockmode); + /* Increment the lock statistics fastpath counter */ + pgstat_count_lock_fastpath(LOCKTAG_RELATION); ++FastPathLocalUseCounts[group]; return true; } @@ -4629,6 +4637,10 @@ VirtualXactLockTableInsert(VirtualTransactionId vxid) MyProc->fpVXIDLock = true; MyProc->fpLocalTransactionId = vxid.localTransactionId; + /* Increment the lock statistics requests and fastpath counters */ + pgstat_count_lock_requests(LOCKTAG_VIRTUALTRANSACTION); + pgstat_count_lock_fastpath(LOCKTAG_VIRTUALTRANSACTION); + LWLockRelease(&MyProc->fpInfoLock); } diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c index fd8318bdf3d..5eee8d1f69d 100644 --- a/src/backend/storage/lmgr/proc.c +++ b/src/backend/storage/lmgr/proc.c @@ -1447,6 +1447,8 @@ ProcSleep(LOCALLOCK *locallock) /* check for deadlocks first, as that's probably log-worthy */ if (got_deadlock_timeout) { + /* Increment the lock statistics deadlock_timeouts counter */ + pgstat_count_lock_deadlock_timeouts(locallock->tag.lock.locktag_type); deadlock_state = CheckDeadLock(); got_deadlock_timeout = false; } diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 21de158adbb..eadb43a283d 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -3472,6 +3472,14 @@ ProcessInterrupts(void) if (lock_timeout_occurred) { + LOCALLOCK *lockAwaited; + + lockAwaited = GetAwaitedLock(); + + /* Increment the lock statistics timeouts counter */ + if (lockAwaited) + pgstat_count_lock_timeouts(lockAwaited->tag.lock.locktag_type); + LockErrorCleanup(); ereport(ERROR, (errcode(ERRCODE_LOCK_NOT_AVAILABLE), 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..110a370b875 --- /dev/null +++ b/src/backend/utils/activity/pgstat_lock.c @@ -0,0 +1,160 @@ +/* ------------------------------------------------------------------------- + * + * 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(requests); + LOCKSTAT_ACC(timeouts); + LOCKSTAT_ACC(deadlock_timeouts); + LOCKSTAT_ACC(fastpath); +#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_requests */ +PGSTAT_COUNT_LOCK_FUNC(requests) + +/* pgstat_count_lock_timeouts */ +PGSTAT_COUNT_LOCK_FUNC(timeouts) + +/* pgstat_count_lock_deadlock_timeouts */ +PGSTAT_COUNT_LOCK_FUNC(deadlock_timeouts) + +/* pgstat_count_lock_fastpath */ +PGSTAT_COUNT_LOCK_FUNC(fastpath) diff --git a/src/include/pgstat.h b/src/include/pgstat.h index fff7ecc2533..8460e2db159 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,25 @@ typedef struct PgStat_IO PgStat_BktypeIO stats[BACKEND_NUM_TYPES]; } PgStat_IO; +typedef struct PgStat_LockEntry +{ + PgStat_Counter requests; + PgStat_Counter timeouts; + PgStat_Counter deadlock_timeouts; + PgStat_Counter fastpath; +} 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 +634,16 @@ 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_requests(uint8 locktag_type); +extern void pgstat_count_lock_timeouts(uint8 locktag_type); +extern void pgstat_count_lock_deadlock_timeouts(uint8 locktag_type); +extern void pgstat_count_lock_fastpath(uint8 locktag_type); + /* * 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 6e2d876a40f..cd3688524af 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 f7b2a14597830286b08bc8a78722c96fc73cafd6 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Thu, 31 Jul 2025 09:35:31 +0000 Subject: [PATCH v4 2/2] Add the pg_stat_lock view This new view reports lock statistics. This commit also adds documentation and a few tests. XXX: Bump catversion --- doc/src/sgml/monitoring.sgml | 117 ++++++++++++++++++ src/backend/catalog/system_views.sql | 10 ++ src/backend/utils/activity/pgstat_lock.c | 8 ++ src/backend/utils/adt/pgstatfuncs.c | 40 ++++++ src/include/catalog/pg_proc.dat | 9 ++ src/include/pgstat.h | 1 + src/test/isolation/expected/deadlock-hard.out | 20 ++- src/test/isolation/specs/deadlock-hard.spec | 5 +- src/test/regress/expected/advisory_lock.out | 18 +++ src/test/regress/expected/rules.out | 7 ++ src/test/regress/sql/advisory_lock.sql | 4 + 11 files changed, 237 insertions(+), 2 deletions(-) 46.2% doc/src/sgml/ 3.2% src/backend/catalog/ 16.3% src/backend/utils/adt/ 6.2% src/include/catalog/ 9.3% src/test/isolation/expected/ 6.7% src/test/isolation/specs/ 7.3% src/test/regress/expected/ 4.4% src/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index b77d189a500..1cba84c1542 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,108 @@ 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_stat_lock</structname></link> for details. + </para> + </entry> + </row> + + <row> + <entry role="catalog_table_entry"> + <para role="column_definition"> + <structfield>requests</structfield> <type>bigint</type> + </para> + <para> + Number of requests for this lock type. + </para> + </entry> + </row> + + <row> + <entry role="catalog_table_entry"> + <para role="column_definition"> + <structfield>timeouts</structfield> <type>bigint</type> + </para> + <para> + Number of times requests for this lock type had to wait longer + than <varname>lock_timeout</varname>. + </para> + </entry> + </row> + + <row> + <entry role="catalog_table_entry"> + <para role="column_definition"> + <structfield>deadlock_timeouts</structfield> <type>bigint</type> + </para> + <para> + Number of times requests for this lock type had to wait longer + than <varname>deadlock_timeout</varname>. + </para> + </entry> + </row> + + <row> + <entry role="catalog_table_entry"> + <para role="column_definition"> + <structfield>fastpath</structfield> <type>bigint</type> + </para> + <para> + Number of times this lock type was taken via fast path. + </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 +5306,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..a51ae67de05 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -985,6 +985,16 @@ 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.requests, + l.timeouts, + l.deadlock_timeouts, + l.fastpath, + 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 110a370b875..e26e24c260c 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..6a699da8bf0 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1737,6 +1737,43 @@ 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 6 + 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->requests); + values[i++] = Int64GetDatum(lck_stats->timeouts); + values[i++] = Int64GetDatum(lck_stats->deadlock_timeouts); + values[i++] = Int64GetDatum(lck_stats->fastpath); + 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 +1958,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 +1976,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..e208ebdbbc3 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,int8,timestamptz}', + proargmodes => '{o,o,o,o,o,o}', + proargnames => '{locktype,requests,timeouts,deadlock_timeouts,fastpath,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 8460e2db159..b06a261572b 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -643,6 +643,7 @@ extern void pgstat_count_lock_requests(uint8 locktag_type); extern void pgstat_count_lock_timeouts(uint8 locktag_type); extern void pgstat_count_lock_deadlock_timeouts(uint8 locktag_type); extern void pgstat_count_lock_fastpath(uint8 locktag_type); +extern PgStat_Lock *pgstat_fetch_stat_lock(void); /* * Functions in pgstat_database.c diff --git a/src/test/isolation/expected/deadlock-hard.out b/src/test/isolation/expected/deadlock-hard.out index 460653f2b86..5ae5a8bf953 100644 --- a/src/test/isolation/expected/deadlock-hard.out +++ b/src/test/isolation/expected/deadlock-hard.out @@ -1,6 +1,12 @@ Parsed test spec with 8 sessions -starting permutation: s1a1 s2a2 s3a3 s4a4 s5a5 s6a6 s7a7 s8a8 s1a2 s2a3 s3a4 s4a5 s5a6 s6a7 s7a8 s8a1 s8c s7c s6c s5c s4c s3c s2c s1c +starting permutation: s1rl s1a1 s2a2 s3a3 s4a4 s5a5 s6a6 s7a7 s8a8 s1a2 s2a3 s3a4 s4a5 s5a6 s6a7 s7a8 s8a1 s8c s8f s7c s6c s5c s4c s3c s2c s1c s1sl +step s1rl: SELECT pg_stat_reset_shared('lock'); +pg_stat_reset_shared +-------------------- + +(1 row) + step s1a1: LOCK TABLE a1; step s2a2: LOCK TABLE a2; step s3a3: LOCK TABLE a3; @@ -21,6 +27,12 @@ step s8a1: <... completed> ERROR: deadlock detected step s7a8: <... completed> step s8c: COMMIT; +step s8f: SELECT pg_stat_force_next_flush(); +pg_stat_force_next_flush +------------------------ + +(1 row) + step s7c: COMMIT; step s6a7: <... completed> step s6c: COMMIT; @@ -34,3 +46,9 @@ step s2a3: <... completed> step s2c: COMMIT; step s1a2: <... completed> step s1c: COMMIT; +step s1sl: SELECT deadlock_timeouts > 0 FROM pg_stat_lock WHERE locktype = 'relation'; +?column? +-------- +t +(1 row) + diff --git a/src/test/isolation/specs/deadlock-hard.spec b/src/test/isolation/specs/deadlock-hard.spec index 60bedca237a..d9ed99836e3 100644 --- a/src/test/isolation/specs/deadlock-hard.spec +++ b/src/test/isolation/specs/deadlock-hard.spec @@ -25,6 +25,8 @@ setup { BEGIN; SET deadlock_timeout = '100s'; } step s1a1 { LOCK TABLE a1; } step s1a2 { LOCK TABLE a2; } step s1c { COMMIT; } +step s1sl { SELECT deadlock_timeouts > 0 FROM pg_stat_lock WHERE locktype = 'relation'; } +step s1rl { SELECT pg_stat_reset_shared('lock'); } session s2 setup { BEGIN; SET deadlock_timeout = '100s'; } @@ -67,6 +69,7 @@ setup { BEGIN; SET deadlock_timeout = '10ms'; } step s8a8 { LOCK TABLE a8; } step s8a1 { LOCK TABLE a1; } step s8c { COMMIT; } +step s8f { SELECT pg_stat_force_next_flush(); } # Note: when s8a1 detects the deadlock and fails, s7a8 is released, making # it timing-dependent which query completion is received first by the tester. @@ -76,4 +79,4 @@ step s8c { COMMIT; } # dummy blocking mark to s8a1 to ensure it will be reported as "waiting" # regardless of that. -permutation s1a1 s2a2 s3a3 s4a4 s5a5 s6a6 s7a7 s8a8 s1a2 s2a3 s3a4 s4a5 s5a6 s6a7 s7a8(s8a1) s8a1(*) s8c s7c s6c s5c s4c s3c s2c s1c +permutation s1rl s1a1 s2a2 s3a3 s4a4 s5a5 s6a6 s7a7 s8a8 s1a2 s2a3 s3a4 s4a5 s5a6 s6a7 s7a8(s8a1) s8a1(*) s8c s8f s7c s6c s5c s4c s3c s2c s1c s1sl diff --git a/src/test/regress/expected/advisory_lock.out b/src/test/regress/expected/advisory_lock.out index 02e07765ac2..fdaa1756ba4 100644 --- a/src/test/regress/expected/advisory_lock.out +++ b/src/test/regress/expected/advisory_lock.out @@ -2,6 +2,12 @@ -- ADVISORY LOCKS -- SELECT oid AS datoid FROM pg_database WHERE datname = current_database() \gset +SELECT pg_stat_reset_shared('lock'); + pg_stat_reset_shared +---------------------- + +(1 row) + BEGIN; SELECT pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2), @@ -48,6 +54,12 @@ WARNING: you don't own a lock of type ShareLock f | f | f | f (1 row) +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + -- automatically release xact locks at commit COMMIT; SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid; @@ -56,6 +68,12 @@ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid 0 (1 row) +SELECT requests FROM pg_stat_lock WHERE locktype = 'advisory'; + requests +---------- + 4 +(1 row) + BEGIN; -- holding both session and xact locks on the same objects, xact first SELECT diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 78a37d9fc8f..80999465baf 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1951,6 +1951,13 @@ 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, + requests, + timeouts, + deadlock_timeouts, + fastpath, + stats_reset + FROM pg_stat_get_lock() l(locktype, requests, timeouts, deadlock_timeouts, fastpath, stats_reset); pg_stat_progress_analyze| SELECT s.pid, s.datid, d.datname, diff --git a/src/test/regress/sql/advisory_lock.sql b/src/test/regress/sql/advisory_lock.sql index 8513ab8e98f..f1bff60fd37 100644 --- a/src/test/regress/sql/advisory_lock.sql +++ b/src/test/regress/sql/advisory_lock.sql @@ -4,6 +4,8 @@ SELECT oid AS datoid FROM pg_database WHERE datname = current_database() \gset +SELECT pg_stat_reset_shared('lock'); + BEGIN; SELECT @@ -26,12 +28,14 @@ SELECT pg_advisory_unlock(1), pg_advisory_unlock_shared(2), pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2); +SELECT pg_stat_force_next_flush(); -- automatically release xact locks at commit COMMIT; SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid; +SELECT requests FROM pg_stat_lock WHERE locktype = 'advisory'; BEGIN; -- 2.34.1
