v3-0001-Add-pg_buffercache_evict_-relation-all-functions-.patch

From 522d285af9e2294efe02f67c964e264f35e855c4 Mon Sep 17 00:00:00 2001
From: Nazir Bilal Yavuz <byavuz81@gmail.com>
Date: Mon, 24 Mar 2025 13:52:04 +0300
Subject: [PATCH v3 1/3] Add pg_buffercache_evict_[relation | all]() functions
 for testing

pg_buffercache_evict_relation(): Evicts all shared buffers in a
relation at once.
pg_buffercache_evict_all(): Evicts all shared buffers at once.

Both functions provide mechanism to evict multiple shared buffers at
once. They are designed to address the inefficiency of repeatedly calling
pg_buffercache_evict() for each individual buffer, which can be
time-consuming when dealing with large shared buffer pools.
(e.g., ~790ms vs. ~270ms for 16GB of shared buffers).

These functions are intended for developer testing and debugging
purposes and are available to superusers only.

Author: Nazir Bilal Yavuz <byavuz81@gmail.com>
Reviewed-by: Andres Freund <andres@anarazel.de>
Reviewed-by: Aidar Imamov <a.imamov@postgrespro.ru>
Reviewed-by: Joseph Koshakow <koshy44@gmail.com>
Discussion: https://postgr.es/m/CAN55FZ0h_YoSqqutxV6DES1RW8ig6wcA8CR9rJk358YRMxZFmw%40mail.gmail.com
---
 src/include/storage/bufmgr.h                  |   3 +-
 src/backend/storage/buffer/bufmgr.c           |  83 ++++++++++++--
 doc/src/sgml/pgbuffercache.sgml               |  56 ++++++++-
 contrib/pg_buffercache/Makefile               |   3 +-
 contrib/pg_buffercache/meson.build            |   1 +
 .../pg_buffercache--1.5--1.6.sql              |  16 +++
 contrib/pg_buffercache/pg_buffercache.control |   2 +-
 contrib/pg_buffercache/pg_buffercache_pages.c | 108 ++++++++++++++++++
 8 files changed, 260 insertions(+), 12 deletions(-)
 create mode 100644 contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql

diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h
index 538b890a51d..e068319b736 100644
--- a/src/include/storage/bufmgr.h
+++ b/src/include/storage/bufmgr.h
@@ -298,7 +298,8 @@ extern uint32 GetAdditionalLocalPinLimit(void);
 extern void LimitAdditionalPins(uint32 *additional_pins);
 extern void LimitAdditionalLocalPins(uint32 *additional_pins);
 
-extern bool EvictUnpinnedBuffer(Buffer buf);
+extern bool EvictUnpinnedBuffer(Buffer buf, uint32 buf_state, bool *flushed);
+extern void EvictUnpinnedBuffersFromSharedRelation(Relation rel, int32 *buffers_evicted, int32 *buffers_flushed);
 
 /* in buf_init.c */
 extern void BufferManagerShmemInit(void);
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 323382dcfa8..74cb1ef1b9f 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -6078,26 +6078,40 @@ ResOwnerPrintBufferPin(Datum res)
  * even by the same block.  This inherent raciness without other interlocking
  * makes the function unsuitable for non-testing usage.
  *
+ * flushed is set to true if the buffer is flushed but this does not
+ * necessarily mean that the buffer is flushed by us, it might be flushed by
+ * someone else.
+ *
  * Returns true if the buffer was valid and it has now been made invalid.
  * Returns false if it wasn't valid, if it couldn't be evicted due to a pin,
  * or if the buffer becomes dirty again while we're trying to write it out.
  */
 bool
-EvictUnpinnedBuffer(Buffer buf)
+EvictUnpinnedBuffer(Buffer buf, uint32 buf_state, bool *flushed)
 {
 	BufferDesc *desc;
-	uint32		buf_state;
 	bool		result;
 
-	/* Make sure we can pin the buffer. */
-	ResourceOwnerEnlarge(CurrentResourceOwner);
-	ReservePrivateRefCountEntry();
+	*flushed = false;
 
 	Assert(!BufferIsLocal(buf));
 	desc = GetBufferDescriptor(buf - 1);
 
-	/* Lock the header and check if it's valid. */
-	buf_state = LockBufHdr(desc);
+	/*
+	 * If the buffer is already locked, we assume that preparations to pinning
+	 * buffer are already done.
+	 */
+	if (!buf_state)
+	{
+		/* Make sure we can pin the buffer. */
+		ResourceOwnerEnlarge(CurrentResourceOwner);
+		ReservePrivateRefCountEntry();
+
+		/* Lock the header if it is not already locked. */
+		buf_state = LockBufHdr(desc);
+	}
+
+	/* Check if it's valid. */
 	if ((buf_state & BM_VALID) == 0)
 	{
 		UnlockBufHdr(desc, buf_state);
@@ -6119,6 +6133,7 @@ EvictUnpinnedBuffer(Buffer buf)
 		LWLockAcquire(BufferDescriptorGetContentLock(desc), LW_SHARED);
 		FlushBuffer(desc, NULL, IOOBJECT_RELATION, IOCONTEXT_NORMAL);
 		LWLockRelease(BufferDescriptorGetContentLock(desc));
+		*flushed = true;
 	}
 
 	/* This will return false if it becomes dirty or someone else pins it. */
@@ -6128,3 +6143,57 @@ EvictUnpinnedBuffer(Buffer buf)
 
 	return result;
 }
