PostgreSQL — это мощная система управления реляционными базами данных с открытым исходным кодом. Она дополняет язык SQL новыми фичами. СУБД определяется не только ее производительностью и встроенными фичами, но и способностью поддерживать персонализированную/дополнительную, специфичную для пользователя функциональность. Некоторые из этих возможностей могут быть представлены в виде конструкций или модулей базы данных, таких как хранимые процедуры или функции, но их объем обычно ограничен функциональностью, предоставляемой СУБД. Например, как вы напишете кастомное приложение для анализа запросов, которое будет находиться внутри вашей СУБД?

Для поддержки таких вариантов PostgreSQL предоставляет подключаемую (pluggable) архитектуру, позволяющую устанавливать расширения. Расширения могут состоять из конфигурационного (управляющего) файла, комбинации SQL-файлов и динамически загружаемых библиотек.

Это означает, что вы можете написать свой собственный код в соответствии с определенными рекомендациями расширения и подключить его к инстансу PostgreSQL, не изменяя фактическое дерево кода PostgreSQL. По своему определению расширение увеличивает возможности PostgreSQL, а более того, оно дает вам возможность взаимодействовать с внешними элементами. Этими внешними элементами могут быть другие системы управления базами данных, такие как ClickHouse, Mongo или HDF (обычно их называют обертками сторонних данных (foreign data wrappers, FDW)), или другие интерпретаторы или компиляторы (что позволяет нам писать функции базы данных на другом языке, например, Java, Python, Perl или TCL и т.д.). Другим потенциальным вариантом использования расширения может быть обфускация кода, которая позволяет защитить ваш сверхсекретный код от посторонних глаз.

Создайте свое собственное расширение

Чтобы создать собственное расширение, вам не нужна полная база кода PostgreSQL. Вы можете собрать и инсталлировать расширение, используя установленный PostgreSQL (для этого может потребоваться поставить пакет devel RPM или Debian). Более подробную информацию о расширениях можно найти в официальной документации PostgreSQL[1]. В каталоге contrib исходника PostgreSQL доступно множество расширений для различных фич. Помимо contrib, в интернете можно найти множество расширений, не являющихся частью исходного дерева PostgreSQL. pg_stat_statements, PL/pgSQL и PostGIS являются примерами наиболее известных или широко используемых расширений.

Общедоступные расширения PostgreSQL можно разделить на четыре основные категории:

• Поддержка нового языкового расширения (PL/pgSQL, PL/Python и PL/Java).

• Расширения типов данных, где можно ввести новые (Hstore, cube и hstore)

• Различные расширения (папка contrib содержит множество разнообразных расширений)

• Расширение Foreign Data Wrapper (postgres_fdw, mysqldb_fdw, clickhousedb_fdw).

Существует четыре основных типа файлов, необходимых для создания расширения:

• Makefile: Который использует инфраструктуру сборки PGXS PostgreSQL для расширений .

• Управляющий файл (Control File): содержит информацию о расширении.

• Файл(ы) SQL: Если расширение обладает каким-либо SQL-кодом, он может находиться в SQL-файлах формы (опционально).

• Код C: Общий объект, который мы хотим собрать (опционально).

Расширение Makefile 

Для компиляции C-кода нам нужен make-файл. Это очень простой make-файл, за исключением "PGXS", который является инфраструктурным make-файлом PostgreSQL для создания расширений. Подключение "PGXS" осуществляется вызовом бинарного файла pg_config с флагом "-pgxs". Подробнее с этим файлом можно ознакомиться на GitHub[2].

Это пример make-файла, который можно использовать для компиляции кода на языке C.

EXTENSION = log
MODULE_big = log
DATA = log--0.0.1.sql
OBJS = log.o 
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Расширение Control File 

Этот файл должен быть назван как [EXTENSION NAME]. control file ([ИМЯ РАСШИРЕНИЯ]. управляющий файл). Управляющий файл может содержать множество опций, которые можно найти в официальной документации [3]. Но в данном примере я использовал несколько основных.

comments: Комментарии о расширении.

default_version: Это SQL-версия расширения. Данная информация содержится в имени SQL-файла.

relocatable (перемещаемый):  Сообщает PostgreSQL, можно ли переместить содержащиеся объекты в другую схему.

module_pathname: Эта информация заменяется на реальный путь к lib-файлу.

comment = 'PostgreSQL Utility Command Logger
'default_version = '0.0.1'
relocatable = true
module_pathname = '$libdir/log'

Содержимое файла можно увидеть с помощью команды psql \dx psql.

postgres=# \dx log      
List of installed extensions Name   | Version | Schema |       Description 
------------------------------------+---------+--------+---------------------------------- 
log                                 | 0.0.1   | public | PostgreSQL Utility Command Logger

Расширение SQL-файл 

Это отображаемый файл, который я использовал для маппинга функции PostgreSQL с соответствующей C-функцией. Всякий раз, когда вы вызываете функцию SQL, будет вызвана соответствующая функция C. Имя файла должно быть [EXTENSION NAME]-[default-version].sql. Это та же самая default_version, которая определена в управляющем файле.

