[Postgresql] auto increment, sequence 처리방법

DB를 개발할때 초반에 고민하는 것중에 하나가 행 입력시 증가하는 일련번호, 시퀀스 번호를 어떻게 동작시킬것인가이다. Postgresql도 여러 방법으로 시퀀스 번호를 동작시키는 메커니즘을 가지고 있다.

 

SEQUENCE 객체 활용

 

전통적인 방법으로 따로 SEQUENCE 객체를 생성하고 테이블에 붙여주는 방법이다.

 

postgresql 기본적인 시퀀스 생성

CREATE SEQUENCE seq_user_id 
	INCREMENT 1 
	START 1 
	MINVALUE 1 
	MAXVALUE 9223372036854775807 
	CACHE 1;

 

시퀀스를 생성한 후에는 ALTER명령으로 속성을 변경할 수도 있다. 아래 링크에서 기능 설명이 되어 있다.

ALTER SEQUENCE [ IF EXISTS ] 이름
    [ AS 자료형 ]
    [ INCREMENT [ BY ] 증가값 ]
    [ MINVALUE 최소값 | NO MINVALUE ] [ MAXVALUE 최대값 | NO MAXVALUE ]
    [ START [ WITH ] 시작값 ]
    [ RESTART [ [ WITH ] 재시작값 ] ]
    [ CACHE 캐시 ] [ [ NO ] CYCLE ]
    [ OWNED BY { 테이블이름.칼럼이름 | NONE } ]
ALTER SEQUENCE [ IF EXISTS ] 이름 OWNER TO { 새소유주 | CURRENT_USER | SESSION_USER }
ALTER SEQUENCE [ IF EXISTS ] 이름 RENAME TO 새이름
ALTER SEQUENCE [ IF EXISTS ] 이름 SET SCHEMA 새스키마

-- https://postgresql.kr/docs/12/sql-altersequence.html

 

시퀀스를 생성하면 information_schema ->sequences 뷰에서 현재 설정된 시퀀스들의 상태를 볼 수 있다. 아래 뷰를 보면 방금 생성한 seq_user_id의 내역이 보인다. 뷰가 어떻게 데이터들을 뽑는지는 sequences 뷰 생성 DDL를 참고하도록 한다.

 

참고로 시퀀스 생성시 CACHE 설정을 저장하는데 시퀀스의 캐쉬 설정은 다음의 의미를 가진다.

Sequence의 Cache는 원하는 숫자 만큼 미리 만들어 Shared Pool의 Library Cache에 상주 시키는 기능이다.

Cache가 저장된 수 만큼 Disk I/O가 발생하지 않는 장점도 있고
반면에 Library Cache가 날아간다면 Sequence Cache도 날아가게 된는 단점도 있다.

DB가 비정상적으로 종료가 되었거나 (PMON이 죽거나, shutdown abort를 사용했을경우)

