0001-Add-system-view-for-shared-buffer-lookup-ta-20250918.patch

From cc90e3f74fe4a14ba95e11664ac68b8daa3ba056 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: Mon, 25 Aug 2025 19:23:50 +0530
Subject: [PATCH 01/16] Add system view for shared buffer lookup table

The view exposes the contents of the shared buffer lookup table for
debugging, testing and investigation.

TODO:

It is better to place this view in pg_buffercache. But it's added as a
system view since BufHashTable is not exposed outside buf_table.c. To
move it to pg_buffercache, we should move the function
pg_get_buffer_lookup_table() to pg_buffercache which invokes
BufTableGetContent() by passing it the tuple store and tuple descriptor.
BufTableGetContent fills the tuple store. The partitions are locked by
pg_get_buffer_lookup_table().

Author: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
---
 doc/src/sgml/system-views.sgml         | 89 ++++++++++++++++++++++++++
 src/backend/catalog/system_views.sql   |  7 ++
 src/backend/storage/buffer/buf_table.c | 61 ++++++++++++++++++
 src/include/catalog/pg_proc.dat        | 11 ++++
 src/test/regress/expected/rules.out    |  7 ++
 5 files changed, 175 insertions(+)

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 4187191ea74..89be9bc333f 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -71,6 +71,11 @@
       <entry>backend memory contexts</entry>
      </row>
 
+     <row>
+      <entry><link linkend="view-pg-buffer-lookup-table"><structname>pg_buffer_lookup_table</structname></link></entry>
+      <entry>shared buffer lookup table</entry>
+     </row>
+
      <row>
       <entry><link linkend="view-pg-config"><structname>pg_config</structname></link></entry>
       <entry>compile-time configuration parameters</entry>
@@ -896,6 +901,90 @@ AND c1.path[c2.level] = c2.path[c2.level];
   </para>
  </sect1>
 
+ <sect1 id="view-pg-buffer-lookup-table">
+  <title><structname>pg_buffer_lookup_table</structname></title>
+  <indexterm>
+   <primary>pg_buffer_lookup_table</primary>
+  </indexterm>
+  <para>
+   The <structname>pg_buffer_lookup_table</structname> view exposes the current
+   contents of the shared buffer lookup table. Each row represents an entry in
+   the lookup table mapping a relation page to the ID of buffer in which it is
+   cached. The shared buffer lookup table is locked for a short duration while
+   reading so as to ensure consistency. This may affect performance if this view
+   is queried very frequently.
+  </para>
+  <table id="pg-buffer-lookup-table-view" xreflabel="pg_buffer_lookup_table">
+   <title><structname>pg_buffer_lookup_table</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>tablespace</structfield> <type>oid</type>
+      </para>
+      <para>
+       OID of the tablespace containing the relation
+      </para></entry>
+     </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>database</structfield> <type>oid</type>
+      </para>
+      <para>
+       OID of the database containing the relation (zero for shared relations)
+      </para></entry>
+     </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>relfilenode</structfield> <type>oid</type>
+      </para>
+      <para>
+       relfilenode identifying the relation
+      </para></entry>
+     </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>forknum</structfield> <type>int2</type>
+      </para>
+      <para>
+       Fork number within the relation (see <xref linkend="storage-file-layout"/>)
+      </para></entry>
+     </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>blocknum</structfield> <type>int8</type>
+      </para>
+      <para>
+       Block number within the relation
+      </para></entry>
+     </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>bufferid</structfield> <type>int4</type>
+      </para>
+      <para>
+       ID of the buffer caching the page 
+      </para></entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+  <para>
+   Access to this view is restricted to members of the
+   <literal>pg_read_all_stats</literal> role by default.
+  </para>
+ </sect1>
+
  <sect1 id="view-pg-config">
   <title><structname>pg_config</structname></title>
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index c77fa0234bb..46fc28396de 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1420,3 +1420,10 @@ REVOKE ALL ON pg_aios FROM PUBLIC;
 GRANT SELECT ON pg_aios TO pg_read_all_stats;
 REVOKE EXECUTE ON FUNCTION pg_get_aios() FROM PUBLIC;
 GRANT EXECUTE ON FUNCTION pg_get_aios() TO pg_read_all_stats;
+
+CREATE VIEW pg_buffer_lookup_table AS
+    SELECT * FROM pg_get_buffer_lookup_table();
+REVOKE ALL ON pg_buffer_lookup_table FROM PUBLIC;
+GRANT SELECT ON pg_buffer_lookup_table TO pg_read_all_stats;
+REVOKE EXECUTE ON FUNCTION pg_get_buffer_lookup_table() FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pg_get_buffer_lookup_table() TO pg_read_all_stats;
diff --git a/src/backend/storage/buffer/buf_table.c b/src/backend/storage/buffer/buf_table.c
index 9d256559bab..1f6e215a2ca 100644
--- a/src/backend/storage/buffer/buf_table.c
+++ b/src/backend/storage/buffer/buf_table.c
@@ -21,7 +21,12 @@
  */
 #include "postgres.h"
 
