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 1fSNJY-0000wy-QF for pgsql-admin@arkaria.postgresql.org; Mon, 11 Jun 2018 13:56:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fSNJW-00027N-Q6 for pgsql-admin@arkaria.postgresql.org; Mon, 11 Jun 2018 13:56:54 +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 1fSNJW-00027C-D0 for pgsql-admin@lists.postgresql.org; Mon, 11 Jun 2018 13:56:54 +0000 Received: from mail-oi0-x241.google.com ([2607:f8b0:4003:c06::241]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fSNJT-0002WN-M8 for pgsql-admin@postgresql.org; Mon, 11 Jun 2018 13:56:53 +0000 Received: by mail-oi0-x241.google.com with SMTP id 188-v6so4556446oid.12 for ; Mon, 11 Jun 2018 06:56: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=VgYGypg26qXxp/XBFbq7iLkJ3ZqU1wHeM/eGckxh/Yk=; b=TOO4rXBEUh1uD1dCBmW9DOL3XmHSqbC4EgPCOA0fyZelfkAdhn1NuC0sQmkBLhz4ef 45xgnDnlv43WfImtZAh9wRQTh6iAQdoME1xSZaw5baZY6k16rXTQIgZdCfkQ2qafFIm/ 51mrzXDT6A/kGimhalpyo6hjTg4LiY/Jmt5RV753tSF0GQfqQJNBAVi4XRUFJQ/58n59 pTGsEZXVju4JqubHxsZgKPjuDokkFpvGlIj36kjB0Eu3LQCw1IuP2tu1RJHwlUguPzoH rUNN8VJusxDjUhFMwYK9LYjb4qUCgEYGKagLGCQ1nCFLfLV6GwKa0WuijwOjuFAMMYFQ qL2A== 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=VgYGypg26qXxp/XBFbq7iLkJ3ZqU1wHeM/eGckxh/Yk=; b=rlSkvWOllXtykpFHaq8oByeAER+ewWxea49P6c9xeTabg/nPm19GWTZ0n18+Au/aPp Yu/QDtt4sxNOe+kz9WDWjrVw+0cTk1Q40bg4qv+6GirfrCjkXuKzJfTRYC2uqgrFQfeG C3Eoebq1wEanS9dv7vQh6QwAWwhUH9hP3dE/Lv0JVMbAkOQpGu7dc2j7yRYlSFZ1YusI YL11uHpKnc63fkPhCQ67poJKC/UBisXJIZsbQ3RH7BkG04EYzD8xD0923Y1rce4TIvQD LPXqxGb/SWuIsnUepB7vkIXfs+oqtUpT48R2hoIM8v3kC5NNP5d9Gz60IA5u6BftPs6s JU1A== X-Gm-Message-State: APt69E2F6za9/rsK2yzA01HkK4vL9H/XP9RbPqoYcA2PSflixOO113RS g3/NIWrBBJt9XcEwxma6HCCiAA== X-Google-Smtp-Source: ADUXVKL8Mze/iOGDSNfxai8vHbjkDyXYY+bSI4FbzSlWDB3bts7ciE2XobMBMHjvKpTElvtIuMKsDg== X-Received: by 2002:aca:38c3:: with SMTP id f186-v6mr5284885oia.310.1528725410563; Mon, 11 Jun 2018 06:56: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 m83-v6sm9573762oia.47.2018.06.11.06.56.49 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 11 Jun 2018 06:56:49 -0700 (PDT) Subject: Re: pg_stat_activity To: pgsql-admin References: <4405957b-2e0f-bfc5-eaf6-5e2864da2796@gmail.com> From: Ron Message-ID: <9884a24a-c129-a1a9-1901-2a9c8987331e@gmail.com> Date: Mon, 11 Jun 2018 08:56:43 -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="------------4D4BB413792AA42FEA450949" 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. --------------4D4BB413792AA42FEA450949 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------4D4BB413792AA42FEA450949 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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.
--------------4D4BB413792AA42FEA450949--