POSTGRESQL Archives - 6 중 3 번째 페이지

[DATABASE/POSTGRESQL] ALTER TABLE … OWNER TO 명령 : 테이블 소유자 변경하기

■ ALTER TABLE … OWNER TO 명령을 사용해 테이블 소유자를 변경하는 방법을 보여준다. ▶ 예제 코드 (SQL)


ALTER TABLE APPLICATION OWNER TO POSTGRES;
            -----------          --------
            테이블명             계정

ALTER TABLE APPLICATION OWNER TO POSTGRES;

----------- --------

테이블명 계정

[DATABASE/POSTGRESQL] ALTER TABLE … ALTER COLUMN … TYPE … 명령 : 컬럼 데이터 타입 변경하기

■ ALTER TABLE … ALTER COLUMN … TYPE … 명령을 사용해 컬럼 데이터 타입을 변경하는 방법을 보여준다. ▶ 예제 코드 (SQL)


ALTER TABLE APPLICATION ALTER COLUMN FTP_SERVER_ID TYPE VARCHAR;
            -----------              -------------      -------
            테이블명                 컬럼명             변경 데이터 타입

ALTER TABLE APPLICATION ALTER COLUMN FTP_SERVER_ID TYPE VARCHAR;

----------- ------------- -------

테이블명 컬럼명 변경 데이터 타입

[DATABASE/POSTGRESQL] ALTER TABLE … RENAME COLUMN 명령 : 컬럼명 변경하기

■ ALTER TABLE … RENAME COLUMN 명령을 사용해 컬럼명을 변경하는 방법을 보여준다. ▶ 예제 코드 (SQL)


ALTER TABLE APPLICATION RENAME COLUMN FTP_SERVER_ID TO FTP_ID;
            -----------               -------------    ------
            테이블명                  기존 컬럼명      변경 컬럼명

ALTER TABLE APPLICATION RENAME COLUMN FTP_SERVER_ID TO FTP_ID;

----------- ------------- ------

테이블명 기존 컬럼명 변경 컬럼명

[DATABASE/POSTGRESQL] ALTER TABLE … ALTER … DROP NOT NULL 명령 : 컬럼 NOT NULL 제거하기

■ ALTER TABLE … ALTER … DROP NOT NULL 명령을 사용해 컬럼의 NOT NULL을 제거하는 방법을 보여준다. ▶ 예제 코드 (SQL)


ALTER TABLE APPLICATION ALTER FTP_SERVER_ID DROP NOT NULL;
            -----------       -------------
            테이블명          컬럼명

ALTER TABLE APPLICATION ALTER FTP_SERVER_ID DROP NOT NULL;

----------- -------------

테이블명 컬럼명

[DATABASE/POSTGRESQL] ALTER TABLE … RENAME TO 명령 : 테이블명 변경하기

■ ALTER TABLE … RENAME TO 명령을 사용해 테이블명을 변경하는 방법을 보여준다. ▶ 예제 코드 (SQL)


ALTER TABLE APPLICATION RENAME TO APPLICATION_MASTER;
            -----------           ------------------
            기존 테이블명         변경 테이블명

ALTER TABLE APPLICATION RENAME TO APPLICATION_MASTER;

----------- ------------------

기존 테이블명 변경 테이블명

[DATABASE/POSTGRESQL] ALTER TABLE … ALTER … SET NOT NULL 명령 : 컬럼 NOT NULL 설정하기

■ ALTER TABLE … ALTER … SET NOT NULL 명령을 사용해 컬럼 NOT NULL을 설정하는 방법을 보여준다. ▶ 예제 코드 (SQL)


ALTER TABLE APPLICATION ALTER FTP_SERVER_ID SET NOT NULL;
            -----------       -------------
            테이블명          컬럼명

ALTER TABLE APPLICATION ALTER FTP_SERVER_ID SET NOT NULL;

----------- -------------

테이블명 컬럼명

[DATABASE/POSTGRESQL] ALTER TABLE … ALTER … DROP DEFAULT 명령 : 컬럼 디폴트 값 제거하기

■ ALTER TABLE … ALTER … DROP DEFAULT 명령을 사용해 컬럼 디폴트 값을 제거하는 방법을 보여준다. ▶ 예제 코드 (SQL)


ALTER TABLE APPLICATION ALTER FTP_SERVER_ID DROP DEFAULT;
            -----------       -------------
            테이블명          컬럼명

ALTER TABLE APPLICATION ALTER FTP_SERVER_ID DROP DEFAULT;

----------- -------------

테이블명 컬럼명

[DATABASE/POSTGRESQL] ALTER TABLE … DROP 명령 : 컬럼 제거하기

■ ALTER TABLE … DROP 명령을 사용해 컬럼을 제거하는 방법을 보여준다. ▶ 예제 코드 (SQL)


ALTER TABLE APPLICATION DROP FTP_SERVER_ID;
            -----------      -------------
            테이블명        컬럼명

ALTER TABLE APPLICATION DROP FTP_SERVER_ID;

----------- -------------

테이블명 컬럼명

[DATABASE/POSTGRESQL] ALTER TABLE … ALTER COLUMN … SET DEFAULT … 명령 : 컬럼 디폴트 값 설정하기

■ ALTER TABLE … ALTER COLUMN … SET DEFAULT … 명령을 사용해 컬럼 디폴트 값을 설정하는 방법을 보여준다. ▶ 예제 코드 (SQL)

