Re: pg_stat_activity

[Pavan Teja <pavan.postgresdba@xxxxxxxxx>]

On Mon, Jun 11, 2018, 9:58 PM Rui DeSousa <rui.desousa@xxxxxxxxxx> wrote:

> On Jun 11, 2018, at 9:56 AM, Ron <ronljohnsonjr@xxxxxxxxx> wrote:
>
> How, then, do I get only long-running SELECT statements?
>

Why only select statements? What about select statements that are really updates?

i.e.

select …. for update;
select do_some_work();   —In this case you will not see the actual executing SQL in the function which could be update, delete, select, etc.

Or what about delete or update with returning?

Hi Ron,

You can use LIKE  operator for getting updates and deletes and inserts like 

" Select * from pg_stat_activity where query like '%UPDATE%' or '%update%' "

The same in case of updates and deletes.  You can also configure table level audit with the help of triggers to capture DML's 

Hope this helps.

Thanks & Regards,

Pavan.