PostgreSQL Notify, Listen

Web System에서 PostgreSQL을 사용하는 환경에서의 Longtime Query에 대한 해결방안을 찾던 중 알게된 내용을 정리해 둡니다.

 아직까지 제가 찾은 방법은 세가지 정도 됩니다만 모두 석연치 않은 면은 있습니다. 그래도 이번에 PostgreSQL과 NodeJS 환경에서 사용한 방법입니다.

2년전 쯤 웹 UI에서 LongTime Query에 대한 고민 중 찾았던 방법은 NodeJS의 비동기 처리와 별도의 트랜잭션 처리를 위해 DBLink를 통한 우회 프로시져 콜을 사용했었습니다. 원하는데로 동작은 하지만 DBLink 관리와 장애 처리등 번거로운 면이 없지 않고 NodeJS에서도 찜찜한 Response Return을 해야 하는 상황이라 내내 맘이 걸려 했던 상황 이었습니다. 

그런 상황에서 이번에는 관리자 페이지를 기획, 준비하는 과정에서 PostgreSQL상 Publish/Subscribe을 지원하는 Notify/Listen에 대한 내용을 알게되고 테스트 해보니 활용도가 참 많아 보였습니다.

 기본적으로 LongTime Query나 Batch 작업에대한 내용을 검색해 보면 OS CronJob과 터미널 Shell 스크립트로 실행하는 경우의 내용을 많이 접하게 됩니다. NodeJS에서 childProcess를 이용한 별도 프로세스와  비동기 처리에대한 내용도 어쩔 수 없는 부분이였던것 같습니다.

 그런데 지금 시스템의 경우에는 Web 말고 전처리 시스템과의 통합부분이 있습니다. 전에는 DB Table에 대한 자료 스캔을 통해 전처리 시스템의 모듈을 실행하고 결과에대한 업데이트를 통해 UI 시스템에서 사용하는 방식으로 시스템이 구축되었습니다. 이런 구조에서 Notify/Listen을 사용한 Web 시스템과의 통합과 WebSOcket을 이용한 Web Client의 실시간 자료 배포까지도 가능한 구조로 확대 업데이트될 수 있습니다.

<pg_notify를 이용한 시스템 구조 (출처 : arctype.medium.com)>

 

목표 시스템 구조

 

/* postgreSQL script */

--create test table
CREATE TABLE public.tmp_notify (
	f_input_date varchar(8) NOT null,
	text text	
);

-- create notify function
CREATE or replace FUNCTION fn_notify_trigger() RETURNS trigger AS $$
DECLARE
BEGIN
PERFORM pg_notify('tmp_notify', row_to_json(NEW)::text);  //channel - tmp_notify
RETURN new;
END;
$$ LANGUAGE plpgsql;


-- create insert trigger
CREATE TRIGGER tmp_notify_trigger AFTER insert or UPDATE ON tmp_notify
FOR EACH ROW EXECUTE PROCEDURE fn_notify_trigger();


--테스트
insert into tmp_notify (f_input_date, text) values ('20210527', 'test'::text);
;

 

/* nodeJS pg_channel subscribe 등록 */

const request = require('request');
const env = require('./env')
var db = require('pg-promise')()(env.pg_connect);

// added listener code for pg listen / notify
db.connect({direct: true})
    .then(function (sco) {
        //event -> 'notification' **** 중요 ***
        sco.client.on('notification', function (data) {
            console.log('Received: ', data);

            /*NotificationResponseMessage {
                length: 24,
                processId: 10032,
                channel: 'tmp_notify',
                payload: 'test',
                name: 'notification' }
            */

         });
		 
    //pg channel subscribe 
    return sco.none('LISTEN "tmp_notify"');  //channel -> tmp_notify **** 중요 ***
})
.catch(function (error) {
    console.error('Error:', error);
});

위와 같이 테스트를 진행해 보았습니다.

시스템 통합 채널을 DBMS-postgreSQL을 사용하는 환경에서는 활용해 볼만한 기능이지 않나 생각합니다. 물론 다른 더좋은 방법도 있겠죠 :)