Re: Enquiry about long-running queries

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 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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=> 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.
>
> 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
>