PostgreSQL 시퀀스 값이 밀려 기본 키 중복 오류가 날 때 확인 방법

PostgreSQL에서 기본값으로 ID를 생성하는 INSERT가 중복 키 오류를 내면, 테이블의 최대 ID와 ID용 시퀀스가 어긋났는지 확인하세요. 다만 오류 코드 23505만으로 시퀀스가 원인이라고 단정할 수는 없습니다. 충돌한 제약조건·열, 애플리케이션이 명시적으로 보낸 ID, 데이터 이관 이력을 먼저 구분한 뒤, 쓰기를 멈출 수 있는 시간에만 시퀀스 조정을 검토해야 합니다.

충돌이 난 열부터 확인하기

예를 들어 public.orders의 id가 자동 생성 열이라고 가정합니다. 오류의 DETAIL이 ‘Key (id)=(...) already exists’라고 보여도, INSERT가 id 값을 직접 전달했다면 시퀀스를 바꿔도 같은 입력은 다시 실패합니다. 에러가 orders_pkey가 아닌 이메일 등 다른 유니크 제약조건을 가리키는 경우에도 이 절차의 대상이 아닙니다. 실패한 SQL에서 id 열이 빠져 있는지, ORM이나 이관 스크립트가 명시적으로 채우지 않았는지 함께 확인하세요.

백필이나 파일 이관에서 큰 ID를 직접 넣어도 시퀀스는 그 값에 맞춰 자동으로 움직이지 않습니다. 반대로 ID 사이에 빈 번호가 있다는 사실만으로 고장을 뜻하지는 않습니다. PostgreSQL의 nextval은 트랜잭션이 취소되어도 이미 받은 값을 재사용하지 않습니다. 번호의 연속성과 기본 키의 유일성은 서로 다른 요구사항입니다.

값을 바꾸지 않고 상태 확인하기

다음은 PostgreSQL 18 기준 읽기 전용 점검 예시입니다. public.orders와 id를 실제 스키마·열로 바꿔 실행하세요. pg_get_serial_sequence는 serial뿐 아니라 identity 열에 연관된 시퀀스 이름도 찾으며, 연결된 시퀀스가 없으면 NULL을 반환합니다. MAX(id)는 실제 테이블 값이고, pg_sequences의 last_value는 디스크에 기록된 시퀀스 값입니다.

SELECT pg_get_serial_sequence('public.orders', 'id') AS seq_name;
SELECT MAX(id) AS max_id, COUNT(*) AS row_count FROM public.orders;
SELECT schemaname, sequencename, increment_by, cache_size, last_value
FROM pg_catalog.pg_sequences
WHERE (schemaname || '.' || sequencename) =
      pg_get_serial_sequence('public.orders', 'id');

시퀀스 이름이 NULL이면 열 기본값이나 identity 정의부터 재확인해야 합니다. 결과 행이 없으면 시퀀스 소유·스키마, 조회 권한을 확인하세요. last_value가 NULL이라고 해서 곧바로 0이라는 뜻도 아닙니다. 아직 사용하지 않았거나 해당 시퀀스의 조회 권한이 없을 수 있습니다. 캐시가 켜져 있다면 last_value는 마지막으로 발급한 번호보다 클 수도 있습니다. 숫자 두 개만 비교해 즉시 수정하지 말고, 증가 방향·캐시·동시 쓰기 여부를 함께 판단하세요.

재설정 전 반드시 결정할 것

• 운영 쓰기와 이관 작업을 중지하고 이미 열린 연결의 동작을 조정할 수 있는지 정합니다. 동시에 INSERT가 진행되면 조회한 MAX(id)가 곧 낡은 값이 됩니다.
• 시퀀스가 양수로 증가하고 순환하지 않는지, 해당 열의 기본값이 실제로 이 시퀀스를 호출하는지 확인합니다. 감소형·순환형·공유 시퀀스에는 MAX(id)를 그대로 적용할 수 없습니다.
• 기존 테이블에 행이 있는지, 시퀀스를 조정할 권한과 작업 후 점검 방법이 있는지 기록합니다. 비어 있는 테이블에는 MAX(id)가 NULL이므로 별도 시작값 판단이 필요합니다.

행이 있고 기본 증가폭이 1인 단순한 시퀀스라면 PostgreSQL 문서의 setval(seq, value, true)는 현재값을 value로 두고 다음 nextval에서 증가시킵니다. 따라서 쓰기가 정지된 상태에서 테이블의 최댓값을 기준으로 재설정하는 방식이 가능합니다. 하지만 캐시된 번호를 다른 연결이 보유했거나 더 큰 값을 다른 시스템이 예약했다면 MAX(id)만으로 안전을 보장할 수 없습니다. 이 글은 운영 시퀀스를 변경하는 SQL을 실행하지 않았고, 서버에서는 setval의 인자 형식만 PREPARE로 문법 확인했습니다. setval의 변경은 ROLLBACK으로 되돌아가지 않으므로 ‘트랜잭션 안에서 해보고 취소’는 안전한 시험 방법이 아닙니다.

조정 뒤에는 기본값을 사용하는 INSERT 경로의 오류가 사라졌는지 애플리케이션 로그에서 확인하고, 명시적 ID를 넣는 이관 코드가 계속 실행 중이지 않은지도 점검하세요. 단순히 nextval을 호출해 시험하면 번호가 소비되므로, 읽기 전용 확인 단계에서는 호출하지 않습니다. 개발·운영 환경에서 쓰기 중지 범위가 다르다면 재설정 절차도 분리해야 합니다.

확인 자료

• PostgreSQL 18 문서: 시퀀스 함수와 롤백 주의사항, pg_get_serial_sequence, pg_sequences 열 설명.

서비스 범위는 소프트모아 홈페이지에서 확인할 수 있습니다.

소프트모아는 해당 시스템을 구축합니다. 문의하기