I'd like to propose exposing the start time of the current heavyweight lock wait in pg_stat_activity, perhaps as:
lock_wait_start timestamptz
pg_stat_activity currently tells us that a backend is waiting on a lock through wait_event_type and wait_event, but not when that wait started.
For example:
SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';
might show:
pid state wait_event_type wait_event
8123 active Lock transactionid
We know PID 8123 is blocked, but not whether it has been waiting for 50 ms or 15 minutes.
The timestamp already exists in PGPROC.waitStart and is exposed as pg_locks.waitstart.
A similar idea was discussed in 2016, when the concern was the cost of acquiring a timestamp for every wait event. For heavyweight locks, that concern should no longer apply. Since commit 46d6e5f5, PostgreSQL already records the timestamp using the deadlock-timeout timing infrastructure:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=46d6e5f5
pg_stat_get_activity() already has the backend's PGPROC available and reads wait_event_info from it, so exposing waitStart would require only another atomic read and no new timing instrumentation.
I suggest the name lock_wait_start to make it clear that this applies only to heavyweight lock waits.
This could allow:
SELECT pid, wait_event, lock_wait_start, now() - lock_wait_start AS waiting_for
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';
Before preparing a patch, does exposing this directly in pg_stat_activity seem reasonable?
If widening pg_stat_activity is undesirable, would a function such as pg_stat_get_backend_lock_wait_start(pid) be preferable?
Thanks,
Alex Shapalov
