public inbox for [email protected]
help / color / mirror / Atom feedEnquiry about long-running queries
6+ messages / 5 participants
[nested] [flat]
* Enquiry about long-running queries
@ 2025-11-07 13:48 Ashish Mukherjee <[email protected]>
2025-11-07 14:53 ` Re: Enquiry about long-running queries Ron Johnson <[email protected]>
2025-11-07 15:29 ` Re: Enquiry about long-running queries Laurenz Albe <[email protected]>
2025-11-07 15:48 ` Re: Enquiry about long-running queries Adrian Klaver <[email protected]>
0 siblings, 3 replies; 6+ messages in thread
From: Ashish Mukherjee @ 2025-11-07 13:48 UTC (permalink / raw)
To: 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.
Regards,
Ashish
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Enquiry about long-running queries
2025-11-07 13:48 Enquiry about long-running queries Ashish Mukherjee <[email protected]>
@ 2025-11-07 14:53 ` Ron Johnson <[email protected]>
2 siblings, 0 replies; 6+ messages in thread
From: Ron Johnson @ 2025-11-07 14:53 UTC (permalink / raw)
To: pgsql-general
On Fri, Nov 7, 2025 at 8:49 AM Ashish Mukherjee <[email protected]>
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!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Enquiry about long-running queries
2025-11-07 13:48 Enquiry about long-running queries Ashish Mukherjee <[email protected]>
@ 2025-11-07 15:29 ` Laurenz Albe <[email protected]>
2025-11-10 08:15 ` Re: Enquiry about long-running queries Ashish Mukherjee <[email protected]>
2 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2025-11-07 15:29 UTC (permalink / raw)
To: Ashish Mukherjee <[email protected]>; pgsql-general
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Enquiry about long-running queries
2025-11-07 13:48 Enquiry about long-running queries Ashish Mukherjee <[email protected]>
2025-11-07 15:29 ` Re: Enquiry about long-running queries Laurenz Albe <[email protected]>
@ 2025-11-10 08:15 ` Ashish Mukherjee <[email protected]>
2025-11-10 14:34 ` Re: Enquiry about long-running queries Greg Sabino Mullane <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Ashish Mukherjee @ 2025-11-10 08:15 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: pgsql-general
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Enquiry about long-running queries
2025-11-07 13:48 Enquiry about long-running queries Ashish Mukherjee <[email protected]>
2025-11-07 15:29 ` Re: Enquiry about long-running queries Laurenz Albe <[email protected]>
2025-11-10 08:15 ` Re: Enquiry about long-running queries Ashish Mukherjee <[email protected]>
@ 2025-11-10 14:34 ` Greg Sabino Mullane <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Greg Sabino Mullane @ 2025-11-10 14:34 UTC (permalink / raw)
To: Ashish Mukherjee <[email protected]>; +Cc: Laurenz Albe <[email protected]>; pgsql-general
Wow, 9 days is a long time. The fact that it is not responding to
pg_terminate_backend means it's not even calling CHECK_FOR_INTERRUPTS
inside the WaitForParallelWorkersToFinish loop, so something is seriously
wrong. If the PID is still there, please do what Laurenz suggested:
strace -tp 356274
Is the problem reproducible, and has it happened more than once?
Cheers,
Greg
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Enquiry about long-running queries
2025-11-07 13:48 Enquiry about long-running queries Ashish Mukherjee <[email protected]>
@ 2025-11-07 15:48 ` Adrian Klaver <[email protected]>
2 siblings, 0 replies; 6+ messages in thread
From: Adrian Klaver @ 2025-11-07 15:48 UTC (permalink / raw)
To: Ashish Mukherjee <[email protected]>; pgsql-general
On 11/7/25 05:48, Ashish Mukherjee wrote:
> Hello,
>
> I have a query like this showing up on my production database -
> The query when run from psql prompt finishes in a jiffy, so query
What client is the stuck query being run from?
>
> Regards,
> Ashish
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-11-10 14:34 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-07 13:48 Enquiry about long-running queries Ashish Mukherjee <[email protected]>
2025-11-07 14:53 ` Ron Johnson <[email protected]>
2025-11-07 15:29 ` Laurenz Albe <[email protected]>
2025-11-10 08:15 ` Ashish Mukherjee <[email protected]>
2025-11-10 14:34 ` Greg Sabino Mullane <[email protected]>
2025-11-07 15:48 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox