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
