PostgreSQL UPDATE FROM의 중복 매칭은 입력 순서로 해결되지 않는다

PostgreSQL의 UPDATE ... FROM에서 source의 두 행이 같은 대상 행에 붙어도 쿼리는 성공할 수 있다. 이때 먼저 넣은 값과 마지막 값 중 무엇이 저장될지 입력 순서로 판단하면 안 된다. 갱신할 대상 한 행에 조인 결과가 최대 한 행만 붙도록 고쳐야 한다.

PostgreSQL 18의 UPDATE 문서↗ (새 탭에서 열림)는 여러 행이 매칭되면 그중 하나를 사용하지만 어떤 행인지는 쉽게 예측할 수 없다고 설명한다. 매번 무작위로 바뀐다는 뜻은 아니다. 몇 번 같은 값이 나와도 그 선택을 업무 규칙으로 삼을 수 없다는 뜻이다.

갱신 전에 대상 키별로 조인 행을 센다

수량 상세 두 줄을 품목별 수량으로 옮기는 예를 보자. 1번 품목에는 10과 30, 2번에는 7이 있다. 각 상세가 별도 수량이므로 이 예제에서 저장하려는 값은 1번의 합계 40과 2번의 7이다. 상세가 없는 3번은 기존 값 99를 유지하기로 한다.

아래 입력은 PostgreSQL 실습용 DB에 접속한 psql에서 실행한다. 새 세션에서 시작하고 뒤의 예제까지 같은 연결을 유지한다. target과 source는 이 세션에만 존재하는 임시 테이블이다.

CREATE TEMP TABLE target (
  id integer PRIMARY KEY,
  quantity bigint
);
CREATE TEMP TABLE source (
  line_no integer PRIMARY KEY,
  target_id integer NOT NULL,
  quantity integer NOT NULL
);
INSERT INTO target VALUES (1, 0), (2, 0), (3, 99);
INSERT INTO source VALUES (1, 1, 10), (2, 1, 30), (3, 2, 7);

SELECT t.id, count(*) AS matches
FROM target AS t
JOIN source AS s ON s.target_id = t.id
GROUP BY t.id
HAVING count(*) > 1;

전용 컨테이너의 PostgreSQL 18.6에서 실행한 결과는 id=1, matches=2 한 행이었다. 전체 조인 후보는 3행이었고, 그중 두 행이 같은 대상 1번을 가리켰다. 이 검사는 갱신할 대상의 기본 키 t.id로 조인 결과를 묶는다. source.line_no가 기본 키라는 사실은 source.target_id의 중복을 막아 주지 않는다. 대상 테이블에 중복 행이 없다는 확인만으로도 부족하다.

자기 쿼리에 적용할 때는 UPDATE에 쓰는 모든 조인과 필터를 같은 SELECT에 옮긴 뒤 대상 키별로 센다. 실제 대상 키가 복합 키라면 그 열들을 함께 묶는다. 중복이 나오면 갱신 전에 어떤 입력들을 합칠지, 어느 입력을 남길지, 잘못된 입력으로 거절할지를 정한다. 중복이 없는데 값이 틀리다면 다음으로 SET에 지정한 열과 값을 만드는 식을 확인한다.

RETURNING의 한 값은 선택 규칙이 아니다

다음 문장은 합계를 계산하지 않는다. 조인된 source 행의 수량을 그대로 대입한다. RETURNING에는 갱신 후 수량과 실제 사용된 상세 번호를 함께 적었다.

UPDATE target AS t
SET quantity = s.quantity
FROM source AS s
WHERE t.id = s.target_id
RETURNING t.id, s.line_no AS used_line, t.quantity;

위 입력 그대로 실행했을 때의 실제 출력은 다음과 같았다. 1번에는 상세 1번의 10이 저장됐다. 마지막에 넣은 상세 2번의 30으로 덮어쓰는 결과가 아니었다.

 id | used_line | quantity
----+-----------+----------
  1 |         1 |       10
  2 |         3 |        7
(2 rows)

UPDATE 2

