첨부 소스 코드는 나눔고딕코딩 폰트를 사용합니다.
728x90
반응형
728x170
WITH RECURSIVE
    C(REQUESTED, CURRENT) AS
    (
        VALUES
        ('AccessShareLock'::TEXT         , 'AccessExclusiveLock'::TEXT     ),
        ('RowShareLock'::TEXT            , 'ExclusiveLock'::TEXT           ),
        ('RowShareLock'::TEXT            , 'AccessExclusiveLock'::TEXT     ),
        ('RowExclusiveLock'::TEXT        , 'ShareLock'::TEXT               ),
        ('RowExclusiveLock'::TEXT        , 'ShareRowExclusiveLock'::TEXT   ),
        ('RowExclusiveLock'::TEXT        , 'ExclusiveLock'::TEXT           ),
        ('RowExclusiveLock'::TEXT        , 'AccessExclusiveLock'::TEXT     ),
        ('ShareUpdateExclusiveLock'::TEXT, 'ShareUpdateExclusiveLock'::TEXT),
        ('ShareUpdateExclusiveLock'::TEXT, 'ShareLock'::TEXT               ),
        ('ShareUpdateExclusiveLock'::TEXT, 'ShareRowExclusiveLock'::TEXT   ),
        ('ShareUpdateExclusiveLock'::TEXT, 'ExclusiveLock'::TEXT           ),
        ('ShareUpdateExclusiveLock'::TEXT, 'AccessExclusiveLock'::TEXT     ),
        ('ShareLock'::TEXT               , 'RowExclusiveLock'::TEXT        ),
        ('ShareLock'::TEXT               , 'ShareUpdateExclusiveLock'::TEXT),
        ('ShareLock'::TEXT               , 'ShareRowExclusiveLock'::TEXT   ),
        ('ShareLock'::TEXT               , 'ExclusiveLock'::TEXT           ),
        ('ShareLock'::TEXT               , 'AccessExclusiveLock'::TEXT     ),
        ('ShareRowExclusiveLock'::TEXT   , 'RowExclusiveLock'::TEXT        ),
        ('ShareRowExclusiveLock'::TEXT   , 'ShareUpdateExclusiveLock'::TEXT),
        ('ShareRowExclusiveLock'::TEXT   , 'ShareLock'::TEXT               ),
        ('ShareRowExclusiveLock'::TEXT   , 'ShareRowExclusiveLock'::TEXT   ),
        ('ShareRowExclusiveLock'::TEXT   , 'ExclusiveLock'::TEXT           ),
        ('ShareRowExclusiveLock'::TEXT   , 'AccessExclusiveLock'::TEXT     ),
        ('ExclusiveLock'::TEXT           , 'RowShareLock'::TEXT            ),
        ('ExclusiveLock'::TEXT           , 'RowExclusiveLock'::TEXT        ),
        ('ExclusiveLock'::TEXT           , 'ShareUpdateExclusiveLock'::TEXT),
        ('ExclusiveLock'::TEXT           , 'ShareLock'::TEXT               ),
        ('ExclusiveLock'::TEXT           , 'ShareRowExclusiveLock'::TEXT   ),
        ('ExclusiveLock'::TEXT           , 'ExclusiveLock'::TEXT           ),
        ('ExclusiveLock'::TEXT           , 'AccessExclusiveLock'::TEXT     ),
        ('AccessExclusiveLock'::TEXT     , 'AccessShareLock'::TEXT         ),
        ('AccessExclusiveLock'::TEXT     , 'RowShareLock'::TEXT            ),
        ('AccessExclusiveLock'::TEXT     , 'RowExclusiveLock'::TEXT        ),
        ('AccessExclusiveLock'::TEXT     , 'ShareUpdateExclusiveLock'::TEXT),
        ('AccessExclusiveLock'::TEXT     , 'ShareLock'::TEXT               ),
        ('AccessExclusiveLock'::TEXT     , 'ShareRowExclusiveLock'::TEXT   ),
        ('AccessExclusiveLock'::TEXT     , 'ExclusiveLock'::TEXT           ),
        ('AccessExclusiveLock'::TEXT     , 'AccessExclusiveLock'::TEXT     )
    )
   ,L AS
    (
        SELECT
            (
                LOCKTYPE
               ,DATABASE
               ,RELATION::REGCLASS::TEXT
               ,PAGE
               ,TUPLE
               ,VIRTUALXID
               ,TRANSACTIONID
               ,CLASSID
               ,OBJID
               ,OBJSUBID
            ) AS TARGET
           ,VIRTUALTRANSACTION
           ,PID
           ,MODE
           ,GRANTED
       FROM PG_CATALOG.PG_LOCKS
    )
   ,T AS
    (
        SELECT
            BLOCKER.TARGET AS BLOCKER_TARGET
           ,BLOCKER.PID    AS BLOCKER_PID
           ,BLOCKER.MODE   AS BLOCKER_MODE
           ,BLOCKED.TARGET AS TARGET
           ,BLOCKED.PID    AS PID
           ,BLOCKED.MODE   AS MODE
        FROM L BLOCKER
        JOIN L BLOCKED ON  NOT BLOCKED.GRANTED
                       AND BLOCKER.GRANTED
                       AND BLOCKED.PID != BLOCKER.PID
                       AND BLOCKED.TARGET IS NOT DISTINCT FROM BLOCKER.TARGET
        JOIN C         ON  C.REQUESTED = BLOCKED.MODE
                       AND C.CURRENT   = BLOCKER.MODE
    )
   ,R AS
    (
        SELECT
            BLOCKER_TARGET
           ,BLOCKER_PID
           ,BLOCKER_MODE
           ,'1'::INT AS DEPTH
           ,TARGET
           ,PID
           ,MODE
           ,BLOCKER_PID::TEXT || ',' || PID::TEXT AS SEQ
        FROM T
        UNION ALL
        SELECT
            BLOCKER.BLOCKER_TARGET
           ,BLOCKER.BLOCKER_PID
           ,BLOCKER.BLOCKER_MODE
           ,BLOCKER.DEPTH + 1
           ,BLOCKED.TARGET
           ,BLOCKED.PID
           ,BLOCKED.MODE
           ,BLOCKER.SEQ || ',' || BLOCKED.PID::TEXT
        FROM R BLOCKER
        JOIN T BLOCKED ON BLOCKED.BLOCKER_PID = BLOCKER.PID
        WHERE BLOCKER.DEPTH < 1000
    )
SELECT *
FROM   R
ORDER BY SEQ;
728x90
반응형
그리드형(광고전용)
Posted by icodebroker

댓글을 달아 주세요