Re: pg_stat_activity - Mailing list pgsql-admin

From Ron
Subject Re: pg_stat_activity
Date
Msg-id 211b19ea-18dc-6897-3c53-8d994550cd15@gmail.com
Whole thread Raw
In response to Re: pg_stat_activity  (Shreeyansh Dba <shreeyansh2014@gmail.com>)
Responses Re: pg_stat_activity  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-admin
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.

pgsql-admin by date:

Previous
From: Shreeyansh Dba
Date:
Subject: Re: pg_stat_activity
Next
From: Pierre Ochsenbein
Date:
Subject: Add to watchdog cluster request is rejected by node