PostgreSQL LOCK TREE 조회 스크립트

PostgreSQL LOCK TREE 조회 스크립트

---

WITH RECURSIVE lt AS (

    SELECT   p.datname

           , p.usename

           , p.pid

           , pg_blocking_pids(p.pid) AS blocking_by

           , p.application_name

           , p.wait_event_type

           , p.state

           , p.query_start

           , p.query

           , ( 

SELECT array_agg(( SELECT relname 

                                    FROM pg_class pc 

                                   WHERE pc.oid = pl.relation ))

                 FROM pg_locks pl

                 WHERE pl.pid = p.pid

                      AND locktype = 'relation'

             ) AS relation

           , 0 AS level

           , row_number() over() AS rn

     FROM pg_stat_activity p

     WHERE cardinality(pg_blocking_pids(p.pid))= 0

     UNION 

    SELECT   p.datname

           , p.usename

           , p.pid

           , pg_blocking_pids(p.pid) AS blocking_by

           , p.application_name

           , p.wait_event_type

           , p.state

           , p.query_start

           , p.query

           , ( SELECT array_agg(( SELECT relname FROM pg_class pc WHERE pc.oid = pl.relation ))

                 FROM pg_locks pl

                WHERE pl.pid = p.pid

                      AND locktype = 'relation'

             ) AS relation

           , level + 1 as level

           , lt.rn

      FROM pg_stat_activity p

           INNER JOIN lt ON lt.pid = any(pg_blocking_pids(p.pid))

) 

SELECT   p.datname

       , p.usename

       , p.pid

       , p.blocking_by

       , p.application_name

       , p.wait_event_type

       , p.state

       , p.relation

       , now() - p.query_start AS elapsed_time

       , p.query

FROM   ( 

      SELECT   lt.rn

     , lt.level

     , lt.datname

     , lt.usename

     , lt.pid

     , lt.blocking_by

     , lt.application_name

     , lt.wait_event_type

                     , lt.state

                     , lt.relation

                     , lt.query_start

                     , lt.query

                     , count( rn ) over( PARTITION BY rn ) AS blocked_cnt

              FROM lt

       ) AS p

WHERE p.blocked_cnt > 1

ORDER by p.rn, p.level, p.blocking_by

;

1) pg_cancel_backend(pid)            -- current query kill and not disconnect

2) pg_terminate_backend(pid)        -- connection disconnect. 

3) kill -9 process