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 <>
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 <> wrote:

> On Sat, Dec 24, 2016 at 9:56 AM, Joel Jacobson <> 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
* <> | Newsroom
<> | LinkedIn
<> | **Twitter

* <>*

Attachment: 0001-pg_stat_get_activity_wait_start.patch
Description: Binary data

Sent via pgsql-hackers mailing list (
To make changes to your subscription:

Reply via email to