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.
