Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uP1S8-00FtFB-EJ for pgsql-general@arkaria.postgresql.org; Tue, 10 Jun 2025 16:03:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uP1S5-006JUL-8K for pgsql-general@arkaria.postgresql.org; Tue, 10 Jun 2025 16:03:53 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uP1S4-006JQJ-5E for pgsql-general@lists.postgresql.org; Tue, 10 Jun 2025 16:03:53 +0000 Received: from fhigh-b1-smtp.messagingengine.com ([202.12.124.152]) by makus.postgresql.org with smtp (Exim 4.96) (envelope-from ) id 1uP1S2-001H6v-2z for pgsql-general@lists.postgresql.org; Tue, 10 Jun 2025 16:03:51 +0000 Received: from phl-compute-05.internal (phl-compute-05.phl.internal [10.202.2.45]) by mailfhigh.stl.internal (Postfix) with ESMTP id D7A972540124; Tue, 10 Jun 2025 12:03:49 -0400 (EDT) Received: from phl-mailfrontend-02 ([10.202.2.163]) by phl-compute-05.internal (MEProxy); Tue, 10 Jun 2025 12:03:49 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm3; t=1749571429; x=1749657829; bh=QiLYKOX+pu/GICH5RAAtGy563VWR9GOrop629f8nNrE=; b= HHi17DH6JfKGAGdX8ZNK7Cl4+/9JNQBZORIy+YG+hTf/r3w2GNrpkOlmcm1FU8Hc ge3f2XpQx5jloCj5zKIqyk7CGh14JTDn3CcsrTOr/6RxAfU50GMPA2pXXef7C9J4 Dntpi4MNUU8LJk8n0AIDrGuu/XMKLwtjZa3JoyYcncXSjUjQxQJ/jWNbQEp1poA+ aaQBHqXAuU2Oy/pljdXIkJwPYim3hDTFLmwDFIHFIrR7ju8ufc9uWdetBZpJP67s JsXY6N5suILp6k/9W1P+oelJBqfiAy2J0RUDI0PGxnwZr7XMdzGhONDckyp0bLH3 djqtEJi/ZyBdYCIwlCwJCg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1749571429; x= 1749657829; bh=QiLYKOX+pu/GICH5RAAtGy563VWR9GOrop629f8nNrE=; b=U XkIJunYv54yIkvEU3hFkS4mJ9n4Q3qmUbAxClJ5v+kaNSt8Rza+K5Oj2uVLavY/l aL84nRhjJkNwXpleP4cBQTLrUsaukzqqj4DUvwfjCUKVIsflF4yyqZcDFX+wAyNn lqJ/drze2bOXIRZyjtnXwEk9CuzsDaAIsahRIjZLheJbHQ/y5yztJaTveWMwSJxK iPN2BfxRtV2EzldkX8u7+MXWMLRRmKymmRsoWCPFITWMGHnZ91h1fUFaPiE7KWw+ I2UGxuePpvLHu9Y05JrhaS8jbIyRpdWWwX3z5qVcwZ2G58hgRfGgU2xNUP/B249C fWx0hZEmIBbkJYZVZoCDw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeeffedrtddugddutdelhecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpggftfghnshhusghstghrihgsvgdp uffrtefokffrpgfnqfghnecuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivg hnthhsucdlqddutddtmdenucfjughrpefkffggfgfuvfevfhfhjggtgfesthekredttddv jeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvg hrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpefgudefkefhveefleev ieeuveehvdduudekuddvvdelhfeuueeijedtuedvvedvueenucffohhmrghinhepphhosh htghhrvghsqhhlrdhorhhgnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehm rghilhhfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomhdpnh gspghrtghpthhtohepfedpmhhouggvpehsmhhtphhouhhtpdhrtghpthhtohepugguvghv ihgvnhhnvgesghhmrghilhdrtghomhdprhgtphhtthhopehtghhlsehsshhsrdhpghhhrd hprgdruhhspdhrtghpthhtohepphhgshhqlhdqghgvnhgvrhgrlheslhhishhtshdrphho shhtghhrvghsqhhlrdhorhhg X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 10 Jun 2025 12:03:48 -0400 (EDT) Message-ID: <16ff305a-9abe-4cb4-98f2-7276d0bf1910@aklaver.com> Date: Tue, 10 Jun 2025 09:03:48 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: is pg_stat_activity "transactional"? How fast does it update? To: Dominique Devienne , Tom Lane Cc: pgsql-general@lists.postgresql.org References: <755975.1749565677@sss.pgh.pa.us> Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 6/10/25 08:46, Dominique Devienne wrote: > On Tue, Jun 10, 2025 at 4:27 PM Tom Lane wrote: >> Dominique Devienne writes: >>> So when and how fast does pg_stat_activity update, in the face of a >>> PQfinish? >> >> The shared state underlying pg_stat_activity is updated >> immediately when something changes. However ... >> >> PQfinish is asynchronous: it sends a "goodbye" message and then >> closes the connection without waiting for a response. So >> depending on system load, the associated backend could still be >> alive for some milliseconds. There might be other client >> libraries that do that differently. >> >> Another thing that might be biting you is that a backend inspecting >> pg_stat_activity() takes a snapshot of the view's contents and then >> holds onto that snapshot until end of transaction. You can get >> around that with pg_stat_clear_snapshot(). > > Many thanks Tom. Didn't know either of those facts. --DD > For more detail see: https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-VIEWS -- Adrian Klaver adrian.klaver@aklaver.com