부분 UNIQUE 인덱스가 있는데 ON CONFLICT가 거절된다

PostgreSQL에서 삭제되지 않은 행만 유일하게 만들었는데 ON CONFLICT (member_key)가 대응하는 고유 제약을 찾지 못한다면, 인덱스의 열뿐 아니라 적용 조건을 확인해야 한다. 부분 UNIQUE는 조건을 만족하는 행 사이의 유일성만 보장한다. 활성 행에만 적용한 인덱스라면 충돌 대상도 ON CONFLICT (member_key) WHERE active처럼 그 범위를 표현해야 한다.

다만 WHERE를 붙이는 것과 전체 키 UNIQUE를 추가하는 것은 다른 수정이다. 활성 행 하나와 여러 비활성 이력을 허용할지, 상태와 관계없이 키마다 한 행만 허용할지 먼저 정해야 한다.

인덱스 정의에서 빠진 조건을 찾는다

먼저 문제가 나는 테이블의 인덱스를 조회한다. PostgreSQL에 연결된 SQL 클라이언트에서 실행하고, 아래의 public과 member_contact는 자신의 스키마·테이블 이름으로 바꾼다. indexdef에는 재구성한 CREATE INDEX 문이 나온다. pg_indexes 문서↗ (새 탭에서 열림)

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'member_contact'
ORDER BY indexname;

별도 실습 DB에서 조회한 정의는 CREATE UNIQUE INDEX member_contact_active_uq ON public.member_contact USING btree (member_key) WHERE active였다. 이 정의는 active가 참인 행끼리만 키 중복을 막는다. 비활성 행까지 포함한 member_key 전체의 유일성은 보장하지 않는다. 부분 인덱스 문서의 Example 11.3↗ (새 탭에서 열림)도 조건에 맞는 부분집합 안에서만 유일성을 강제한다고 설명한다.

조회 결과가 없다면 먼저 스키마·테이블 이름과 접속 DB를 확인한다. 인덱스가 있다면 열·표현식과 WHERE 조건을 함께 읽는다. 전체 키 UNIQUE도 있는지는 뒤의 선택 결과를 해석할 때 필요하다.

충돌 대상에 WHERE를 붙인다

아래는 member_key text, active boolean, payload text가 모두 NOT NULL인 테이블을 대상으로 한 변경 예다. 개발 DB에 같은 열 구성과 위 부분 인덱스가 있고, 활성 A의 payload는 live-v1, 같은 키의 비활성 이력은 archived인 상태에서 비교했다. 실제 장애 이력이 아니라 PostgreSQL 18.6의 합성 실험이다. 자신의 INSERT에서는 테이블·열·입력값을 맞춰 적용한다.

먼저 조건 없이 키만 적은 다음 문장이 실패하는지 본다. 두 INSERT를 비교할 때는 자동 커밋으로 각각 실행한다. 명시적 트랜잭션에서 첫 문장이 실패했다면 ROLLBACK한 뒤 수정안을 실행한다.

INSERT INTO public.member_contact (member_key, active, payload)
VALUES ('A', true, 'live-v2')
ON CONFLICT (member_key)
DO UPDATE SET payload = EXCLUDED.payload;

결과는 SQLSTATE 42P10과 “there is no unique or exclusion constraint matching the ON CONFLICT specification” 오류였다. 입력을 비활성 A로 바꿔도 같았다. VALUES의 true가 충돌 대상의 조건을 대신하지는 않았다. 일반 INSERT로 활성 A를 중복 입력했을 때의 23505와도 다르다. 전자는 충돌 중재자로 쓸 인덱스를 찾지 못한 결과이고, 후자는 실제 고유성 위반이었다. PostgreSQL 오류 코드↗ (새 탭에서 열림)

수정할 곳은 충돌 키 괄호 바로 뒤다. 다음 문장으로 같은 활성 A가 갱신되는지 확인한다. EXCLUDED.payload는 이번에 넣으려던 값이다.

INSERT INTO public.member_contact (member_key, active, payload)
VALUES ('A', true, 'live-v2')
ON CONFLICT (member_key) WHERE active
DO UPDATE SET payload = EXCLUDED.payload
RETURNING member_key, active, payload;

