Message-ID: <69982fc8a6ea2b7c2030d8206e16a8af081e7c46.camel@cybertec.at>
Subject: Re: Enquiry about long-running queries
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ashish Mukherjee <ashish.mukherjee@gmail.com>, 
	pgsql-general@postgresql.org
Date: Fri, 07 Nov 2025 16:29:25 +0100
In-Reply-To: <CACgMzfwJQy8QmOFWdb3B1QNBchxC=4PjpBYMk4zgQz_WY8qmLQ@mail.gmail.com>
References: 
	<CACgMzfwJQy8QmOFWdb3B1QNBchxC=4PjpBYMk4zgQz_WY8qmLQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/69982fc8a6ea2b7c2030d8206e16a8af081e7c46.camel%40cybertec.at>
Precedence: bulk

On Fri, 2025-11-07 at 19:18 +0530, Ashish Mukherjee wrote:
> I have a query like this showing up on my production database -=C2=A0
>=20
> 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)
> =C2=A0FROM =C2=A0pg_stat_activity
> =C2=A0WHERE now() - query_start > '5 minutes'::interval and state =3D 'ac=
tive'
> =C2=A0ORDER BY runtime DESC;
> =C2=A0 =C2=A0pid =C2=A0 | user | usename | application_name | =C2=A0clien=
t_addr =C2=A0| client_hostname | client_port | datname | =C2=A0 =C2=A0 =C2=
=A0 =C2=A0runtime
> =C2=A0 =C2=A0 =C2=A0 =C2=A0| state =C2=A0| wait_event_type | =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
> -------+--------+-----------------+----------------+---------------------=
-----------------------------------------------
> =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
> .65577 | active | IPC =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Paralle=
lFinish | select scac_code from scac where supported_by_smc =3D true
>=20
> The query when run from psql prompt finishes in a jiffy, so query perform=
ance/cost is not the problem.
> Also, when I try to kill the query through pg_terminate_backend or pg_can=
cel_backend, it does not get killed.
>=20
> I am wondering what could be the root cause of this problem and how it co=
uld be addressed.=C2=A0 Any pointers would be appreciated.

That is strange.  The wait event means that the backend is waiting for para=
llel
workers to finish.  But any existing parallel worker processes would also h=
ave
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, i=
f
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