Library Cache에서 우선순위 경합에 밀려 cache aged out 되는 경우다. 다시말해, 사용 빈도가 적어 메모리에서 삭제되는 경우를 말한다.
(참고: http://kr.forums.oracle.com/forums/thread.jspa?threadID=463133)
 
해결 방법은 2가지다.

Cache를 사용하지 않는 것. 
dbms_shared_pool package를 이용해 Library Cache에 항상 상주하도록 강제설정하는 방법이다. 

출처 : http://blog.naver.com/xacti/80109572891

안정적인 시퀀스 생성을 하고자 한다면 CACHE 1 을 사용한다. (postgresql에서는 1이면 사용안함)

 

시퀀스 사용 권한 계정에 등록

 

만들어진 시퀀스는 계정별로 권한 부여를 할 수 있다. 필요시 권한 설정을 하도록 한다.

-- postgres가 OWNER이고 
ALTER SEQUENCE seq_user_id OWNER TO postgres; 

-- 아래 사람들에게 모든 권한을 준다 
GRANT ALL ON SEQUENCE seq_user_id TO postgres; 
GRANT ALL ON SEQUENCE seq_user_id TO users;

 

시퀀스를 사용해 테이블 생성

CREATE TABLE users ( 
    user_id integer NOT NULL DEFAULT nextval('seq_user_id'::regclass), 
    name varchar(20),
    CONSTRAINT user_pkey PRIMARY KEY (user_id) 
)

 

시퀀스 삭제

DROP SEQUENCE seq_user_id;

 

Insert문으로 데이터 입력

-- 따로 시퀀스 컬럼을 지정하지 않으면 자동 채번되어 들어간다.
insert into users (name) VALUES ('홍길동');

-- 물론 직접 시퀀스 번호를 넣어줄수도 있다. 단 직접 넣었을 경우에는 시퀀스가 바뀌지 않으므로 주의해야 한다.
insert into users (user_id,name) VALUES (10,'홍길동');

 

시퀀스 값 조회, 변경

-- 현재 시퀀스 값 조회 (현재값 100 -> 100 조회)
SELECT currval('seq_user_id')

-- 다음 시퀀스 값 조회 (현재값 100 ->101 조회)
SELECT nextval('seq_user_id')

-- 시퀀스 값 1000으로 변경
SELECT setval('seq_user_id', 1000)

 

SERIAL 자료형

 

위 sequence 개체를 사용하는 경우, 몇 가지 불편한 점이 있다. 미리 시퀀스를 만들어야 하며, 테이블을 만들 때, 긴 칼럼 정의를 해야하고, 테이블을 지울 때, 시퀀스도 따로 지워야 한다. 이런 불편함을 해결 하기 위에서 PostgreSQL에서는 serial 이라는 자료형을 제공하고 PRIMARY KEY 지정해주면 자동증가가 가능하다.

 

CREATE TABLE users (
    user_id serial PRIMARY KEY,
    NAME VARCHAR(20)
)

 

예측 했겠지만 위와 같이 생성을 하면 결국 내부적으로 시퀀스를 생성하고 등록하게 된다. 내부 동작 과정은 결국 시퀀스 객체를 이용한다는 예기다.

 

아래와 같이 serial로 생성시 테이블명 + 컬럼명 기준으로 시퀀스를 자동 생성해 주는것을 볼 수 있다.

 

아래와 같이 자동으로 채번되고 직접 시퀀스를 넣어줄수도 있다.

-- 따로 시퀀스 컬럼을 지정하지 않으면 자동 채번되어 들어간다.
insert into users (name) VALUES ('홍길동');

-- 물론 직접 시퀀스 번호를 넣어줄수도 있다. 단 직접 넣었을 경우에는 시퀀스가 바뀌지 않으므로 주의해야 한다.
insert into users (user_id,name) VALUES (10,'홍길동');

 

그러니 serial 구문은 postgresql특화 기능으로 표준 sql이 아니다. 추후 타 DB로 마이그레이션등을 고려한다면 바람직하지는 않다.

 

GENERATED ALWAYS AS IDENTITY

 

ANSI SQL 2003 규약은 이 일련 번호 처리를 위한 구문으로

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY

Postgres 10부터는 SQL 표준에 정의 된 자격 증명 열도 지원합니다.

 

always 를 사용하여 테이블을 생성하였다. insert 구문에서 직접 id 필드에 값을 할당할 경우 오류가 발생됩니다.

create table users 
(
  user_id integer generated always as identity,
  NAME VARCHAR(20)
);

-- insert 성공
insert into users (name) VALUES ('홍길동');

-- 직접 시퀀스 입력 시 에러 발생
insert into users (user_id,name) VALUES (10,'홍길동');

 

GENERATED DEFAULT AS IDENTITY

default 를 사용한 예제. serial 과 동일한 작동을 하게된다. id 값을 강제로 입력하여 저장도 가능합니다.

 

create table users 
(
  user_id integer generated by default as identity,
  NAME VARCHAR(20)
);

-- 자동채번, 직접 시퀀스 입력 모두 가능함
insert into users (name) VALUES ('홍길동');
insert into users (user_id,name) VALUES (10,'홍길동');

 

-- The End --