Re: pg_stat_activity.waiting_start

Attached is a patch implementing the seconds-resolution wait_start, but
presented as a timestamptz to the user, just like the other *_start fields:

commit c001e5c537e36d2683a7e55c7c8bfcc154de4c9d
Author: Joel Jacobson <joel(at)trustly(dot)com>
Date: Sat Dec 24 13:20:09 2016 +0700

Add OUT parameter "wait_start" timestamptz to pg_stat_get_activity()
and pg_catalog.pg_stat_activity

This is set to the timestamptz with seconds resolution
when the process started waiting, and reset to NULL
when it's not waiting any longer.

This is useful if you want to know not only what the process is
waiting for, but also how long it has been waiting,
which can be useful in situations when it might be
normal for different users/applications to wait for some amount of time,
but abnormal if they are waiting longer than some threshold.

When such a threshold is exceeded, monitoring applications
could then alert the user or possibly cancel/terminate
the blocking processes.

Without information on how long time processes have been waiting,
the monitoring applications would have no other option than
to cancel/terminate a process as soon as something is waiting,
or keep track of how long time processes have been waiting
by polling and keeping track on a per process basis,
which is less user-friendly than if PostgreSQL would provide
the information directly to the user.

src/backend/catalog/system_views.sql | 3 ++-
src/backend/postmaster/pgstat.c | 1 +
src/backend/storage/lmgr/proc.c | 1 +
src/backend/utils/adt/pgstatfuncs.c | 7 ++++++-
src/include/catalog/pg_proc.h | 2 +-
src/include/pgstat.h | 6 +++++-
src/include/storage/proc.h | 3 +++
7 files changed, 19 insertions(+), 4 deletions(-)

On Sat, Dec 24, 2016 at 12:32 PM, Joel Jacobson <joel(at)trustly(dot)com> wrote:

> On Sat, Dec 24, 2016 at 9:56 AM, Joel Jacobson <joel(at)trustly(dot)com> wrote:
> >> The difficulty with that is it'd require a gettimeofday() call for
> >> every wait start. Even on platforms where those are relatively cheap,
>
> I just realized how this can be optimized.
> We only need to set wait_start for every new waiting period,
> not for every wait start, i.e. not for every call to
> pgstat_report_wait_start():
>
> Example:
>
> In pgstat_report_wait_start():
> if (proc->wait_start == 0)
> proc->wait_start = (pg_time_t) time(NULL);
>
> And then in pgstat_report_wait_end():
> proc->wait_start = 0;
>
> This means we only need to call time() or gettimeofday() once per
> waiting period.
>
>
> --
> Joel Jacobson
>

--
Joel Jacobson

Mobile: +46703603801
*Trustly.com <http://trustly.com/> | Newsroom
<http://www.mynewsdesk.com/trustly_en> | LinkedIn
<https://www.linkedin.com/company/trustly-group-ab> | **Twitter
<https://twitter.com/Trustly>*

* <https://trustly.com/>*