CREATE FUNCTION pg_all_queries(OUT query TEXT, pid OUT TEXT)
RETURNS SETOF RECORDAS 'MODULE_PATHNAME', 
'pg_all_queries'
LANGUAGE C STRICT VOLATILE;

Расширение C-код 

Существует три вида функций, которые вы можете написать на языке c.

Первый — это когда вы вызываете свою c-код функцию, используя SQL-функцию, написанную в SQL-файле расширения.

Второй тип функций — обратные вызовы (колбэки). Вы регистрируете такой колбэк, присваивая указатель функции. Здесь нет необходимости в SQL-функции. Вы вызываете эту функцию автоматически при наступлении определенного события.

Третий тип функций вызывается автоматически, даже без регистрации. Это происходит при таких событиях, как время загрузки/выгрузки расширения и т.д.

Это C-файл, содержащий определение кода C. Не существует ограничений ни по имени, ни по количеству C-файлов.

#include "postgres.h"
/* OS Includes */
/* PostgreSQL Includes */
PG_MODULE_MAGIC;
void _PG_init(void);
void _PG_fini(void);
Datum pg_all_queries(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(pg_all_queries);
static void process_utility(PlannedStmt *pstmt, const char *queryString,ProcessUtilityContext context,ParamListInfo params,QueryEnvironment *queryEnv,DestReceiver *dest,       char *completionTag);

Для расширения необходимо включить postgres.h. Вы можете включить другие файлы PostgreSQL по мере необходимости. PG_MODULE_MAGIC — макрос, который необходимо включить в C-файл для расширения. _PG_init и _PG_fini — это функции, которые вызываются при загрузке или выгрузке расширения соответственно.

Вот пример функций загрузки и выгрузки расширения.

void _PG_init(void)
{
    /* ... C code here at time of extension loading ... */
    ProcessUtility_hook = process_utility;
}

Void _PG_fini(void)
{
    /* ... C code here at time of extension unloading ... */
}

Вот пример колбэк-функции, которую можно запускать всякий раз, когда вы вызываете утилиту, например, любой DDL-оператор. Переменная "queryString" содержит собственно текст запроса.

static void process_utility(PlannedStmt *pstmt, 
                           const char *queryString,
                           ProcessUtilityContext context,
                           ParamListInfo params,
                           QueryEnvironment *queryEnv,DestReceiver *dest,
                           char *completionTag)
{
    /* ... C code here ... */
    standard_ProcessUtility(pstmt,   
                            queryString,   
                            context,   
                            params,   
                            queryEnv,   
                            dest, 
                            completionTag);
    /* ... C code here ... */
}

Наконец, вот пример C-функции, которая вызывается при помощи определяемой пользователем SQL-функции. Это внутренне вызывает C-функцию, содержащуюся в нашем общем объекте.

Datum pg_all_queries(PG_FUNCTION_ARGS)
{
    /* ... C code here ... */
    tupstore = tuplestore_begin_heap(true, false, work_mem);
    /* ... C code here ... */     
    values[0] = CStringGetTextDatum(query);
    values[1] = CStringGetTextDatum(pid);
    /* ... C code here ... */
    tuplestore_donestoring(tupstore);
    return (Datum) 0;
}

Компиляция и установка

Перед компиляцией необходимо установить PATH для каталога bin PostgreSQL, если в путях системы нет доступа к pg_config.

export PATH=/usr/local/pgsql/bin:$PATH

make USE_PGXS=1

make USE_PGXS=1 install

Вывод

Теперь мы можем использовать наше расширение с помощью простого SQL-запроса. Ниже приведен вывод, который получается непосредственно из расширения, написанного на языке программирования C.

postgres=# select * from pg_all_queries();          
query                      | pid 
--------------------------+|------ 
create table foo(a int);  +| 8196 
create table bar(a int);  +| 8196 
drop table foo;           +| 8196 
(3 rows)

Я надеюсь, что этот пример послужит отправной точкой для создания более полезных расширений, которые не только помогут вам и вашей компании, но и дадут возможность поделиться и помочь развитию сообщества PostgreSQL.

Полный текст примера можно найти на Github[4].

[1]: https://www.postgresql.org/docs/current/external-extensions.html
[2]: https://github.com/postgres/postgres/blob/master/src/makefiles/pgxs.mk
[3]: https://www.postgresql.org/docs/9.1/extend-extensions.html
[4]: https://github.com/ibrarahmad/Blog-Examples/tree/master/log

Приглашаем на открытое занятие «Инструментарий UNIX-разработчика: исправляем утечку памяти в curl». На нем рассмотрим важные элементы инструментария разработчика под UNIX-подобными ОС и с их помощью продиагностируем и исправим утечку памяти в библиотеке для работы с HTTP/2 libcurl. Регистрируйтесь по ссылке.