INSERT 실패 뒤 SELECT도 25P02라면 트랜잭션부터 복구한다

PostgreSQL에서 BEGIN으로 시작한 트랜잭션 안의 UNIQUE 위반 예외를 잡았는데 다음 SELECT도 25P02로 실패한다면, 서버의 트랜잭션 상태부터 확인한다. 예외를 잡아 프로그램 실행을 이어 가는 것만으로는 서버의 실패한 트랜잭션이 복구되지 않는다. 같은 트랜잭션에서 조회를 재시도하기 전에 전체 ROLLBACK 또는 오류 전에 만든 저장점으로의 ROLLBACK TO SAVEPOINT가 필요하다.

먼저 두 오류를 구분한다. 이번 재현에서 첫 INSERT 오류는 23505, 뒤의 SELECT 오류는 25P02였다. PostgreSQL 18 오류 코드 목록↗ (새 탭에서 열림)은 각각 unique_violation, in_failed_sql_transaction으로 구분한다. 25P02를 받은 SELECT만 고치기보다 같은 트랜잭션에서 그보다 먼저 실패한 문장과 SQLSTATE를 찾아야 한다. 연결 예외는 별도의 08 계열로 분류된다. 25P02 자체를 연결 단절의 증거로 삼을 수는 없다.

전체 롤백 전후를 같은 연결에서 비교했다

PostgreSQL 18.6과 같은 버전의 psql을 새 전용 컨테이너에서 실행했다. 실제 애플리케이션 DB 대신 합성 문자열만 넣은 테이블을 사용했다. 아래 입력은 PostgreSQL 서버와 psql이 준비된 새 실습용 DB에서 순서대로 실행한다. tx_probe라는 테이블이 없는 상태로 시작하고, 다음 저장점 예제까지 같은 psql 세션을 유지한다.

처음 네 줄은 psql 설정이다. 문장마다 자동 커밋하는 기본 동작을 명시하고, 의도한 오류 뒤에도 입력을 계속 읽되 자동 저장점 복구는 끈다. 오류 메시지에는 SQLSTATE가 보이게 한다. ON_ERROR_STOP off는 psql이 다음 명령을 읽도록 할 뿐 서버의 실패 상태를 해제하지 않는다. psql 설정 문서↗ (새 탭에서 열림)를 참고할 수 있다.

\set AUTOCOMMIT on
\set ON_ERROR_STOP off
\set ON_ERROR_ROLLBACK off
\set VERBOSITY verbose
CREATE TABLE tx_probe (label text PRIMARY KEY);
INSERT INTO tx_probe VALUES ('committed');

BEGIN;
INSERT INTO tx_probe VALUES ('before_failure');
INSERT INTO tx_probe VALUES ('committed');
SELECT 1;
ROLLBACK;
SELECT 1;
SELECT label FROM tx_probe ORDER BY label;

기본 키도 값의 중복을 막으므로 두 번째 committed 삽입은 고유성 제약을 위반한다. 실제 오류 출력에서 핵심 두 줄을 발췌하면 다음과 같다.

ERROR:  23505: duplicate key value violates unique constraint "tx_probe_pkey"
ERROR:  25P02: current transaction is aborted, commands ignored until end of transaction block

첫 번째 SELECT 1은 테이블을 읽지도 않는데 실패했다. 그 뒤 ROLLBACK은 성공했고, 두 번째 SELECT 1은 1을 반환했다. 재현 로그에서 롤백 전후의 pg_backend_pid()도 같았다. 재접속 없이 같은 연결에서 다시 SQL을 실행한 것이다.

마지막 조회에는 committed만 남았다. 이 행은 BEGIN 전에 이미 커밋됐다. 반면 before_failure는 INSERT 당시 성공했어도 이번 트랜잭션에 속했으므로 취소됐다. ROLLBACK 문서↗ (새 탭에서 열림)가 설명하는 취소 범위는 현재 트랜잭션 전체다. 실패한 INSERT 한 문장만 취소하고 앞선 성공을 보존하는 명령이 아니다.

앞선 쓰기를 남겨야 한다면 오류 전에 저장점을 둔다

한 작업이 실패하면 앞선 변경도 모두 취소해야 하는 업무라면 전체 롤백이 맞다. 반대로 특정 구간을 취소해도 나머지를 커밋해도 되는 업무라면, 그 구간에 들어가기 전에 저장점을 만들 수 있다. SAVEPOINT 문서↗ (새 탭에서 열림)는 저장점을 현재 트랜잭션 안의 복구 지점으로 설명한다.

앞 예제를 마친 같은 세션에는 committed 한 행이 있다. 이어서 다음을 실행했다. after_savepoint는 실패하는 INSERT보다 앞에서 성공하도록 일부러 넣었다. 어느 범위가 취소되는지 보기 위해서다.

