Re: Add connection active, idle time to pg_stat_activity

On Tue, Oct 26, 2021 at 5:17 PM Rafia Sabih <rafia(dot)pghackers(at)gmail(dot)com> wrote:
>
> >
> > To provide this information I was digging into how the statistics
> > collector is working and found out there is already information like
> > total time that a connection is active as well as idle computed in
> > pgstat_report_activity[1]. Ideally, this would be the values we would
> > like to see per process in pg_stat_activity.
> >
> > Curious to know your thoughts on this.

+1 for the idea

> Please find the attached patch for the idea of our intentions.
> It basically adds three attributes for idle, idle_in_transaction, and
> active time respectively.
> Please let me know your views on this and I shall add this to the
> upcoming commitfest for better tracking.

About the patch, IIUC earlier all the idle time was accumulated in the
"pgStatTransactionIdleTime" counter, now with your patch you have
introduced one more counter which specifically tracks the
STATE_IDLEINTRANSACTION state. But my concern is that the
STATE_IDLEINTRANSACTION_ABORTED is still computed under STATE_IDLE and
that looks odd to me. Either STATE_IDLEINTRANSACTION_ABORTED should
be accumulated in the "pgStatTransactionIdleInTxnTime" counter or
there should be a separate counter for that. But after your patch we
can not accumulate this in the "pgStatTransactionIdleTime" counter.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com