Re: Add LWLock blocker(s) information

Hi,
This is a very interesting topic. I did apply the 2nd patch to master branch and performed a quick test. I can observe below information,
postgres=# select * from pg_lwlock_blocking_pid(26925);
requested_mode | last_holder_pid | last_holder_mode | nb_holders
----------------+-----------------+------------------+------------
LW_EXCLUSIVE | 26844 | LW_EXCLUSIVE | 1
(1 row)

postgres=# select query,pid,state,wait_event,wait_event_type,pg_lwlock_blocking_pid(pid),pg_blocking_pids(pid) from pg_stat_activity where state='active' and pid != pg_backend_pid();
query | pid | state | wait_event | wait_event_type | pg_lwlock_blocking_pid | pg_blocking_pids
--------------------------------------------------------------+-------+--------+------------+-----------------+-------------------------------------+------------------
INSERT INTO orders SELECT FROM generate_series(1, 10000000); | 26925 | active | WALWrite | LWLock | (LW_EXCLUSIVE,26844,LW_EXCLUSIVE,1) | {}
(1 row)

At some points, I have to keep repeating the query in order to capture the "lock info". I think this is probably part of the design, but I was wondering,
if a query is in deadlock expecting a developer to take a look using the methods above, will the process be killed before a developer get the chance to execute the one of the query?
if some statistics information can be added, it may help the developers to get an overall idea about the lock status, and if the developers can specify some filters, such as, the number of times a query entered into a deadlock, the queries hold the lock more than number of ms, etc, it might help to troubleshooting the "lock" issue even better. And moreover, if this feature can be an independent extension, similar to "pg_buffercache" it will be great.
Best regards,

David