Date: Mon, 26 Jan 2026 06:59:28 +0000
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: Fujii Masao <masao.fujii@gmail.com>
Cc: Sami Imseih <samimseih@gmail.com>,
	Michael Paquier <michael@paquier.xyz>,
	pgsql-hackers@lists.postgresql.org,
	Zsolt Parragi <zsolt.parragi@percona.com>
Subject: Re: Flush some statistics within running transactions
Message-ID: <aXcQ0Bdff5OsUDWY@ip-10-97-1-34.eu-west-3.compute.internal>
References: <CAA5RZ0s9j3x5UPpgaQdDyGA=MNjVEkT73SL7LMoVEKUwiZrVqA@mail.gmail.com>
 <aW4gwqVybB4INCvu@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAA5RZ0s6FkEHFdgKf8J6vueZGwsH+08LvV0YPBXa4Dw_8QgtTw@mail.gmail.com>
 <aXCroXBLiUpxk0Ap@ip-10-97-1-34.eu-west-3.compute.internal>
 <aXFpAisDKy6g_cKx@paquier.xyz>
 <CAA5RZ0sQxAu6bU8wTgvs+aTSvhBcziH9jCJ27aS1hzKsm2kmTQ@mail.gmail.com>
 <CAHGQGwGBkPEK=NpLuk1HSmccu6OA2FYqkkGcA-Hb3WLs6dX=cg@mail.gmail.com>
 <aXHVC6NxERhcThZV@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAHGQGwHttst8tv_WWYNoGGfL1UAq4kiy6dpFXoxEkJwHMS9FtQ@mail.gmail.com>
 <aXJUK90lKXw3wrZn@ip-10-97-1-34.eu-west-3.compute.internal>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="8uxM0EZUDIpqMe6c"
Content-Disposition: inline
In-Reply-To: <aXJUK90lKXw3wrZn@ip-10-97-1-34.eu-west-3.compute.internal>
Archived-At: <https://www.postgresql.org/message-id/aXcQ0Bdff5OsUDWY%40ip-10-97-1-34.eu-west-3.compute.internal>
Precedence: bulk


--8uxM0EZUDIpqMe6c
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Hi,

On Thu, Jan 22, 2026 at 04:45:31PM +0000, Bertrand Drouvot wrote:
> On Thu, Jan 22, 2026 at 09:12:18PM +0900, Fujii Masao wrote:
> > 
> > With this setup, the following messages were logged once per second:
> > 
> >     LOG:  process 72199 still waiting for ShareLock on transaction 771
> > after 63034.119 ms
> >     DETAIL:  Process holding the lock: 72190. Wait queue: 72199.
> > 
> 
> Thanks!
> 
> I see, the WaitLatch() in ProcSleep() is "woken up" every 1s due to the 
> enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT,...) being set unconditionally
> in ProcessInterrupts(). We need to be more restrictive as to when to enable the
> timeout, I'll fix in the next version.

The attached, to apply on top of 0001, fix the issue. However it handles only the
WaitLatch in ProcSleep() case and I start to have concern about the others WaitLatch()
that would/could be "woken up" every 1s.

Using disable_timeout() and enable_timeout_after() in WaitEventSetWait() does not
look like a great answer to this concern, so I wonder if we should use a larger
flush frequency instead (as proposed up-thread), thoughts? 

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

--8uxM0EZUDIpqMe6c
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="fix_ProcSleep.txt"

diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index 063826ae576..7376dd1f316 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -1322,6 +1322,7 @@ ProcSleep(LOCALLOCK *locallock)
 	bool		allow_autovacuum_cancel = true;
 	bool		logged_recovery_conflict = false;
 	ProcWaitStatus myWaitStatus;
+	bool anytime_timeout_was_active = false;
 
 	/* The caller must've armed the on-error cleanup mechanism */
 	Assert(GetAwaitedLock() == locallock);
@@ -1398,6 +1399,10 @@ ProcSleep(LOCALLOCK *locallock)
 		standbyWaitStart = GetCurrentTimestamp();
 	}
 
+	anytime_timeout_was_active = get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT);
+	if (anytime_timeout_was_active)
+		disable_timeout(ANYTIME_STATS_UPDATE_TIMEOUT, false);
+
 	/*
 	 * If somebody wakes us between LWLockRelease and WaitLatch, the latch
 	 * will not wait. But a set latch does not necessarily mean that the lock
@@ -1661,6 +1666,9 @@ ProcSleep(LOCALLOCK *locallock)
 		}
 	} while (myWaitStatus == PROC_WAIT_STATUS_WAITING);
 
+	if (anytime_timeout_was_active)
+		enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL);
+
 	/*
 	 * Disable the timers, if they are still running.  As in LockErrorCleanup,
 	 * we must preserve the LOCK_TIMEOUT indicator flag: if a lock timeout has

--8uxM0EZUDIpqMe6c--