[postgreSQL] 재귀쿼리 만들기 (Recursive) ( + 매우 주의해야하는 케이스 : 무한루프)

 재귀쿼리를 쓸 경우가 종종 있는데 나같은 경우에는 조직도 트리와 폴더 트리에 사용해본 적이 있다. 간단하게 사용법을 설명하고 실제 케이스에서 엄청난 오류?를 만들어냈던 경험을 공유해보려고 한다.

---

0. 재귀쿼리를 테스트해보기 위한 사전 작업

조직도가 제일 무난할 것 같아 심플한 데이터를 준비해보았다.

CREATE TABLE division
(
    division_code character varying(20) NOT NULL PRIMARY KEY,
    division_name character varying(20) NOT NULL,
    upper_division_code character varying(20) NOT NULL
);

INSERT INTO division VALUES ('develop_division', '개발본부', '');
INSERT INTO division VALUES ('front_team', '프론트팀', 'develop_division');
INSERT INTO division VALUES ('infra_team', '인프라팀', 'develop_division');
INSERT INTO division VALUES ('back_team', '백엔드팀', 'develop_division');
INSERT INTO division VALUES ('web_part', '웹파트', 'front_team');
INSERT INTO division VALUES ('aos_part', 'AOS파트', 'front_team');
INSERT INTO division VALUES ('ios_part', 'IOS파트', 'front_team');
INSERT INTO division VALUES ('1_part', '1파트', 'back_team');
INSERT INTO division VALUES ('2_part', '2파트', 'back_team');

 

1. 재귀쿼리 (모든 하위 부서 찾기)

첫번째 케이스는 기준 부서의 조직도 아래 모든 부서를 찾아본다.

WITH RECURSIVE find_division(division_code, division_name, upper_division_code, DEPTH) 
as (
  --START_QUERY
  select division_code, division_name, upper_division_code, 1
  from division
  where 1=1
  and division_code = 'develop_division'	
  
  union
  
  --REPEAT_QUERY
  select d.division_code, d.division_name, d.upper_division_code, fd.DEPTH+1
  from find_division fd
  INNER JOIN division d on d.upper_division_code = fd.division_code --case1. 조직도 아래의 모든 부서 찾기 
)
--VIEW_QUERY
select * from find_division;

쿼리 실행에 관하여 순서대로 설명해보면,

1. --START_QUERY 영역에 해당 부분을 실행하여 '개발본부'의 데이터를 일단 가져온다.
2. 기준이 되는 데이터를 find_division 이라는 임시뷰에 저장한다.
3. --REPEAT_QUERY 영역에 해당 부분을 실행하여,
   이미 들어있는 임시뷰와 또 다시 조직도 테이블을 조인하여
   '개발본부'의 코드인 'develop_division'을 상위부서로 갖는 부서들을 찾는다.
   => 데이터상 프론트팀, 인프라팀, 백엔드팀이 나온다. 
4. 해당 데이터도 find_division 이라는 임시뷰에 저장한다.
5. 추가된 데이터를 대상으로 본인코드들을 상위부서로 갖는 부서들을 찾는다.
6. 지속 반복하여 모든 하위 부서를 찾는다.

 

2. 재귀쿼리 (모든 상위 부서 찾기)

내 기준 상위 부서를 찾고 싶을수도 있다. 예를 들어, '웹파트'의 상위 부서를 찾는다고 하면, case1 해당하는 줄을 주석하고 case2에 해당하는 부분의 주석을 풀면 쉽게 '웹파트 => 프론트팀 => 개발본부' 라는 결과를 얻을 수 있다.

WITH RECURSIVE find_division(division_code, division_name, upper_division_code, DEPTH) 
as (
  --START_QUERY
  select division_code, division_name, upper_division_code, 1
  from division
  where 1=1
  and division_code = 'web_part'
	
  union
  --REPEAT_QUERY
  select d.division_code, d.division_name, d.upper_division_code, fd.DEPTH+1
  from find_division fd
  INNER JOIN division d on fd.upper_division_code = d.division_code
)
--VIEW_QUERY
select * from find_division;

 

3. 재귀에서 가장 주의해야할 무한루프

데이터가 알맞게 들어가 문제없이 재귀쿼리를 수행하면 큰 문제는 없지만, 결국 데이터라는게 사람이 만들고 주입하는 것이기에 오류가 날 수 있다.
 '웹파트'의 상위부서 코드가 '웹파트' 본인으로 들어가는 경우, 결과는 '웹파트 => 웹파트 => 웹파트 => ......' 된다. 테스트 해보고 싶다면, 아래의 쿼리로 데이터 갱신 후 모든 상위부서 찾기 쿼리를 돌려본다. 100% 뻗으니 쿼리를 중단해야한다.

--테스트하고 (1=>1)
UPDATE division SET upper_division_code = 'web_part' WHERE division_code = 'web_part'; 
--원복하시길
UPDATE division SET upper_division_code = 'front_team' WHERE division_code = 'web_part';

--이것도 테스트하고 (1=>2, 2=>1)
UPDATE division SET upper_division_code = 'web_part' WHERE division_code = 'front_team'; 
--이렇게 원복하시길
UPDATE division SET upper_division_code = 'develop_division' WHERE division_code = 'front_team';

뿐만 아니라, '프론트팀'의 상위부서 코드가 '개발본부'에서 '웹파트'로 잘못들어갔다고 생각해보자. 결과는 '웹파트 => 프론트팀 => 웹파트 => 프론트팀 =>웹파트 => 프론트팀 => ......' 로 데이터 베이스가 뻗을 수 밖에 없다. 이 말은, 재귀를 통해 본인으로 다시 돌아올수 있는 순회 구조라면 뻗을수 밖에 없다는 사실이다.

출처 : https://www.clien.net/service/board/park/6570612

(실제 운영에서 조직도 밀어넣기를 통해 데이터를 넣었는데, 1=>2, 2=>3, 3=>1 로 되는 데이터 구조 때문에 뻗은 경험이있다.ㅠㅠ)

 

4. 무한루프를 방지하기 위하여

데이터를 넣는 시점에 순회구조가 발생하지 않도록 하는 방법도 있고, 데이터를 조회하는 시점에 방어로직이 들어가는 방법도 있는데, 여기서는 조회시점 방어로직만 다룬다.

WITH RECURSIVE find_division(division_code, division_name, upper_division_code, DEPTH) 
as (
  --START_QUERY
  select division_code, division_name, upper_division_code, 1
  from division
  where 1=1
  and division_code = 'web_part'
	
  union
  --REPEAT_QUERY
  select d.division_code, d.division_name, d.upper_division_code, fd.DEPTH+1
  from find_division fd
  INNER JOIN division d on fd.upper_division_code = d.division_code 
  and d.division_code != 'web_part' --★★★방어로직★★★
)
--VIEW_QUERY
select * from find_division;

방법은 매우 간단하다. 기준이 되는 --START_QUERY의 조건이 되는 코드를 --REPEAT_QUERY의 조인 조건에서 한 번 걸러주면 된다. 반복하며 조인하다가 본인이 등장하는 순간 멈추는 것이다. 
 무한로딩이 생겼던 위의 테스트케이스를 각각 돌려봐도 정상적으로 멈추는 것을 확인할 수 있다.

---

재귀는 그렇게 자주 쓰는 쿼리는 아니라 쿼리 문법을 까먹을 수도 있겠지만은 분명한 것 무한루프가 돌지 않도록 꼭 테스트해봐야한다는 점 기억하면 좋을 것 같다.