Hi hackers,
Please find attached a patch to add a new view (namely pg_stat_lock) that
provides
lock statistics.
It’s output is like the following:
postgres=# select * from pg_stat_lock;
locktype | requests | waits | timeouts | deadlock_timeouts | deadlocks
| fastpath | stats_reset
------------------+----------+-------+----------+-------------------+-----------+----------+-------------------------------
relation | 612775 | 1 | 0 | 0 | 0
| 531115 | 2025-08-01 09:18:26.476275+00
extend | 3128 | 0 | 0 | 0 | 0
| 0 | 2025-08-01 09:18:26.476275+00
frozenid | 11 | 0 | 0 | 0 | 0
| 0 | 2025-08-01 09:18:26.476275+00
page | 1 | 0 | 0 | 0 | 0
| 0 | 2025-08-01 09:18:26.476275+00
tuple | 3613 | 0 | 0 | 0 | 0
| 0 | 2025-08-01 09:18:26.476275+00
transactionid | 6130 | 0 | 0 | 0 | 0
| 0 | 2025-08-01 09:18:26.476275+00
virtualxid | 15390 | 0 | 0 | 0 | 0
| 15390 | 2025-08-01 09:18:26.476275+00
spectoken | 12 | 0 | 0 | 0 | 0
| 0 | 2025-08-01 09:18:26.476275+00
object | 8393 | 0 | 0 | 0 | 0
| 0 | 2025-08-01 09:18:26.476275+00
userlock | 0 | 0 | 0 | 0 | 0
| 0 | 2025-08-01 09:18:26.476275+00
advisory | 44 | 0 | 0 | 0 | 0
| 0 | 2025-08-01 09:18:26.476275+00
applytransaction | 0 | 0 | 0 | 0 | 0
| 0 | 2025-08-01 09:18:26.476275+00
It means that it provides historical trends of locks usage per lock type.
It can be used for example for:
1. checking if "waits" is close to "requests". Then it means you usually have to
wait before acquiring the lock, which means you may have a concurrency issue.
2. lock_timeout and deadlock_timeout tuning (lock_timeout is visible only in the
logs if log_min_error_statement is set appropriately).
3. checking the "requests"/"fastpath" ratio to see if
"max_locks_per_transaction"
needs tuning (see c4d5cb71d2).
If any points need more details, it might be a good idea to start sampling
pg_locks.
The patch is made of 2 sub-patches:
0001 - It adds 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: Number of requests for this lock type.
- waits: Number of times requests for this lock type had to wait.
- timeouts: Number of times requests for this lock type had to wait longer than
lock_timeout.
- deadlock_timeouts: Number of times requests for this lock type had to wait
longer
than deadlock_timeout.
- deadlocks: Number of times a deadlock occurred on this lock type.
- fastpath: Number of times this lock type was taken via fast path.
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.
Those new counters are incremented outside of the wait events code path,
as suggested in [1].
There are no major design choices, it relies on the current statistics
machinery.
0002 - It adds the pg_stat_lock view
It also adds documentation and some tests.
Remarks:
- maybe we could add some metrics related to the lock duration (we have some
hints
thanks to the timeout ounters though)
- if this is merged, a next step could be to record those metrics per backend
[1]:
https://www.postgresql.org/message-id/CA%2BTgmobptuUWo7X5zcQrWKh22qeAn4eL%2B%3Dwtb8_ajCOR%2B7_tcw%40mail.gmail.com
Looking forward to your feedback,
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
>From 18a9a9ca8fefa25c1afcf676765576346c3e7fe7 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <[email protected]>
Date: Tue, 29 Jul 2025 08:36:35 +0000
Subject: [PATCH v1 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
waits
timeouts
deadlock_timeouts
deadlocks
fastpath
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 | 18 +++
src/backend/storage/lmgr/proc.c | 3 +
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 | 168 +++++++++++++++++++++++
src/include/pgstat.h | 34 +++++
src/include/utils/pgstat_internal.h | 21 +++
src/include/utils/pgstat_kind.h | 5 +-
src/tools/pgindent/typedefs.list | 4 +
11 files changed, 279 insertions(+), 2 deletions(-)
13.3% src/backend/storage/lmgr/
62.5% src/backend/utils/activity/
8.3% src/include/utils/
12.1% src/include/
3.6% src/
diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c
index 62f3471448e..e42919d132b 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"
@@ -870,6 +871,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",
@@ -1201,6 +1205,8 @@ LockAcquireExtended(const LOCKTAG *locktag,
}
else
{
+ /* Increment the lock statistics deadlocks counter */
+ pgstat_count_lock_deadlocks(locallock->tag.lock.locktag_type);
DeadLockReport();
/* DeadLockReport() will not return */
}
@@ -1212,6 +1218,8 @@ LockAcquireExtended(const LOCKTAG *locktag,
*/
if (waitResult == PROC_WAIT_STATUS_WAITING)
{
+ /* Increment the lock statistics waits counter */
+ pgstat_count_lock_waits(locktag->locktag_type);
Assert(!dontWait);
PROCLOCK_PRINT("LockAcquire: sleeping on lock", proclock);
LOCK_PRINT("LockAcquire: sleeping on lock", lock, lockmode);
@@ -1232,6 +1240,8 @@ LockAcquireExtended(const LOCKTAG *locktag,
* now.
*/
Assert(!dontWait);
+ /* Increment the lock statistics deadlocks counter */
+ pgstat_count_lock_deadlocks(locallock->tag.lock.locktag_type);
DeadLockReport();
/* DeadLockReport() will not return */
}
@@ -2767,6 +2777,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;
}
}
@@ -2776,6 +2788,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;
}
@@ -4600,6 +4614,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 e9ef0fbfe32..dd930fb9bd4 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -1456,9 +1456,12 @@ 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);
CheckDeadLock();
got_deadlock_timeout = false;
}
+
CHECK_FOR_INTERRUPTS();
}
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 0cecd464902..df02719ad63 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -3433,6 +3433,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 9c2443e1ecd..4293d0f9efd 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 d8e56b49c24..3d3d7b67b5f 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 6bc91ce0dad..d78dc5e3d46 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
@@ -446,6 +447,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..fb4ed5c7362
--- /dev/null
+++ b/src/backend/utils/activity/pgstat_lock.c
@@ -0,0 +1,168 @@
+/* -------------------------------------------------------------------------
+ *
+ * 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(waits);
+ LOCKSTAT_ACC(timeouts);
+ LOCKSTAT_ACC(deadlock_timeouts);
+ LOCKSTAT_ACC(deadlocks);
+ 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_waits */
+PGSTAT_COUNT_LOCK_FUNC(waits)
+
+/* pgstat_count_lock_timeouts */
+PGSTAT_COUNT_LOCK_FUNC(timeouts)
+
+/* pgstat_count_lock_deadlock_timeouts */
+PGSTAT_COUNT_LOCK_FUNC(deadlock_timeouts)
+
+/* pgstat_count_lock_deadlocks */
+PGSTAT_COUNT_LOCK_FUNC(deadlocks)
+
+/* pgstat_count_lock_fastpath */
+PGSTAT_COUNT_LOCK_FUNC(fastpath)
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 202bd2d5ace..7893f7311ae 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -15,6 +15,7 @@
#include "portability/instr_time.h"
#include "postmaster/pgarch.h" /* for MAX_XFN_CHARS */
#include "replication/conflict.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"
@@ -339,6 +340,27 @@ typedef struct PgStat_IO
PgStat_BktypeIO stats[BACKEND_NUM_TYPES];
} PgStat_IO;
+typedef struct PgStat_LockEntry
+{
+ PgStat_Counter requests;
+ PgStat_Counter waits;
+ PgStat_Counter timeouts;
+ PgStat_Counter deadlock_timeouts;
+ PgStat_Counter deadlocks;
+ 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;
@@ -603,6 +625,18 @@ 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_waits(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_deadlocks(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 6cf00008f63..182feec1235 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -396,6 +396,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 */
@@ -492,6 +502,7 @@ typedef struct PgStat_ShmemControl
PgStatShared_BgWriter bgwriter;
PgStatShared_Checkpointer checkpointer;
PgStatShared_IO io;
+ PgStatShared_Lock lock;
PgStatShared_SLRU slru;
PgStatShared_Wal wal;
@@ -524,6 +535,8 @@ typedef struct PgStat_Snapshot
PgStat_IO io;
+ PgStat_Lock lock;
+
PgStat_SLRUStats slru[SLRU_NUM_ELEMENTS];
PgStat_WalStats wal;
@@ -673,6 +686,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 eb5f0b3ae6d..f8e95f55533 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 e6f2e93b2d6..6456d53b13f 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2218,6 +2218,7 @@ PgStatShared_HashEntry
PgStatShared_IO
PgStatShared_InjectionPoint
PgStatShared_InjectionPointFixed
+PgStatShared_Lock
PgStatShared_Relation
PgStatShared_ReplSlot
PgStatShared_SLRU
@@ -2240,8 +2241,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 6949485ab3951b6a6497f3e312b6d16ec8983210 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <[email protected]>
Date: Thu, 31 Jul 2025 09:35:31 +0000
Subject: [PATCH v1 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 | 151 ++++++++++++++++++
src/backend/catalog/system_views.sql | 12 ++
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/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 | 9 ++
src/test/regress/sql/advisory_lock.sql | 4 +
11 files changed, 274 insertions(+), 2 deletions(-)
51.5% doc/src/sgml/
3.3% src/backend/catalog/
14.3% src/backend/utils/adt/
5.7% src/include/catalog/
8.4% src/test/isolation/expected/
5.9% src/test/isolation/specs/
6.7% src/test/regress/expected/
3.8% src/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 823afe1b30b..0d18124f239 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
@@ -3027,6 +3036,142 @@ 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:
+ <literal>relation</literal>,
+ <literal>extend</literal>,
+ <literal>frozenid</literal>,
+ <literal>page</literal>,
+ <literal>tuple</literal>,
+ <literal>transactionid</literal>,
+ <literal>virtualxid</literal>,
+ <literal>spectoken</literal>,
+ <literal>object</literal>,
+ <literal>userlock</literal>,
+ <literal>advisory</literal>, or
+ <literal>applytransaction</literal>.
+ (See also <xref linkend="wait-event-lock-table"/>.)
+ </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>waits</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times requests for this lock type had to wait.
+ </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>deadlocks</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times a deadlock occurred on this lock type.
+ </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>
@@ -5044,6 +5189,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 f6eca09ee15..d493a00c544 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -950,6 +950,18 @@ 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.waits,
+ l.timeouts,
+ l.deadlock_timeouts,
+ l.deadlocks,
+ 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 fb4ed5c7362..73bdeb5ff66 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 1c12ddbae49..5e63f2d2a38 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1696,6 +1696,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 8
+ 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];
+
+ locktypename = LockTagTypeNames[lcktype];
+
+ values[0] = CStringGetTextDatum(locktypename);
+ values[1] = Int64GetDatum(lck_stats->requests);
+ values[2] = Int64GetDatum(lck_stats->waits);
+ values[3] = Int64GetDatum(lck_stats->timeouts);
+ values[4] = Int64GetDatum(lck_stats->deadlock_timeouts);
+ values[5] = Int64GetDatum(lck_stats->deadlocks);
+ values[6] = Int64GetDatum(lck_stats->fastpath);
+ values[7] = TimestampTzGetDatum(lock_stats->stat_reset_timestamp);
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+ }
+
+ return (Datum) 0;
+}
+
/*
* Returns statistics of SLRU caches.
*/
@@ -1880,6 +1916,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);
@@ -1897,6 +1934,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 3ee8fed7e53..c5faff30317 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6001,6 +6001,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,int8,int8,timestamptz}',
+ proargmodes => '{o,o,o,o,o,o,o,o}',
+ proargnames => '{locktype,requests,waits,timeouts,deadlock_timeouts,deadlocks,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 7893f7311ae..e653bce4aab 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -636,6 +636,7 @@ extern void pgstat_count_lock_timeouts(uint8 locktag_type);
extern void pgstat_count_lock_deadlock_timeouts(uint8 locktag_type);
extern void pgstat_count_lock_deadlocks(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..ff448da5299 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 deadlocks > 0, deadlock_timeouts > 0 FROM pg_stat_lock WHERE locktype = 'relation';
+?column?|?column?
+--------+--------
+t |t
+(1 row)
+
diff --git a/src/test/isolation/specs/deadlock-hard.spec b/src/test/isolation/specs/deadlock-hard.spec
index 60bedca237a..6e8330662b4 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 deadlocks > 0, 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 dce8c672b40..c790164ea82 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1938,6 +1938,15 @@ 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,
+ waits,
+ timeouts,
+ deadlock_timeouts,
+ deadlocks,
+ fastpath,
+ stats_reset
+ FROM pg_stat_get_lock() l(locktype, requests, waits, timeouts, deadlock_timeouts, deadlocks, 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
