데이터 이관 후 serial/identity 시퀀스 충돌을 setval로 복구

문제 상황

다른 DB에서 데이터를 이관하며 id를 명시적으로 INSERT했다. 이후 앱이 INSERT하자 duplicate key value violates unique constraint. 시퀀스가 이관된 최대 id보다 낮은 값을 발급하기 때문이다.

전제

• PostgreSQL 16, psql
• 실습은 TEMP TABLE만 사용(세션 종료 시 사라짐, 운영 무영향)

재현

CREATE TEMP TABLE t (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text
);
INSERT INTO t (name) VALUES ('a');            -- id=1
INSERT INTO t (id, name) VALUES (100, 'x');   -- 명시적 id=100 (이관 흉내)
INSERT INTO t (name) VALUES ('b');            -- 시퀀스는 아직 2를 발급 시도

마지막 줄에서 id=2가 들어가고, 계속 발급하면 언젠가 100과 충돌한다. 시퀀스가 명시적 삽입을 인지하지 못한 것이다.

진단

연결된 시퀀스 이름 조회:

SELECT pg_get_serial_sequence('t', 'id');

현재 시퀀스가 발급할 다음 값이 테이블 max(id)보다 낮은지 비교한다.

진단하려고 nextval을 호출하면 값 하나를 실제로 소비한다. 번호를 확인만 하려는 경우에도 부작용을 구분한다. 이 예제는 기본 시작값 1·증가량 1인 identity이며, 감소 시퀀스나 별도 시작값은 계산이 다르다.

실습에서 충돌도 확인하려면 임시 테이블의 시퀀스를 다음처럼 조정한다. 운영 시퀀스에는 실행하지 않는다.

SELECT setval(pg_get_serial_sequence('t','id'), 100, false);
INSERT INTO t (name) VALUES ('collision'); -- id=100 중복키 오류 예상

psql 기본 자동 커밋 상태를 전제로 한다. 명시적인 트랜잭션 안에서 오류가 났다면 복구 명령 전에 ROLLBACK으로 실패 상태를 끝낸다.

복구

SELECT setval(
  pg_get_serial_sequence('t','id'),
  (SELECT max(id) FROM t),
  true          -- is_called=true → 다음 nextval은 max+1
);

• 세 번째 인자 true: "이 값은 이미 사용됨" → 다음은 max+1.
• 테이블이 비어 max가 NULL이면 이 방식은 못 쓴다. 빈 테이블은 setval(seq, start, false)처럼 start 값과 false(아직 미사용)를 써야 첫 nextval이 start가 된다.

복구 후에는 새 삽입으로 결과를 검증한다.

INSERT INTO t (name) VALUES ('after-repair') RETURNING id;
-- 위 실습 흐름에서는 101
SELECT count(*), max(id) FROM t;
-- 오류 난 행은 없으므로 count=4, max=101

빈 테이블 처리는 이 예제의 시작값 1을 전제로 setval(seq, 1, false)다. 데이터가 있을 때만 max를 사용하고, 기존 시퀀스가 이미 더 앞서 있다면 이유 없이 낮추지 않는다. 실습에 성공했다고 운영 시퀀스를 일괄 조정해서는 안 된다.

운영에서의 주의

• setval과 nextval은 시퀀스 자체를 바꾸며 트랜잭션 ROLLBACK으로도 되돌아가지 않는다. 실습 후 원상복구를 ROLLBACK에 기대지 말 것.
• 운영에서는 시퀀스 cache와 동시 writer 때문에 단순 max(id)만 보고 setval하면 경합으로 다시 충돌할 수 있다. → 이관 중에는 쓰기 중지, 남은 세션 정리, 조정 전후 값 기록 후 진행한다.
• 시퀀스는 gapless를 보장하지 않는다. 롤백/캐시로 번호에 구멍이 생기는 건 정상이며, 연속 번호가 필요한 회계 등에는 부적합하다.

되돌리기 / 한계

• 실습은 TEMP TABLE이라 세션을 끊으면 전부 사라진다.
• 위 값(id=100 등)은 모두 가상 예시다. 운영 스키마명·시퀀스명은 반드시 실제 확인 후 적용한다.

출처

• sequence functions
• ALTER SEQUENCE