+
+/*
+ * Try to evict all shared buffers in the relation by calling
+ * EvictUnpinnedBuffer() for all the shared buffers in the relation.
+ *
+ * We need this helper function because of the following reason.
+ * ReservePrivateRefCountEntry() needs to be called before acquiring the
+ * buffer header lock but ReservePrivateRefCountEntry() is static and it would
+ * be better to have it as static. Hence, it can't be called from outside of
+ * this file. This helper function is created to bypass that problem.
+ *
+ * buffers_evicted and buffers_flushed is set the total number of buffers
+ * evicted and flushed respectively.
+ *
+ * If the relation uses local buffers retun false, otherwise return true.
+ */
+void
+EvictUnpinnedBuffersFromSharedRelation(Relation rel, int32 *buffers_evicted, int32 *buffers_flushed)
+{
+	bool		flushed;
+
+	*buffers_evicted = *buffers_flushed = 0;
+
+	Assert(!RelationUsesLocalBuffers(rel));
+
+	for (int buf = 1; buf <= NBuffers; buf++)
+	{
+		uint32		buf_state = 0;
+		BufferDesc *bufHdr = GetBufferDescriptor(buf - 1);
+
+		/* An unlocked precheck should be safe and saves some cycles. */
+		if (!BufTagMatchesRelFileLocator(&bufHdr->tag, &rel->rd_locator))
+			continue;
+
+		/* Make sure we can pin the buffer. */
+		ResourceOwnerEnlarge(CurrentResourceOwner);
+		ReservePrivateRefCountEntry();
+
+		/*
+		 * No need to call UnlockBufHdr() if BufTagMatchesRelFileLocator()
+		 * returns true, EvictUnpinnedBuffer() will take care of it.
+		 */
+		buf_state = LockBufHdr(bufHdr);
+		if (BufTagMatchesRelFileLocator(&bufHdr->tag, &rel->rd_locator))
+		{
+			if (EvictUnpinnedBuffer(buf, buf_state, &flushed))
+				(*buffers_evicted)++;
+			if (flushed)
+				(*buffers_flushed)++;
+		}
+		else
+			UnlockBufHdr(bufHdr, buf_state);
+	}
+}
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index 802a5112d77..d99aa979410 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -27,12 +27,22 @@
   <primary>pg_buffercache_evict</primary>
  </indexterm>
 
+ <indexterm>
+  <primary>pg_buffercache_evict_relation</primary>
+ </indexterm>
+
+ <indexterm>
+  <primary>pg_buffercache_evict_all</primary>
+ </indexterm>
+
  <para>
   This module provides the <function>pg_buffercache_pages()</function>
   function (wrapped in the <structname>pg_buffercache</structname> view),
   the <function>pg_buffercache_summary()</function> function, the
-  <function>pg_buffercache_usage_counts()</function> function and
-  the <function>pg_buffercache_evict()</function> function.
+  <function>pg_buffercache_usage_counts()</function> function, the
+  <function>pg_buffercache_evict()</function>, the
+  <function>pg_buffercache_evict_relation()</function> function and the
+  <function>pg_buffercache_evict_all()</function> function.
  </para>
 
  <para>
@@ -65,6 +75,18 @@
   function is restricted to superusers only.
  </para>
 
+ <para>
+  The <function>pg_buffercache_evict_relation()</function> function allows all
+  shared buffers in the relation to be evicted from the buffer pool given a
+  relation identifier.  Use of this function is restricted to superusers only.
+ </para>
+
+ <para>
+  The <function>pg_buffercache_evict_all()</function> function allows all
+  shared buffers to be evicted in the buffer pool.  Use of this function is
+  restricted to superusers only.
+ </para>
+
  <sect2 id="pgbuffercache-pg-buffercache">
   <title>The <structname>pg_buffercache</structname> View</title>
 
