Re: Enquiry about long-running queries

On Fri, Nov 7, 2025 at 8:49 AM Ashish Mukherjee <ashish(dot)mukherjee(at)gmail(dot)com>
wrote:

> 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.
>

If it runs in a jiffy, *do you have time* to kill it before it finishes?

> I am wondering what could be the root cause of this problem and how it
> could be addressed. Any pointers would be appreciated.
>

What problem? The query existing, or not being able to kill a query that
finishes before you have time to kill it?

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!