업무를 진행하다 보니 특정 프로세스상에서 기존 테이블 스키마와 동일한 테이블을 동적으로 생성해야 할 경우가 생겼었다.
구현 시 요구되었던 조건은 다음과 같았다.
1. 접근 계정은 조회(select), 생성(create) 권한만 있고, 기본 public 스키마 내 함수 이외에 실행(execute) 권한은 없다.
2. psql 콘솔, function, stored procedure를 사용하지 않고, 단순 쿼리만으로 DDL을 생성해야 함.
3. 테이블 및 컬럼에 대한 comment문을 생성할 필요는 없지만, index 생성구문은 필요하다.
일단 PostgreSQL 메뉴얼 문서를 찾아 살펴보니, 시스템 카탈로그 와 시스템 테이블 내에 스카마, 테이블, 컬럼 등과 같은 다양한 정보가 기록되어 있음을 알 수 있었다.
하지만 지금까지 시스템 연관 테이블 정보를 굳이 깊게 살펴볼 일이 없어 관련 지식이 얕았고, 빈번하게 쓰이는 부분도 아니다 보니, 관련 내용 또한 찾기 힘들었다. 결국 직접 하나하나 목차를 살펴가며 필요한 부분을 찾다 보니 시간은 조금 걸려버렸다.
하지만 몇번의 삽질과 테스트 끝에 결국 테이블 구성에 필요한 컬럼 정보와 인덱스 생성 구문을 얻어낼 수 있었고, 테이블 생성 DDL문을 만들어 해당 시스템에 적용하여 잘 마무리하였다.
앞으로 누군가 배치시스템이나 데이터 모니터링 시스템을 구축하려 할 때, 내 경험이 참고가 되길 바라며 당시 진행했었던 내용을 정리해볼까 한다.
설명하기에 앞서, 혼란을 피하기 위해 주로 쓰이는 명칭을 통일하도록 하자.
긴 단어의 경우에는 축약어로, 특정 영어 단어의 경우에 일반적으로 쓰이는 한글명칭을, 최대한 일관되게 쓰도록 하겠다.
데이터베이스 = DB
데이터베이스(관리)시스템 = DBMS
table = 테이블
column, 열 = 컬럼
sequence = 시퀀스
view = 뷰
DB접속 및 SQL문 질의를 위한 DB클라이언트 툴은 DBeaver 를 사용하였다.
무료툴임에도 매우 다양한 DB를 지원하고, 수많은 개발자들이 참여하여 지금 이 순간에도 빠르게 기능 개선이 되어가고 있다.
또한 공간데이터를 OSM(OpenStreetMap)에 올려 볼 수 있는 view를 제공하기도 해서, 개인적으로 DataGrip 보다 자주 애용하고 있다.
DBeaver Community | Free Universal Database Tool
DBeaver Universal Database Tool Free multi-platform database tool for developers, database administrators, analysts and all people who need to work with databases. Supports all popular databases: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase,
dbeaver.io
시스템 카탈로그?
일종의 데이터 사전으로, DBMS을 효율적으로 관리하기 위해 DB 내 여러 정보를 각각의 시스템 테이블들에 나누어 저장/관리하고 있다.
pg_catalog 스키마에 존재하는 접두사 pg_ 로 시작하는 시스템 테이블에는, 여러 테이블과 테이블 내 컬럼에 대한 정보, 각종 부가정보 및 규칙들이 저장되어있다.
| 테이블명 | 설명 |
| pg_class |
테이블, 인덱스, 시퀀스, 뷰 객체에 대한 자세한 정보를 담고 있다. relkind 컬럼 값을 참조하면 어떤 종류의 객체인지 알 수 있다. >> 테이블 = r / 인덱스 = i / 시퀀스 = P / 뷰 = v
이 외에 다른 형태의 뷰나 테이블 종류도 relkind 컬럼 값을 보면 알 수 있다. |
| pg_attribute |
테이블에 존재하는 컬럼에 대한 정보를 담고 있다. - attrelid 값은 pg_class 테이블의 oid 컬럼 값을 참조한다. - 알반 컬럼은 attmun 값 1부터 번호가 매겨진다. 단, 시스템 컬럼인 경우에는 음수가 붙는다. |
| pg_attrdef |
기본값이 설정된 컬럼에 대한 정보를 담고 있다. 예를 들면, 해당 컬럼의 default value 가 설정되어있는 경우다.
기본적인 default값 설정뿐 아니라, 컬럼 타입이 bigserial 인 경우에는 내부적으로 시퀀스를 가지고 해당 시퀀스의 nextval() 값을 호출하므로 해당 컬럼에 대한 정보 또한 이곳에 담겨있다.
마찬가지로 테이블내 attrelid 값은 pg_class 테이블의 oid 컬럼 값을 참조한다. adbin 컬럼에는 설정된 기본값이 저장되어 있는데, 내부형식으로 저장되어 어떤값인지 알 수 없다. pg_get_expr 함수를 호출하여 해당 컬럼 값을 조회하면, 일반적인 형식으로 볼 수 있다. |
|
pg_constraint |
테이블 제약조건(constraint) 정보를 담고 있다. contype 컬럼 값을 참고하자. >> PK = p / FK = f / UNIQUE = u / CHECK = c |
| pg_indexes |
DB에 존재하는 인덱스정보를 담고 있다. indexdef 컬럼에 해당 인덱스 생성 구문 잘 들어가있다. 편하게 그대로 사용해주자. |
각 테이블에 대해서 간략히 살펴보았으니, 이제 순차적으로 우리가 원하는 정보를 조회해보도록 하자.
여기서 예로 들 테이블은, PostGIS 설치 시 public 스키마에 생성되는 spatial_ref_sys - 좌표체계(EPSG코드 정의) 테이블이다.
step 1) public 스키마에 존재하는 spatial_ref_sys 테이블의 oid 컬럼 값 획득
relnamespace = 'public' | relname = 'spatial_ref_sys' | relkind = 'r' 로 질의해보자.
SELECT oid, relname, relnamespace::regnamespace, relnamespace, relkind
FROM pg_class
WHERE relnamespace::regnamespace::varchar = 'public'
AND relname::varchar = 'spatial_ref_sys'
AND relkind = 'r';
'spatial_ref_sys' 테이블의 oid 값은 63336 이다.
자, 그런데 여기서 스키마명을 설정할 때, 왜 relnamespace::regnamespace::varchar = 'public' 과 같은 형식인지 궁금할 것이다.
결론부터 말하자면, oid에 대한 별칭(alias)을 사용한 질의 방법이다.
PostgreSQL은 내부적으로 여러 시스템 테이블의 기본 키로 oid를 사용하는데, 이 oid에 대한 몇몇 별칭(alias)을 제공하고 있다.
ex) regclass , regtype , regrole , regnamespace 등등...
좀 더 자세한 사항은 PostgreSQL - Object Identifier Types 이곳을 참고하길 바란다.
pg_class, pg_namespace 테이블에 존재하는 oid에 대해 설명해보면,
- pg_class 테이블의 oid 컬럼 값은 객체(테이블, 인덱스, 뷰 등등) 각각의 고유한 키 값이며, 다른 시스템 테이블에서 이를 참조하기도 한다.
- pg_namespace 테이블에서의 oid 값은 스키마 각각의 고유한 키 값인 것이다.
이전 쿼리 결과에서 relnamespace = 2200 이 나왔는데, 이 값은 pg_namespace 테이블의 'public' 스키마의 oid 값인 것이다.
결국, 위 쿼리는 내부적으로 'public' 스키마의 oid 값이 2220 인 것을 모르지만, 해당 oid 에 대한 별칭인 regnamespace를 사용해 좀 더 편리하고 직관적으로 조회한 것이다.
마지막으로 정말 맞는지 스키마의 oid 값을 확인해보자.
SELECT oid, nspname
FROM pg_namespace
WHERE nspname = 'public';
이제 다음 스탭으로 넘어가 'spatial_ref_sys' 테이블 내 컬럼 정보를 구해보자.
step 2) 'spatial_ref_sys' 테이블 내 컬럼 정보 획득
step 1) 에서 획득한 'spatial_ref_sys' 테이블의 oid 값 63336 , 별칭(alias)으로도 질의해보자.
--1. oid 로 직접 질의
SELECT attrelid, attname, pg_catalog.format_type(pa.atttypid, pa.atttypmod), atttypid, atttypmod
FROM pg_catalog.pg_attribute pa
WHERE attnum > 0 AND NOT pa.attisdropped
AND attrelid = 63336;
--2. oid alias로 질의
SELECT attrelid, attname, pg_catalog.format_type(pa.atttypid, pa.atttypmod), atttypid, atttypmod
FROM pg_catalog.pg_attribute pa
WHERE attnum > 0 AND NOT pa.attisdropped
AND attrelid::regclass::varchar = 'spatial_ref_sys';
배운 사람답게 2가지 방법으로 모두 질의해보았다. 결과는 둘 다 동일할 것이다.
여기서 pg_catalog.format_type(pa.atttypid, pa.atttypmod) 이 부분만 추가로 살펴보겠다.
format_type 은 시스템에서 제공하는 기본 함수로, 테이터 타입의 명칭과 길이를 얻을 때 사용한다.
- 호출시 2개의 인자를 필요로 하는데, 데이터 타입의 oid 와 타입의 길이 값을 넣어 호출한다.
- 특별히 길이를 설정되지 않은 타입의 경우, 2번째 인자값은 -1 이다.
format_type(type_oid, typemod)
컬럼의 default 값 설정 정보 조회
'spatial_ref_sys' 테이블 내 default 값이 설정된 컬럼은 없지만, 만약 설정된 컬럼이 있으면 해당 정보도 찾아야 한다.
확인을 위해서 default값 컬럼을 가진 임시 테이블 'test_info' 을 만들어보자.
CREATE table public.test_info (
info_id bigserial NOT NULL,
info_name varchar(255) NULL,
is_use bool NULL DEFAULT false,
ref_id int8 NULL,
CONSTRAINT test_info_pk PRIMARY KEY (info_id),
CONSTRAINT test_info_uk UNIQUE (info_name)
);
CREATE INDEX test_info_idx1 ON public.test_info USING btree (ref_id);
잘 만들어졌다면, 바로 이어서 컬럼의 default 값 설정 정보를 조회해보자. 편의상 별칭(alias)으로 질의해보았다.
SELECT adrelid, adnum, pg_catalog.pg_get_expr(adbin, adrelid), adsrc, adbin
FROM pg_attrdef
WHERE adrelid::regclass::varchar = 'test_info';
쿼리 결과를 살펴보자.
adnum 컬럼은 테이블내 컬럼의 순번을 나타낸다. 1번째 info_id, 3번째 is_use 컬럼이 default 값을 가지고 있음을 보여준다.
pg_get_expr 함수는, 이전에 설명한 format_type과 마찬가지로 시스템 기본 함수다.
- 1번째 인자는 내부데이터 형식의 문자열, 2번째는 해당 테이블의 oid 값을 넣어 호출한다.
- 호출시 1번째 인자로 전달된 내부데이터를 디컴파일한 문자열을 리턴한다.
pg_get_expr(pg_node_tree, relation_oid)
그런데 여기서 pg_get_expr 함수를 통해 나온 default 값 형식과 adsrc 컬럼 값 사이에 차이가 없음을 알 수 있다.
궁금해 관련 내용을 찾아보니, 다음과 같은 내용을 확인 할 수 있었다.
pg_attrdef.adsrc열 사용은 오랫동안 권장하지 않았으며, PostgreSQL 12에서 열이 제거되었습니다.
권장하지 않은 이유까지는 찾을 수 없었지만, v12부터는 더 이상 참조할 수 없으므로 pg_get_expr 를 사용하도록 하자.
컬럼의 Constraint 설정 정보 조회
바로 전에 만든 임시 테이블 'test_info' 내 컬럼 제약조건(Constraint) 정보를 조회해보자.
SELECT conname, contype
, conindid, conindid::regclass::varchar
, connamespace, connamespace::regnamespace::varchar
FROM pg_catalog.pg_constraint
WHERE connamespace::regnamespace::varchar = 'public'
AND conrelid::regclass::varchar = 'test_info';
해당 컬럼 값에 regnamespace, regclass 둘 중 어떤 별칭(alias)을 써야할지 아직 조금 헷갈린다면,
위에 링크 했던 PostgreSQL - Object Identifier Types 문서를 다시 한번 읽어보길 권한다.
step 3) 'spatial_ref_sys' 테이블 내 index 생성구문 조회
Mysql, Oracle 에서 지금과 비슷한 작업을 했던 경험이 있다.
그때 당시 index 생성 구문을 만들기 위해 많은 고민을 했었는데, PosgreSQL에서는 생각보다 훨씬 쉽게 처리할 수 있었다.
--1. 'spatial_ref_sys' 테이블 내 index 정보
SELECT indexname, indexdef
FROM pg_catalog.pg_indexes
WHERE schemaname='public' AND tablename='spatial_ref_sys';
--2. 'test_info' 테이블 내 index 정보
SELECT indexname, indexdef
FROM pg_catalog.pg_indexes
WHERE schemaname='public' AND tablename='spatial_ref_sys';
'spatial_ref_sys' 테이블에는 pk 에 대한 index 생성구문 밖에 없어서, 'test_info' 테이블의 조회 결과를 스크린샷으로 남겨보았다.
test_info_idx1
- 일반(normal) index. (ref_id 컬럼)
test_info_uk
- 고유(unique) index. (info_name 컬럼)
test_info_pk
- PK설정시 자동으로 생성된 고유(index) index. (info_id 컬럼)
indexdef 컬럼에 있는 문자열을 활용하면 추가적인 처리없이 정말로 간단하게 index 생성 구문을 만들어 낼 수 있다.
테이블 DDL생성 쿼리 작성
지금까지 파악한 내용을 기반으로 테이블 구성정보(컬럼명, 타입, default값, constraint, index정보 등등) 질의문을 작성해보았다.
사용된 질의문을 조금 응용해보면, 최종적으로 아래와 같은 테이블 DDL 생성 쿼리를 만들 수 있다. (참 쉽죠?)
생성 대상은 역시나 풍부한(default값도 있고, uk도 있는) 케이스를 가진 public.test_info 테이블로 하였다.
SELECT string_agg(ddl_txt::text, E'\n')
FROM (
(
SELECT 'CREATE TABLE ' || 'public.test_info' || '(' ||
string_agg(pa.attname || ' ' || pg_catalog.format_type(pa.atttypid, pa.atttypmod) ||
coalesce((SELECT ' DEFAULT '|| substring(pg_get_expr(paf.adbin, paf.adrelid) for 128)
FROM pg_attrdef paf
WHERE paf.adrelid = pa.attrelid AND paf.adnum = pa.attnum AND pa.atthasdef), '')
||
CASE WHEN pa.attnotnull = true THEN
' NOT NULL'
ELSE
'' END,E'\n, ')
||
coalesce((SELECT E'\n, ' || 'CONSTRAINT' || ' ' || conindid::regclass::varchar || ' ' || pg_get_constraintdef(oid)
FROM pg_constraint
WHERE connamespace::regnamespace::varchar = 'public'
AND conrelid::regclass::varchar = 'test_info'
AND contype = 'p'), '')
|| E'\n);' AS ddl_txt
FROM pg_attribute pa
JOIN pg_class pc ON pa.attrelid=pc.oid
WHERE pc.relnamespace::regnamespace::varchar='public'
AND pc.relname::varchar = 'test_info'
AND pa.attnum > 0 AND NOT pa.attisdropped AND pc.relkind='r'
GROUP BY pa.attrelid
)
UNION ALL
(
SELECT string_agg(indexdef || ';' ::text, E'\n') AS ddl_txt
FROM pg_indexes
WHERE schemaname='public' AND tablename='test_info'
)
) AS t
대부분 중복되는 내용이므로, 빠르게 핵심적인 내용만 정리해 추려본다.
1. pg_attribute, pg_class 두 테이블 join 후 where 조건으로 필터링.
- pg_class : 스키마명(relnamespace), 테이블명(relname), 객체종류(relkind)
- pg_attribute : 컬럼순번(attnum), 삭제되었는지 여부(attisdropped)
2. pg_attrdef 테이블로부터 해당 컬럼의 default 값 정보 획득.
3. pg_constraint 테이블에서 PK constraint 정보 획득.
- UNIQUE constraint 정보는 취하지 않는다. index구성 정보와 constraint 둘 중 한군데만 포함되면 되기 때문이다.
- PK constraint의 경우, index구성정보에도 있지만 constraint 를 구성해야 하는 이유는,
테이블 무결성을 보장하고 unique index와 다르게 not null을 허용하지 않기 위함이다.
- 이번 예제에서는 FK, CHECK constraint 정보를 생성하는 구문은 포함되어있지 않다.
그렇지만 지금까지 설명한 내용을 잘 이해했다면, 손쉽게 해당 내용을 추가할 수 있을 것이다.
4. string_agg 함수를 사용하여, 1~3 단계에서 조회된 컬럼 정보를 단일 테이블 생성 DDL 문자열로 만든다.
5. pg_indexes 테이블로부터 index 생성구문 획득.
- 마찬가지로 string_agg 함수를 호출하여 단일 문자열로 만든다.
6. 4번에서 생성된 테이블 DDL 문자열과 5번 index 생성 문자열을 union all 한다.
여러분은 이제 고작 원 쿼리로 한 테이블의 생성 DDL문을 만들 수 있게 되었다. 짝짝짝.
마치며...
핵심만 요약해 간단하고 빠르게 정리해보려 했는데, 글을 쓰다 보니 생각보다 꽤 많은 시간이 걸려버렸다.
여기까지 서툴고 긴 글 읽어주셔서 감사드리며, 내용상 틀린 점이나 보충할 사항 있으면 알려주면 잘 보완하도록 하겠습니다.