@@ -378,6 +400,36 @@
   </para>
  </sect2>
 
+ <sect2 id="pgbuffercache-pg-buffercache-evict-relation">
+  <title>The <structname>pg_buffercache_evict_relation</structname> Function</title>
+  <para>
+   The <function>pg_buffercache_evict_relation()</function> function is very
+   similar to the <function>pg_buffercache_evict()</function> function.  The
+   difference is that the <function>pg_buffercache_evict_relation()</function>
+   takes a relation identifier instead of buffer identifier.  Then, it tries
+   to evict all buffers in that relation.  It returns the number of evicted and
+   flushed buffers.  Flushed buffers aren't necessarily flushed by us, they
+   might be flushed by someone else.  The result is immediately out of date
+   upon return, as the buffer might become valid again at any time due to
+   concurrent activity.  The function is intended for developer testing only.
+  </para>
+ </sect2>
+
+ <sect2 id="pgbuffercache-pg-buffercache-evict-all">
+  <title>The <structname>pg_buffercache_evict_all</structname> Function</title>
+  <para>
+   The <function>pg_buffercache_evict_all()</function> function is very
+   similar to the <function>pg_buffercache_evict()</function> function.  The
+   difference is, the <function>pg_buffercache_evict_all()</function> function
+   does not take an argument; instead it tries to evict all buffers in the
+   buffer pool.  It returns the number of evicted and flushed buffers.
+   Flushed buffers aren't necessarily flushed by us, they might be flushed by
+   someone else.  The result is immediately out of date upon return, as the
+   buffer might become valid again at any time due to concurrent activity.
+   The function is intended for developer testing only.
+  </para>
+ </sect2>
+
 <sect2 id="pgbuffercache-sample-output">
   <title>Sample Output</title>
 
diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile
index eae65ead9e5..2a33602537e 100644
--- a/contrib/pg_buffercache/Makefile
+++ b/contrib/pg_buffercache/Makefile
@@ -8,7 +8,8 @@ OBJS = \
 EXTENSION = pg_buffercache
 DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \
 	pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \
-	pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.sql
+	pg_buffercache--1.3--1.4.sql pg_buffercache--1.4--1.5.sql \
+	pg_buffercache--1.5--1.6.sql
 PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time"
 
 REGRESS = pg_buffercache
diff --git a/contrib/pg_buffercache/meson.build b/contrib/pg_buffercache/meson.build
index 12d1fe48717..9b2e9393410 100644
--- a/contrib/pg_buffercache/meson.build
+++ b/contrib/pg_buffercache/meson.build
@@ -23,6 +23,7 @@ install_data(
   'pg_buffercache--1.2.sql',
   'pg_buffercache--1.3--1.4.sql',
   'pg_buffercache--1.4--1.5.sql',
+  'pg_buffercache--1.5--1.6.sql',
   'pg_buffercache.control',
   kwargs: contrib_data_args,
 )
diff --git a/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql
new file mode 100644
index 00000000000..2494a0a19b1
--- /dev/null
+++ b/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql
@@ -0,0 +1,16 @@
+\echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.6'" to load this file. \quit
+
+CREATE FUNCTION pg_buffercache_evict_relation(
+    IN regclass,
+    OUT buffers_evicted int4,
+    OUT buffers_flushed int4)
+RETURNS record
+AS 'MODULE_PATHNAME', 'pg_buffercache_evict_relation'
+LANGUAGE C PARALLEL SAFE VOLATILE STRICT;
+
+CREATE FUNCTION pg_buffercache_evict_all(
+    OUT buffers_evicted int4,
+    OUT buffers_flushed int4)
+RETURNS record
+AS 'MODULE_PATHNAME', 'pg_buffercache_evict_all'
+LANGUAGE C PARALLEL SAFE VOLATILE;
diff --git a/contrib/pg_buffercache/pg_buffercache.control b/contrib/pg_buffercache/pg_buffercache.control
index 5ee875f77dd..b030ba3a6fa 100644
--- a/contrib/pg_buffercache/pg_buffercache.control
+++ b/contrib/pg_buffercache/pg_buffercache.control
@@ -1,5 +1,5 @@
 # pg_buffercache extension
 comment = 'examine the shared buffer cache'
-default_version = '1.5'
+default_version = '1.6'
 module_pathname = '$libdir/pg_buffercache'
 relocatable = true
diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c
index 3ae0a018e10..7d3bb50b942 100644
--- a/contrib/pg_buffercache/pg_buffercache_pages.c
+++ b/contrib/pg_buffercache/pg_buffercache_pages.c
@@ -9,16 +9,21 @@
 #include "postgres.h"
 
 #include "access/htup_details.h"
+#include "access/relation.h"
 #include "catalog/pg_type.h"
 #include "funcapi.h"
 #include "storage/buf_internals.h"
 #include "storage/bufmgr.h"
+#include "utils/builtins.h"
+#include "utils/rel.h"
 
 
 #define NUM_BUFFERCACHE_PAGES_MIN_ELEM	8
 #define NUM_BUFFERCACHE_PAGES_ELEM	9
 #define NUM_BUFFERCACHE_SUMMARY_ELEM 5
 #define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4
+#define NUM_BUFFERCACHE_EVICT_RELATION_ELEM 2
+#define NUM_BUFFERCACHE_EVICT_ALL_ELEM 2
 
 PG_MODULE_MAGIC;
 
@@ -64,6 +69,8 @@ PG_FUNCTION_INFO_V1(pg_buffercache_pages);
 PG_FUNCTION_INFO_V1(pg_buffercache_summary);
 PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts);
 PG_FUNCTION_INFO_V1(pg_buffercache_evict);
+PG_FUNCTION_INFO_V1(pg_buffercache_evict_relation);
+PG_FUNCTION_INFO_V1(pg_buffercache_evict_all);
 
 Datum
 pg_buffercache_pages(PG_FUNCTION_ARGS)
@@ -367,3 +374,104 @@ pg_buffercache_evict(PG_FUNCTION_ARGS)
 
 	PG_RETURN_BOOL(EvictUnpinnedBuffer(buf));
 }
+
+/*
+ * Try to evict specified relation.
+ */
+Datum
+pg_buffercache_evict_relation(PG_FUNCTION_ARGS)
+{
+	Datum		result;
+	TupleDesc	tupledesc;
+	HeapTuple	tuple;
+	Datum		values[NUM_BUFFERCACHE_EVICT_RELATION_ELEM];
+	bool		nulls[NUM_BUFFERCACHE_EVICT_RELATION_ELEM] = {0};
+
+	Oid			relOid;
+	Relation	rel;
+	int32		buffers_evicted = 0;
+	int32		buffers_flushed = 0;
+
+	if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	if (!superuser())
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("must be superuser to use pg_buffercache_evict_relation function")));
+
+	if (PG_ARGISNULL(0))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("relation cannot be null")));
+
+
+	relOid = PG_GETARG_OID(0);
+
+	/* Open relation. */
+	rel = relation_open(relOid, AccessExclusiveLock);
+
+	if (RelationUsesLocalBuffers(rel))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("relation uses local buffers,"
+						"pg_buffercache_evict_relation function is intended to"
+						"used for shared buffers only")));
+
+	EvictUnpinnedBuffersFromSharedRelation(rel, &buffers_evicted, &buffers_flushed);
+
+	/* Close relation, release lock. */
+	relation_close(rel, AccessExclusiveLock);
+
+	values[0] = Int32GetDatum(buffers_evicted);
+	values[1] = Int32GetDatum(buffers_flushed);
+
+	/* Build and return the tuple. */
+	tuple = heap_form_tuple(tupledesc, values, nulls);
+	result = HeapTupleGetDatum(tuple);
+
+	PG_RETURN_DATUM(result);
+}
+
+
+/*
+ * Try to evict all shared buffers.
+ */
+Datum
+pg_buffercache_evict_all(PG_FUNCTION_ARGS)
+{
+	Datum		result;
+	TupleDesc	tupledesc;
+	HeapTuple	tuple;
+	Datum		values[NUM_BUFFERCACHE_EVICT_ALL_ELEM];
+	bool		nulls[NUM_BUFFERCACHE_EVICT_ALL_ELEM] = {0};
+
+	int32		buffers_evicted = 0;
+	int32		buffers_flushed = 0;
+	bool		flushed;
+
+	if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	if (!superuser())
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("must be superuser to use pg_buffercache_evict_all function")));
+
+	for (int buf = 1; buf <= NBuffers; buf++)
+	{
+		if (EvictUnpinnedBuffer(buf, 0, &flushed))
+			buffers_evicted++;
+		if (flushed)
+			buffers_flushed++;
+	}
+
+	values[0] = Int32GetDatum(buffers_evicted);
+	values[1] = Int32GetDatum(buffers_flushed);
+
+	/* Build and return the tuple. */
+	tuple = heap_form_tuple(tupledesc, values, nulls);
+	result = HeapTupleGetDatum(tuple);
+
+	PG_RETURN_DATUM(result);
+}
-- 
2.47.2