Hi, On Thu, Jun 25, 2026 at 11:40:19PM +0800, Rui Zhao wrote: > Hi Bertrand, > > I reviewed and tested v2; it builds cleanly. The implementation closely > mirrors the existing per-backend IO/WAL stats (same flush path, struct > layout, and backend-type guard), and the hot path is untouched: the > per-backend counters piggyback on the existing global counting points > (pgstat_count_lock_waits / pgstat_count_lock_fastpath_exceeded), so they > only fire where pg_stat_lock already counts -- fastpath_exceeded when the > fast-path slot limit is exceeded, and waits/wait_time only after a wait > longer than deadlock_timeout.
Thanks for the review. Attached is a rebase due to c776550e466 and making use of the same changes. > One tiny nit: in pgstat_count_backend_lock_waits() and > pgstat_count_backend_lock_fastpath_exceeded(), the Assert() is directly > followed by the counter update. A blank line after the Assert() would read > a bit better and is the more usual style in this code. Yeah, done in the attached. Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
>From 1ad276bc977d930fbbade278af60e9d85b7a542a Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Tue, 30 Jun 2026 04:48:56 +0000 Subject: [PATCH v3 1/2] Refactor pg_stat_get_lock() to use a helper function Extract the tuple-building logic from pg_stat_get_lock() into a new static helper pg_stat_lock_build_tuples(). This is in preparation for pg_stat_get_backend_lock() which will reuse the same helper, following the pattern established by pg_stat_io_build_tuples() for IO stats and pg_stat_wal_build_tuple() for WAL stats. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Michael Paquier <[email protected]> Reviewed-by: Tristan Partin <[email protected]> Reviewed-by: Tatsuya Kawata <[email protected]> Reviewed-by: Rui Zhao <[email protected]> Discussion: https://postgr.es/m/aiAzEY%2BcMQb/W8yu%40bdtpg --- src/backend/utils/adt/pgstatfuncs.c | 47 +++++++++++++++++++---------- 1 file changed, 31 insertions(+), 16 deletions(-) 100.0% src/backend/utils/adt/ diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 0c59df17901..1f9165f1616 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1737,38 +1737,53 @@ pg_stat_get_wal(PG_FUNCTION_ARGS) wal_stats->stat_reset_timestamp)); } -Datum -pg_stat_get_lock(PG_FUNCTION_ARGS) +/* + * pg_stat_lock_build_tuples + * + * Helper routine for pg_stat_get_lock(), filling a result tuplestore with one + * tuple for each lock type. + */ +static void +pg_stat_lock_build_tuples(ReturnSetInfo *rsinfo, + PgStat_LockEntry *lock_stats, + TimestampTz stat_reset_timestamp) { #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]; + PgStat_LockEntry *lck_stats = &lock_stats[lcktype]; int i = 0; - locktypename = LockTagTypeNames[lcktype]; - - values[i++] = CStringGetTextDatum(locktypename); + values[i++] = CStringGetTextDatum(LockTagTypeNames[lcktype]); values[i++] = Int64GetDatum(lck_stats->waits); values[i++] = Float8GetDatum(pg_stat_us_to_ms(lck_stats->wait_time)); values[i++] = Int64GetDatum(lck_stats->fastpath_exceeded); - values[i] = TimestampTzGetDatum(lock_stats->stat_reset_timestamp); + if (stat_reset_timestamp != 0) + values[i] = TimestampTzGetDatum(stat_reset_timestamp); + else + nulls[i] = true; Assert(i + 1 == PG_STAT_LOCK_COLS); tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls); } +} + +Datum +pg_stat_get_lock(PG_FUNCTION_ARGS) +{ + ReturnSetInfo *rsinfo; + PgStat_Lock *lock_stats; + + InitMaterializedSRF(fcinfo, 0); + rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + + lock_stats = pgstat_fetch_stat_lock(); + + pg_stat_lock_build_tuples(rsinfo, lock_stats->stats, + lock_stats->stat_reset_timestamp); return (Datum) 0; } -- 2.34.1
>From 1ebe6581c7c741e6be858cb9ee3a4c46a9f2af75 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Tue, 30 Jun 2026 04:53:52 +0000 Subject: [PATCH v3 2/2] Add per-backend lock statistics This commit adds per-backend lock statistics, providing the same information as pg_stat_lock, except that it is now possible to retrieve those stats (lock wait counts, wait times, and fast-path exceeded count) on a per-backend basis. This data can be retrieved with a new system function called pg_stat_get_backend_lock(), that returns one tuple per lock type based on the PID provided in input. Like pg_stat_get_backend_io(), this is useful when joined with pg_stat_activity to get a live picture of the locks behavior for each running backend. pgstat_flush_backend() gains a new flag value, able to control the flush of the lock stats. This commit relies mostly on the infrastructure provided by 9aea73fc61d4, that has introduced backend statistics. XXX: Bump catalog version. A bump of PGSTAT_FILE_FORMAT_ID is not required, as backend stats do not persist on disk. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Michael Paquier <[email protected]> Reviewed-by: Tristan Partin <[email protected]> Reviewed-by: Tatsuya Kawata <[email protected]> Reviewed-by: Rui Zhao <[email protected]> Discussion: https://postgr.es/m/aiAzEY%2BcMQb/W8yu%40bdtpg --- doc/src/sgml/monitoring.sgml | 19 ++++++ src/backend/utils/activity/pgstat_backend.c | 72 +++++++++++++++++++++ src/backend/utils/activity/pgstat_lock.c | 4 ++ src/backend/utils/adt/pgstatfuncs.c | 29 ++++++++- src/include/catalog/pg_proc.dat | 8 +++ src/include/pgstat.h | 12 ++++ src/include/utils/pgstat_internal.h | 3 +- src/test/regress/expected/stats.out | 12 ++++ src/test/regress/sql/stats.sql | 9 +++ 9 files changed, 165 insertions(+), 3 deletions(-) 14.7% doc/src/sgml/ 38.4% src/backend/utils/activity/ 14.8% src/backend/utils/adt/ 8.7% src/include/catalog/ 10.1% src/include/ 6.7% src/test/regress/expected/ 6.1% src/test/regress/sql/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 6dcf05eb702..316d3c5767a 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -5545,6 +5545,25 @@ description | Waiting for a newly initialized WAL file to reach durable storage </para></entry> </row> + <row> + <entry id="pg-stat-get-backend-lock" role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>pg_stat_get_backend_lock</primary> + </indexterm> + <function>pg_stat_get_backend_lock</function> ( <type>integer</type> ) + <returnvalue>setof record</returnvalue> + </para> + <para> + Returns lock statistics about the backend with the specified + process ID. The output fields are exactly the same as the ones in the + <structname>pg_stat_lock</structname> view. + </para> + <para> + The function does not return lock statistics for the checkpointer, + the background writer, the startup process and the autovacuum launcher. + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c index 73461c9bca5..b736b2ccc6f 100644 --- a/src/backend/utils/activity/pgstat_backend.c +++ b/src/backend/utils/activity/pgstat_backend.c @@ -39,6 +39,7 @@ */ static PgStat_BackendPending PendingBackendStats; static bool backend_has_iostats = false; +static bool backend_has_lockstats = false; /* * WAL usage counters saved from pgWalUsage at the previous call to @@ -86,6 +87,39 @@ pgstat_count_backend_io_op(IOObject io_object, IOContext io_context, pgstat_report_fixed = true; } +/* + * Utility routines to report lock stats for backends, kept here to avoid + * exposing PendingBackendStats to the outside world. + */ +void +pgstat_count_backend_lock_waits(uint8 locktag_type, PgStat_Counter usecs) +{ + if (!pgstat_tracks_backend_bktype(MyBackendType)) + return; + + Assert(locktag_type <= LOCKTAG_LAST_TYPE); + + PendingBackendStats.pending_lock.stats[locktag_type].waits++; + PendingBackendStats.pending_lock.stats[locktag_type].wait_time += usecs; + + backend_has_lockstats = true; + pgstat_report_fixed = true; +} + +void +pgstat_count_backend_lock_fastpath_exceeded(uint8 locktag_type) +{ + if (!pgstat_tracks_backend_bktype(MyBackendType)) + return; + + Assert(locktag_type <= LOCKTAG_LAST_TYPE); + + PendingBackendStats.pending_lock.stats[locktag_type].fastpath_exceeded++; + + backend_has_lockstats = true; + pgstat_report_fixed = true; +} + /* * Returns statistics of a backend by proc number. */ @@ -262,6 +296,36 @@ pgstat_flush_backend_entry_wal(PgStat_EntryRef *entry_ref) prevBackendWalUsage = pgWalUsage; } +/* + * Flush out locally pending backend lock statistics. Locking is managed + * by the caller. + */ +static void +pgstat_flush_backend_entry_lock(PgStat_EntryRef *entry_ref) +{ + PgStatShared_Backend *shbackendent; + PgStat_PendingLock *bktype_shstats; + + if (!backend_has_lockstats) + return; + + shbackendent = (PgStatShared_Backend *) entry_ref->shared_stats; + bktype_shstats = &shbackendent->stats.lock_stats; + + for (int i = 0; i <= LOCKTAG_LAST_TYPE; i++) + { +#define LOCKSTAT_ACC(fld) \ + (bktype_shstats->stats[i].fld += PendingBackendStats.pending_lock.stats[i].fld) + LOCKSTAT_ACC(waits); + LOCKSTAT_ACC(wait_time); + LOCKSTAT_ACC(fastpath_exceeded); +#undef LOCKSTAT_ACC + } + + MemSet(&PendingBackendStats.pending_lock, 0, sizeof(PgStat_PendingLock)); + backend_has_lockstats = false; +} + /* * Flush out locally pending backend statistics * @@ -286,6 +350,10 @@ pgstat_flush_backend(bool nowait, uint32 flags) pgstat_backend_wal_have_pending()) has_pending_data = true; + /* Some lock data pending? */ + if ((flags & PGSTAT_BACKEND_FLUSH_LOCK) && backend_has_lockstats) + has_pending_data = true; + if (!has_pending_data) return false; @@ -301,6 +369,9 @@ pgstat_flush_backend(bool nowait, uint32 flags) if (flags & PGSTAT_BACKEND_FLUSH_WAL) pgstat_flush_backend_entry_wal(entry_ref); + if (flags & PGSTAT_BACKEND_FLUSH_LOCK) + pgstat_flush_backend_entry_lock(entry_ref); + pgstat_unlock_entry(entry_ref); return false; @@ -339,6 +410,7 @@ pgstat_create_backend(ProcNumber procnum) MemSet(&PendingBackendStats, 0, sizeof(PgStat_BackendPending)); backend_has_iostats = false; + backend_has_lockstats = false; /* * Initialize prevBackendWalUsage with pgWalUsage so that diff --git a/src/backend/utils/activity/pgstat_lock.c b/src/backend/utils/activity/pgstat_lock.c index 8910a15634d..c20c7599683 100644 --- a/src/backend/utils/activity/pgstat_lock.c +++ b/src/backend/utils/activity/pgstat_lock.c @@ -131,6 +131,8 @@ pgstat_count_lock_fastpath_exceeded(uint8 locktag_type) PendingLockStats.stats[locktag_type].fastpath_exceeded++; have_lockstats = true; pgstat_report_fixed = true; + + pgstat_count_backend_lock_fastpath_exceeded(locktag_type); } /* @@ -147,4 +149,6 @@ pgstat_count_lock_waits(uint8 locktag_type, PgStat_Counter usecs) PendingLockStats.stats[locktag_type].wait_time += usecs; have_lockstats = true; pgstat_report_fixed = true; + + pgstat_count_backend_lock_waits(locktag_type, usecs); } diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 1f9165f1616..0c6a20843a5 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1740,8 +1740,8 @@ pg_stat_get_wal(PG_FUNCTION_ARGS) /* * pg_stat_lock_build_tuples * - * Helper routine for pg_stat_get_lock(), filling a result tuplestore with one - * tuple for each lock type. + * Helper routine for pg_stat_get_lock() and pg_stat_get_backend_lock(), + * filling a result tuplestore with one tuple for each lock type. */ static void pg_stat_lock_build_tuples(ReturnSetInfo *rsinfo, @@ -1788,6 +1788,31 @@ pg_stat_get_lock(PG_FUNCTION_ARGS) return (Datum) 0; } +/* + * Returns lock statistics for a backend with given PID. + */ +Datum +pg_stat_get_backend_lock(PG_FUNCTION_ARGS) +{ + int pid; + ReturnSetInfo *rsinfo; + PgStat_Backend *backend_stats; + + InitMaterializedSRF(fcinfo, 0); + rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + + pid = PG_GETARG_INT32(0); + backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL); + + if (!backend_stats) + return (Datum) 0; + + pg_stat_lock_build_tuples(rsinfo, backend_stats->lock_stats.stats, + backend_stats->stat_reset_timestamp); + + return (Datum) 0; +} + /* * Returns statistics of SLRU caches. */ diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index efe13b7866a..73bb7fbb430 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -6092,6 +6092,14 @@ proargmodes => '{i,o,o,o,o,o,o}', proargnames => '{backend_pid,wal_records,wal_fpi,wal_bytes,wal_fpi_bytes,wal_buffers_full,stats_reset}', prosrc => 'pg_stat_get_backend_wal' }, +{ oid => '9682', descr => 'statistics: backend lock statistics', + proname => 'pg_stat_get_backend_lock', prorows => '10', proretset => 't', + provolatile => 'v', proparallel => 'r', prorettype => 'record', + proargtypes => 'int4', + proallargtypes => '{int4,text,int8,float8,int8,timestamptz}', + proargmodes => '{i,o,o,o,o,o}', + proargnames => '{backend_pid,locktype,waits,wait_time,fastpath_exceeded,stats_reset}', + prosrc => 'pg_stat_get_backend_lock' }, { oid => '6248', descr => 'statistics: information about WAL prefetching', proname => 'pg_stat_get_recovery_prefetch', prorows => '1', proretset => 't', provolatile => 'v', prorettype => 'record', proargtypes => '', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 72496695999..58a44857f13 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -523,6 +523,7 @@ typedef struct PgStat_Backend TimestampTz stat_reset_timestamp; PgStat_BktypeIO io_stats; PgStat_WalCounters wal_counters; + PgStat_PendingLock lock_stats; } PgStat_Backend; /* --------- @@ -535,6 +536,12 @@ typedef struct PgStat_BackendPending * Backend statistics store the same amount of IO data as PGSTAT_KIND_IO. */ PgStat_PendingIO pending_io; + + /* + * Backend statistics store the same amount of lock data as + * PGSTAT_KIND_LOCK. + */ + PgStat_PendingLock pending_lock; } PgStat_BackendPending; /* @@ -586,6 +593,11 @@ extern void pgstat_count_backend_io_op(IOObject io_object, IOContext io_context, IOOp io_op, uint32 cnt, uint64 bytes); + +/* used by pgstat_lock.c for lock stats tracked in backends */ +extern void pgstat_count_backend_lock_waits(uint8 locktag_type, PgStat_Counter usecs); +extern void pgstat_count_backend_lock_fastpath_exceeded(uint8 locktag_type); + extern PgStat_Backend *pgstat_fetch_stat_backend(ProcNumber procNumber); extern PgStat_Backend *pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype); diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index 3ca4f454895..b3dc3ff7d8b 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -705,7 +705,8 @@ extern void pgstat_archiver_snapshot_cb(void); /* flags for pgstat_flush_backend() */ #define PGSTAT_BACKEND_FLUSH_IO (1 << 0) /* Flush I/O statistics */ #define PGSTAT_BACKEND_FLUSH_WAL (1 << 1) /* Flush WAL statistics */ -#define PGSTAT_BACKEND_FLUSH_ALL (PGSTAT_BACKEND_FLUSH_IO | PGSTAT_BACKEND_FLUSH_WAL) +#define PGSTAT_BACKEND_FLUSH_LOCK (1 << 2) /* Flush lock statistics */ +#define PGSTAT_BACKEND_FLUSH_ALL (PGSTAT_BACKEND_FLUSH_IO | PGSTAT_BACKEND_FLUSH_WAL | PGSTAT_BACKEND_FLUSH_LOCK) extern bool pgstat_flush_backend(bool nowait, uint32 flags); extern bool pgstat_backend_flush_cb(bool nowait); diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index bbb1db3c433..fa550676f83 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -2019,6 +2019,10 @@ BEGIN END; $$; SELECT fastpath_exceeded AS fastpath_exceeded_before FROM pg_stat_lock WHERE locktype = 'relation' \gset +-- Test pg_stat_get_backend_lock() +SELECT fastpath_exceeded AS backend_fastpath_exceeded_before + FROM pg_stat_get_backend_lock(pg_backend_pid()) + WHERE locktype = 'relation' \gset -- Needs a lock on each partition SELECT count(*) FROM part_test; count @@ -2039,5 +2043,13 @@ SELECT fastpath_exceeded > :fastpath_exceeded_before FROM pg_stat_lock WHERE loc t (1 row) +SELECT fastpath_exceeded > :backend_fastpath_exceeded_before + FROM pg_stat_get_backend_lock(pg_backend_pid()) + 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 610fd21fae4..f5683302a75 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -998,6 +998,11 @@ $$; SELECT fastpath_exceeded AS fastpath_exceeded_before FROM pg_stat_lock WHERE locktype = 'relation' \gset +-- Test pg_stat_get_backend_lock() +SELECT fastpath_exceeded AS backend_fastpath_exceeded_before + FROM pg_stat_get_backend_lock(pg_backend_pid()) + WHERE locktype = 'relation' \gset + -- Needs a lock on each partition SELECT count(*) FROM part_test; @@ -1006,6 +1011,10 @@ SELECT pg_stat_force_next_flush(); SELECT fastpath_exceeded > :fastpath_exceeded_before FROM pg_stat_lock WHERE locktype = 'relation'; +SELECT fastpath_exceeded > :backend_fastpath_exceeded_before + FROM pg_stat_get_backend_lock(pg_backend_pid()) + WHERE locktype = 'relation'; + DROP TABLE part_test; -- End of Stats Test -- 2.34.1
