Hi,
This is a very interesting topic. I did apply the 2nd patch to master branch and performed a quick test. I can observe
belowinformation,
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_activitywhere 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
ofthe 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
beforea 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,
andif the developers can specify some filters, such as, the number of times a query entered into a deadlock, the
querieshold 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