PostgreSQL: database locks

Блокировки в БД PostgreSQL (GreenPlum)

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

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

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

Информация по блокировкам сессий в PostgreSQSL (SQL type => PostgreSQL (session))

В окне выводится информация о сессиях работа которых заблокирована другими сессиями в базе данных PostgreSQL

List of lock

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

Информация по блокировкам сессий в GreenPlum (SQL type => GreenPlum (session))

В окне выводится информация о сессиях работа которых заблокирована другими сессиями в базе данных GreenPlum

List of lock

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
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