Proposal: expose heavyweight lock wait start time in pg_stat_activity

Hi,

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