꿀잠

최근 프로젝트에서 Oracle 프로시저 기반의 프로젝트를 Postgresql 로 변환하는 작업을 진행하고 있다.

Aws Schema Conversion Tool를 이용하여 1차 변환 후 문제가 없는지 확인하며 추가 변환작업을 진행하는 형태로 진행하였다. 변환 작업을 하면서 많이 느꼈던 부분은 PostgreSQL의 경우 "데이터 타입"에 많은 신경을 써야한다는 것이었다.

변환 작업을 하면서 변환 시 체크해야 하는 상황들을 일부 정리해본다.

1. CURSOR 이용시 RECORD 타입 선언 필요

FOR V_RECORD IN SOME_CURSOR
LOOP
    ~~~ V_RECROD 를 이용
END LOOP;

위와 같은 커서 문을 작성한다고 하면
Oracle은 V_RECORD 라는 변수를 바로 참조하여 사용할 수 있지만
PostgreSQL의 경우 V_RECORD라는 변수를 DECLARE 문에 RECORD 타입으로 미리 선언해주어야 했다.

2. FROM DUAL

테이블의 컬럼을 바라보지 않는 Select 문 사용 시
Oracle 은 FROM DUAL 문법을 뒤에 붙여주어야 하지만
PostgreSQL 의 경우 필요하지 않았다.

3. 파이프라인을 이용한 문자열 합치기

# Oracle Case
SELECT 'a' || NULL || 'b' FROM DUAL; -- 결과: 'ab'

# Postgresql Case
SELECT 'a' || NULL || 'b'; -- 결과: NULL

파이프(||)를 이용한 문자열 합치기를 사용할 경우 Oracle 의 경우 NULL 값을 공백으로 처리하여 합쳐진다. 이는 Oracle 의 경우 공백을 NULL 로 판정하기 때문에 발생하는 결과인 것으로 예상된다.
조회 관련 쿼리에서 '%' 를 문자열의 앞뒤로 붙여 LIKE 검색을 할 경우 파이프(||)를 이용할 시 Oracle과 PostgreSQL의 결과가 완전히 달라지므로 주의해야 한다. 변환 작업 시 CONCAT_WS를 이용하였다.

4. 현재 날짜 및 시간

Oracle 의 경우 SYSDATE,
PostgreSQL 의 경우 NOW()를 이용하여 현재 시간을 불러올 수 있다.

5. 날짜 연산

Oracle 의 경우 '+', '-' 연산을 이용하여 바로 날짜 계산이 가능하지만,
Postgresql 의 아래와 같은 문법으로 타입을 명시해야한다.

# Oracle Case
SELECT SYSDATE + 1 FROM DUAL;

# Postgresql Case
SELECT NOW() + '1 days'::INTERVAL; -- INTERVAL 타입으로 연산해야한다.

6. 데이터 수 제한 검색 문법 및 ORDER BY 주의점

Oracle의 경우 ROWNUM 을 WHERE 절에서 비교하여 SELECT 문의 조회 건수를 제한할 수 있으며
PostgreSQL의 경우 LIMIT 문법을 이용하여 제한하여 조회할 수 있다.

이 때 ORDER BY 절이 포함된 SELECT 문이라면 변환 시 주의해야 한다.
Oracle의 ROWNUM은 ORDER BY 로 정렬되기 전에 주어지고 WHERE 절에서 제한한다면,
PosgreSQL의 LIMIT의 경우 ORDER BY 된 상태에서 LIMIT 조회가 적용되기 때문이다.
Oracle에서 PostgreSQL로 변환 시에는 인라인뷰에서 ORDER BY 하여 SELECT 문을 작성한 이후 LIMIT 를 걸어야 했다.

# Oracle Case
SELECT *
  FROM A_TABLE A
 WHERE ROWNUM <= 10;
 ORDER BY A.A_COLUMN

# PostgreSQL
SELECT *
  FROM (SELECT *
          FROM A_TABLE 
         ORDER BY A_COLUMN) A
 LIMIT 10;

