[트러블슈팅] PostgreSQL alter table 무한로딩

TL;DR

• 개발 서버에서 새로 추가된 테이블의 FK와 인덱스를 제거하려는데 무한로딩이 발생.
• pg_stat_activity 로 원인을 추적해 `ALTER TABLE` 쿼리를 강제 종료했지만, 재시도에도 문제 지속.
• FK의 대상 테이블에서 `SHOW TRANSACTION ISOLATION LEVEL` 명령어가 idle in transaction 상태로 남아 있음을 발견하고, 해당 명령어를 실행한 세션을 종료해 문제 해결.

문제발생

• 개발서버 데이터베이스에서 새로운 테이블에 변경사항이 발생해 FK 를 Drop 하고있었다.
• ALTER TABLE 명령어가 완료되지 않고 계속 실행되고있었다.
• 개발서버에서 Lambda Timeout 에러가 발생하고, RDS 세션을 조회했더니 세션하나가 종료되지 않고 있었다.

문제원인 탐색 및 해결 과정

1. 우선 방금 실행한 ALTER TALBE 명령어를 kill 했다.
   a. 방금 실행한 ALTER TABLE 명령어의 PID 찾기

   SELECT 
       pid, application_name, backend_start, state, query, query_start
   FROM pg_stat_activity
   WHERE 
       state = 'active'
   ORDER BY query_start ASC;

   b. 해당 PID KILL

   SELECT pg_terminate_backend(<pid>);

2. 재시도
   a. 재시도 해도 무한대기 발생.
   b. ALTER TABLE 이 아닌 다른 명령어는 잘 동작함.
3. 제거하려는 FK 가 연결되어있던 테이블에 걸린 트랜잭션 확인
   a. FK 대상 테이블에 걸린 트랜잭션 확인

   -- 락을 점유하고 있는 트랜잭션 조회
   SELECT
    pg_stat_activity.pid,
    pg_stat_activity.query,
    pg_locks.locktype,
    pg_locks.mode,
    pg_locks.granted,
    pg_stat_activity.state
   FROM pg_stat_activity
         JOIN pg_locks ON pg_stat_activity.pid = pg_locks.pid
   WHERE pg_locks.locktype = 'relation'
   AND pg_locks.relation = '<FK 대상 테이블 이름>'::regclass;

4. SHOW TRANSACTION ISOLATION LEVEL 명령어가 idle in transaction 상태로 AccessShareLock 을 잡고있는것을 확인.
   a. AccessShareLock 은 CRUD 작업은 가능하지만 Alter, Drop 같은 DDL 작업은 불가능.
5. 팀에 상황을 공유하고 LOCK 을 풀어달라고 요청드렸고, LOCK 풀리고 FK drop 에 성공