pg_extension은 CREATE EXTENSION 명령어를 이용하여 생성된 익스텐션 확장 모듈에 대한 정보를 조회할 수 있는 시스템 카탈로그이다.
시스템 카탈로그가 포함되어 있는 pg_catalog 스키마에 속해있으며, 모든 데이터베이스가 익스텐션 정보를 공유하는 것이 아닌, 해당 데이터베이스에 속해있는 익스텐션만 조회할 수 있다.
pg_extension은 아래와 같은 정보를 담고 있다.
| 컬럼명 | 데이터 타입 | 설명 |
| oid | oid | 익스텐션 객체가 가지고 있는 oid 값 |
| extname | name | 익스텐션의 이름 |
| extowner | oid | 익스텐션 소유자의 oid 값 (pg_authid.oid를 참조) |
| extnamespace | oid | 익스텐션의 하위 객체들(함수, 오퍼레이터, 타입 등) 이 속해있는 스키마 이름(pg_namespace.oid를 참조), 모든 익스텐션이 1개의 스키마를 사용하는 것은 아님!! |
| extrelocatable | bool | 익스텐션의 하위 객체들을 ALTER EXTENSION 명령어를 사용하여 다른 스키마에 속하도록 변경할 수 있는지 여부 |
| extversion | text | 익스텐션의 버전 |
| extconfig | oid[] | 익스텐션 설치 시 테이블이 같이 설치되는 경우 테이블들의 oid , 없을 시 NULL |
| extcondition | text[] | 익스텐션 테이블들(extconfig)에 대한 WHERE-caluse 필터 조건들, 없을 시 NULL pg_dump로 익스텐션 추출 시 해당 조건에 해당하는 데이터만 추출된다. |
extrelocatable이 t(true)인 확장 모듈의 경우, ALTER EXTENSION SET SCHEMA new_schema 명령어를 사용하여 익스텐션에 속해있는 객체들을 다른 스키마로 재배치할 수 있다.
재배치가 가능한 확장모듈인지 확인하는 방법은 설치 후에 시스템 카탈로그에서 조회할 수도 있고, 익스텐션에 포함된 control 파일을 확인할 수도 있다.
아래의 예시는 pg_trgm 모듈의 control 파일이다.
# Redhat 계열 OS에서 postgresql14-contrib 바이너리 설치 시 /usr/pgsql-14/share/extension에 위치
# pg_trgm extension
comment = 'text similarity measurement and index searching based on trigrams'
default_version = '1.6'
module_pathname = '$libdir/pg_trgm'
relocatable = true
trusted = truepg_trgm은 3글자 이상의 문자열에 대해 LIKE 성능을 향상해주는 확장모듈이다.
3글자 미만의 문자열에 대해서는 성능이 역으로 떨어질 수 있으므로 주의하여 사용하여야 한다.
재배치를 수행하게 되면, PostgreSQL 인터널 함수를 호출하여 객체를 참조하고 있는 다른 객체의 참조값을 변경해 주는 것으로 보인다.
재배치 수행 시 참조값이 변경 되는지 확인을 하기 위해 아래와 같이 테스트를 수행 해봤다.
먼저 기본 설치 상태의 extension 목록을 조회하면, 아래와 같이 기본으로 생성되어 있는 plpgsql만 존재한다.
postgres=# SELECT * FROM pg_extension;
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+---------+----------+--------------+----------------+------------+-----------+--------------
14472 | plpgsql | 10 | 11 | f | 1.0 | |
(1 row)
pg_trgm 확장모듈을 설치한다.
search_path가 default 값이기 때문에 public 스키마에 생성된다.
postgres=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION
postgres=# SELECT * FROM pg_extension ;
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+---------+----------+--------------+----------------+------------+-----------+--------------
14472 | plpgsql | 10 | 11 | f | 1.0 | |
43942 | pg_trgm | 10 | 2200 | t | 1.6 | |
(2 rows)
테이블 t1을 생성하고 pg_trgm의 GIN 인덱스용 operator를 사용하는 GIN 인덱스를 생성한다.
search_path가 default 값이기 때문에 public 스키마에 생성된다.
postgres=# CREATE TABLE t1 (
postgres(# col1 text PRIMARY KEY
postgres(# );
CREATE TABLE
postgres=# CREATE INDEX t1_gin ON t1 USING GIN(col1 gin_trgm_ops);
CREATE INDEX
아래와 같이 public 스키마의 t1 테이블에 t1_gin이름의 인덱스가 생성되었고 operator로 gin_trgm_ops를 사용하는 것을 확인할 수 있다.
postgres=# SELECT c.relname, pg_get_indexdef(i.indexrelid) as def
FROM pg_index as i RIGHT OUTER JOIN pg_class as c ON i.indrelid=c.oid
WHERE c.relname='t1';
relname | def
---------+----------------------------------------------------------------
t1 | CREATE UNIQUE INDEX t1_pkey ON public.t1 USING btree (col1)
t1 | CREATE INDEX t1_gin ON public.t1 USING gin (col1 gin_trgm_ops)
(2 rows)
trgm 익스텐션의 객체를 참조하는 함수 test를 생성하고 pg_proc 카탈로그에서 함수 코드를 조회해본다.
postgres=# CREATE OR REPLACE FUNCTION test(IN str text, OUT trgm text)
postgres-# AS $$
postgres$# BEGIN
postgres$# SELECT * FROM show_trgm($1) INTO trgm;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# SELECT proname, prosrc FROM pg_proc WHERE proname='test';
proname | prosrc
---------+----------------------------------------
test | +
| BEGIN +
| SELECT * FROM show_trgm($1) INTO trgm;+
| END; +
|
(1 row)
함수를 호출해도 아래와 같이 정상적으로 결과가 출력된다.
postgres=# SELECT * FROM test('test');
trgm
-----------------------------
{" t"," te",est,"st ",tes}
(1 row)
새로운 스키마 test_schema를 생성하고, ALTER EXTENSION을 이용하여 pg_trgm을 test_schema로 옮겨본다.
postgres=# CREATE SCHEMA test_schema;
CREATE SCHEMA
postgres=# ALTER EXTENSION pg_trgm SET SCHEMA test_schema;
ALTER EXTENSION
이제 다시 인덱스가 참조하고 있는 값을 확인해보자.
postgres=# SELECT c.relname, pg_get_indexdef(i.indexrelid) as def
FROM pg_index as i RIGHT OUTER JOIN pg_class as c ON i.indrelid=c.oid
WHERE c.relname='t1';
relname | def
---------+----------------------------------------------------------------------------
t1 | CREATE UNIQUE INDEX t1_pkey ON public.t1 USING btree (col1)
t1 | CREATE INDEX t1_gin ON public.t1 USING gin (col1 test_schema.gin_trgm_ops)
(2 rows)기존에는 GIN 인덱스가 public.gin_trgm_ops를 참조하고 있었는데, 지금은 test_schema.gin_trgm_ops를 참조하고 있는 것을 확인할 수 있다.
ALTER EXTENSION SET SCHEMA로 인한 재배치가 성공적으로 이루어 졌다.
함수의 내용은 어떻게 바뀌었고, 함수 호출시에는 어떨까?
postgres=# SELECT proname, prosrc FROM pg_proc WHERE proname='test';
proname | prosrc
---------+----------------------------------------
test | +
| BEGIN +
| SELECT * FROM show_trgm($1) INTO trgm;+
| END; +
|
(1 row)
postgres=# SELECT * FROM test('test');
ERROR: function show_trgm(text) does not exist
LINE 1: SELECT * FROM show_trgm($1)
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT * FROM show_trgm($1)
CONTEXT: PL/pgSQL function test(text) line 3 at SQL statement함수의 소스에서 참조하고 있는 값은 변경되지 않는 것으로 확인된다.
익스텐션의 스키마를 변경 한다면, 이 점을 유의해야 할 것 같다.
최초 함수 생성시에 객체가 속한 스키마를 명시해주지 않고 생성한 다음, search_path를 변경하여 사용하는 것이 더 쉬운 방법일 수도 있을 것 같다.
