PostgreSQL의 사용자 정의 연산자
0. 들어가며
PostgreSQL에서는 C++ 프로그래밍 언어처럼 데이터베이스 사용자가 자신이 쓸 연산자를 직접 만들어 쓸 수 있습니다.
데이터베이스에서 연산자까지 만들어 쓸 필요까지 있겠냐만은, 새로운 연산자가 있으면, 쿼리문 작성이 한결 간편해 집니다. 또한 쿼리문을 눈으로 읽는데도 한결 편해집니다.
물론 이 글에서 이야기하는 부분은 기업 내 표준화 된 자료 설계안에 완벽하게 반할 가능성이 큽니다. 하지만, 유용한 기능이니 특수 상황 - 예, 다른 종류의 데이터베이스에서 PostgreSQL로 데이터베이스를 옮기는 경우 - 에서 잘 쓰면 개발 비용을 많이 줄일 수 있습니다.
1. 연산자 가독성
add(1,2) 라는 함수를 1 + 2 형태로 연산자를 사용해서 같은 일을 하는 것은 프로그래밍에서 연산자가 있는 첫번째 이유입니다. 이처럼 직관적으로 표현함으로 가독성을 높이는 것이지요.
데이터베이스 안에서도 마찬가지입니다.
다음은 표준 SQL에서 문자열 합치는 || 연산자에 대해서 pg_operator 시스템 카탈로그 테이블을 조회한 결과입니다.
postgres=# select oprname, format_type(oprleft,null) as l, format_type(oprright,null) as r, oprcode as function from pg_operator where oprname = '||' and format_type(oprleft,null) = 'text' and format_type(oprright,null) = 'text'; oprname | l | r | function ---------+------+------+---------- || | text | text | textcat (1개 행)
여기서 볼 것은 그 연산자 역할을 하는 원래 함수입니다.
즉,
postgres=# select textcat('하늘과','호수');
textcat
------------
하늘과호수
(1개 행)
postgres=# select '하늘과' || '호수';
?column?
------------
하늘과호수
(1개 행)
위 두 개의 쿼리는 완벽하게 동일합니다.
2. 연산자 오버로드
한 예로, 실무에서도 충분히 쓸 수 있는, 앞에서 설명한 || 연산자의 오버로드를 다루어 보겠습니다.
특정 데이터베이스는 이 문자열 합치기 연산자에서 한 쪽이 null 값인 경우에 연산 결과가 대부분의 데이터베이스와 다르게 작동합니다.
postgres=# select '하늘' || null; ?column? ---------- (1개 행)
이 처럼 PostgreSQL에서는 문자열을 합칠 때 한 쪽이 null 값인 경우 그 연산 결과도 null 로 반환합니다. 윗 쿼리의 결과로 '하늘'이 나오도록 하려면, coalesce(null,'') 함수를 함께 사용하거나, 아니면, || 연산자를 포기하고 concat() 함수를 사용하는 방법 뿐입니다.
postgres=# select concat('하늘', null);
concat
--------
하늘
(1개 행)
다행이 concat() 함수를 제공하고 있어, || 연산자 오버로드용 함수를 따로 만들지 않아도 될 문제로 보입니다. (물론 아닙니다. 뒤에서 설명함)
사용자 정의 연산자를 만드는 구문은 PostgreSQL 설명서 CREATE OPERATOR 페이지를 참조합니다.
일단 도움말을 보면서 만들어봅니다.
postgres=# create operator || ( PROCEDURE = concat , LEFTARG = text , RIGHTARG = text); ERROR: function concat(text, text) does not exist
오류가 발생했습니다. 연산에 쓸 함수가 없습니다. 앞에서 썼던 concat() 함수의 인자는 VARIADIC "any" 이기 때문입니다. 즉 문자열을 합치는 || 연산자에서 사용할 concat() 함수의 인자는 두 개여야 하고, 각각 text 형으로 먼저 만들어져 있어야 합니다.
다시 이번에는 함수 오버로드로 concat(text,text) 함수를 만듭니다. 함수 오버로드에 대한 이야기는 이 글 범위를 벗어나기에 여기서는 그냥 만드는 방법만 소개합니다.
postgres=# create function concat (text, text) returns text language internal stable as 'text_concat'; CREATE FUNCTION
다행이 기본 concat 함수가 VARIADIC 형 복수 인자를 사용하도록 되어 있기 때문에 그냥 그 내장 함수를 그대로 사용하고 단지 새로 오버로드하는 함수의 인자는 딱 두 개다고 지정하기만 하면 됩니다. 이제 만들어진 함수를 먼저 확인합니다.
postgres=# \df concat
함수 목록
스키마 | 이름 | Result 자료형 | 자료형 인수 | 형태
------------+--------+---------------+----------------+------
pg_catalog | concat | text | VARIADIC "any" | 일반
public | concat | text | text, text | 일반
(2개 행)
concat() 함수의 오버로드는 잘 되었지만, 여기서 중요한 사실은 이 함수들의 스키마가 다르다는 점입니다. 이는 곧 위에 언급한 오류가 났던 새로운 연산자에서 사용할 함수에 대한 구체적인 스키마 명시까지 해야 새로 오버로드한 함수를 사용할 것이다는 것을 의미합니다.
postgres=# create operator || ( PROCEDURE = public.concat , LEFTARG = text , RIGHTARG = text); CREATE OPERATOR
이제 만든 연산자를 psql에서 확인합니다.
postgres=# select oprnamespace,oprname,
format_type(oprleft,null) as l,
format_type(oprright,null) as r,
oprcode as function
from pg_operator
where oprname = '||'
and format_type(oprleft,null) = 'text'
and format_type(oprright,null) = 'text';
oprnamespace | oprname | l | r | function
--------------+---------+------+------+---------------
11 | || | text | text | textcat
2200 | || | text | text | public.concat
(2개 행)
여기서는 그 연산자의 스키마까지 살펴보았습니다. 왜냐하면, 함수처럼 같은 이름의 연산자가 pg_catalog(11)에도, public(2200)에도 각각 있기 때문에 어느 것이 이번에 만든 연산자인지 확인하기 위해서입니다.
이제 테스트
postgres=# select '하늘' || null; ?column? ---------- (1개 행)
달라진 것이 없습니다. 실패입니다. 분명 연산자를 만들었고, 그 결과로 '하늘'이 나오기를 예상했지만, 그렇지 못했습니다. 원인은 각 스키마들의 검색 우선 순위 정책에 밀려 pg_catalog.|| 연산자가 먼저 사용되었기 때문입니다. 이 문제를 해결 하려면, 연산자에도 스키마 이름을 지정해야 하고, 그것을 다시 이것은 연산자라고 지정하는 예약어를 사용해야 합니다. 이런 형태라면 원래 연산자 오버로드를 하려고 했던 목적에 위배되어버립니다. 이 문제는 search_path 데이터베이스 환경 설정값을 바꿔주면 간단히 풀립니다.
postgres=# select '하늘' operator(public.||) null; ?column? ---------- 하늘 (1개 행) postgres=# set search_path="$user",public,pg_catalog; SET postgres=# select '하늘' || null; ?column? ---------- 하늘 (1개 행)
작업이 마무리 되었습니다. 이제 이런 || 연산에 대해 아주 낯설게 반응했던 데이터베이스를 사용했던 응용 프로그램과 데이터 이전 문제는 한결 편해질 것입니다.
3. 사용자 정의 연산자의 옵션들
지금까지 이야기한 사용자 정의 연산자는 단지 프로그래밍 언어로써의 이야기였습니다. 하지만, 데이터베이스의 연산자는 단지 가독성을 좋게 한다는 기능 뿐만 아니라, 쿼리 실행 계획 최적화와 그 쿼리 결과에 대해서도 영향을 미칩니다.
회원 관리 시스템을 설계 할 때, 사용자 ID가 대소문자 구분이 없어야 한다는 요구 조건이 있다고 가정합니다.
이를 위해 좀 엽기적인 (궁극적으로는 PostgreSQL 데이터베이스의 기능을 최대한 활용한) 설계를 해봅니다.
먼저 itext 형이라는 자료형을 하나 만들고, 회원 ID를 찾는 = 연산자를 text 형 = 연산자오버로드를 하면, 아주 요상한 쿼리들이 작성 될 수 있습니다.
(물론 이 문제는 실무 환경에서는 함수 기반 인덱스를 만들고, where lower(userid) = lower('사용자입력ID') 형태의 쿼리를 사용하는 것이 일반적입니다. 하지만 여기서는 좀더 꼬여서 다른 사용자가 쪽지를 보낼 때 받는 사용자 ID를 사용자가 직접 입력하게 되어 대소문자를 마음대로 썼다고 가정했을 때, 쪽지 테이블의 사용자ID 칼럼의 인덱스도, 두 테이블간의 join 구문에서의 lower() 함수 사용도 아주 빈번하게 일어나겠지요.
이런 문제도 사용자 정의 연산자로 풀면, 마치, PostgreSQL에서는 대소문자를 구분하는 문자열 자료형과 대소문자를 구분하지 않는 문자열 자료형이 따로 있는 것 처럼 만들어 낼 수도 있습니다.
앞에서 이야기 한 것을 복습하는 겸 쭉 쿼리 작업을 진행했습니다. 중간 중간 주석으로 설명합니다.
-- 먼저 text 기반 itext 자료형을 만듭니다.
postgres=# create domain itext as text;
CREATE DOMAIN
postgres=# \dT itext
자료형 목록
스키마 | 이름 | 설명
--------+-------+------
public | itext |
(1개 행)
-- ~* 연산자에서 사용하는 함수를 오버로드
postgres=# create function texticregexeq (itext,itext)
returns bool language internal
immutable
as 'texticregexeq';
CREATE FUNCTION
-- 연산자를 만듭니다.
postgres=# create operator =
(PROCEDURE = public.texticregexeq,
LEFTARG = itext, RIGHTARG = itext);
CREATE OPERATOR
-- 테스트
postgres=# select 'a'::itext = 'A'::itext;
?column?
----------
t
(1개 행)
-- 샘플테이블과 자료입력
postgres=# create table users (userid itext, username text);
CREATE TABLE
postgres=# insert into users values ('Superman', '슈퍼맨');
INSERT 0 1
postgres=# insert into users values ('BatMan', '배트맨');
INSERT 0 1
postgres=# create table memos (fromid itext, toid itext, memo text);
CREATE TABLE
postgres=# insert into memos values ('superman', 'batman', '안녕');
INSERT 0 1
postgres=# insert into memos values ('Superman', 'BatMan', '또 안녕');
INSERT 0 1
postgres=# insert into memos values ('Superman', 'BatMan', '또또 안녕');
INSERT 0 1
postgres=# insert into memos values ('Superman', 'BatMan', '안녕 또');
INSERT 0 1
postgres=# insert into memos values ('Superman', 'BatMan', '안녕 또또');
INSERT 0 1
-- 이제 쿼리 테스트
postgres=# select *
from users u, memos m where u.userid = m.fromid;
userid | username | fromid | toid | memo
----------+----------+----------+--------+-----------
Superman | 슈퍼맨 | superman | batman | 안녕
Superman | 슈퍼맨 | Superman | BatMan | 또 안녕
Superman | 슈퍼맨 | Superman | BatMan | 또또 안녕
Superman | 슈퍼맨 | Superman | BatMan | 안녕 또
Superman | 슈퍼맨 | Superman | BatMan | 안녕 또또
(5개 행)
postgres=# explain select *
from users u, memos m where u.userid = m.fromid;
QUERY PLAN
--------------------------------------------------------------------
Nested Loop (cost=0.00..2.23 rows=5 width=45)
Join Filter: (u.userid = m.fromid)
-> Seq Scan on memos m (cost=0.00..1.05 rows=5 width=27)
-> Materialize (cost=0.00..1.03 rows=2 width=18)
-> Seq Scan on users u (cost=0.00..1.02 rows=2 width=18)
(5개 행)
의도한 대로 잘 된 것 처럼 보입니다. 하지만 데이터베이스 튜닝을 조금 해 본 분이라면, 실행 계획이 뭔가 이상한 것을 눈치챘을 것입니다. 왜냐하면, 저런 형태의 자료량에 저런 쿼리라면, hash join으로 실행계획이 잡혀야 하거든요.
이 부분의 문제를 푸는 것이 바로 사용자 정의 연산자 옵션들입니다. 이 부분은 PostgreSQL 설명서 35.13. 연산자 최적화 정보 페이지에서 자세히 다루고 있습니다.
이 문서를 보고 다시 연산자를 만들어봅니다.
postgres=# drop operator = (itext, itext); DROP OPERATOR postgres=# create operator = (procedure = public.texticregexeq, leftarg = itext, rightarg = itext, commutator = =, restrict = eqsel, join = eqjoinsel, hashes, merges); CREATE OPERATOR
옵션 다섯개를 추가 했습니다. 이만큼의 옵션이 추가 되어야 최적화기가 실행계획을 최적화 할 수 있습니다. 자세한 이야기는 위 링크 페이지를 꼼꼼히 읽어보세요. 이제 쿼리 실행계획을 살펴보겠습니다.
postgres=# explain select *
from users u, memos m where u.userid = m.fromid;
QUERY PLAN
--------------------------------------------------------------------
Hash Join (cost=1.04..2.16 rows=5 width=45)
Hash Cond: (m.fromid = u.userid)
-> Seq Scan on memos m (cost=0.00..1.05 rows=5 width=27)
-> Hash (cost=1.02..1.02 rows=2 width=18)
-> Seq Scan on users u (cost=0.00..1.02 rows=2 width=18)
(5개 행)
postgres=# select *
from users u, memos m where u.userid = m.fromid;
ERROR: could not find hash function for hash operator 1365335
예상한 대로 해당 쿼리는 해시 조인을 최적화된 실행 계획이다고 짰지만, 실행하면 오류를 냅니다. 왜냐하면, 해당 자료형에 대한 해시 함수 정의를 하지 않았기 때문입니다. 해시 함수 지정은 이 연산자에서 하지 않고, 연산자 클래스에서 합니다. 일단 아래와 같이 hashtext() 함수를 이용한 hashitext() 함수를 하나 만들고, 그것을 연산자 클래스에 정의합니다.
postgres=# create function hashitext(itext) returns int4 language sql immutable as 'select hashtext(lower($1))'; CREATE FUNCTION postgres=# create operator class itext_ops default for type itext using hash as operator 1 =, function 1 hashitext(itext); CREATE OPERATOR CLASS postgres=# select * from users u, memos m where u.userid = m.fromid; userid | username | fromid | toid | memo ----------+----------+----------+--------+----------- Superman | 슈퍼맨 | superman | batman | 안녕 Superman | 슈퍼맨 | Superman | BatMan | 또 안녕 Superman | 슈퍼맨 | Superman | BatMan | 또또 안녕 Superman | 슈퍼맨 | Superman | BatMan | 안녕 또 Superman | 슈퍼맨 | Superman | BatMan | 안녕 또또 (5개 행)
이렇게 머지 조인도 의도하는 대로 만들 수 있습니다. 물론 이게 가능하려면, itext 자료형에 대한 정렬 함수도 정의하고, 그것을 btree 인덱스 연산자 클래스에 등록해야 합니다. btree 쪽은 hash 보다는 좀 더 복잡합니다. (당연한 이야기겠지만)
이렇게 사용자 정의 연산자에 대한 가장 좋은 예는 배포판 소스 안에 있는 isn 확장 모듈입니다.
4. 마무리
지금까지 PostgreSQL 사용자 정의 연산자에 대해서 살펴보았습니다. 글을 쓰다가 보니, 사용자 정의 자료형, 사용자 정의 함수, 사용자 정의 인덱스, 연산자 가족, 연산자 클래스 이 모든 것에 대해서 알고 있어야 좀 더 유연하게 사용자 정의 연산자를 쓸 수 있겠다는 생각이 듭니다.
사용자 정의 연산자는 실무에서는 이 글에서와 같이 간단하게 사용되지는 않습니다. 멋진 사용자 정의 자료형을 하나 만들고, 그 자료형을 사용하는 함수, 인덱스, 연산자를 함께 정리해서 공개하려는 이들에게 이 글이 조금이나마 도움이 되었으면 합니다.