[DATABASE/POSTGRESQL] ALTER TABLE … ADD 명령 : 컬럼 추가하기

■ ALTER TABLE … ADD 명령을 사용해 컬럼을 추가하는 방법을 보여준다. ▶ 예제 코드 (SQL)


ALTER TABLE APPLICATION ADD FTP_SERVER_ID VARCHAR;
            -----------     ------------- -------
            테이블명        컬럼명        데이터 타입

ALTER TABLE APPLICATION ADD FTP_SERVER_ID VARCHAR;

----------- ------------- -------

테이블명 컬럼명 데이터 타입

[DATABASE/POSTGRESQL] 재귀적으로 잠금 프로세스 조회하기

■ 재귀적으로 잠금 프로세스를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


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;

100

101

102

103

104

105

106

107

108

109

110

111

112

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;

[DATABASE/POSTGRESQL] 잠금 프로세스 조회하기

■ 잠금 프로세스를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    WAITING.LOCKTYPE           AS WAITING_LOCKTYPE
   ,WAITING.RELATION::REGCLASS AS WAITING_TABLE
   ,WAITING_STM.QUERY          AS WAITING_QUERY
   ,WAITING.MODE               AS WAITING_MODE
   ,WAITING.PID                AS WAITING_PID
   ,OTHER.LOCKTYPE             AS OTHER_LOCKTYPE
   ,OTHER.RELATION::REGCLASS   AS OTHER_TABLE
   ,OTHER_STM.QUERY            AS OTHER_QUERY
   ,OTHER.MODE                 AS OTHER_MODE
   ,OTHER.PID                  AS OTHER_PID
   ,OTHER.GRANTED              AS OTHER_GRANTED
FROM PG_CATALOG.PG_LOCKS         AS WAITING
JOIN PG_CATALOG.PG_STAT_ACTIVITY AS WAITING_STM ON  WAITING_STM.PID       = WAITING.PID
JOIN PG_CATALOG.PG_LOCKS         AS OTHER       ON  WAITING."database"    = OTHER."database"
                                                AND WAITING.RELATION      = OTHER.RELATION
                                                OR  WAITING.TRANSACTIONID = OTHER.TRANSACTIONID
JOIN PG_CATALOG.PG_STAT_ACTIVITY AS OTHER_STM   ON  OTHER_STM.PID         = OTHER.PID
WHERE NOT WAITING.GRANTED
AND   WAITING.PID <> OTHER.PID;

SELECT

WAITING.LOCKTYPE AS WAITING_LOCKTYPE

,WAITING.RELATION::REGCLASS AS WAITING_TABLE

,WAITING_STM.QUERY AS WAITING_QUERY

,WAITING.MODE AS WAITING_MODE

,WAITING.PID AS WAITING_PID

,OTHER.LOCKTYPE AS OTHER_LOCKTYPE

,OTHER.RELATION::REGCLASS AS OTHER_TABLE

,OTHER_STM.QUERY AS OTHER_QUERY

,OTHER.MODE AS OTHER_MODE

,OTHER.PID AS OTHER_PID

,OTHER.GRANTED AS OTHER_GRANTED

FROM PG_CATALOG.PG_LOCKS AS WAITING

JOIN PG_CATALOG.PG_STAT_ACTIVITY AS WAITING_STM ON WAITING_STM.PID = WAITING.PID

JOIN PG_CATALOG.PG_LOCKS AS OTHER ON WAITING."database" = OTHER."database"

AND WAITING.RELATION = OTHER.RELATION

OR WAITING.TRANSACTIONID = OTHER.TRANSACTIONID

JOIN PG_CATALOG.PG_STAT_ACTIVITY AS OTHER_STM ON OTHER_STM.PID = OTHER.PID

WHERE NOT WAITING.GRANTED

AND WAITING.PID <> OTHER.PID;

[DATABASE/POSTGRESQL] 잠금 프로세스 조회하기

■ 잠금 프로세스를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    A.DATNAME
   ,L.RELATION::REGCLASS
   ,L.TRANSACTIONID
   ,L.MODE
   ,L.GRANTED
   ,A.USENAME
   ,A.QUERY
   ,A.QUERY_START
   ,AGE(NOW(), A.QUERY_START) AS AGE
   ,A.PID
FROM PG_STAT_ACTIVITY A
JOIN PG_LOCKS         L ON L.PID = A.PID
ORDER BY A.QUERY_START;

SELECT

A.DATNAME

,L.RELATION::REGCLASS

,L.TRANSACTIONID

,L.MODE

,L.GRANTED

,A.USENAME

,A.QUERY

,A.QUERY_START

,AGE(NOW(), A.QUERY_START) AS AGE

,A.PID

FROM PG_STAT_ACTIVITY A

JOIN PG_LOCKS L ON L.PID = A.PID

ORDER BY A.QUERY_START;

[DATABASE/POSTGRESQL] 잠금 프로세스 조회하기

■ 잠금 프로세스를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    BLOCKED_LOCKS.PID                  AS BLOCKED_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;

SELECT

BLOCKED_LOCKS.PID AS BLOCKED_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;

[DATABASE/POSTGRESQL] 잠금 프로세스 조회하기

■ 잠금 프로세스를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    BLOCKED_LOCKS.PID         AS BLOCKED_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
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;