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 <[email protected]> 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 >
