[EN] Information on object locking (SQL type => PostgreSQL (

December 14, 2023

PostgreSQL monitor: lock

[EN]

Information on object locking (SQL type => PostgreSQL (relation))

The window displays information about object locks imposed on them when executing sql commands by PostgreSQL database users when executing queries/commands.

List of lock

select l.locktype,

d.datname,

c.relname,

l.page,

l.tuple,

l.transactionid,

cl.relname,

l.objid,

l.pid,

l.mode

from pg_locks l

left join pg_database d on l.database=d.oid

left join pg_class c on l.relation=c.oid

left join pg_class cl on l.classid=cl.oid

order by 2

Process

select * from pg_stat_activity where pid=$$PID

Information on session locks in PostgreSQL (SQL type => PostgreSQL (session))

The window displays information about sessions whose work is blocked by other sessions in the PostgreSQL database

SELECT blocked_locks.pid AS pid,

blocked_activity.usename AS blocked_user,

blocking_locks.pid AS blocking_pid,

blocking_activity.usename AS blocking_user,

blocked_activity.query AS blocked_statement,

blocking_activity.query AS current_statement_in_blocking_process,

blocked_activity.application_name AS blocked_application,

blocking_activity.application_name AS blocking_application

FROM pg_catalog.pg_locks blocked_locks

JOIN pg_catalog.pg_stat_activity blocked_activity

ON blocked_activity.pid = blocked_locks.pid

JOIN pg_catalog.pg_locks blocking_locks

ON blocking_locks.locktype = blocked_locks.locktype

AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE

AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation

AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page

AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple

AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid

AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid

AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid

AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid

AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid

AND blocking_locks.pid != blocked_locks.pid

JOIN pg_catalog.pg_stat_activity blocking_activity

ON blocking_activity.pid = blocking_locks.pid

WHERE NOT blocked_locks.GRANTED

Information on session locking in GreenPlum (SQL type => GreenPlum (session))

The window displays information about sessions whose work is blocked by other sessions in the GreenPlum database

SELECT kl.pid as blocking_pid,

ka.usename as blocking_user,

ka.current_query as blocking_query,

bl.pid as blocked_pid,

a.usename as blocked_user,

a.current_query as blocked_query,

to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as age

FROM pg_catalog.pg_locks bl

JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.procpid

JOIN pg_catalog.pg_locks kl ON bl.locktype = kl.locktype

and bl.database is not distinct from kl.database

and bl.relation is not distinct from kl.relation

and bl.page is not distinct from kl.page

and bl.tuple is not distinct from kl.tuple f

and bl.transactionid is not distinct from kl.transactionid

and bl.classid is not distinct from kl.classid

and bl.objid is not distinct from kl.objid

and bl.objsubid is not distinct from kl.objsubid

and bl.pid <> kl.pid

JOIN pg_catalog.pg_stat_activity ka ON kl.pid = ka.procpid

WHERE kl.granted and not bl.granted

ORDER BY a.query_start

[RU]

Информация по блокировкам объектов (SQL type => PostgreSQL (relation))

В окне выводится информация о блокировках объектов наложенных на них при выполнении sql команд пользователями базы данных PostgreSQL при выполнении запросов/команд.