이번에는 A, true, live-v2 한 행을 반환하고 INSERT 0 1로 성공했다. 비활성 이력은 그대로였다. INSERT 문서↗ (새 탭에서 열림)에서 이 위치의 조건은 부분 고유 인덱스를 추론하는 index_predicate다.

DO UPDATE 뒤의 WHERE는 이 조건을 대신하지 못한다. 같은 실험에서 충돌 대상의 WHERE active를 빼고 SET 뒤에 WHERE dst.active를 둔 문장은 다시 42P10이었다. 이때 dst는 대상 테이블의 별칭이다. DO UPDATE ... WHERE는 충돌 행을 찾은 뒤 갱신할지를 정한다. 올바른 충돌 대상을 유지한 채 갱신 조건을 거짓으로 만들었을 때는 오류 없이 INSERT 0 0이 됐고 RETURNING도 0행이었다.

WHERE가 비활성 입력을 걸러 주지는 않는다

부분 인덱스만 있는 테이블에서 같은 충돌 대상을 유지하고 비활성 A를 두 번 넣어 보았다. 두 입력 모두 새 행으로 저장돼 A는 활성 1행과 비활성 3행이 됐다. 비활성 B만 있던 키에 활성 B를 넣었을 때도 기존 이력을 되살리는 대신 새 활성 행이 생겼다.

충돌 대상의 WHERE는 인덱스를 추론하는 조건이다. INSERT할 행을 걸러내는 조건이 아니다. 이 실험의 비활성 입력은 부분 인덱스의 유일성 검사 범위 밖에 있었으므로 그대로 삽입됐다. 비활성 입력 자체를 받지 않아야 한다면 입력을 선별하는 규칙이 별도로 필요하다.

반대로 조건 없는 UNIQUE (member_key)를 추가하면 활성·비활성 이력이 같은 키를 공유하는 구조와 충돌한다. 또한 충돌 대상에 WHERE active를 적어도 다른 요건을 만족하는 전체 키 고유 인덱스가 함께 추론될 수 있다. 이는 INSERT 문서에 명시된 동작이다.

자기 INSERT가 선택하는 인덱스를 확인하려면 다음처럼 EXPLAIN (COSTS OFF)를 붙인다. 이 명령은 계획을 확인한다. ANALYZE를 붙이면 INSERT가 실제 실행되므로 구분한다. EXPLAIN 문서↗ (새 탭에서 열림)

EXPLAIN (COSTS OFF)
INSERT INTO public.member_contact (member_key, active, payload)
VALUES ('A', true, 'live-v2')
ON CONFLICT (member_key) WHERE active
DO UPDATE SET payload = EXCLUDED.payload;

부분 인덱스만 있던 경우에는 Conflict Arbiter Indexes: member_contact_active_uq가 나왔다. 별도 대조 테이블에 전체 키 인덱스와 활성 전용 인덱스를 함께 만들자 member_global_all_uq, member_global_active_uq 둘이 표시됐다. 그 테이블에서는 같은 키의 비활성 입력이 새 이력을 만드는 대신 기존 비활성 행을 갱신했다. 전체 키 인덱스도 충돌을 감지했기 때문이다.

활성 하나와 여러 비활성 이력이 업무 규칙이라면 인덱스와 충돌 대상에 그 조건을 함께 표현한다. 상태와 관계없이 키마다 한 행이어야 한다면 전체 키 유일성이 맞는지 검토한다. 삭제 시각을 쓰는 구조도 먼저 실제 인덱스의 WHERE deleted_at IS NULL 같은 조건을 읽는다. 조건을 맞춰도 추론이 실패하면 충돌 대상의 열·표현식과 인덱스 정의를 다시 대조한다. 다르게 쓴 조건식을 PostgreSQL이 언제나 같은 범위로 인식한다고 가정하지 않는다.

직접 확인한 범위는 PostgreSQL 18.6의 단일 연결과 작은 합성 테이블이다. 삭제 여부는 NOT NULL boolean으로 모델링했다. 동시 요청, ORM 생성 SQL, 준비된 문장의 매개변수·계획 캐시, deleted_at 조건의 직접 실행, 운영 인덱스 변경의 잠금과 소요 시간은 검증하지 않았다.