Re: Enquiry about long-running queries - Mailing list pgsql-general

Hello,

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.