agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: Scott Ribe <scott_ribe@elevated-dev.com>
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Better way to find long-running queries?
Date: Thu, 11 Jul 2024 10:11:39 -0600
Message-ID: <2B0E4039-5871-4E31-920D-772C2377C78E@elevated-dev.com> (raw)
In-Reply-To: <CANzqJaB0+-r-M+HXy81Ef__vSVxWSwgS2jFRgn-uD=_ETrhieQ@mail.gmail.com>
References: <CANzqJaB0+-r-M+HXy81Ef__vSVxWSwgS2jFRgn-uD=_ETrhieQ@mail.gmail.com>
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';
>
view thread (3+ messages) latest in thread
Message-ID: <2B0E4039-5871-4E31-920D-772C2377C78E@elevated-dev.com>
Permalink: ../2B0E4039-5871-4E31-920D-772C2377C78E@elevated-dev.com/
Also on: postgresql.org/message-id/2B0E4039-5871-4E31-920D-772C2377C78E@elevated-dev.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scott_ribe@elevated-dev.com, ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Better way to find long-running queries?
In-Reply-To: <2B0E4039-5871-4E31-920D-772C2377C78E@elevated-dev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox