pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBetter way to find long-running queries?
3+ messages / 3 participants
[nested] [flat]
* Better way to find long-running queries?
@ 2024-07-11 16:03 Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Ron Johnson @ 2024-07-11 16:03 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
This query works, and works quite well, but fails if the query starts with
a comment.
So far, I've accepted that "false negative" error, because being too
aggressive at finding the word SELECT in a query is a worse problem. (For
example, the string "select" might be in a column name that's part of a
long-running COPY or ALTER.)
But I've always hoped for something better. Thus: is there any way in SQL
to parse pg_stat_activity.query for the purpose of excluding comments?
PG versions 9.6.24 (yes, it's EOL), 14.12, 15.7 and 16.3, if it makes
a difference.
SELECT datname,
pid,
client_addr,
client_hostname,
query_start,
to_char(EXTRACT(epoch FROM now()-query_start), '99,999.99') as
elapsed_secs,
md5(query)
pg_stat_activity
WHERE datname not in ('postgres', 'template0', 'template1')
AND state != 'idle'
AND client_hostname !~ 'db[1-8].example.com'
AND EXTRACT(epoch FROM now() - query_start) > 1800
AND SUBSTRING(upper(query) from 1 for 6) = 'SELECT';
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Better way to find long-running queries?
@ 2024-07-11 16:11 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Scott Ribe @ 2024-07-11 16:11 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Well, in the past I have approached it from the other end:
(AND query NOT ILIKE ('insert') AND query NOT ILIKE...)
excluding queries I didn't care about
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/
> On Jul 11, 2024, at 10:03 AM, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> This query works, and works quite well, but fails if the query starts with a comment.
>
> So far, I've accepted that "false negative" error, because being too aggressive at finding the word SELECT in a query is a worse problem. (For example, the string "select" might be in a column name that's part of a long-running COPY or ALTER.)
>
> But I've always hoped for something better. Thus: is there any way in SQL to parse pg_stat_activity.query for the purpose of excluding comments?
>
> PG versions 9.6.24 (yes, it's EOL), 14.12, 15.7 and 16.3, if it makes a difference.
>
> SELECT datname,
> pid,
> client_addr,
> client_hostname,
> query_start,
> to_char(EXTRACT(epoch FROM now()-query_start), '99,999.99') as elapsed_secs,
> md5(query)
> pg_stat_activity
> WHERE datname not in ('postgres', 'template0', 'template1')
> AND state != 'idle'
> AND client_hostname !~ 'db[1-8].example.com'
> AND EXTRACT(epoch FROM now() - query_start) > 1800
> AND SUBSTRING(upper(query) from 1 for 6) = 'SELECT';
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Better way to find long-running queries?
@ 2024-07-11 17:07 Holger Jakobs <holger@jakobs.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Holger Jakobs @ 2024-07-11 17:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Not even queries with CTEs will be found this way. They start with "with".
A space at the beginning will also miss it.
Am 11. Juli 2024 18:03:22 MESZ schrieb Ron Johnson <ronljohnsonjr@gmail.com>:
>This query works, and works quite well, but fails if the query starts with
>a comment.
>
>So far, I've accepted that "false negative" error, because being too
>aggressive at finding the word SELECT in a query is a worse problem. (For
>example, the string "select" might be in a column name that's part of a
>long-running COPY or ALTER.)
>
>But I've always hoped for something better. Thus: is there any way in SQL
>to parse pg_stat_activity.query for the purpose of excluding comments?
>
>PG versions 9.6.24 (yes, it's EOL), 14.12, 15.7 and 16.3, if it makes
>a difference.
>
>SELECT datname,
> pid,
> client_addr,
> client_hostname,
> query_start,
> to_char(EXTRACT(epoch FROM now()-query_start), '99,999.99') as
>elapsed_secs,
> md5(query)
>pg_stat_activity
>WHERE datname not in ('postgres', 'template0', 'template1')
> AND state != 'idle'
> AND client_hostname !~ 'db[1-8].example.com'
> AND EXTRACT(epoch FROM now() - query_start) > 1800
> AND SUBSTRING(upper(query) from 1 for 6) = 'SELECT';
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-07-11 17:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-11 16:03 Better way to find long-running queries? Ron Johnson <ronljohnsonjr@gmail.com>
2024-07-11 16:11 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-07-11 17:07 ` Holger Jakobs <holger@jakobs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox