Re: Enquiry about long-running queries

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