Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sRxH6-002R31-C5 for pgsql-admin@arkaria.postgresql.org; Thu, 11 Jul 2024 17:08:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sRxH4-00GUQE-WE for pgsql-admin@arkaria.postgresql.org; Thu, 11 Jul 2024 17:08:07 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sRxH4-00GUQ4-Ka for pgsql-admin@lists.postgresql.org; Thu, 11 Jul 2024 17:08:06 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sRxH0-001ZBe-SR for pgsql-admin@lists.postgresql.org; Thu, 11 Jul 2024 17:08:05 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id AF566380C2D for ; Thu, 11 Jul 2024 19:07:59 +0200 (CEST) Received: from rs.plausibolo.de ([127.0.0.1]) by localhost (h2367442.stratoserver.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id i2n0ZaOlMxXW for ; Thu, 11 Jul 2024 19:07:59 +0200 (CEST) Received: from [127.0.0.1] (xdsl-87-79-171-224.nc.de [87.79.171.224]) by rs.plausibolo.de (Postfix) with ESMTPSA id 2D78838021D for ; Thu, 11 Jul 2024 19:07:59 +0200 (CEST) Date: Thu, 11 Jul 2024 19:07:57 +0200 From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Subject: Re: Better way to find long-running queries? User-Agent: K-9 Mail for Android In-Reply-To: References: Message-ID: <29699672-E729-4983-BBC4-30E9B66511B3@jakobs.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=----GC55UNYJZAZPSW1G326WJKARGMX427 Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------GC55UNYJZAZPSW1G326WJKARGMX427 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Not even queries with CTEs will be found this way=2E They start with "with"= =2E A space at the beginning will also miss it=2E=20 Am 11=2E Juli 2024 18:03:22 MESZ schrieb Ron Johnson : >This query works, and works quite well, but fails if the query starts wit= h >a comment=2E > >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=2E = (For >example, the string "select" might be in a column name that's part of a >long-running COPY or ALTER=2E) > >But I've always hoped for something better=2E Thus: is there any way in = SQL >to parse pg_stat_activity=2Equery for the purpose of excluding comments? > >PG versions 9=2E6=2E24 (yes, it's EOL), 14=2E12, 15=2E7 and 16=2E3, if it= makes >a difference=2E > >SELECT datname, > pid, > client_addr, > client_hostname, > query_start, > to_char(EXTRACT(epoch FROM now()-query_start), '99,999=2E99') as >elapsed_secs, > md5(query) >pg_stat_activity >WHERE datname not in ('postgres', 'template0', 'template1') > AND state !=3D 'idle' > AND client_hostname !~ 'db[1-8]=2Eexample=2Ecom' > AND EXTRACT(epoch FROM now() - query_start) > 1800 > AND SUBSTRING(upper(query) from 1 for 6) =3D 'SELECT'; ------GC55UNYJZAZPSW1G326WJKARGMX427 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Not even queries with CTEs will = be found this way=2E They start with "with"=2E

A space at the beginn= ing will also miss it=2E



<= div dir=3D"auto">Am 11=2E Juli 2024 18:03:22 MESZ schrieb Ron Johnson <r= onljohnsonjr@gmail=2Ecom>:
This query works, and works quite well, but fails if= the query starts with a comment=2E

So far, I've accepte= d that "false negative" error, because being too aggressive at finding the = word SELECT in a query is a worse  problem=2E  (For example, the = string "select" might be in a column name that's part of a long-running COP= Y or ALTER=2E)

But I've always hoped for something= better=2E  Thus: is there any way in SQL to parse pg_stat_activi= ty=2Equery for the purpose of excluding comments?

= PG versions 9=2E6=2E24 (yes, it's EOL), 14=2E12, 15=2E7 and 16=2E3, if it m= akes a difference=2E

SELEC= T datname,
       pid,
      &nb= sp;client_addr,
       client_hostname,
  =      query_start,
       to_char(EXT= RACT(epoch FROM now()-query_start), '99,999=2E99') as elapsed_secs,
&nb= sp;      md5(query)
pg_stat_activity
WHERE datname no= t in ('postgres', 'template0', 'template1')
  AND state !=3D 'idle= '
  AND client_hostname !~ 'db[1-8]=2Eexample=2Ecom'
  AND EXTRACT(epoch FROM now() - query_start= ) > 1800
  AND SUBSTRING(upper(query) from 1 for 6) =3D 'SELECT'= ;

------GC55UNYJZAZPSW1G326WJKARGMX427--