BEGIN;
INSERT INTO tx_probe VALUES ('before_savepoint');
SAVEPOINT optional_write;
INSERT INTO tx_probe VALUES ('after_savepoint');
INSERT INTO tx_probe VALUES ('committed');
SELECT 1;
ROLLBACK TO SAVEPOINT optional_write;
SELECT 1;
RELEASE SAVEPOINT optional_write;
INSERT INTO tx_probe VALUES ('after_recovery');
COMMIT;
SELECT label FROM tx_probe ORDER BY label;

중복 INSERT는 다시 23505, 바로 다음 SELECT는 다시 25P02였다. 저장점이 존재한다는 사실만으로 자동 복구되지는 않았다. ROLLBACK TO SAVEPOINT optional_write를 실행한 뒤에야 SELECT 1이 성공했다. 최종 COMMIT 뒤의 조회 결과는 다음 세 행이었다.

after_recovery
before_savepoint
committed

새 연결을 따로 열어 읽어도 같은 세 행이 보였다. 저장점 이전의 before_savepoint와 복구 이후의 after_recovery는 커밋됐고, 저장점 뒤에서 성공했던 after_savepoint는 남지 않았다. ROLLBACK TO는 실패한 문장만 건너뛰는 기능이 아니라 저장점 이후 구간을 되돌리는 기능이다. ROLLBACK TO SAVEPOINT 문서↗ (새 탭에서 열림)가 설명하는 범위와도 일치했다.

이때 저장점 이전 변경은 아직 바깥 트랜잭션 안에 있다. RELEASE SAVEPOINT는 저장점을 없애는 명령이며 독립적인 커밋이 아니다. 나머지 변경을 확정하는 시점은 마지막 COMMIT이다. PostgreSQL의 트랜잭션 설명↗ (새 탭에서 열림)은 전체 롤백 대신 오류 전에 만든 저장점으로 돌아가 실패 상태에서 복구할 수 있다고 설명한다.

실패한 다음에 저장점을 만들면 늦다. 별도 비교에서 첫 UNIQUE 위반 뒤 SAVEPOINT too_late;를 실행했을 때도 25P02였다. 이미 만들어 둔 적절한 저장점이 없다면 전체 롤백으로 현재 트랜잭션을 끝내야 한다.

같은 오류인데 다음 조회가 성공할 때

위 실험을 마친 뒤, BEGIN 없이 다음 두 문장을 각각 실행했다. psql의 AUTOCOMMIT은 계속 on이었다.

INSERT INTO tx_probe VALUES ('committed');
SELECT 1;

INSERT는 23505로 실패했지만 SELECT는 곧바로 1을 반환했다. 앞선 예제와 달리 두 문장이 명시적 트랜잭션 하나에 묶이지 않았기 때문이다. PostgreSQL 문서에 따르면 별도 BEGIN이 없을 때 각 문장은 개별 트랜잭션으로 처리된다. 다만 클라이언트 라이브러리가 자동으로 BEGIN을 보낼 수도 있으므로, 코드에 BEGIN 문자열이 없다는 이유만으로 같은 조건이라고 판단하면 안 된다.

psql에서만 재현이 다르다면 ON_ERROR_ROLLBACK 설정도 확인한다. 앞의 예제에서는 이를 명시적으로 껐다. 공식 psql 문서에 따르면 on일 때는 클라이언트가 문장 앞에 저장점을 만들고 오류 시 그 지점으로 되돌린다. 이 자동 복구 조건은 이번 실험에서 실행하지 않았다.

자기 코드에서는 첫 오류를 보존한 뒤, 실패한 연결의 트랜잭션을 누가 시작하고 끝내는지 확인한다. 작업 전체가 하나의 성공 단위라면 그 API로 전체 롤백하고, 성공했던 앞부분까지 취소된다는 전제로 다시 처리한다. 부분 실패를 허용한다면 트랜잭션 API의 저장점 지원을 확인하고, 취소해도 되는 구간 앞에 경계를 둔다. 실패한 트랜잭션에서 다음 SELECT만 반복해도 이 경계는 바뀌지 않는다.

이번 관찰은 PostgreSQL 18.6의 단일 쓰기 연결에서 직접 보낸 SQL과 별도 연결의 커밋 후 조회에 한정된다. 애플리케이션 언어의 예외 처리, JDBC·ORM의 트랜잭션 관리, 커넥션 풀의 연결 정리, 실제 네트워크 단절은 실행하지 않았다. 해당 환경의 API 상태까지 이 SQL 실험으로 확인했다고 볼 수는 없다.