Re: Better way to find long-running queries?

[Holger Jakobs <holger@xxxxxxxxxx>]

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@xxxxxxxxx>:

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';