PostgreSQL 이관 뒤 자동 INSERT가 중복 키로 실패하면 시퀀스를 확인한다

PostgreSQL 테이블에 기존 데이터를 옮긴 뒤, id를 생략한 새 INSERT가 이미 있는 ID와 충돌할 수 있다. bigserial 열에 ID를 직접 넣으면 그 값은 저장되지만, 기본값을 만드는 시퀀스의 위치까지 따라 올라가지는 않는다. 테이블에는 ID 42가 있어도 시퀀스는 여전히 1을 발급할 수 있다.

이때 확인할 것은 그 열에서 실제로 번호를 만드는 경로와 시퀀스 상태다. max(id)부터 시퀀스에 덮어쓰면 안 된다. 이미 앞서 있는 번호를 되돌리거나, 동시에 실행 중인 INSERT가 받은 번호를 다시 발급할 수 있기 때문이다.

명시적 ID는 들어갔지만 다음 자동 ID는 1이었다

아래는 실제 서비스 장애 기록이 아니라 새 PostgreSQL 18.6 컨테이너와 전용 DB에서 실행한 실험이다. 외부 포트는 열지 않았고 기본 설정인 시작값 1, 증가폭 1, 최솟값 1, 비순환, 캐시 크기 1을 사용했다. 비교용 새 테이블에서는 ID를 생략한 두 INSERT가 1과 2를 정상 발급했다.

직접 확인하려면 PostgreSQL과 psql을 준비하고 기존 데이터가 없는 새 연습 DB에 접속한다. 아래 이름의 테이블이 없는 상태에서 SQL을 문장 단위로 실행한다. 마지막 INSERT의 오류는 의도한 결과다. 트랜잭션으로 묶지 않고 기본 자동 커밋 상태에서 비교했다.

CREATE TABLE public.seq_demo (
  id bigserial PRIMARY KEY,
  note text NOT NULL
);
INSERT INTO public.seq_demo(id, note)
VALUES (1, 'import-a'), (7, 'import-b'), (42, 'import-c');
SELECT max(id) FROM public.seq_demo;
SELECT last_value, is_called FROM public.seq_demo_id_seq;
INSERT INTO public.seq_demo(note) VALUES ('new') RETURNING id;

max(id)는 42였지만 자동 INSERT 직전의 시퀀스 조회 결과는 다음과 같았다. 출력의 f는 false다.

last_value|is_called
1|f

is_called=false인 상태에서는 다음 번호로 last_value 자체를 반환한다. 따라서 새 행의 ID는 1이 됐고, 아래 오류가 발생했다. 오류 원문에서 코드·제약 이름·상세만 발췌했다.

ERROR:  23505: duplicate key value violates unique constraint "seq_demo_pkey"
DETAIL:  Key (id)=(1) already exists.

실패 뒤에도 이관한 세 행은 그대로였지만 시퀀스 상태는 1|t로 바뀌었다. INSERT에 실패했다고 번호 발급까지 취소되지는 않았다. 계속 재시도하면 다음 번호로 넘어가 일부 요청이 성공할 수도 있으므로, 한 번 성공했다는 이유로 이관 후 상태가 정상이라고 판단할 수 없다.

serial 형식 문서↗ (새 탭에서 열림)는 bigserial 같은 선언이 열·시퀀스·기본값을 만드는 방식임을 설명한다. ID를 생략하거나 DEFAULT를 지정하면 시퀀스를 호출하는 기본값이 쓰인다. 위 이관 INSERT는 ID를 직접 지정해서 그 기본값을 사용하지 않았다.

연결된 시퀀스와 실제 기본값을 함께 확인한다

실제 문제를 조사할 때는 아래의 스키마·테이블·열 이름을 자신의 대상에 맞춘다. 테이블 이름 뒤에 _id_seq를 붙여 시퀀스 이름을 추측하지 않는다.

SELECT column_default, is_identity, identity_generation
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'seq_demo' AND column_name = 'id';
SELECT pg_get_serial_sequence('public.seq_demo', 'id');

실험의 첫 조회는 column_default에 nextval('seq_demo_id_seq'::regclass), is_identity에 NO, identity_generation에 NULL을 반환했다. 두 번째 조회는 public.seq_demo_id_seq였다. 기본값이 호출하는 시퀀스와 열에 연결된 시퀀스가 같은 것을 확인한 뒤, 앞의 last_value, is_called 조회로 상태를 읽었다. currval()은 현재 연결에서 마지막으로 발급받은 값이므로 전체 상태 확인을 대신하지 못한다. 확인용으로 nextval()을 부르면 번호가 실제로 진행된다. 이 차이는 시퀀스 함수 문서↗ (새 탭에서 열림)에 정의돼 있다.

여기서 조회 결과가 다르면 먼저 생성 경로를 구분한다.

• is_identity=YES라면 identity 열이다. 별도 실험의 GENERATED BY DEFAULT AS IDENTITY 열은 column_default가 NULL이었지만 연결 시퀀스가 있었고, 명시적 ID 이관 후 같은 충돌이 났다. 기본값이 NULL이라는 이유만으로 자동 발급이 없다고 판단하면 안 된다. columns 정보↗ (새 탭에서 열림)와 identity 열 설명↗ (새 탭에서 열림)을 함께 본다. ALWAYS 열은 명시적 ID 입력 조건도 다르다.
• pg_get_serial_sequence()가 NULL이면 열과의 연결 관계가 없다는 뜻이다. 직접 만든 시퀀스를 기본값에서 호출하되 소유 관계를 연결하지 않은 합성 열에서도 이 함수는 NULL을 반환했다. 이 함수는 임의의 기본값 식을 분석해서 모든 시퀀스를 찾아 주지 않는다. 함수의 연결 관계 정의↗ (새 탭에서 열림)를 확인하고 실제 기본값을 읽는다.
• 기본값·identity가 번호를 만들지 않는다면 트리거나 애플리케이션이 ID를 넣는지 확인한다. 오류의 충돌 열이 정말 id인지도 본다. 다른 유니크 열의 중복은 이 시퀀스를 바꿔도 해결되지 않는다.

시퀀스 이름을 확인했으면 발급 조건도 읽는다. 아래는 앞에서 확인한 이름을 넣은 조회다.

SELECT start_value, min_value, increment_by, cycle, cache_size
FROM pg_sequences
WHERE schemaname = 'public' AND sequencename = 'seq_demo_id_seq';

실험 출력은 열 순서대로 1|1|1|f|1이었다. pg_sequences 문서↗ (새 탭에서 열림)의 last_value는 캐시를 쓰면 이미 예약한 값까지 반영할 수 있다. 그 숫자 하나만 보고 모든 연결의 다음 ID를 단정하지 않는다. 조회가 권한 오류로 실패하거나 대상 열 자체가 안 보이면, 값을 추측해 재설정하지 말고 대상과 조회 권한부터 확인한다.

뒤처진 새 이관 대상에서만 위치를 맞춘다

다음 수정은 다른 쓰기와 번호 발급이 없고, 시퀀스가 실제로 뒤처진 것을 확인한 대상에 한정한다. 실험의 ID는 모두 1 이상의 정수이고 시퀀스 증가폭은 1이다. 운영 중에는 앱·배치·이관 도구의 INSERT와 직접 nextval()을 호출하는 경로까지 멈추고 진행 중 작업을 정리할 수 있는 구간을 먼저 확보해야 한다.

또한 기존 연결이 캐시해 둔 번호와 애플리케이션이 미리 확보한 ID가 남아 있지 않은지 확인해야 한다. CREATE SEQUENCE의 캐시 설명↗ (새 탭에서 열림)에 따르면 다른 연결은 미리 받아 둔 번호를 소진하기 전까지 setval 변경을 알아채지 못할 수 있다. 테이블 잠금 하나로 모든 번호 발급 경로를 통제했다고 간주하면 안 된다.

실험의 세 행을 유지한 채 다음을 실행했다.

SELECT setval(pg_get_serial_sequence('public.seq_demo', 'id'), max(id), true)
FROM public.seq_demo;
INSERT INTO public.seq_demo(note) VALUES ('after-reset') RETURNING id;

setval 반환값은 42였고, 이어진 INSERT의 RETURNING id는 43이었다. 세 번째 인자인 true는 42를 이미 사용한 상태로 놓는다는 뜻이다. 두 인자만 쓰는 setval(시퀀스, 42)도 같은 의미다. 반환된 42를 “다음 INSERT가 받을 값”으로 읽으면 안 된다. 실제 수정에는 해당 시퀀스의 UPDATE 권한이 필요하다.

반대로 setval('public.seq_demo_id_seq', 42, false)를 먼저 실행한 비교에서는 상태가 42|f가 됐고, 다음 INSERT가 ID 42와 충돌했다. false는 설정한 값을 다음에 그대로 내보낸다. 이미 42가 있는 테이블에서 max(id)와 false를 조합하면 같은 키를 다시 내놓는 셈이다.

빈 테이블은 별도로 다뤄야 한다. 새 empty_demo에서는 max(id)가 NULL이었다. 이를 0으로 바꿔 setval에 넣자 최솟값 1보다 작아 22003: setval: value 0 is out of bounds로 실패했다. 이 실험처럼 시작값·최솟값·증가폭이 모두 1인 새 대상에서는 다음처럼 빈 경우만 1, false로 나눌 수 있다.

CREATE TABLE public.empty_demo (id bigserial PRIMARY KEY, note text);
SELECT setval(
  pg_get_serial_sequence('public.empty_demo', 'id'),
  COALESCE(max(id), 1),
  max(id) IS NOT NULL
)
FROM public.empty_demo;
SELECT last_value, is_called FROM public.empty_demo_id_seq;
INSERT INTO public.empty_demo(note) VALUES ('first') RETURNING id;

상태 조회는 1|f, 첫 자동 INSERT는 ID 1이었다. 같은 수식에 ID 42가 있는 별도 테이블을 넣었을 때는 42, true가 적용돼 다음 ID가 43이었다. 이 수식은 빈 테이블과 양의 ID가 있는 새 이관 대상을 나눈 예다. 시작값·범위·증가폭이 다른 시퀀스에 그대로 쓰거나, 사용 중이던 테이블이 비었다는 이유만으로 번호 재사용을 허용하는 규칙으로 삼지 않는다.

테이블 최댓값과 시퀀스가 다르다고 늘 수정할 필요도 없다. 별도 비교에서는 테이블의 최댓값이 42, 시퀀스 상태가 100|t였고 다음 자동 ID는 101로 정상 입력됐다. 이미 앞선 시퀀스를 42로 내릴 이유는 없었다. 동시 INSERT가 있다면 max(id)를 읽은 뒤 재설정하기 전에도 새 행과 번호가 생길 수 있다. 그런 상태에서 이 단일 연결 실험의 SQL을 온라인 복구 절차로 사용하지 않는다.

ROLLBACK해도 번호는 돌아오지 않았다

새 테이블에서 ID 1을 자동 발급받은 INSERT를 롤백하자 행 수는 0이 됐다. 그러나 다음 INSERT의 ID는 2였다. 이어서 트랜잭션 안에서 setval(시퀀스, 80, true)를 실행하고 롤백해도 시퀀스 상태는 80|t로 남았고, 다음 INSERT는 81을 받았다.

시퀀스 함수의 주의 사항↗ (새 탭에서 열림)은 nextval로 받은 번호와 setval의 상태 변경이 트랜잭션 롤백으로 되돌아가지 않는다고 설명한다. 따라서 BEGIN으로 감싼 뒤 되돌려 보겠다는 방식으로 실제 대상의 재설정을 시험하지 않는다. 충돌을 확인하려고 던진 INSERT도 번호를 소비할 수 있다.

직접 확인한 범위는 PostgreSQL 18.6의 합성 테이블, 양의 ID, 기본 시퀀스 설정과 identity의 BY DEFAULT 방식이다. 동시 쓰기를 허용한 재설정, 여러 연결의 캐시 소진, 트리거·ORM의 별도 ID 할당, 서버 비정상 종료 뒤 복구는 실험하지 않았다. 이관 후에는 실제 생성 경로가 같은지 확인하고, 쓰기가 없는 구간에서 뒤처진 시퀀스만 조정한 뒤 ID를 생략한 INSERT 결과를 확인한다.