서비스 개편을 하면서 디비 스키마를 새로 바꿨어야 했다.
(바꿨다라기 보다 그냥 새롭게 만들었다 가 더 맞는 표현인 것 같다.)
새롭게 설계를 할 때 중점으로 생각한 것들은 다음과 같다.
- 어느정도 정규화 규칙을 지켜야 한다.
- 관계 테이블을 만들자.
- 기존 스키마를 고려하여 마이그레이션 할 수 있어야 한다.
디비를 설계 하면서 동시에 API 개발을 하려고 하니 인력 분배하는 것이 중요했다.
나는 디비 스키마 설계 및 stored procedure, user-defined-function (편하게 sp 및 fn 이라고 말하겠다.) 을 개발하는 일종의 DBA 역할을 담당하게 되었다.
회사에서 디비엔진으로 postgresql을 사용하고 있었는데, 개편 전에는 한번도 procedure 를 사용하지 않았고, 전부 orm 및 raw query 를 사용했었다.
둘다 써본 입장에서 두가지의 장점 및 단점을 정리하자면...
orm 의 장점 및 단점
장점
1. 코드를 빠르게 작성할 수 있다 → 생산성을 높일 수 있다.
2. 디버깅을 하기 쉽다.
3. 소스코드의 가독성이 좋다.
단점
1. 테이블간의 관계를 정확하게 맺어줘야 한다 → 스키마를 설계할 때는 물론이고, 소스코드 상에서도 @OneToMany 및 @ManyToOne 를 정확하게 선언해줘야 사용하기 좋다.
2. 소스코드에서도 스키마의 변화를 관리해줘야 하고, 디비 서버에서도 관리를 해줘야 한다. 두 환경에서의 싱크가 맞지 않는 경우, 골치 아픈 상황이 발생한다.
3. 서비스 함수 혹은 어떠한 로직을 짤 때, 디비에 접근하는 횟수가 너무 많다.
→ 디비 네트워크 비용이 너무 많다.
sp 및 fn 의 장점 및 단점
장점
1. 스키마 변화의 관리포인트가 한 곳이다. 디비서버에서만 관리하면 돼.
→ 소스코드에서는 스키마가 어떻게 생겼고, 어떤 변화가 있었는지에 대해 전혀 알 필요가 없다.
2. 서비스 함수 혹은 어떠한 로직을 짤 때, 디비에 한번만 접근하면 된다.
→ 필요한 테이블들을 하나의 sp 혹은 fn에서 한번에 접근할 수 있다.
→ 디비 네트워크 비용이 현저히 낮다.
3. API 개발자는 온전히 어플리케이션 개발에만 신경 쓸 수 있다.
4. 로직이 변경하는 경우, 서버 배포 없이 sp, fn만 수정하면 된다.
→ 가장 큰 장점이라고 생각된다.
5. transaction 관리가 수월해진다.
→ sp안에서 핸들링 가능
단점
1. 생산성이 떨어진다.
2. sp, fn 의 가독성이 떨어진다.
3. 디버깅 하기 어렵다. raise 이 유일하다.
4. 전문 DBA 가 없는 회사에서는 운용하기 쉽지 않다.
이 정도인 것 같다.
지금 우리회사에는 전문 DBA가 없어서, procedure 을 사용하기 위해 내가 담당하고 공부했었는데, 생각보다 어려워서 애를 먹었다.
그래도 지금은 어느정도는 사용한다고 생각해서 fn, sp에 대한 설명 및 예시를 기록하려고 한다.
fn, sp 차이
procedure 와 function 간의 차이는 다음과 같다.
| 기준 | function | procedure |
|---|---|---|
| transaction(commit, rollback, etc...) 처리 | 불가능 | 가능 |
| return type | value, record, set, etc... | inout parameter 사용 필요 |
| parameter | in, out, inout | inout(14 버전부터 out 가능) |
| compile time | 실행할 때 마다 | 처음에만 한번 |
| execute command | select, perform | call |
transaction 관리
create procedure()
$
begin
// A transaction
begin
[statement1]
end
// B transaction
begin
[statement2]
exception // 여기 있으면 B 만 rollback
end
exception // 여기 있으면 A,B 트랜잭션 모두 rollback
end
$SP 예시코드
1. insert sample
create procedure sample_sp_insert
(
_email character varying,
INOUT _inout character varying DEFAULT NULL::character varying
)
language plpgsql
as
$$
declare -- 사용할 변수를 미리 선언해줘야 함.
_RETURNED_SQLSTATE text;
_MESSAGE_TEXT text;
_PG_EXCEPTION_DETAIL text;
_PG_EXCEPTION_HINT text;
_PG_EXCEPTION_CONTEXT text;
_stack text;
begin
if exists(select * from [table_name] t where t.email = _email) then
raise exception '400'; -- error code
end if;
insert into [table_name](email)
values
(_email);
call sample_other_sp();
exception
when sqlstate 'P0001' then -- user defined error
GET STACKED DIAGNOSTICS
_MESSAGE_TEXT = MESSAGE_TEXT;
_inout = _MESSAGE_TEXT;
raise exception '%', _MESSAGE_TEXT;
when others then
GET STACKED DIAGNOSTICS -- get error stack
_RETURNED_SQLSTATE = RETURNED_SQLSTATE,
_MESSAGE_TEXT = MESSAGE_TEXT,
_PG_EXCEPTION_DETAIL = PG_EXCEPTION_DETAIL,
_PG_EXCEPTION_HINT = PG_EXCEPTION_HINT,
_PG_EXCEPTION_CONTEXT = PG_EXCEPTION_CONTEXT;
GET DIAGNOSTICS _stack = PG_CONTEXT;
call sp_get_curr_name_custom(_stack, _inout);
call sp_save_procedure_error(_inout, _RETURNED_SQLSTATE, _MESSAGE_TEXT, _PG_EXCEPTION_DETAIL, _PG_EXCEPTION_HINT, _PG_EXCEPTION_CONTEXT);
commit;
raise exception 'SP_ERROR';
end
$$;-
begin ~ end: 하나의 transaction block 을 뜻한다.
에러가 발생하는 경우 exception block 으로 이동하며 자동으로 rollback 된다. -
exception block(A) 안에서 다른 sp 혹은 fn 을 호출하는 경우에 반드시
commit을 해줘야 한다.
다른 sp를 호출하면 호출당한 sp에서 또 하나의 트랜잭션(B)을 만든다.
그러면 B는 A의 sub transaction이 된다. B에서 커밋을 했다고 해도 부모 트랜잭션에서도 커밋을 해야 저장이 된다. -
sample_other_sp(): 이 sp에서 에러가 발생(raise exception을 하는 경우 포함)해서 rollback을 해야 하는 경우에는 해당 sp 를 호출한 transaction block안에서 전부 rollback 이 된다 → insert 문도 rollback 됨. -
when sqlstate 'P0001' then: 사용자가 선언한 raise exception 경우이다.
그외의 에러들(raise exception이 아닌, 사용자가 핸들링 할 수 없는 에러들)은when others then쪽으로 오게 된다.
[REF]postgres error code -
stack error code
command description GET STACKED DIAGNOSTICS 에러가 났을 경우(exception block에 도달하는 경우)발생한 에러에 대한 stack의 정보를 가져온다. RETURNED_SQLSTATE the SQLSTATE error code of the exception MESSAGE_TEXT the text of the exception's primary message PG_EXCEPTION_DETAIL the text of the exception's detail message, if any PG_EXCEPTION_CONTEXT line(s) of text describing the call stack at the time of the exception PG_CONTEXT returns a text string with line(s) of text describing the call stack.
2. 현재 실행중인 sp의 이름을 가져오는 sp
create or replace procedure sp_get_curr_name(_stack text, inout _inout varchar)
language plpgsql
as
$$
DECLARE
curr_sp text;
BEGIN
curr_sp := substring(_stack from 'function (.*?) line');
_inout := curr_sp::regprocedure::regproc::text;
END
$$; 여기서 사용하는 stack이 위에서 가져온 PG_CONTEXT 이다.
3. how to call sp in source code
call sample_sp_insert('sampleEmail@email.com');
// 여기서 에러가 나서 raise exception 하면
// 소스코드에서는 throw error 와 같은 현상이 발생한다.
// -> try catch 문에서 잡으면 됨.
return true;REF
혹시나 sp 및 fn 이 날라갈 수 있을 수도...?
방지하기 위해 백업하는 sp 도 같이 기록하자. 이게 날라가면 무용지물이기 때문에.
stored procedure, user defined function, user defined type 들을 s3 bucket에 저장하는 SP이다
create procedure sp_backup_db_to_s3()
language plpgsql
as
$$
declare back_up_function text;
declare back_up_procedure text;
declare back_up_cron text;
declare back_up_user_defined_type text;
declare i text;
declare tables record;
declare routines record;
declare types record;
BEGIN
for i in
SELECT
t.table_name
FROM
capa.INFORMATION_SCHEMA.TABLES as t
WHERE
table_schema = 'public'
loop
select
*
into
tables
from
aws_s3.query_export_to_s3(format('select * from %I', i),
aws_commons.create_s3_uri('[s3_bucket_name]', concat('[버킷안에서의 디렉토리 이름]', to_char(now(), 'YYYYMMDD'), concat('/', i)), 'ap-northeast-2'),
options := 'format csv');
end loop;
back_up_function :=
'select
pg_get_functiondef(oid),
proname
from (
SELECT oid, proname
FROM pg_proc
WHERE proname in (
SELECT r.routine_name
FROM capa.INFORMATION_SCHEMA.ROUTINES as r
where LEFT(ROUTINE_NAME, 3) IN (''fn_'', ''_fn'')
)
)as a;';
back_up_procedure :=
'select
pg_get_functiondef(oid),
proname
from (
SELECT oid, proname
FROM pg_proc
WHERE proname in (
SELECT r.routine_name
FROM capa.INFORMATION_SCHEMA.ROUTINES as r
where LEFT(ROUTINE_NAME, 3) IN (''sp_'', ''_sp'')
)
)as a;';
back_up_cron :=
'select
pg_get_functiondef(oid),
proname
from (
SELECT oid, proname
FROM pg_proc
WHERE proname in (
SELECT r.routine_name
FROM capa.INFORMATION_SCHEMA.ROUTINES as r
where LEFT(ROUTINE_NAME, 3) IN (''cr_'', ''_cr'')
)
)as a;';
back_up_user_defined_type :=
'WITH types AS (
SELECT n.nspname,
pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
CASE
WHEN t.typrelid != 0 THEN CAST ( ''tuple'' AS pg_catalog.text )
WHEN t.typlen < 0 THEN CAST ( ''var'' AS pg_catalog.text )
ELSE CAST ( t.typlen AS pg_catalog.text )
END AS obj_type,
coalesce ( pg_catalog.obj_description ( t.oid, ''pg_type'' ), '''' ) AS description
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n
ON n.oid = t.typnamespace
WHERE ( t.typrelid = 0
OR ( SELECT c.relkind = ''c''
FROM pg_catalog.pg_class c
WHERE c.oid = t.typrelid ) )
AND NOT EXISTS (
SELECT 1
FROM pg_catalog.pg_type el
WHERE el.oid = t.typelem
AND el.typarray = t.oid )
AND n.nspname <> ''pg_catalog''
AND n.nspname <> ''information_schema''
AND n.nspname !~ ''^pg_toast''
),
cols AS (
SELECT n.nspname::text AS schema_name,
pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
a.attname::text AS column_name,
pg_catalog.format_type ( a.atttypid, a.atttypmod ) AS data_type,
a.attnotnull AS is_required,
a.attnum AS ordinal_position,
pg_catalog.col_description ( a.attrelid, a.attnum ) AS description
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_type t
ON a.attrelid = t.typrelid
JOIN pg_catalog.pg_namespace n
ON ( n.oid = t.typnamespace )
JOIN types
ON ( types.nspname = n.nspname
AND types.obj_name = pg_catalog.format_type ( t.oid, NULL ) )
WHERE a.attnum > 0
AND NOT a.attisdropped
)
SELECT
cols.obj_name
,json_agg(json_build_object(''column_name'', cols.column_name, ''data_type'', cols.data_type, ''ordinal_position'', cols.ordinal_position,
''is_required'', cols.is_required, ''description'', coalesce ( cols.description, '''' )) order by cols.ordinal_position)
FROM cols
where cols.schema_name <> ''aws_commons''
group by cols.obj_name
ORDER BY cols.obj_name';
select
*
into
routines
from
aws_s3.query_export_to_s3(
back_up_function,
aws_commons.create_s3_uri('[s3_bucket_name]', concat('[버킷안에서의 디렉토리 이름]', to_char(now(), 'YYYYMMDD'), '[해당 디렉토리 안의 디렉토리]'), 'ap-northeast-2'),
options :='format csv');
select
*
into
routines
from
aws_s3.query_export_to_s3(
back_up_procedure,
aws_commons.create_s3_uri('[s3_bucket_name]', concat('[버킷안에서의 디렉토리 이름]', to_char(now(), 'YYYYMMDD'), '[해당 디렉토리 안의 디렉토리]'), 'ap-northeast-2'),
options :='format csv');
select
*
into
routines
from
aws_s3.query_export_to_s3(
back_up_cron,
aws_commons.create_s3_uri('[s3-bucket_name]', concat('[버킷안에서의 디렉토리 이름]', to_char(now(), 'YYYYMMDD'), '[해당 디렉토리 안의 디렉토리]'), 'ap-northeast-2'),
options :='format csv');
select
*
into
types
from
aws_s3.query_export_to_s3(
back_up_user_defined_type,
aws_commons.create_s3_uri('[s3_bucket_name]', concat('[버킷안에서의 디렉토리 이름]', to_char(now(), 'YYYYMMDD')), 'ap-northeast-2'),
options :='format csv');
END
$$;이렇게 sp를 사용하면 소스코드에서는 스키마의 정보를 알필요가 없다.
하지만 혹시 알아야 하는 경우가 생길수도 있기 때문에...
그럴때는 typeorm-model-generator 를 사용하면 된다.
$ npm i --save typeorm-model-generator$ npx typeorm-model-generator -h [dbhost주소] -d [데이터베이스이름] -p [port] -u [유저] -x [비밀번호] -e [db종류] -o [entity 파일 위치] --case-file none위 command 를 실행하면 스키마에 있는 모든 테이블, procedure, function, type, 등등 필요한 모든 정보를 특정 폴더 위치에 가져올 수 있게 된다.
❗ 그냥 가져오게 되면 테이블 명이 camel case가 아닌, snake case 로 가져오게 되는데 뒤에 --case-file none 옵션을 달면 camelCase 로 가져오게 된다.
