안전 조건이 중첩되며 D1 SQL 한도를 넘는 문제 해결

• 분야: DB
• 기술 스택: TypeScript · SQLite · Cloudflare D1
• 관련 기술: 표현식 깊이 · MATERIALIZED CTE · 원자적 조건 검사 · 데이터 보존

문제와 원인

폐기 대상 실기 콘텐츠를 정리하는 SQL은 삭제 전 상태를 정확히 비교하고, 대체 콘텐츠 준비 여부와 학습 기록 연결 여부를 검사했다. 각각의 조건에 JSON 탐색·집계·여러 테이블의 EXISTS가 포함됐다. 이 SQL을 감사 기록의 SELECT·WHERE 안에 다시 삽입하면서 표현식 트리가 깊어졌다.

조건식이 논리적으로 맞아도 SQL 엔진이 파싱할 수 있는 깊이를 넘으면 실행 전에 실패한다. 같은 조건을 여러 위치에 붙여 넣으면서 SQL 크기도 커졌다. 이를 확인하기 위해 표현식 깊이 100, 복합 SELECT 10으로 제한한 SQLite 연결에서 원본 SQL을 실행했다.

이미지 확대

수정 전 SQL 실행에서 발생한 Expression tree is too large 최대 깊이 100 오류

닫기

화면 너비에 맞춤원본 크기−

100%

+원본 보기 (새 창)

확대한 이미지는 상하좌우로 이동할 수 있습니다. 키보드는 이미지 영역에 초점을 두고 방향키를 사용하세요. Esc로 닫습니다.

수정 전 코드

applyPracticalRetirement()의 감사 기록 조건

현재 상태·준비 조건·차단 조건을 INSERT ... SELECT 안에 직접 중첩했다.

const commands = [{
  sql: `INSERT INTO admin_audit_logs
    (id, admin_user_hash, action, target_type, target_id,
      before_summary, after_summary, success, failure_reason, created_at)
    SELECT ?, ?, 'practical-retirement-apply', 'retired-content',
      'practical-reconstructed-content', (${countsSql}),
      '{"sourceRemoved":true,"backupsDeleted":false}', 1, '', ?
    WHERE (${fingerprintSql}) = ?
      AND (${readinessSql}) AND (${blockerSql}) = 0
    RETURNING before_summary`,
  values: [previewId, identity.hash, completedAt, inspection.snapshot],
}];

검사를 일부 빼면 요청을 준비한 뒤 데이터가 바뀐 경우에도 정리가 진행될 수 있다. 조건을 유지하면서 중첩 구조를 바꿨다.

수정 후 코드

상태와 안전 조건을 CTE로 분리

retirement_state는 비교할 전체 상태를, retirement_ready는 준비·차단 조건을 각각 계산한다. MATERIALIZED로 결과를 고정한 뒤 감사 기록과 같은 배치의 변경문에서 사용한다.

WITH retirement_state AS MATERIALIZED (
  ${fingerprintSql}
), retirement_ready AS MATERIALIZED (
  SELECT (${readinessSql}) AND (${blockerSql}) = 0 AS ready
)
INSERT INTO admin_audit_logs
  (id, admin_user_hash, action, target_type, target_id,
    before_summary, after_summary, success, failure_reason, created_at)
SELECT ?, ?, 'practical-retirement-apply', 'retired-content',
  'practical-reconstructed-content',
  json_extract(retirement_state.snapshot, '$.counts'),
  '{"sourceRemoved":true,"backupsDeleted":false}', 1, '', ?
FROM retirement_state, retirement_ready
WHERE retirement_state.snapshot = ? AND retirement_ready.ready
RETURNING before_summary

미리보기의 정확한 본문 해시와 전체 메타데이터 비교는 유지했다. 진단 조회도 큰 복합 SELECT 하나로 합치는 대신 제한된 읽기로 나눴다. 변경 배치의 첫 조건이 통과하지 않거나 뒤의 삭제가 실패하면 감사 기록·삭제·revision 변경이 함께 롤백된다.

검증 결과

같은 로컬 DB에 정리 대상 문항 400개와 보존할 문항 66개, 풀이 기록 1개, 기존 백업 5개를 넣었다. 수정 전·후 모두 표현식 깊이 100·복합 SELECT 10 제한으로 실행했다. DBeaver에서도 같은 깊이 오류와 수정 후 조건 SQL의 반환 행을 확인했다.

정상 배치를 끝낸 DB를 DBeaver에서 다시 조회한 결과, 정리 대상은 0개이고 문항 66개·풀이 1개·백업 5개는 남았다.

이미지 확대

정리 후 대상 문항 0개와 보존 문항 66개·풀이 1개·백업 5개

닫기

화면 너비에 맞춤원본 크기−

100%

+원본 보기 (새 창)

확대한 이미지는 상하좌우로 이동할 수 있습니다. 키보드는 이미지 영역에 초점을 두고 방향키를 사용하세요. Esc로 닫습니다.

본문을 같은 길이로 바꾸고 수정 시각을 유지한 경우, 대체 콘텐츠 준비 상태·메타데이터·백업 staging·다른 학습 범위의 연결을 바꾼 경우, 삭제 트리거가 실패한 경우를 각각 실행했다. 여섯 조건 모두 배치 전체가 거절 또는 롤백됐고 대상 400개가 보존됐다. 성공한 요청을 다시 실행해도 추가 삭제나 감사 기록 증가 없이 기존 결과를 반환했다.