PostgreSQL 23P01 오류 원인과 해결 방법 완벽 가이드

23P01 exclusion violation 는?

PostgreSQL 에러 코드 23P01 exclusion violation은 배제 제약 조건(Exclusion Constraint) 을 위반했을 때 발생하는 에러입니다. 배제 제약 조건이란 테이블에 삽입 또는 업데이트되는 행이 기존 행과 특정 연산자 조건에 따라 충돌하지 않도록 강제하는 고급 제약 조건입니다. 예를 들어, 회의실 예약 시스템에서 같은 방에 겹치는 시간대로 예약이 두 건 이상 존재할 수 없도록 보장하는 데 자주 활용됩니다.

이 에러는 단순한 UNIQUE 제약과 달리 = 연산자 외에도 &&(범위 겹침), <>(비교) 등 다양한 연산자를 사용할 수 있어 더 복잡한 비즈니스 규칙을 데이터베이스 레벨에서 강제할 수 있습니다. 주로 btree_gist 또는 gist 인덱스와 함께 사용되며, 시간 범위, 공간 범위, IP 대역 등 범위 기반 중복 방지에 특히 강력합니다.

---

주요 발생 원인

1. 시간 범위(tsrange, daterange) 겹침으로 인한 충돌

가장 흔한 발생 원인입니다. 예약 시스템, 스케줄링, 리소스 관리 등 시간 범위를 다루는 애플리케이션에서 새로 삽입하거나 업데이트하는 행의 시간 범위가 기존 행의 시간 범위와 겹칠 때 발생합니다. 예를 들어 회의실 예약 테이블에서 A 회의실이 09:00~11:00로 이미 예약되어 있는데, 10:00~12:00로 같은 방을 또 예약하려 하면 이 에러가 발생합니다. 배제 제약이 && 연산자로 설정되어 있기 때문에 조금이라도 겹치는 구간이 있으면 INSERT 또는 UPDATE가 거부됩니다.

2. 잘못된 데이터 마이그레이션 또는 대량 INSERT

기존 시스템에서 데이터를 이관하거나 외부 소스로부터 대량의 데이터를 삽입할 때, 원본 데이터 자체에 이미 겹치는 레코드가 존재하는 경우 이 에러가 발생합니다. 레거시 시스템에는 배제 제약이 없었거나 검증 로직이 애플리케이션 레이어에만 있어서 더러운(dirty) 데이터가 쌓여 있을 수 있습니다. 이런 경우 마이그레이션 스크립트가 중간에 실패하고 트랜잭션이 롤백되어 전체 작업이 중단됩니다.

3. 배제 제약 조건의 범위 또는 연산자를 잘못 이해한 경우

개발자 또는 DBA가 배제 제약이 설정된 컬럼의 범위나 연산자를 정확히 파악하지 못한 채 데이터를 조작할 때 발생합니다. 예를 들어, 특정 방 번호(room_id)와 시간 범위(during) 두 컬럼을 조합한 배제 제약이 있는데, 방 번호가 다르더라도 시간만 겹치면 에러가 발생한다고 오해하거나, 반대로 같은 방이면 어떤 시간이든 안 된다고 혼동하는 경우입니다. 이는 제약 정의를 명확히 확인하지 않아 생기는 실수이며, 예상치 못한 에러로 이어질 수 있습니다.

---

해결 방법

원인 1 해결: 겹치는 시간 범위 사전 확인 후 INSERT

INSERT 또는 UPDATE 전에 애플리케이션 레벨 또는 쿼리 레벨에서 겹치는 레코드가 있는지 먼저 확인합니다.

-- 예약 테이블 생성 예시
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_reservations (
    id          SERIAL PRIMARY KEY,
    room_id     INT NOT NULL,
    reserved_by VARCHAR(100) NOT NULL,
    during      TSRANGE NOT NULL,
    EXCLUDE USING GIST (
        room_id WITH =,
        during  WITH &&
    )
);

-- 겹치는 예약이 있는지 사전 확인 쿼리
SELECT id, room_id, reserved_by, during
FROM room_reservations
WHERE room_id = 101
  AND during && '[2024-06-01 10:00, 2024-06-01 12:00)'::TSRANGE;

-- 겹치는 예약이 없을 때만 INSERT
INSERT INTO room_reservations (room_id, reserved_by, during)
VALUES (101, 'Alice', '[2024-06-01 10:00, 2024-06-01 12:00)');

충돌 시 에러를 발생시키지 않고 조용히 무시하려면 ON CONFLICT DO NOTHING을 사용할 수 있지만, 배제 제약은 ON CONFLICT로 직접 처리하기 어렵습니다. 이 경우 예외 처리를 애플리케이션 코드에서 구현하는 것이 좋습니다.

-- 에러 발생 시 메시지를 확인하는 PL/pgSQL 예시
DO $$
BEGIN
    INSERT INTO room_reservations (room_id, reserved_by, during)
    VALUES (101, 'Bob', '[2024-06-01 11:00, 2024-06-01 13:00)');
EXCEPTION
    WHEN exclusion_violation THEN
        RAISE NOTICE '해당 시간대에 이미 예약이 존재합니다. 다른 시간을 선택하세요.';
END;
$$;

---

