소소한 내용이다.
회사에서 cron-job 혹은 복잡한 로직들을 소스코드가 아닌 postgresql procedure, function 으로 관리를 하였다.
procedure 버전관리를 어떻게 해야하지
개발환경마다 디비를 다르게 해서 관리를 해서 배포를 하는 방법으로 진행했지만,
소스코드는 github로 버전관리를 하면 되는데, procedure, function 은 버전관리를 깃허브와 완전 비슷하게 우리 입맛에 구현해주는 툴이 없었다.
datagrip 에서 github repository 를 연동해서 하면 된다고 했는데, 예상보다 쉽지 않았고, 뭔가 맘에 들지 않았다.
그래서 팀원 모두들 찾아보다가 툴 말고 그냥 디비안에서 관리를 해야겠다는 생각을 했다.
그 결과 trigger를 사용하기로 했다.
trigger 는 어떠한 액션에 대해서 자체적으로 이벤트를 캐치해서 원하는 동작을 하게 해주는 기술이다. postgresql에만 있는건 아니고 다른 rdbms, nosql 에도 다~ 있다. 일반적인 트리거는 특정 테이블에서 발생하는 DML 을 캐치해서 그에 따른 처리를 해주는 것이 일반적이다.
하지만 우리는 테이블의 상태 변화가 아닌, procedure, function 의 상태 변화를 캐치했어야 했다.
그래서 구현한 방법이 일반적인 트리거가 아닌 event trigger 이다.
Event trigger
특정 하나의 테이블에서 발생하는 DML 만을 캐치하는것이 아닌,
event trigger는 특정 한 테이블에 국한되는 것이 아닌, 전역적으로 모든 DDL 이벤트를 캐치하는 트리거이다.
postgres 16 버전에서는
- ddl_command_start
- ddl_command_end
- table_rewrite
- sql_drop
위 4가지 이벤트에 대해서 이벤트 트리거가 발동한다고 한다.
그 외의 기능들은 추후에 추가될 예정이라고 한다.
1. ddl_command_start
CREATE, ALTER, DROP, SECURITY LABEL, COMMENT, GRANT, REVOKE, SELECT INTO(CREATE TABLE AS)
위 명령어들이 시작 하기 전에 발동한다.
2. ddl_command_end
ddl_command_start 와 같은 이벤트들에 대해서 명령어 후에 발동한다.
해당 명령어들이 끝났을 때 어떤 명령어가 끝났고, 그에 대한 추가 정보가 필요하면 pg_event_trigger_ddl_commands() 함수를 이용하면 정보를 알 수 있다. 예를들어, create procedure 후에 pg_event_trigger_ddl_commands() 함수를 실행하면, 어떤 프로시저가 생성되고, 생성시점, 추가 정보 등을 알 수 있다.
대신에 해당 이벤트는 트랜잭션이 커밋되기전에 발동한다.
3. sql_drop
sql_drop 은 ddl_command_end 바로 직전에 어떠한 객체가 drop 될 때 발동한다.
어떤 객체들이 drop 되었는지 알기 위해서는 pg_event_trigger_dropped_objects() 함수를 이용하면 알 수 있다.
이 이벤트는 시스템 카탈로그에서 객체가 사라지면 확인할 수 있다.
system catalog: 데이터베이스의 모든 객체에 대한 정의 와 정보를 저장하고 있는 시스템 테이블이다. 성능 평가를 위한 모든 통계정보도 저장하고, 카탈로그에 저장된 데이터를 meta data라고 한다.
4. table_rewrite
테이블의 상태가 변하는 ALTER TABLE, ALTER TYPE 명령어 바로 직전에 실행되는 이벤트이다.
🔥 주의할 점 🔥
이벤트 트리거는 트랜잭션이 롤백되면 실행되지 않는다.
그렇기 때문에,
1. ddl_command_start 실패하면 event trigger 또한 실행되지 않는다.
2. ddl 이 실패하면 ddl_command_end 또한 실행되지 않는다.
3. ddl_command_end 이 실패하면 ddl 또한 롤백된다.
ddl_command_end 이 commit 되기 전에 실행되기 때문에 3번 같은 문제가 발생하는데, 그렇기 때문에 ddl_command_end 이벤트에 따른 이벤트트리거는 신중하게 작성해야겠다.
그래서 어떻게 설계를 했는가
procedure, function 이 CREATE, ALTER, DROP 중 하나의 상태가 되면 해당 이벤트를 잡아서 액션을 취하도록 하였다.
1) 로그를 저장할 테이블을 만들자
create table tb_sp_fn_ddl_log
(
name text, // procedure, function 이름
obj_id integer, // trigger action id
ddl text, // procedure, function DDL
create_date timestamp with time zone default now() not null
);2) 이벤트 트리거가 걸리면 작동할 function을 만들어준다.
create function cr_sp_fn_ddl_log() returns event_trigger
language plpgsql
as
$$
DECLARE
r RECORD;
ddl text;
BEGIN
IF tg_tag in
('CREATE PROCEDURE', 'CREATE FUNCTION')
THEN
r := pg_event_trigger_ddl_commands();
select * into ddl from pg_get_functiondef(r.objid);
insert into tb_sp_fn_ddl_log (name, obj_id, ddl) values (r.object_identity, r.objid, ddl);
END IF;
END;
$$;이벤트 트리거로 작동할 함수는 반드시 event_trigger 를 반환하는 형식의 함수여야 한다.
- tg_tag 는 postgresql event_trigger 를 반환하는 함수에서 자체적으로 수집할 수 있는 객체라고 생각된다.
- tg_tag 가
create procedure혹은create function일 경우에 이 함수가 실행되는데,pg_event_trigger_ddl_commands()를 통해서 ddl_command_end 이벤트의 정보를 조회할 수 있다. pg_get_functiondef()함수는 procedure, function 의 정의 DDL 를 조회할 수 있다.- 조회할 결과를 토대로 사전에 생성한 테이블에 저장한다.
3) 마지막으로 해당 function을 일으킬 트리거를 생성해준다.
create event trigger tr_sp_fn_ddl_log
on ddl_command_end
execute procedure public.cr_sp_fn_ddl_log();이렇게 하면 테이블에 그동안 특정 procedure 혹은 function 이 어떻게 변화했는가 모든 로그를 트래킹할 수 있다. 저장 시간까지 저장하기 때문에, 원하는 시점에 어떤 DDL이었는지 알 수 있기 때문에, 롤백 및 버전관리를 하기 수월해졌다.
Reference
https://www.postgresql.org/docs/current/event-triggers.html
https://www.postgresql.org/docs/current/event-trigger-definition.html
https://code-lab1.tistory.com/133
