[Postgres] 중복 Sequence 에러

postgres에 데이터를 날려 테스트하던 중 아래와 같은 에러를 직면했습니다.

"msg":"duplicate key value violates unique constraint \"PK_c813b1b88ec7ba92f1d60814ca8\""

그냥 바로 보면 중복된 키가 있다는 뜻인데, 코드 상에는 문제가 없었습니다. sequence 값이 꼬인 것이 원인으로 확인

그래서 postgres에서 sequence 값을 해봤습니다…만 sequence 값의 이름(명명법)을 몰라서 해당 값을 확인을 하는데 한참을 헤맸습니다. 이런 헤매는 과정을 줄이기 위해 postgres의 명명법을 소개합니다.

간단히 form을 지정하자면 아래와 같습니다.

Seq 이름 찾기 공식

* `테이블 이름`_`테이블 id 컬럼 이름`_seq 의 형태를 따릅니다.
* 모든 띄어쓰기는 '_'로 대체합니다.

예를 들어 중복이 발생한 테이블의 이름이 ‘premium_user_sub_info’이고, id값은 ‘sub_info_id’라고 하면 해당 id을 위해 사용되는 sequence의 이름은 아래와 같습니다.

premium_user_sub_info_sub_info_id_seq

어째서 저런 괴상한 이름이지는 알 수 없으나 혹시 헤매는 사람을 위해 기록을 남깁니다.

그리고 제가 저 문제를 직면했을 당시는 premium_user_sub_info에 record가 625개 이상 있으며, id값이 일정하게 쭉 있었지만 DB에 저장된 seq 값은 4였습니다… 아마 DB를 dump할 때 seq 값은 안 옮겨져서 1부터 다시 시작하는게 아닌가 하는 추측..

잠깐, 여기서 Sequence란?

간단히 말하자면, id 생성기입니다. 유일한 값(주로 id)를 생성하게 도와주는 객체이며, 테이블과는 독립적으로 저장되고 생성되기 때문에, 여러 테이블에서 참조될 수도 있습니다. 여러 테이블의 Id 값을 맞추기 위해서 사용할 수도 있겠습니다.

Sequence를 정의하는 방법

CREATE SEQUENCE `seq_name`
	INCREMENT 1
	START 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	CACHE 1;

위와 같이 Sequence의 여러 값들을 지정해서 생성할 수 있습니다. 또한 해당 Sequence에 대한 사용 권한을 계정별로 등록해줘야 사용할 수 있습니다.

-- seq_name의 owner 지정
ALTER SEQUENCE `seq_name` OWNER TO `ownername`;

-- 아래 사용자들에게 모든 권한 제공
GRANT ALL ON SEQUENCE `seq_name` TO `username1`;
GRANT ALL ON SEQUENCE `seq_name` TO `username2`;
...

이후 Sequence를 테이블에 적용하면 됩니다.

CREATE TABLE users
(
    user_id integer NOT NULL DEFAULT nextval('seq_name'::regclass),
    id character varying COLLATE pg_catalog."default",
    pw character varying COLLATE pg_catalog."default",
    CONSTRAINT user_pkey PRIMARY KEY (user_id)
)

이렇게 선언해주면, user_id가 기본키가 되며, seq_name을 갖는 sequence에 따라 자동으로 값이 증가하게 된다.

이렇게 postgres와 조금 더 가까워지기…