MIME-Version: 1.0
References: <CACgMzfwJQy8QmOFWdb3B1QNBchxC=4PjpBYMk4zgQz_WY8qmLQ@mail.gmail.com>
 <69982fc8a6ea2b7c2030d8206e16a8af081e7c46.camel@cybertec.at>
In-Reply-To: <69982fc8a6ea2b7c2030d8206e16a8af081e7c46.camel@cybertec.at>
From: Ashish Mukherjee <ashish.mukherjee@gmail.com>
Date: Mon, 10 Nov 2025 13:45:19 +0530
Message-ID: <CACgMzfx0m=R8QrvQ19+AOv6Y6eu31bqp+881mzAdX8rK28ggyQ@mail.gmail.com>
Subject: Re: Enquiry about long-running queries
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000812dc906433922b1"
Archived-At: <https://www.postgresql.org/message-id/CACgMzfx0m%3DR8QrvQ19%2BAOv6Y6eu31bqp%2B881mzAdX8rK28ggyQ%40mail.gmail.com>
Precedence: bulk

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

pgsql 17.5 on Red Hat Enterprise Linux release 8.10 (Ootpa)

The query is run from one of our php applications.

On Fri, Nov 7, 2025 at 8:59=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec.=
at>
wrote:

> On Fri, 2025-11-07 at 19:18 +0530, Ashish Mukherjee wrote:
> > 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 'activ=
e'
> >  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 =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.
> >
> > I am wondering what could be the root cause of this problem and how it
> could be addressed.  Any pointers would be appreciated.
>
> That is strange.  The wait event means that the backend is waiting for
> parallel
> workers to finish.  But any existing parallel worker processes would also
> have
> to show up in the query result.
>
> On what operating system does PostgreSQL run?
>
> What exact version is it?
>
> You could try to "strace" the backend process (or use an equivalent tool,
> if
> you are not on Linux) and see if the process issues any system calls.
> To see what's going on, you'd have to attach to the backend process with =
a
> debugger and take a stack trace.
>
> Yours,
> Laurenz Albe
>

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

<div dir=3D"ltr">pgsql 17.5 on=C2=A0Red Hat Enterprise Linux release 8.10 (=
Ootpa)<div><br></div><div>The query is run from one of our php applications=
.</div></div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=
=3D"ltr" class=3D"gmail_attr">On Fri, Nov 7, 2025 at 8:59=E2=80=AFPM Lauren=
z Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cyberte=
c.at</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex">On Fri, 2025-11-07 at 19:18 +0530, Ashish Mukherjee wrote:<br>
&gt; I have a query like this showing up on my production database -=C2=A0<=
br>
&gt; <br>
&gt; s05=3D&gt; SELECT pid, user, usename, application_name, client_addr, c=
lient_hostname, client_port, datname, now() - query_start as &quot;runtime&=
quot;,<br>
&gt; state, wait_event_type, wait_event,<br>
&gt; substr(query, 0, 100)<br>
&gt; =C2=A0FROM =C2=A0pg_stat_activity<br>
&gt; =C2=A0WHERE now() - query_start &gt; &#39;5 minutes&#39;::interval and=
 state =3D &#39;active&#39;<br>
&gt; =C2=A0ORDER BY runtime DESC;<br>
&gt; =C2=A0 =C2=A0pid =C2=A0 | user | usename | application_name | =C2=A0cl=
ient_addr =C2=A0| client_hostname | client_port | datname | =C2=A0 =C2=A0 =
=C2=A0 =C2=A0runtime<br>
&gt; =C2=A0 =C2=A0 =C2=A0 =C2=A0| state =C2=A0| wait_event_type | =C2=A0 wa=
it_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>
&gt; -------+--------+-----------------+----------------+------------------=
--------------------------------------------------<br>
&gt; =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 d=
ays 18:45:37<br>
&gt; .65577 | active | IPC =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Para=
llelFinish | select scac_code from scac where supported_by_smc =3D true<br>
&gt; <br>
&gt; The query when run from psql prompt finishes in a jiffy, so query perf=
ormance/cost is not the problem.<br>
&gt; Also, when I try to kill the query through pg_terminate_backend or pg_=
cancel_backend, it does not get killed.<br>
&gt; <br>
&gt; 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.<br>
<br>
That is strange.=C2=A0 The wait event means that the backend is waiting for=
 parallel<br>
workers to finish.=C2=A0 But any existing parallel worker processes would a=
lso have<br>
to show up in the query result.<br>
<br>
On what operating system does PostgreSQL run?<br>
<br>
What exact version is it?<br>
<br>
You could try to &quot;strace&quot; the backend process (or use an equivale=
nt tool, if<br>
you are not on Linux) and see if the process issues any system calls.<br>
To see what&#39;s going on, you&#39;d have to attach to the backend process=
 with a<br>
debugger and take a stack trace.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>

--000000000000812dc906433922b1--