From: Fujii Masao <masao.fujii@oss.nttdata.com>
To: Andres Freund <andres@anarazel.de>
Cc: Fujii Masao <masao.fujii@gmail.com>
Cc: maxim.boguk@gmail.com
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #10123: Weird entries in pg_stat_activity
Date: Thu, 17 Oct 2024 13:19:52 +0900
Message-ID: <b5c6f74d-302a-41a9-8b9f-7b5ceb230d4a@oss.nttdata.com> (raw)
In-Reply-To: <d7a858f9-404f-4535-8160-3222e43cf8c5@oss.nttdata.com>
References: <20140424101827.2714.39486@wrigleys.postgresql.org>
<CAHGQGwEQWk6J+vqQnnVFFngtMdMuw-AwuEFnq=u3Wb2STXu3TA@mail.gmail.com>
<2153c106-8979-430d-ba86-34d35e565ab4@oss.nttdata.com>
<20240704220316.srzqzkb7zgzh76xy@alap3.anarazel.de>
<d7a858f9-404f-4535-8160-3222e43cf8c5@oss.nttdata.com>
On 2024/10/17 1:59, Fujii Masao wrote:
>
>
> On 2024/07/05 7:03, Andres Freund wrote:
>> Calls to pgstat_report_activity() aren't exactly free. This substantially
>> increases the number of calls to it for common workloads. There got to be a
>> more targeted way of dealing with this.
>
> Yes. So, as an alternative approach, how about setting xact_start and
> query_start to NULL whenever the state is idle, since non-NULL values
> aren't expected in that case? This would prevent the weired pg_stat_activity
> entries I mentioned earlier, with minimal performance impact.
Patch attached.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
Attachments:v2-0001-Prevent-misleading-entries-in-pg_stat_activity.patch (text/plain, 3.2K) download
From e9040fe4c26378daa45b8dea752a0c0ee8b256a9 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Thu, 17 Oct 2024 11:52:20 +0900
Subject: [PATCH v2] Prevent misleading entries in pg_stat_activity.
In some cases, such as handling "Describe" messages, cleaning up
temporary relations at exit, or managing client read interrupts,
backends can remain in an "idle" state but still set xact_start
in pg_stat_activity. This results in misleading entries where
"idle" backends appear to have non-NULL transaction times.
Additionally, during temp relation cleanup or interrupt handling,
xact_start might incorrectly reflect the timestamp of
the last executed query, further confusing users.
This commit addresses the issue by ensuring pg_stat_activity
does not show transaction and query start times for "idle" backends,
setting them to NULL instead. This prevents confusing entries
for "idle" backends.
While it would be possible to correctly track backend states and
set timestamps, this approach would introduce performance overhead
due to frequent updates of state and timestamp. Moreover, it is not
particularly beneficial to treat processes like "Describe" message handling,
temp relation cleanup, or client read interrupts as regular transactions.
Therefore, the simpler and more efficient solution was adopted.
Discussion: https://postgr.es/m/20140424101827.2714.39486@wrigleys.postgresql.org
---
src/backend/utils/adt/pgstatfuncs.c | 21 +++++++++++++++++++--
1 file changed, 19 insertions(+), 2 deletions(-)
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.cindex f7b50e0b5a..5967b6afb5 100644--- a/src/backend/utils/adt/pgstatfuncs.c+++ b/src/backend/utils/adt/pgstatfuncs.c@@ -460,14 +460,31 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
* Don't expose transaction time for walsenders; it confuses
* monitoring, particularly because we don't keep the time up-to-
* date.
+ *+ * Also, don't show transaction time for backends in the "idle"+ * state. There are cases, like during "Describe" message+ * handling, removing temporary relations at exit, or processing+ * client read interrupts, where the backend remains "idle" but+ * still sets transaction time. This can lead to incorrect "idle"+ * entries with non-NULL transaction times in pg_stat_activity. To+ * prevent these misleading entries, avoid exposing transaction+ * time for idle backends.
*/
if (beentry->st_xact_start_timestamp != 0 &&
- beentry->st_backendType != B_WAL_SENDER)+ beentry->st_backendType != B_WAL_SENDER &&+ (beentry->st_state != STATE_IDLE ||+ beentry->st_backendType != B_BACKEND))
values[8] = TimestampTzGetDatum(beentry->st_xact_start_timestamp);
else
nulls[8] = true;
- if (beentry->st_activity_start_timestamp != 0)+ /*+ * Don't expose query start time for idle backends for the same+ * reasons mentioned above regarding transaction time.+ */+ if (beentry->st_activity_start_timestamp != 0 &&+ (beentry->st_state != STATE_IDLE ||+ beentry->st_backendType != B_BACKEND))
values[9] = TimestampTzGetDatum(beentry->st_activity_start_timestamp);
else
nulls[9] = true;
--
2.46.2
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: masao.fujii@oss.nttdata.com, andres@anarazel.de, masao.fujii@gmail.com, maxim.boguk@gmail.com
Subject: Re: [BUGS] BUG #10123: Weird entries in pg_stat_activity
In-Reply-To: <b5c6f74d-302a-41a9-8b9f-7b5ceb230d4a@oss.nttdata.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox