From 3dfcb99c8208d6c121d9e32231d1b111a498cbd2 Mon Sep 17 00:00:00 2001
From: alterego665 <824662526@qq.com>
Date: Sun, 15 Jun 2025 15:47:30 +0800
Subject: [PATCH v3] Add threshold-based sleep to XactLockTableWait functions

XactLockTableWait() and ConditionalXactLockTableWait() currently use
a fixed 1ms sleep when waiting for transaction completion. In logical
replication scenarios, particularly during CREATE REPLICATION SLOT,
these functions may wait for very long periods (minutes to hours) for
old transactions to complete, leading to excessive CPU usage due to
frequent polling.

This patch implements a threshold-based approach: sleep for 1ms for
the first 5 seconds (5000 iterations), then switch to 1s sleeps for
the remainder of the wait. This balances responsiveness for normal
operations (which typically complete within seconds) against CPU
efficiency for long waits common in logical replication scenarios.
---
 src/backend/storage/lmgr/lmgr.c | 26 ++++++++++++++++++++++----
 1 file changed, 22 insertions(+), 4 deletions(-)

diff --git a/src/backend/storage/lmgr/lmgr.c b/src/backend/storage/lmgr/lmgr.c
index 3f6bf70bd3c..c81b2fbe849 100644
--- a/src/backend/storage/lmgr/lmgr.c
+++ b/src/backend/storage/lmgr/lmgr.c
@@ -667,6 +667,7 @@ XactLockTableWait(TransactionId xid, Relation rel, ItemPointer ctid,
 	XactLockTableWaitInfo info;
 	ErrorContextCallback callback;
 	bool		first = true;
+	int			left_till_hibernate = 5000;
 
 	/*
 	 * If an operation is specified, set up our verbose error context
@@ -713,13 +714,22 @@ XactLockTableWait(TransactionId xid, Relation rel, ItemPointer ctid,
 		 * as when building snapshots for logical decoding.  It is possible to
 		 * see a transaction in ProcArray before it registers itself in the
 		 * locktable.  The topmost transaction in that case is the same xid,
-		 * so we try again after a short sleep.  (Don't sleep the first time
-		 * through, to avoid slowing down the normal case.)
+		 * so we try again after a sleep.  We sleep 1ms for the first 5 seconds
+		 * to keep normal operations responsive, then 1s to reduce CPU overhead
+		 * during long waits.  (Don't sleep the first time through, to avoid
+		 * slowing down the normal case.)
 		 */
 		if (!first)
 		{
 			CHECK_FOR_INTERRUPTS();
-			pg_usleep(1000L);
+
+			if (left_till_hibernate > 0)
+			{
+				pg_usleep(1000L);
+				left_till_hibernate--;
+			}
+			else
+				pg_usleep(1000000L); /* 1s */
 		}
 		first = false;
 		xid = SubTransGetTopmostTransaction(xid);
@@ -740,6 +750,7 @@ ConditionalXactLockTableWait(TransactionId xid, bool logLockFailure)
 {
 	LOCKTAG		tag;
 	bool		first = true;
+	int			left_till_hibernate = 5000;
 
 	for (;;)
 	{
@@ -762,7 +773,14 @@ ConditionalXactLockTableWait(TransactionId xid, bool logLockFailure)
 		if (!first)
 		{
 			CHECK_FOR_INTERRUPTS();
-			pg_usleep(1000L);
+
+			if (left_till_hibernate > 0)
+			{
+				pg_usleep(1000L);
+				left_till_hibernate--;
+			}
+			else
+				pg_usleep(1000000L);
 		}
 		first = false;
 		xid = SubTransGetTopmostTransaction(xid);
-- 
2.48.1