PostgreSQL 묶음 upsert가 같은 행을 두 번 갱신할 수 없다고 실패한다

PostgreSQL의 ON CONFLICT DO UPDATE가 단건에서는 성공하다가 여러 행을 한 번에 넣으면 ON CONFLICT DO UPDATE command cannot affect row a second time으로 실패할 수 있다. 이 메시지만 보고 대상 테이블에서 중복 행을 찾으면 원인을 놓친다. 한 INSERT에 들어가는 입력들이 같은 충돌 키를 공유해, 한 대상 행을 다시 쓰려고 하는지 먼저 확인해야 한다.

해결할 때도 어떤 입력을 남길지부터 정한다. 여기서는 계정별 잔액 스냅샷을 저장하며, 버전이 큰 행을 선택하고 같은 버전에서는 순번이 큰 행을 선택한다. 모든 증감 이벤트를 빠짐없이 합산해야 하는 데이터라면 이 선택 규칙을 그대로 적용하면 안 된다.

빈 테이블에서도 같은 오류가 난다

PostgreSQL 18.6의 새 임시 DB에서 합성 행으로 비교했다. 기존 행 하나에 입력 하나를 upsert하면 성공했고, 서로 다른 키 두 개를 한 INSERT에 넣어도 성공했다. 같은 키 두 개를 넣었을 때는 테이블이 비어 있어도 실패했다.

직접 확인하려면 PostgreSQL 서버와 psql이 준비된 실습용 DB에 접속해 아래를 실행한다. CREATE TEMP TABLE로 만든 테이블은 현재 세션에만 존재하므로 뒤의 예제까지 같은 세션을 유지한다. 첫 줄은 의도한 오류 뒤에도 ROLLBACK을 실행하기 위한 psql 설정이다.

\set ON_ERROR_STOP off
CREATE TEMP TABLE account_snapshot (
  account_key text PRIMARY KEY,
  version integer NOT NULL,
  seq integer NOT NULL,
  balance integer NOT NULL
);

BEGIN;
INSERT INTO account_snapshot VALUES
  ('A', 2, 1, 200),
  ('A', 3, 2, 320)
ON CONFLICT (account_key) DO UPDATE
SET version = EXCLUDED.version,
    seq = EXCLUDED.seq,
    balance = EXCLUDED.balance;
ROLLBACK;
SELECT count(*) FROM account_snapshot;

관찰한 오류 메시지의 핵심은 다음과 같았다. SQLSTATE는 21000이었다.

ON CONFLICT DO UPDATE command cannot affect row a second time

ROLLBACK 뒤의 count(*)는 0이었다. 이어서 INSERT INTO account_snapshot VALUES ('A', 1, 0, 100);으로 기존 행을 하나 만든 뒤, 위의 BEGIN부터 다시 실행해도 같은 오류가 났다. 롤백 후 기존 행은 버전 1·순번 0·잔액 100 그대로였다. 첫 번째 입력의 200만 저장되는 결과는 나오지 않았다.

이 비교에서 대상 테이블에는 중복 행이 없었다. account_key가 기본 키이고, 첫 실험에서는 행 자체가 없었다. 두 입력이 같은 A를 쓰려고 한 것이 문제였다. PostgreSQL의 INSERT 문서↗ (새 탭에서 열림)는 한 ON CONFLICT DO UPDATE 명령이 같은 대상 행에 두 번 영향을 주는 것을 허용하지 않으며, 입력들도 충돌 판정에 쓰는 키에서 겹치지 않아야 한다고 설명한다. 오류 코드 목록↗ (새 탭에서 열림)은 21000을 cardinality_violation으로 구분한다.

실패 직후 롤백하지 않고 같은 트랜잭션에서 SELECT를 실행했을 때는 25P02가 나와 조회도 거절됐다. 오류 뒤 상태는 먼저 ROLLBACK으로 실패한 트랜잭션을 끝내고 확인한다. 이 실험의 INSERT가 남긴 변경은 없었지만, 별도 트랜잭션에서 앞서 커밋한 작업까지 취소됐다는 뜻은 아니다. 문장별 암묵적 트랜잭션과 명시적 트랜잭션의 차이는 트랜잭션 문서↗ (새 탭에서 열림)에 설명돼 있다.

중복은 INSERT에 들어가는 행에서 센다

대상 테이블 조회 대신 이번 입력 묶음을 충돌 키로 묶어 본다. 아래 incoming은 외부에서 받은 묶음을 흉내 낸 임시 테이블이다. 예제에서는 버전·순번이 NULL이 아니고, 계정별 같은 버전·순번 조합은 유일하도록 제한했다.