7. Alias

Oracle 의 경우 인라인뷰에는 Alias 가 필수가 아니지만
PostgreSQL의 경우 인라인뷰에 Alias 가 필수다.

Oracle의 경우 MERGE, UPDATE, INSERT 문 등에서도 테이블에 Alias를 붙이는 것에 제한사항이 없다.
PostgeSQL의 경우 변경되는 기준이 되는 컬럼에는 Alias를 붙이면 안된다.

# Oracle Case
UPDATE A_TABE A
   SET A.A_COLUMN = 'NEW_A';Z

# PostgreSQL
# A.ACOLUMN 이런식으로 쓰면 안된다. 우항에서 값을 참조할때는 Alias 를 사용 가능하다.
UPDATE A_TABE A
   SET A_COLUMN = 'NEW_A';

8. 재귀 쿼리

Oracle 의 경우 CONNECT BY LEVEL 문법이 존재하여 재귀 쿼리를 짧게 작성할 수 있다.
PostgreSQL 의 경우 WITH RECURSIVE 문을 이용하여 재귀 쿼리를 작성 해야한다. Oracle에 비하여 쿼리 길이가 길어지며 가독성이 떨어진다고 느꼈다. 쿼리 작성 난이도도 상승한다.

9. SELECT 'STRICT' INTO

Oracle 에서 변수에 값을 담기 위하여 SELECT INTO 문법 사용 시 조회 되는 값이 없을 경우 NO DATA EXCEPTION 이 발생한다.
PostgreSQL의 경우 SELECT INTO 문법 사용 시 조회 되는 값이 없어도 EXCEPTION이 발생하지 않는다.

Oracle -> PostgreSQL 변환 시 Oracle과 같은 형태로 Exception 발생을 위해선 SELECT INTO STRICT를 사용하여야 한다.

10. Mybatis 관련

Spring Mybatis 에서 Function 을 호출하여 CURSOR 결과를 리턴 받을 시 jdbcType 설정
Oracle의 경우 "CURSOR" 타입으로 설정한 후 CALL 값을 등호로 받는다.
PostgreSQL의 경우 "OTHER" 타입으로 설정한 후 Function의 추가 파라미터로 넣은 후 CALL 한다.

11. 프로시져 실행 시 CASE WHEN 문의 오류 검사

변환 작업 시 CASE WHEN 문을 이용하여 잘못된 결과일 경우 1/0 값을 결과로 하여 강제로 오류를 발생 시키는 로직이 있었다.

Oracle 의 경우 실제 CASE WHEN 문에서 조건에 해당할 경우 DIVISION BY ZERO 오류가 발생하여 원하는 대로 컨트롤 할 수 있지만.
PostgreSQL의 경우 CASE WHEN 조건에 걸리지 않더라도 바로 DIVISION BY ZERO가 발생하였다. 아마도 PostgreSQL 에선 CASE WHEN 문의 결과 값을 미리 체크하는 형태로 동작하는 것으로 생각되었다. 하지만 1/0의 값을 CASE WHEN 문에 바로 작성하지 않고 별도의 Function 에서 리턴하는 형태로 작성할 경우 오류가 발생하지 않고 조건에 걸리는 경우에만 발생하였다. 따라서 기존 Oracle 프로그램과 같은 형태로 변환하기 위하여 DIVISION BY ZERO 를 리턴할 수 있는 별도의 Function으로 처리하기로 하였다.

12. ROW 위치를 지정하는 논리적 주소 값

Oracle의 경우 Row 마다 위치를 지정하는 물리적 주소 값 ROWID 가 존재한다. UPDATE 시에도 변하지 않는다.
PostgreSQL의 경우 Row 마다 위치를 지정하는 물리적 주소 값 CTID 가 존재한다. UPDATE 시에는 값이 변한다.

