PostgreSQL UNIQUE 값을 맞바꾸는데 첫 UPDATE가 실패한다

PostgreSQL에서 두 행의 고유 값 A와 B를 순서대로 맞바꾸면 첫 UPDATE가 duplicate key value violates unique constraint로 실패할 수 있다. 첫 행을 B로 바꾼 순간에는 두 행이 모두 B이기 때문이다. 트랜잭션으로 묶는 것만으로 UNIQUE 검사가 COMMIT까지 미뤄지지는 않는다. 실제 제약이 지연 가능한지 확인하고, 가능하다면 첫 변경 전에 그 제약만 지연해야 한다.

오류에 나온 제약의 설정부터 확인한다

문제가 난 테이블에서 고유 제약을 조회하고, 오류 메시지의 제약 이름을 conname과 대조한다. 아래 swap_lab.defer_slot은 예제 이름이므로 자신의 스키마·테이블 이름으로 바꾼다.

SELECT conname, condeferrable, condeferred
FROM pg_constraint
WHERE conrelid = 'swap_lab.defer_slot'::regclass
  AND contype = 'u';

condeferrable이 f면 검사를 지연할 수 없다. t라면 지연 가능하며, condeferred가 f인 경우 기본은 IMMEDIATE, t인 경우 기본부터 지연한다. condeferred는 초기 설정이다. 현재 트랜잭션에서 SET CONSTRAINTS로 바꾼 모드를 조회하는 값은 아니다. 조회가 0행이면 대상 테이블 이름과 고유 인덱스를 포함한 실제 정의부터 다시 확인한다. pg_constraint의 설정 의미↗ (새 탭에서 열림)

첫 UPDATE 앞에서 해당 제약을 지연한다

다음 SQL은 swap_lab.defer_slot에 id 정수 기본 키와 code NOT NULL text 열이 있고, 현재 행이 (1, 'A'), (2, 'B')인 경우다. code의 제약은 CONSTRAINT uq_defer_code UNIQUE (code) DEFERRABLE INITIALLY IMMEDIATE로 정의돼 있어야 한다. 앞의 조회 결과는 uq_defer_code / t / f다. 아래 결과는 기존 앱 DB와 분리한 새 PostgreSQL 18.6의 소유 스키마에서 이 두 행으로 확인했다.

같은 DB 연결에서 BEGIN 뒤에 제약 이름을 지정하고, 두 행을 바꾼 다음 미뤄 둔 검사를 수행한다. 자신의 코드에 적용할 때는 테이블·제약 이름과 행 ID·교환값을 함께 맞춘다.

BEGIN;
SET CONSTRAINTS swap_lab.uq_defer_code DEFERRED;
UPDATE swap_lab.defer_slot SET code = 'B' WHERE id = 1;
UPDATE swap_lab.defer_slot SET code = 'A' WHERE id = 2;
SET CONSTRAINTS swap_lab.uq_defer_code IMMEDIATE;
COMMIT;
SELECT id, code FROM swap_lab.defer_slot ORDER BY id;

두 UPDATE는 각각 UPDATE 1이었고 IMMEDIATE 전환과 COMMIT도 성공했다. 마지막 조회에는 1|B, 2|A가 남았다. 별도 조회에서 첫 UPDATE 직후에는 같은 트랜잭션 안의 두 행이 모두 B였다. 이 중간 중복을 허용해야 두 번째 UPDATE까지 갈 수 있다.

SET CONSTRAINTS ... IMMEDIATE는 지금까지 미뤄 둔 변경을 그 명령 안에서 검사한다. 이를 생략하고 계속 지연하면 COMMIT에서 검사한다. 어느 경로든 최종 중복까지 허용되는 것은 아니다. 지연 설정은 현재 트랜잭션에만 적용되므로 다음 트랜잭션은 다시 초기 모드로 시작한다. BEGIN 밖에서 SET 명령을 실행하면 경고만 나고 효과가 없다. SET CONSTRAINTS의 검사 시점과 적용 범위↗ (새 탭에서 열림)

