MIME-Version: 1.0
References: <CACgMzfwJQy8QmOFWdb3B1QNBchxC=4PjpBYMk4zgQz_WY8qmLQ@mail.gmail.com>
In-Reply-To: <CACgMzfwJQy8QmOFWdb3B1QNBchxC=4PjpBYMk4zgQz_WY8qmLQ@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 7 Nov 2025 09:53:45 -0500
Message-ID: <CANzqJaDiGxC6yR1Bog_G4DQNKuRiE9i1eoLBiP4Lyam7MN6Z7A@mail.gmail.com>
Subject: Re: Enquiry about long-running queries
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ded7410643025968"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaDiGxC6yR1Bog_G4DQNKuRiE9i1eoLBiP4Lyam7MN6Z7A%40mail.gmail.com>
Precedence: bulk

--000000000000ded7410643025968
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Nov 7, 2025 at 8:49=E2=80=AFAM Ashish Mukherjee <ashish.mukherjee@g=
mail.com>
wrote:

> Hello,
>
> I have a query like this showing up on my production database -
>
> s05=3D> 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 =3D '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 fro=
m
> scac where supported_by_smc =3D 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=
.
>

If it runs in a jiffy, *do you have time* to kill it before it finishes?


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

What problem?  The query existing, or not being able to kill a query that
finishes before you have time to kill it?

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Nov 7, 2025 at 8:49=E2=80=AFAM As=
hish Mukherjee &lt;<a href=3D"mailto:ashish.mukherjee@gmail.com">ashish.muk=
herjee@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_=
container"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.=
8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"lt=
r">Hello,<div><br></div><div>I have a query like this showing up on my prod=
uction database -=C2=A0<br><div><br></div><div>s05=3D&gt; SELECT pid, user,=
 usename, application_name, client_addr, client_hostname, client_port, datn=
ame, now() - query_start as &quot;runtime&quot;,<br>state, wait_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 s=
tate =3D &#39;active&#39;<br>=C2=A0ORDER BY runtime DESC;<br>=C2=A0 =C2=A0p=
id =C2=A0 | user | usename | application_name | =C2=A0client_addr =C2=A0| c=
lient_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_type | =C2=A0 wai=
t_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 scac where suppo=
rted_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. A=
lso, when I try to kill the query through pg_terminate_backend or pg_cancel=
_backend, it does not get killed.<br></div></div></div></blockquote><div><b=
r></div><div>If it runs in a jiffy, <b>do you have time</b> to kill it befo=
re it finishes?</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex"><div dir=3D"ltr"><div><div>I am wondering what could be the ro=
ot cause of this problem and how it could be addressed.=C2=A0 Any pointers =
would be appreciated.</div></div></div></blockquote><div></div></div><div><=
br></div><div>What problem?=C2=A0 The query existing, or not being able to =
kill a query that finishes before you have time to kill it?</div><div><br><=
/div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" =
class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and b=
utter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;R=
edacted&gt; lobster!</div></div></div></div></div></div>

--000000000000ded7410643025968--