MIME-Version: 1.0
From: Ashish Mukherjee <ashish.mukherjee@gmail.com>
Date: Fri, 7 Nov 2025 19:18:57 +0530
Message-ID: <CACgMzfwJQy8QmOFWdb3B1QNBchxC=4PjpBYMk4zgQz_WY8qmLQ@mail.gmail.com>
Subject: Enquiry about long-running queries
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000259757064301720b"
Archived-At: <https://www.postgresql.org/message-id/CACgMzfwJQy8QmOFWdb3B1QNBchxC%3D4PjpBYMk4zgQz_WY8qmLQ%40mail.gmail.com>
Precedence: bulk

--000000000000259757064301720b
Content-Type: text/plain; charset="UTF-8"

Hello,

I have a query like this showing up on my production database -

s05=> SELECT pid, user, usename, application_name, client_addr,
client_hostname, client_port, datname, now() - query_start as "runtime",
state, wait_event_type, wait_event,
substr(query, 0, 100)
 FROM  pg_stat_activity
 WHERE now() - query_start > '5 minutes'::interval and state = 'active'
 ORDER BY runtime DESC;
   pid   | user | usename | application_name |  client_addr  |
client_hostname | client_port | datname |        runtime
       | state  | wait_event_type |   wait_event   |
                        substr
-------+--------+-----------------+----------------+--------------------------------------------------------------------
  356274 | s05  | s05     | scandir          | 192.168.64.61 |
    |       44098 | s05     | 9 days 18:45:37
.65577 | active | IPC             | ParallelFinish | select scac_code from
scac where supported_by_smc = true

The query when run from psql prompt finishes in a jiffy, so query
performance/cost is not the problem. Also, when I try to kill the query
through pg_terminate_backend or pg_cancel_backend, it does not get killed.

I am wondering what could be the root cause of this problem and how it
could be addressed.  Any pointers would be appreciated.

Regards,
Ashish

--000000000000259757064301720b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello,<div><br></div><div>I have a query like this showing=
 up on my production database -=C2=A0<br><div><br></div><div>s05=3D&gt; SEL=
ECT pid, user, usename, application_name, client_addr, client_hostname, cli=
ent_port, datname, now() - query_start as &quot;runtime&quot;,<br>state, wa=
it_event_type, wait_event,<br>substr(query, 0, 100)<br>=C2=A0FROM =C2=A0pg_=
stat_activity<br>=C2=A0WHERE now() - query_start &gt; &#39;5 minutes&#39;::=
interval and state =3D &#39;active&#39;<br>=C2=A0ORDER BY runtime DESC;<br>=
=C2=A0 =C2=A0pid =C2=A0 | user | usename | application_name | =C2=A0client_=
addr =C2=A0| client_hostname | client_port | datname | =C2=A0 =C2=A0 =C2=A0=
 =C2=A0runtime<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0| state =C2=A0| wait_event_typ=
e | =C2=A0 wait_event =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 substr<br>-------+--------+--=
---------------+----------------+------------------------------------------=
--------------------------<br>=C2=A0 356274 | s05 =C2=A0| s05 =C2=A0 =C2=A0=
 | scandir =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 192.168.64.61 | =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 44098 =
| s05 =C2=A0 =C2=A0 | 9 days 18:45:37<br>.65577 | active | IPC =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | ParallelFinish | select scac_code from sc=
ac where supported_by_smc =3D true</div><div><br></div><div>The query when =
run from psql prompt finishes in a jiffy, so query performance/cost is not =
the problem. Also, when I try to kill the query through pg_terminate_backen=
d or pg_cancel_backend, it does not get killed.<br><br>I am wondering what =
could be the root cause of this problem and how it could be addressed.=C2=
=A0 Any pointers would be appreciated.</div><div><br></div><div>Regards,</d=
iv><div>Ashish</div></div></div>

--000000000000259757064301720b--