여기서 swap_lab은 스키마, uq_defer_code는 제약 이름이다. SET 명령에는 테이블명이 들어가지 않는다. ALL로 모든 지연 가능 제약을 바꾸기보다 조회한 이름을 지정한다. 다만 같은 스키마의 동명 제약들이 있으면 SET은 모두 대상으로 삼으므로, 같은 이름이 다른 테이블에도 붙었는지 확인해야 한다. 제약 이름 해석 규칙↗ (새 탭에서 열림)

일반 UNIQUE에는 이 방법을 바로 적용할 수 없다. 같은 두 행을 넣은 일반 UNIQUE와 지연을 요청하지 않은 DEFERRABLE INITIALLY IMMEDIATE를 비교하자 둘 다 첫 UPDATE에서 23505로 실패했다. 일반 UNIQUE는 행 변경 때 즉시 검사하고, 지연하지 않은 DEFERRABLE UNIQUE는 문장 끝에서 검사한다. 두 UPDATE로 나누면 첫 문장이 끝나도 B가 중복이므로 양쪽 모두 막힌다. 일반 UNIQUE와 문장 끝 검사의 차이↗ (새 탭에서 열림)

일반 제약에 DEFERRED를 요청한 비교는 42809: constraint "uq_ordinary_code" is not deferrable로 거절됐다. SET은 기존 제약을 DEFERRABLE로 바꾸는 명령이 아니다. 조회에서 지연 불가로 확인됐다면 제약 설계 변경이 가능한지부터 별도로 검토한다. 이 실험에서는 기존 제약을 교체하지 않았다.

UPDATE가 성공해도 COMMIT은 실패할 수 있다

교환을 마친 (1, 'B'), (2, 'A')에서 1번 행만 A로 바꾸고 끝내면 두 행이 모두 A다. 이 잘못된 입력으로 지연 검사의 마지막 경계를 확인한다.

BEGIN;
SET CONSTRAINTS swap_lab.uq_defer_code DEFERRED;
UPDATE swap_lab.defer_slot SET code = 'A' WHERE id = 1;
COMMIT;

UPDATE는 UPDATE 1로 성공했지만 COMMIT에서 23505와 Key (code)=(A) already exists.가 나왔다. 23505는 고유 제약 위반인 unique_violation이다. 트랜잭션은 취소됐고, 바로 앞의 SELECT를 다시 실행하자 이전에 커밋한 1|B, 2|A가 남아 있었다. UPDATE의 성공 응답만으로 교환 완료를 처리하지 말고 COMMIT 결과까지 확인해야 한다. PostgreSQL 오류 코드↗ (새 탭에서 열림)

같은 잘못된 UPDATE 뒤에 COMMIT 대신 SET CONSTRAINTS swap_lab.uq_defer_code IMMEDIATE;를 실행한 비교에서도 그 명령이 23505로 실패했다. 이때는 실패한 트랜잭션이 열린 채 남으므로 ROLLBACK;으로 끝낸 뒤 저장된 행을 확인했다. 기본 오류 처리에서 첫 UPDATE가 실패한 경우도 ROLLBACK이 필요하다. COMMIT 자체의 실패로 트랜잭션이 끝난 경우와, COMMIT 전에 문장이 실패한 경우를 구분해야 한다. 이 관찰은 오류마다 자동 저장점을 만드는 클라이언트 설정을 끈 상태에서 확인했다.

확인한 범위는 PostgreSQL 18.6의 단일 연결, 합성 두 행, 일반 text UNIQUE다. 동시 교환의 안전성·잠금 경합, ORM이 보내는 실제 SQL 순서, 참조 관계가 있는 기존 테이블의 제약 변경 절차는 검증하지 않았다. ON CONFLICT 동작도 실행하지 않았다. 공식 문서는 지연 가능한 제약을 ON CONFLICT의 충돌 판정 제약으로 사용할 수 없다고 명시하므로, 교환 성공을 upsert에도 적용할 근거로 삼으면 안 된다. DEFERRABLE의 적용 조건↗ (새 탭에서 열림)