Add pg_exclusive_locks view for fast AccessExclusiveLock checks

From d48b69c187a802f8ac84d8ec9e6e2a5a54b7fde7 Mon Sep 17 00:00:00 2001
From: Lukas Fittl <lukas@fittl.com>
Date: Thu, 17 Jul 2025 16:50:12 -0700
Subject: [PATCH v0] Introduce pg_exclusive_locks view

---
 src/backend/catalog/system_views.sql |  5 ++
 src/backend/storage/ipc/standby.c    |  4 +-
 src/backend/storage/lmgr/lock.c      | 13 +++--
 src/backend/utils/adt/lockfuncs.c    | 87 ++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat      |  7 +++
 src/include/storage/lock.h           |  2 +-
 src/test/regress/expected/rules.out  |  6 ++
 7 files changed, 116 insertions(+), 8 deletions(-)

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index b2d5332effc..860a0616851 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -911,6 +911,11 @@ CREATE VIEW pg_stat_activity AS
         LEFT JOIN pg_database AS D ON (S.datid = D.oid)
         LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);
 
+CREATE VIEW pg_exclusive_locks AS
+    SELECT L.database, L.relation, L.transactionid, A.pid
+      FROM pg_exclusive_lock_status() L
+           LEFT JOIN pg_stat_activity A ON (L.transactionid = A.backend_xid);
+
 CREATE VIEW pg_stat_replication AS
     SELECT
             S.pid,
diff --git a/src/backend/storage/ipc/standby.c b/src/backend/storage/ipc/standby.c
index 4222bdab078..4a31a620c52 100644
--- a/src/backend/storage/ipc/standby.c
+++ b/src/backend/storage/ipc/standby.c
@@ -1302,7 +1302,7 @@ LogStandbySnapshot(void)
 	/*
 	 * Get details of any AccessExclusiveLocks being held at the moment.
 	 */
-	locks = GetRunningTransactionLocks(&nlocks);
+	locks = GetCurrentAccessExclusiveLocks(&nlocks, true);
 	if (nlocks > 0)
 		LogAccessExclusiveLocks(nlocks, locks);
 	pfree(locks);
@@ -1456,7 +1456,7 @@ LogAccessExclusiveLockPrepare(void)
 	 * hack, but for a corner case not worth adding code for into the main
 	 * commit path. Second, we must assign an xid before the lock is recorded
 	 * in shared memory, otherwise a concurrently executing
-	 * GetRunningTransactionLocks() might see a lock associated with an
+	 * GetCurrentAccessExclusiveLocks() might see a lock associated with an
 	 * InvalidTransactionId which we later assert cannot happen.
 	 */
 	(void) GetCurrentTransactionId();
diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c
index 62f3471448e..076de9a5296 100644
--- a/src/backend/storage/lmgr/lock.c
+++ b/src/backend/storage/lmgr/lock.c
@@ -962,7 +962,7 @@ LockAcquireExtended(const LOCKTAG *locktag,
 	 *
 	 * Only AccessExclusiveLocks can conflict with lock types that read-only
 	 * transactions can acquire in a standby server. Make sure this definition
-	 * matches the one in GetRunningTransactionLocks().
+	 * matches the one in GetCurrentAccessExclusiveLocks().
 	 */
 	if (lockmode >= AccessExclusiveLock &&
 		locktag->locktag_type == LOCKTAG_RELATION &&
@@ -4127,8 +4127,11 @@ GetSingleProcBlockerStatusData(PGPROC *blocked_proc, BlockedProcsData *data)
 
 /*
  * Returns a list of currently held AccessExclusiveLocks, for use by
- * LogStandbySnapshot().  The result is a palloc'd array,
- * with the number of elements returned into *nlocks.
+ * LogStandbySnapshot(), or user level reporting. The skip_committed argument
+ * allows skipping locks by committed transactions that are not yet released.
+ *
+ * The result is a palloc'd array, with the number of elements returned into
+ * *nlocks.
  *
  * XXX This currently takes a lock on all partitions of the lock table,
  * but it's possible to do better.  By reference counting locks and storing
@@ -4138,7 +4141,7 @@ GetSingleProcBlockerStatusData(PGPROC *blocked_proc, BlockedProcsData *data)
  * is pretty dubious though.
  */
 xl_standby_lock *
-GetRunningTransactionLocks(int *nlocks)
+GetCurrentAccessExclusiveLocks(int *nlocks, bool skip_committed)
 {
 	xl_standby_lock *accessExclusiveLocks;
 	PROCLOCK   *proclock;
@@ -4191,7 +4194,7 @@ GetRunningTransactionLocks(int *nlocks)
 			 * lock. It is still possible that we see locks held by already
 			 * complete transactions, if they haven't yet zeroed their xids.
 			 */
-			if (!TransactionIdIsValid(xid))
+			if (skip_committed && !TransactionIdIsValid(xid))
 				continue;
 
 			accessExclusiveLocks[index].xid = xid;
diff --git a/src/backend/utils/adt/lockfuncs.c b/src/backend/utils/adt/lockfuncs.c
index 00e67fb46d0..388b1dbe4a4 100644
--- a/src/backend/utils/adt/lockfuncs.c
+++ b/src/backend/utils/adt/lockfuncs.c
@@ -65,6 +65,17 @@ typedef struct
 /* Number of columns in pg_locks output */
 #define NUM_LOCK_STATUS_COLUMNS		16
 
+/* Working status for pg_exclusive_lock_status */
+typedef struct
+{
+	xl_standby_lock *locks;		/* exclusive lock data from lmgr */
+	int			nlocks;			/* number of exclusive locks */
+	int			currIdx;		/* current lock data index */
+}			PG_Exclusive_Lock_Status;
+
+/* Number of columns in pg_exclusive_lock_status output */
+#define NUM_EXCLUSIVE_LOCK_STATUS_COLUMNS		3
+
 /*
  * VXIDGetDatum - Construct a text representation of a VXID
  *
@@ -442,6 +453,82 @@ pg_lock_status(PG_FUNCTION_ARGS)
 	SRF_RETURN_DONE(funcctx);
 }
 
+/*
+ * pg_exclusive_lock_status - produce a view with one row per AccessExclusiveLock currently held
+ */
+Datum
+pg_exclusive_lock_status(PG_FUNCTION_ARGS)
+{
+	FuncCallContext *funcctx;
+	PG_Exclusive_Lock_Status *mystatus;
+
+	if (SRF_IS_FIRSTCALL())
+	{
+		TupleDesc	tupdesc;
+		MemoryContext oldcontext;
+
+		/* create a function context for cross-call persistence */
+		funcctx = SRF_FIRSTCALL_INIT();
+
+		/*
+		 * switch to memory context appropriate for multiple function calls
+		 */
+		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+		/* build tupdesc for result tuples */
+		/* this had better match function's declaration in pg_proc.h */
+		tupdesc = CreateTemplateTupleDesc(NUM_EXCLUSIVE_LOCK_STATUS_COLUMNS);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "database",
+						   OIDOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 2, "relation",
+						   OIDOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 3, "transactionid",
+						   XIDOID, -1, 0);
+
+		funcctx->tuple_desc = BlessTupleDesc(tupdesc);
+
+		/*
+		 * Collect all the locking information that we will format and send
+		 * out as a result set.
+		 */
+		mystatus = (PG_Exclusive_Lock_Status *) palloc(sizeof(PG_Exclusive_Lock_Status));
+		funcctx->user_fctx = mystatus;
+
+		mystatus->locks = GetCurrentAccessExclusiveLocks(&mystatus->nlocks, false);
+		mystatus->currIdx = 0;
+
+		MemoryContextSwitchTo(oldcontext);
+	}
+
+	funcctx = SRF_PERCALL_SETUP();
+	mystatus = (PG_Exclusive_Lock_Status *) funcctx->user_fctx;
+
+	while (mystatus->currIdx < mystatus->nlocks)
+	{
+		Datum		values[NUM_EXCLUSIVE_LOCK_STATUS_COLUMNS] = {0};
+		bool		nulls[NUM_EXCLUSIVE_LOCK_STATUS_COLUMNS] = {0};
+		HeapTuple	tuple;
+		Datum		result;
+		xl_standby_lock *lock;
+
+		lock = &(mystatus->locks[mystatus->currIdx]);
+		mystatus->currIdx++;
+
+		/*
+		 * Form tuple with appropriate data.
+		 */
+		values[0] = ObjectIdGetDatum(lock->dbOid);
+		values[1] = ObjectIdGetDatum(lock->relOid);
+		values[2] = ObjectIdGetDatum(lock->xid);
+
+		tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+		result = HeapTupleGetDatum(tuple);
+		SRF_RETURN_NEXT(funcctx, result);
+	}
+
+	SRF_RETURN_DONE(funcctx);
+}
+
 
 /*
  * pg_blocking_pids - produce an array of the PIDs blocking given PID
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 1fc19146f46..16d6ed4130c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6543,6 +6543,13 @@
   proargmodes => '{o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
   proargnames => '{locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid,virtualtransaction,pid,mode,granted,fastpath,waitstart}',
   prosrc => 'pg_lock_status' },
+{ oid => '8089', descr => 'view exclusive system lock information',
+  proname => 'pg_exclusive_lock_status', prorows => '1000', proretset => 't',
+  provolatile => 'v', prorettype => 'record', proargtypes => '',
+  proallargtypes => '{oid,oid,xid}',
+  proargmodes => '{o,o,o}',
+  proargnames => '{database,relation,transactionid}',
+  prosrc => 'pg_exclusive_lock_status' },
 { oid => '2561',
   descr => 'get array of PIDs of sessions blocking specified backend PID from acquiring a heavyweight lock',
   proname => 'pg_blocking_pids', provolatile => 'v', prorettype => '_int4',
diff --git a/src/include/storage/lock.h b/src/include/storage/lock.h
index 826cf28fdbd..c04f2b45a12 100644
--- a/src/include/storage/lock.h
+++ b/src/include/storage/lock.h
@@ -595,7 +595,7 @@ extern void RemoveFromWaitQueue(PGPROC *proc, uint32 hashcode);
 extern LockData *GetLockStatusData(void);
 extern BlockedProcsData *GetBlockerStatusData(int blocked_pid);
 
-extern xl_standby_lock *GetRunningTransactionLocks(int *nlocks);
+extern xl_standby_lock *GetCurrentAccessExclusiveLocks(int *nlocks, bool skip_committed);
 extern const char *GetLockmodeName(LOCKMETHODID lockmethodid, LOCKMODE mode);
 
 extern void lock_twophase_recover(FullTransactionId fxid, uint16 info,
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index dce8c672b40..f4c6f1a9c8b 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1344,6 +1344,12 @@ pg_dsm_registry_allocations| SELECT name,
     type,
     size
    FROM pg_get_dsm_registry_allocations() pg_get_dsm_registry_allocations(name, type, size);
+pg_exclusive_locks| SELECT l.database,
+    l.relation,
+    l.transactionid,
+    a.pid
+   FROM (pg_exclusive_lock_status() l(database, relation, transactionid)
+     LEFT JOIN pg_stat_activity a ON ((l.transactionid = a.backend_xid)));
 pg_file_settings| SELECT sourcefile,
     sourceline,
     seqno,
-- 
2.47.1