+#include "fmgr.h"
+#include "funcapi.h"
 #include "storage/buf_internals.h"
+#include "storage/lwlock.h"
+#include "utils/rel.h"
+#include "utils/builtins.h"
 
 /* entry for buffer lookup hashtable */
 typedef struct
@@ -159,3 +164,59 @@ BufTableDelete(BufferTag *tagPtr, uint32 hashcode)
 	if (!result)				/* shouldn't happen */
 		elog(ERROR, "shared buffer hash table corrupted");
 }
+
+/*
+ * SQL callable function to report contents of the shared buffer lookup table.
+ */
+Datum
+pg_get_buffer_lookup_table(PG_FUNCTION_ARGS)
+{
+#define PG_GET_BUFFER_LOOKUP_TABLE_COLS 6
+	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+	HASH_SEQ_STATUS hstat;
+	BufferLookupEnt *ent;
+	Datum		values[PG_GET_BUFFER_LOOKUP_TABLE_COLS];
+	bool		nulls[PG_GET_BUFFER_LOOKUP_TABLE_COLS];
+	int			i;
+
+	memset(nulls, 0, sizeof(nulls));
+
+	/*
+	 * We put all the tuples into a tuplestore in one scan of the hashtable.
+	 * This avoids any issue of the hashtable possibly changing between calls.
+	 */
+	InitMaterializedSRF(fcinfo, 0);
+
+	Assert(rsinfo->setDesc->natts == PG_GET_BUFFER_LOOKUP_TABLE_COLS);
+
+	/*
+	 * Lock all buffer mapping partitions to ensure a consistent view of the
+	 * hash table during the scan. Must grab LWLocks in partition-number order
+	 * to avoid LWLock deadlock.
+	 */
+	for (i = 0; i < NUM_BUFFER_PARTITIONS; i++)
+		LWLockAcquire(BufMappingPartitionLockByIndex(i), LW_SHARED);
+
+	hash_seq_init(&hstat, SharedBufHash);
+	while ((ent = (BufferLookupEnt *) hash_seq_search(&hstat)) != NULL)
+	{
+		values[0] = ObjectIdGetDatum(ent->key.spcOid);
+		values[1] = ObjectIdGetDatum(ent->key.dbOid);
+		values[2] = ObjectIdGetDatum(ent->key.relNumber);
+		values[3] = ObjectIdGetDatum(ent->key.forkNum);
+		values[4] = UInt32GetDatum(ent->key.blockNum);
+		values[5] = Int32GetDatum(ent->id);
+
+		tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+							 values, nulls);
+	}
+
+	/*
+	 * Release all buffer mapping partition locks in the reverse order so as
+	 * to avoid LWLock deadlock.
+	 */
+	for (i = NUM_BUFFER_PARTITIONS - 1; i >= 0; i--)
+		LWLockRelease(BufMappingPartitionLockByIndex(i));
+
+	return (Datum) 0;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 03e82d28c87..1e53b7a4ae5 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8592,6 +8592,17 @@
   proargmodes => '{o,o,o}', proargnames => '{name,type,size}',
   prosrc => 'pg_get_dsm_registry_allocations' },
 
+# buffer lookup table
+{ oid => '5102',
+  descr => 'shared buffer lookup table',
+  proname => 'pg_get_buffer_lookup_table', prorows => '6', proretset => 't',
+  provolatile => 'v', prorettype => 'record',
+  proargtypes => '', proallargtypes => '{oid,oid,oid,int2,int8,int4}',
+  proargmodes => '{o,o,o,o,o,o}',
+  proargnames => '{tablespace,database,relfilenode,forknum,blocknum,bufferid}',
+  prosrc => 'pg_get_buffer_lookup_table'
+},
+
 # memory context of local backend
 { oid => '2282',
   descr => 'information about all memory contexts of local backend',
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 35e8aad7701..760bb13fe95 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1330,6 +1330,13 @@ pg_backend_memory_contexts| SELECT name,
     free_chunks,
     used_bytes
    FROM pg_get_backend_memory_contexts() pg_get_backend_memory_contexts(name, ident, type, level, path, total_bytes, total_nblocks, free_bytes, free_chunks, used_bytes);
+pg_buffer_lookup_table| SELECT tablespace,
+    database,
+    relfilenode,
+    forknum,
+    blocknum,
+    bufferid
+   FROM pg_get_buffer_lookup_table() pg_get_buffer_lookup_table(tablespace, database, relfilenode, forknum, blocknum, bufferid);
 pg_config| SELECT name,
     setting
    FROM pg_config() pg_config(name, setting);

base-commit: 2e66cae935c2e0f7ce9bab6b65ddeb7806f4de7c
-- 
2.34.1