15.3 락 대기·세션 문제

SELECT pid, usename, application_name, client_addr,
       state, wait_event_type, wait_event,
       xact_start, now() - xact_start AS xact_age,
       query_start, now() - query_start AS query_age,
       LEFT(query, 100) AS query
  FROM pg_stat_activity
 WHERE state != 'idle'
 ORDER BY xact_start NULLS LAST;

SELECT bl.pid AS blocked, bl.usename AS u1, bl.query AS blocked_query,
       kl.pid AS blocker, kl.usename AS u2, kl.query AS blocker_query
  FROM pg_stat_activity bl
  JOIN LATERAL (
    SELECT * FROM pg_stat_activity
     WHERE pid = ANY(pg_blocking_pids(bl.pid))
  ) kl ON true
 WHERE cardinality(pg_blocking_pids(bl.pid)) > 0;

WITH RECURSIVE chain AS (
  SELECT pid, pg_blocking_pids(pid) AS blockers, 0 AS depth
    FROM pg_stat_activity
   WHERE cardinality(pg_blocking_pids(pid)) > 0
  UNION ALL
  SELECT a.pid, pg_blocking_pids(a.pid), c.depth + 1
    FROM chain c
    JOIN pg_stat_activity a ON a.pid = ANY(c.blockers)
   WHERE c.depth < 5
)
SELECT * FROM chain;

SELECT pg_cancel_backend(<pid>);     -- 현재 쿼리 취소
SELECT pg_terminate_backend(<pid>);  -- 세션 강제 종료

ALTER TABLE orders ADD COLUMN ...

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN ...;   -- 5초 안 잡히면 ERROR

UPDATE accounts SET balance = ... WHERE id = popular_id
-- 같은 popular row를 100 트랜잭션이 동시에

SELECT pid, query, wait_event FROM pg_stat_activity
 WHERE backend_type = 'autovacuum worker';

SELECT * FROM pg_prepared_xacts;

FATAL: too many connections

-- 사용자별 연결 수
SELECT usename, count(*)
  FROM pg_stat_activity
 GROUP BY usename
 ORDER BY count(*) DESC;

-- application별
SELECT application_name, count(*)
  FROM pg_stat_activity
 GROUP BY application_name;

-- state별
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

SELECT wait_event, count(*) FROM pg_stat_activity
 WHERE wait_event_type = 'LWLock'
 GROUP BY wait_event;

SELECT pid, locktype, classid, objid, mode, granted, query
  FROM pg_locks l
  JOIN pg_stat_activity a USING (pid)
 WHERE locktype = 'advisory';

-- 한 번에 모든 진단
SELECT 'long tx' AS issue, count(*) FROM pg_stat_activity WHERE xact_start < now() - interval '5 min' AND state != 'idle'
UNION ALL
SELECT 'idle in tx',     count(*) FROM pg_stat_activity WHERE state = 'idle in transaction' AND state_change < now() - interval '5 min'
UNION ALL
SELECT 'blocked',        count(*) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0
UNION ALL
SELECT 'prepared',       count(*) FROM pg_prepared_xacts
UNION ALL
SELECT 'replication slot inactive', count(*) FROM pg_replication_slots WHERE NOT active;