Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: Better way to find long-running queries?
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: 
 <CANzqJaB0+-r-M+HXy81Ef__vSVxWSwgS2jFRgn-uD=_ETrhieQ@mail.gmail.com>
Date: Thu, 11 Jul 2024 10:11:39 -0600
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <2B0E4039-5871-4E31-920D-772C2377C78E@elevated-dev.com>
References: 
 <CANzqJaB0+-r-M+HXy81Ef__vSVxWSwgS2jFRgn-uD=_ETrhieQ@mail.gmail.com>
To: Ron Johnson <ronljohnsonjr@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/2B0E4039-5871-4E31-920D-772C2377C78E%40elevated-dev.com>
Precedence: bulk

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=E2=80=AFAM, Ron Johnson =
<ronljohnsonjr@gmail.com> wrote:
>=20
> This query works, and works quite well, but fails if the query starts =
with a comment.
>=20
> 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.)
>=20
> 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?
>=20
> PG versions 9.6.24 (yes, it's EOL), 14.12, 15.7 and 16.3, if it makes =
a difference.
>=20
> SELECT datname,=20
>        pid,=20
>        client_addr,=20
>        client_hostname,=20
>        query_start,=20
>        to_char(EXTRACT(epoch FROM now()-query_start), '99,999.99') as =
elapsed_secs,=20
>        md5(query)
> pg_stat_activity=20
> WHERE datname not in ('postgres', 'template0', 'template1')=20
>   AND state !=3D '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) =3D 'SELECT';
>=20