When joining pg_locks there is no need to consider every single
locktype independantly to figure out which rows should be used for join.
Join on all possible rows with using 'IS NOT DISTINCT FROM' works the
same and easy to read and support.
Query will return unique list of pids in locked_by
For 9.6 use pg_blocking_pids instead of retrieving list of pids
from pg_locks. The old technique is too obscure and doesn't really work
for parallel queries. In addition to that reformat all queries to look similar.
report only heavy-weight locks ('Lock' wait_event_type) in pg_stat_activity for the 'locked' status in pg_view.
Partial changes by @CyberDem0n
When joining pg_locks there is no need to consider every single locktype independantly to figure out which rows should be used for join. Join on all possible rows with using 'IS NOT DISTINCT FROM' works the same and easy to read and support.
Query will return unique list of pids in locked_by
For 9.6 use pg_blocking_pids instead of retrieving list of pids from pg_locks. The old technique is too obscure and doesn't really work for parallel queries. In addition to that reformat all queries to look similar.
report only heavy-weight locks ('Lock' wait_event_type) in pg_stat_activity for the 'locked' status in pg_view.