상세 행들을 역순으로 넣어 따로 실행하자 1번에는 상세 2번의 30이 저장됐다. 이 두 번의 선택을 근거로 먼저 넣은 행이 항상 선택된다고 일반화해서도 안 된다. 자신의 실행에서 30이 나와도 중복 문제가 해결됐다는 뜻은 아니다. 실행 계획별 선택 규칙을 검증한 실험이 아니며, 어느 후보를 쓸지 의존하지 말라는 판단은 공식 문서에 근거한다.

UPDATE 2는 갱신한 대상이 두 행이라는 뜻이다. 앞서 센 조인 후보 3행이나 1번의 중복 매칭을 이 숫자만으로 알 수는 없다. 후보 자체를 대상마다 한 행으로 줄여야 한다.

모든 상세가 필요하면 먼저 합계를 만든다

이 예제의 업무 규칙은 두 상세의 합산이다. GROUP BY target_id로 대상마다 한 행을 만든 뒤 그 결과와 조인한다.

WITH one_per_target AS (
  SELECT target_id, sum(quantity) AS quantity
  FROM source
  GROUP BY target_id
)
UPDATE target AS t
SET quantity = s.quantity
FROM one_per_target AS s
WHERE t.id = s.target_id
RETURNING t.id, t.quantity;

이 쿼리의 RETURNING은 1번의 40과 2번의 7을 반환했다. 별도 SELECT로 확인한 3번은 99 그대로였다. source 입력 순서를 뒤집은 조건에서도 같은 세 값이 저장됐다.

GROUP BY 문서↗ (새 탭에서 열림)에서 설명하는 그룹 행이 여기서는 1번과 2번에 각각 하나씩 만들어진다. SUM 문서↗ (새 탭에서 열림)에 따라 integer의 합계는 bigint이므로 대상 열도 그 타입으로 두었다. 이 문장은 기존 수량에 더하는 것이 아니라 상세 합계로 교체한다. source가 없는 3번은 조인되지 않아 갱신 대상에서 빠진다.

합산이 맞는 데이터에만 이 수정을 적용한다. source가 이미 계산된 최종 수량의 여러 버전이라면 더해서는 안 된다. 반대로 원래 대상마다 source가 정확히 하나여야 한다면 source의 실제 조인 키에 UNIQUE 또는 기본 키를 두어 중복을 거절하는 편이 맞다. 이 예제에서는 그 키가 target_id다. 다른 테이블을 추가로 조인하면 다시 행이 늘 수 있으므로 최종 조인 결과도 확인해야 한다.

스칼라 하위쿼리는 여러 행이면 오류를 낸다

같은 source를 다음처럼 한 값으로 대입하면 동작이 달라진다. 아래 블록의 오류는 의도한 비교다. psql이 오류 뒤에도 ROLLBACK까지 처리하도록 설정하고 트랜잭션으로 감쌌다. 앞의 임시 테이블을 만든 같은 세션에서 실행한다.

\set ON_ERROR_STOP off
BEGIN;
UPDATE target AS t
SET quantity = (
  SELECT s.quantity
  FROM source AS s
  WHERE s.target_id = t.id
);
ROLLBACK;
\set ON_ERROR_STOP on

실행에서는 more than one row returned by a subquery used as an expression 오류와 SQLSTATE 21000을 확인했다. 롤백 뒤 앞의 합계 40·7과 미매칭 값 99는 유지됐다. 이 경우는 여러 후보 중 한 행을 사용하는 FROM 조인과 달리, 여러 행을 한 값으로 대입하려다 실패한 것이다.

스칼라 하위쿼리 문서↗ (새 탭에서 열림)는 여러 행을 한 값으로 사용할 수 없다고 명시한다. 따라서 중복을 조용히 통과시키지 않는 형태로 쓸 수 있다. 다만 일치하는 행이 없을 때는 NULL을 대입한다. 별도 비교에서 2·3번만 대상으로 이 하위쿼리를 실행하자 2번은 7, source가 없는 3번은 NULL이 됐다. 미매칭 대상을 그대로 두는 UPDATE ... FROM과 의미가 같지 않으므로 무조건 치환하면 안 된다.

실험은 합성 정수 데이터와 단일 연결에서 수행했다. 동시 변경이나 실행 계획별 후보 선택은 검증하지 않았다. 자신의 갱신 쿼리에서는 조인 중복을 센 뒤, 정한 업무 규칙으로 대상마다 한 행이 남는지와 RETURNING의 실제 저장값을 함께 확인한다.