Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fSOQH-0007sG-Ul for pgsql-admin@arkaria.postgresql.org; Mon, 11 Jun 2018 15:07:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fSOQG-0008HU-8Y for pgsql-admin@arkaria.postgresql.org; Mon, 11 Jun 2018 15:07:56 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fSOQF-0008HK-Lg for pgsql-admin@lists.postgresql.org; Mon, 11 Jun 2018 15:07:56 +0000 Received: from mail-ot0-x241.google.com ([2607:f8b0:4003:c0f::241]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fSOQC-0004BM-0C for pgsql-admin@postgresql.org; Mon, 11 Jun 2018 15:07:54 +0000 Received: by mail-ot0-x241.google.com with SMTP id n3-v6so24154708ota.5 for ; Mon, 11 Jun 2018 08:07:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=BkOVup05ayLxwnFTILiXuJDP6XXFKEhIAs8gDbgdAHE=; b=l11wuywwKymzD1nIHRfIDoqTTxUiUF8Kved+bIbJiwMZ+JOGAhXAjerbFbs8XSDv+3 VPN4LI5qI8bd/MCE3kafaacfzhXuF6UuMltDvVK0BWgM8bwMLfiXrhOh1ToHVfX99xX4 0fC3EhHZVWHdThDW88JXjvWIsmHuVLQpaiTQtbtAop4HzlHGGPmCUn1dSoThnXfquPjk LWqF4SOwtfpxo6HqgLge3QZ9AWDiODksNh8w+mmsnmdoJcriO5dsCPpDb0VUpcukUnmL RCJJvCRZTrspqMbLLssD7k3TEnnJwirvNLf3zlyS/2U7tk1YQRkN95ghH+XuDkHEwZMh G/bQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=BkOVup05ayLxwnFTILiXuJDP6XXFKEhIAs8gDbgdAHE=; b=kNiWRYaxQIRGThyfAcVRAoFaAwUAtpTd28nnTLTrYxxcPkl+Orvvo+6z0/cThWQuIg b0t4fxmZ8r+Bd/ELFslfd3IKrj4wrp8RKbvVsRp7skobKTbuoJ5WF4+ySseoiRdS1mUt 4KBD2TjnJ5M5D58Pn7mTnTB2V3+az9fiGtqwaLHzhW5SOGexshnqHGlZE2+v6stoCA48 dhx1JHMuLCHM3Aj1GLeUJ0/pLrz1VO6Z3Q3oVriBh+MJlRFtwKAzv1UKEnySu2v7Qryj GRvzr3ZEAwjMlxs3nRwx3SuSvMdgxtapQpRJxZGgJp9XBFTin/P34/rKjpX99WNF3vUn DuSQ== X-Gm-Message-State: APt69E1Z3vmiea9ch9VujOQncca5l6aW7+UpOB/ATX5qAv12mIUpRhOb f6sfNNk5Vot2NReyCSYaRS1DgA== X-Google-Smtp-Source: ADUXVKJaJKaTVdbNFOAtl6QlHVE6dkm1pPFYTfOc4fXByuUONeGCCkrtCvyjNaHbETnsCUyrxxo6BQ== X-Received: by 2002:a9d:4455:: with SMTP id f21-v6mr10982378otj.284.1528729670569; Mon, 11 Jun 2018 08:07:50 -0700 (PDT) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id x194-v6sm18803336oix.38.2018.06.11.08.07.49 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 11 Jun 2018 08:07:49 -0700 (PDT) Subject: Re: pg_stat_activity To: pgsql-admin References: <4405957b-2e0f-bfc5-eaf6-5e2864da2796@gmail.com> <9884a24a-c129-a1a9-1901-2a9c8987331e@gmail.com> From: Ron Message-ID: <211b19ea-18dc-6897-3c53-8d994550cd15@gmail.com> Date: Mon, 11 Jun 2018 10:07:49 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------6E280ECAA67E1B3C90596B24" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------6E280ECAA67E1B3C90596B24 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit That won't work, because that would catch any query on a table which happens to have the string "select" in a table, index or column name. On 06/11/2018 09:53 AM, Shreeyansh Dba wrote: > Hi Ron, > > You can use LIKE  operator to get only select query and specify LIMIT as > per your requirement. > > Hope this helps.. > > > > On Mon, Jun 11, 2018 at 7:26 PM, Ron > wrote: > > Thanks. > > How, then, do I get only long-running SELECT statements? > > ​​ > I've tried this (since I don't want to inadvertently get other DML & > DDL statements > ​​ > which might have "select" in a field name), but CTEs and queries that > start with a comment slip by this filter. > > FROM pg_stat_activity > WHERE state <> 'idle' >   AND SUBSTRING(UPPER(query) FROM 1 FOR 6) = 'SELECT'; > > Thanks > > > On 06/11/2018 08:47 AM, Shreeyansh Dba wrote: >> Ron, >> >> Yes, It can list the insert,delete and update statement as well.. >> >> >> >> On Mon, Jun 11, 2018 at 7:07 PM, Ron > > wrote: >> >> Hi, >> >> Does pg_stat_activity only list SELECT statements, or does it >> also list INSERT, DELETE and UPDATE statements? >> >> > > -- > Angular momentum makes the world go 'round. > > -- Angular momentum makes the world go 'round. --------------6E280ECAA67E1B3C90596B24 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit That won't work, because that would catch any query on a table which happens to have the string "select" in a table, index or column name.

On 06/11/2018 09:53 AM, Shreeyansh Dba wrote:
Hi Ron,

You can use LIKE  operator to get only select query and specify LIMIT as per your requirement.

Hope this helps..



On Mon, Jun 11, 2018 at 7:26 PM, Ron <ronljohnsonjr@gmail.com> wrote:
Thanks.

How, then, do I get only long-running SELECT statements?

​​
I've tried this (since I don't want to inadvertently get other DML & DDL statements
​​
which might have "select" in a field name), but CTEs and queries that start with a comment slip by this filter.

FROM pg_stat_activity
WHERE state <> 'idle'
  AND SUBSTRING(UPPER(query) FROM 1 FOR 6) = 'SELECT';

Thanks


On 06/11/2018 08:47 AM, Shreeyansh Dba wrote:
Ron,

Yes, It can list the insert,delete and update statement as well..



On Mon, Jun 11, 2018 at 7:07 PM, Ron <ronljohnsonjr@gmail.com> wrote:
Hi,

Does pg_stat_activity only list SELECT statements, or does it also list INSERT, DELETE and UPDATE statements?


--
Angular momentum makes the world go 'round.


--
Angular momentum makes the world go 'round.
--------------6E280ECAA67E1B3C90596B24--