From: | Holger Jakobs <holger(at)jakobs(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: Better way to find long-running queries? |
Date: | 2024-07-11 17:07:57 |
Message-ID: | 29699672-E729-4983-BBC4-30E9B66511B3@jakobs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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(at)gmail(dot)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';
From | Date | Subject | |
---|---|---|---|
Next Message | Sam Stearns | 2024-07-12 00:02:01 | Oracle to Postgres |
Previous Message | Kashif Zeeshan | 2024-07-11 16:46:36 | Re: Create Partitions ! |