원인 2 해결: 마이그레이션 전 중복 데이터 정제

대량 삽입 전 원본 데이터에서 겹치는 레코드를 미리 찾아내고 정제합니다.

-- 임시 테이블에 원본 데이터 로드
CREATE TEMP TABLE staging_reservations (
    room_id     INT,
    reserved_by VARCHAR(100),
    start_time  TIMESTAMP,
    end_time    TIMESTAMP
);

-- CSV 등에서 데이터 로드 (예시)
-- COPY staging_reservations FROM '/tmp/reservations.csv' CSV HEADER;

-- 겹치는 레코드 탐지 쿼리
SELECT
    a.room_id,
    a.reserved_by AS res_a,
    a.start_time AS start_a,
    a.end_time   AS end_a,
    b.reserved_by AS res_b,
    b.start_time AS start_b,
    b.end_time   AS end_b
FROM staging_reservations a
JOIN staging_reservations b
  ON a.room_id = b.room_id
 AND a.ctid < b.ctid  -- 자기 자신과의 비교 방지
 AND tsrange(a.start_time, a.end_time) && tsrange(b.start_time, b.end_time);

-- 중복 제거 후 정제된 데이터만 본 테이블에 삽입
INSERT INTO room_reservations (room_id, reserved_by, during)
SELECT DISTINCT ON (room_id, tsrange(start_time, end_time))
    room_id,
    reserved_by,
    tsrange(start_time, end_time)
FROM staging_reservations
ORDER BY room_id, tsrange(start_time, end_time), reserved_by;

---

원인 3 해결: 배제 제약 조건 정의 확인

현재 테이블에 설정된 배제 제약 조건을 시스템 카탈로그를 통해 정확히 확인합니다.

-- 특정 테이블의 배제 제약 조건 상세 확인
SELECT
    conname        AS constraint_name,
    contype        AS constraint_type,
    pg_get_constraintdef(oid) AS constraint_definition
FROM pg_constraint
WHERE conrelid = 'room_reservations'::REGCLASS
  AND contype = 'x';  -- 'x'는 exclusion constraint를 의미

-- 인덱스 정보 함께 확인
SELECT
    i.relname   AS index_name,
    ix.indisexclusion,
    pg_get_indexdef(ix.indexrelid) AS index_definition
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
WHERE ix.indrelid = 'room_reservations'::REGCLASS
  AND ix.indisexclusion = TRUE;

---

예방 방법

1. 애플리케이션 레이어에서의 사전 검증과 명확한 에러 처리

배제 제약은 데이터베이스 레벨의 최후 방어선으로 활용하고, 애플리케이션 레이어에서도 충돌 여부를 사전에 확인하는 이중 검증 구조를 갖추세요. 에러 코드 23P01을 명시적으로 캐치하여 사용자에게 친절한 메시지를 제공하는 예외 처리 로직을 반드시 구현하고, 로그에도 기록하여 운영 중 데이터 이상 징후를 모니터링할 수 있도록 합니다.

-- 배제 제약 위반 시 트리거를 활용한 커스텀 에러 메시지 (선택적)
CREATE OR REPLACE FUNCTION check_reservation_overlap()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM room_reservations
        WHERE room_id = NEW.room_id
          AND during && NEW.during
          AND id IS DISTINCT FROM NEW.id
    ) THEN
        RAISE EXCEPTION '회의실 %번에 시간대 % 예약이 이미 존재합니다.',
            NEW.room_id, NEW.during
            USING ERRCODE = '23P01';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 배제 제약 조건 문서화 및 테스트 자동화

팀 내에서 배제 제약이 설정된 테이블에 대한 명세를 명확히 문서화하고, 해당 제약을 검증하는 자동화 테스트를 CI/CD 파이프라인에 포함시키세요. 새로운 배제 제약을 추가하기 전에는 반드시 기존 데이터와의 충돌 여부를 아래와 같이 검증하는 단계를 거칩니다.

-- 제약 추가 전 기존 데이터 충돌 여부 사전 점검
SELECT
    a.id AS id_a,
    b.id AS id_b,
    a.room_id,
    a.during AS during_a,
    b.during AS during_b
FROM room_reservations a
JOIN room_reservations b
  ON a.room_id = b.room_id
 AND a.id < b.id
 AND a.during && b.during;
-- 결과가 0건이어야 제약 추가 가능

---

관련 에러

• 23000 integrity_constraint_violation: 무결성 제약 위반의 상위 범주 에러로, 23P01은 이 카테고리에 속합니다.
• 23505 unique_violation: UNIQUE 제약 위반 에러. 단일 컬럼 또는 복합 컬럼의 중복 값 삽입 시 발생하며, 배제 제약의 단순 버전이라 볼 수 있습니다.
• 23502 not_null_violation: NOT NULL 제약 위반으로, 배제 제약이 걸린 컬럼에 NULL 값을 삽입하려 할 때 함께 발생할 수 있습니다.
• 23514 check_violation: CHECK 제약 위반으로, 배제 제약과 함께 복합적인 데이터 검증 시나리오에서 동시에 마주칠 수 있습니다.

---

본 포스트는 AI가 생성한 기술 가이드입니다. 운영 환경 적용 전 충분한 검토를 권장합니다.