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