CREATE TEMP TABLE incoming (
  account_key text NOT NULL,
  version integer NOT NULL,
  seq integer NOT NULL,
  balance integer NOT NULL,
  PRIMARY KEY (account_key, version, seq)
);
INSERT INTO incoming VALUES
  ('A', 3, 1, 300),
  ('A', 2, 9, 200),
  ('A', 3, 2, 320),
  ('B', 1, 3, 100);

SELECT account_key, count(*)
FROM incoming
GROUP BY account_key
HAVING count(*) > 1
ORDER BY account_key;

결과는 A 한 행, 개수 3이다. SELECT DISTINCT account_key, version, seq, balance FROM incoming으로 전체 행의 중복을 없애도 실험에서는 4행이 모두 남았다. 키가 같아도 나머지 값이 다르기 때문이다.

실제 코드가 INSERT ... SELECT라면 원본 파일만 확인하지 말고 INSERT에 전달되는 최종 SELECT 결과를 검사한다. 조인 뒤 행이 늘어났을 수도 있다. 복합 충돌 키라면 해당 열들을 함께 묶어야 한다. 이 글은 단순 텍스트 기본 키를 사용했으며, 표현식 인덱스나 트리거로 키가 바뀌는 구성은 별도로 확인해야 한다.

남길 행을 정한 뒤 upsert한다

이 입력에서 A의 승자는 버전 3·순번 2·잔액 320이다. 순번 9인 행은 버전이 2여서 탈락한다. 버전과 순번을 함께 정렬해야 이 업무 규칙을 SQL로 옮길 수 있다.

WITH chosen AS (
  SELECT DISTINCT ON (account_key)
         account_key, version, seq, balance
  FROM incoming
  ORDER BY account_key, version DESC, seq DESC
)
INSERT INTO account_snapshot (account_key, version, seq, balance)
SELECT account_key, version, seq, balance
FROM chosen
ON CONFLICT (account_key) DO UPDATE
SET version = EXCLUDED.version,
    seq = EXCLUDED.seq,
    balance = EXCLUDED.balance;

SELECT account_key, version, seq, balance
FROM account_snapshot
ORDER BY account_key;

빈 테이블에서 실행한 경우와 A의 기존 행이 버전 1인 경우 모두 성공했다. 결과를 열 순서대로 적으면 A | 3 | 2 | 320, B | 1 | 3 | 100이다. 입력 행을 다른 순서로 배치한 별도 비교에서도 같은 두 행이 선택됐다.

DISTINCT ON (account_key)는 키마다 첫 행만 남긴다. 여기서 첫 행을 정하는 부분이 ORDER BY account_key, version DESC, seq DESC다. SELECT 문서↗ (새 탭에서 열림)는 정렬 없이 선택되는 첫 행을 예측할 수 없다고 설명한다. 버전만 정렬하면 버전 3인 두 행 중 누가 이길지 정해지지 않는다. 실제 입력에서 버전·순번까지 같은데 내용이 다를 수 있다면, 안정적인 추가 우선순위를 정하거나 그 입력을 거절해야 한다. 단순히 먼저 읽힌 행을 최신으로 취급하지 않는다.

DO NOTHING으로 바꾸면 기존 행의 갱신을 건너뛰게 되므로 잔액을 최신 스냅샷으로 바꾼다는 요구가 사라진다. 오류를 없애는 방법과 올바른 입력을 고르는 방법을 혼동하지 않아야 한다. 반대로 모든 입력이 필요한 증감 이벤트라면 한 행 선택 대신 합산이나 별도 이벤트 저장이 맞는지 결정해야 한다.

위 쿼리가 비교하는 최신성은 이번 묶음 안에서의 순위다. 대상 테이블에 이미 버전 4가 있으면 무조건 갱신하는 SET은 버전 3으로 되돌릴 수 있다. 오래된 스냅샷을 막아야 한다면 위 INSERT의 대상을 account_snapshot AS dst로 바꾸고, SET 뒤에 WHERE (EXCLUDED.version, EXCLUDED.seq) > (dst.version, dst.seq)를 붙여 기존 행과도 비교한다. 별도 실험에서 이 조건을 적용하자 A의 버전 4·잔액 400이 유지됐다. 이 WHERE는 입력을 한 행으로 고른 뒤 적용하는 비교 조건이다.

실험은 단일 연결과 단순 기본 키를 대상으로 했다. 동시 쓰기, ORM의 배치 재작성, 표현식·부분 유니크 인덱스의 동작은 검증하지 않았다. 입력 중복이 없는데 같은 오류가 계속된다면 실제 전송된 한 INSERT의 행들과 충돌 키 변환을 확인해야 한다. 쿼리를 여러 번으로 나눠 통과시키기 전에, 여러 후보 중 어느 값이 남아야 하는지부터 코드에 드러내는 것이 이 문제의 수정 기준이다.