Oracle 에서 UPDATE 로직이 있는 ROWID 와 관련된 값을 PosgreSQL로 변환하기 위해선 별도의 Unique 값을 채번해서 사용하여야 한다. 만약 UPDATE 가 없는 경우라면 CTID 값으로 변환하여 적용하여도 문제는 없을 것으로 판단된다.

프로젝트에서 인터페이스 관련한 부분을 유지보수 할 때 알게된 내용이다.
정해진 데이터를 순서대로 테이블에 INSERT 해야 하는 상황이었다.

그런데 자꾸 하나의 트랜잭션에서 for문 돌면서 순서대로 insert 하는 데이터가 순서대로 들어가지 않는 것이었다.

대략 위와 같은 데이터를 INSERT 하는 상황이었다고 가정하고 설명하겠다.
분명히 순서대로 데이터를 INSERT 문을 실행하였고, Spring 로그에 찍힌 ISNERT 문도 A 데이터 기준으로 1,2,3 순서대로 되었는데 말이다.

당시 입력되는 순서를 보고 테스트해주시던 분이 다른 데이터 기준으로 입력되는 것 같다고 말씀해주셨다. A 데이터 기준으로 말하자면 3->1->2 순서로 입력되고 있었다.

도대체 어찌된 일인지 테이블의 구성을 살펴보던 중 예상되는 이유를 발견하였고,
해당 부분을 수정하고 테스트한 결과 원하는 순서대로 INSERT 할 수 있었다.

그 이유는 바로 테이블 키에 있었다. 당시 해당 테이블엔 별도의 PK 는 없었고, 유니크 키만 B,C,D의 복합키로 걸려있었다.

데이터가 실제로 입력되던 3->1->2(A 기준) 이 B -> C -> D 의 키 순으로 정렬되어 INSERT 되고 있다면 딱 설명이 되는 상황이었던 것이다.

실제로 해당 테이블의 키 설정을 A 컬럼을 PK로 하여 다시 설정한 후에는 원하는 순서대로 INSERT 할 수 있게 되었다.

해당 상황은 인덱스와 관련이 있지 않을까 생각된다. 인덱스 관련하여 학습 했을 때 데이터베이스는 인덱스를 기준으로 정렬한 테이블을 저장된다는 내용을 본 적이있다. 트랜잭션에서 데이터들을 해당 테이블에 INSERT 할 때 PK 값이 없다보니 유니크키에 걸린 인덱스를 기준으로 정렬해서 INSERT 된것으로 예상된다.

PK는 설정시 인덱스를 기본적으로 생성한다. 따라서 테이블에 PK를 설정한 이후에는 PK 인덱스를 기준으로 순서대로 INSERT 된 것으로 판단된다.

얼마전에 DBeaver 툴을 이용해서 데이터를 수정하는데 자꾸 다른 row가 수정되는 것이었다.
분명히 나는 한 row를 수정했는데 테이블 전체의 데이터가 자꾸 변경되는 것이었다.

그랬던 이유를 결국 찾게 되었는데 바로 키때문이었다. 당시 테이블에 키 설정을 새로 하려고 기존에 있던 키를 제거한 상황이었다. 테이블 전체에 아무런 키 설정이 되어있지 않다보니 DBeaver 툴에서 Update를 실행할 때 내가 수정한 row가 어느 row 인지 인식을 못하는 것이었다. 새로운 키를 다시 생성하자 정상적으로 Update가 되었다.

DBeaver 최초 설치시 JDBC 드라이버를 설정해주는 걸 생각해보면, 결국 DBeaver 툴이 데이터를 수정할때는 DB 자체를 수정하는 것이 아니라 JDBC를 통해 DB를 조작하는 것으로 예상된다. 

내부적으로 where 1=1 + key를 이용한 조건을 쓰는걸까? ㄷㄷ..
DBeaver 툴 무료 버젼으로도 상당히 편하게 잘 쓸 수 있지만 키 식별이 안되어 update row를 확인할 수 없다면 update가 안되어야 하는 것이 아닌가 생각이 든다.

좋은 툴이지만 조심해서 써야겠다.