Коллеги, подскатиже есть у кого под рукой скрипт для построения

Question

pgsql – PostgreSQL

Anton Bushmelev

Коллеги, подскатиже есть у кого под рукой скрипт для построения

дерева блокировок на 9.6 ?

#backend #devops #pgsql #programming #russian

0

12.05.2017

1 ответов

14 просмотров

Игорь · Accepted Answer

SELECT COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item, blockeda.pid AS blocked_pid, blockeda.query as blocked_query, blockedl.mode as blocked_mode, blockinga.pid AS blocking_pid, blockinga.query as blocking_query, blockingl.mode as blocking_mode FROM pg_catalog.pg_locks blockedl JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid JOIN pg_catalog.pg_locks blockingl ON( ( (blockingl.transactionid=blockedl.transactionid) OR (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype) ) AND blockedl.pid != blockingl.pid) JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid WHERE NOT blockedl.granted AND blockinga.datname='DATABASE'; CREATE VIEW lock_monitor AS( SELECT COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item, now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid, blockeda.query as blocked_query, blockedl.mode as blocked_mode, blockinga.pid AS blocking_pid, blockinga.query as blocking_query, blockingl.mode as blocking_mode FROM pg_catalog.pg_locks blockedl JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid JOIN pg_catalog.pg_locks blockingl ON( ( (blockingl.transactionid=blockedl.transactionid) OR (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype) ) AND blockedl.pid != blockingl.pid) JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid AND blockinga.datid = blockeda.datid WHERE NOT blockedl.granted AND blockinga.datname = current_database() );

172 похожих чатов

Коллеги, подскатиже есть у кого под рукой скрипт для построения

1 ответов

Похожие вопросы