From: | Ron Johnson <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Better way to find long-running queries? |
Date: | 2024-07-11 16:03:22 |
Message-ID: | CANzqJaB0+-r-M+HXy81Ef__vSVxWSwgS2jFRgn-uD=_ETrhieQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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';
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Ribe | 2024-07-11 16:11:39 | Re: Better way to find long-running queries? |
Previous Message | Sathish Reddy | 2024-07-11 13:13:35 | Debizium configure from Kafka side |