Здравствуйте, читатели! Многие из Вас сталкивались с вынесением бизнес-логики в СУБД в виде хранимых функций/процедур, облегчая клиент. В этом есть как и преимущества, так и недостатки. Сегодня я бы хотел рассказать Вам как создавать хранимые функции в PostgreSQL, написанные на языке C. В статье будут самые основы, которые необходимо знать для начала работы с ними.
Описание пользовательских функций
На данный момент PostgreSQL позволяет определять пользовательские функции следующих типов:
- SQL-функции
- функции на языке C
- функции на процедурных языках (plpgsql, pltcl, plperl и т.д.)
SQL-функции — это функции, в теле которых есть один или несколько SQL-запросов, при этом возращаемым результатом будет результат последнего запроса. При этом, если возращаемый результат не void, допускается использование INSERT, UPDATE, или DELETE с конструкцией RETURNING.
Функции на языке C бывают статически и динамически загружаемые. Статически загружаемые (также называемые внутренние функции) создаются на сервере при инициализации кластера базы данных, динамически загружаемые — подгружаются сервером по требованию.
Функции на процедурных языках требуют создания соответсвующих расширений, причем некоторые из языков могут быть двух видов — доверенные и не доверенные (для последних отсутствует возможность ограничить действия пользователя). В базовой поставке PostgreSQL идут plpgsql, pltcl, plperl и plpython, список других языков можно посмотреть тут. Расширения для процедурных языков создаются через SQL:
CREATE EXTENSION pltcl; -- создание доверенного расширения pltcl
CREATE EXTENSION pltclu; -- создание не доверенного расширения pltcl
Или через консоль (plpython доступен только в не доверенном виде):
createlang plpythonu
Динамически загружаемые функции на C
Динамически загружаемые функции на C содержатся в динамически загружаемых (или разделяемых) библиотеках, которые подгружаются при первом вызове функции. Пример создания такой функции:
CREATE OR REPLACE FUNCTION grayscale ( r double precision, g double precision, b double precision )
RETURNS double precision AS
'utils', 'grayscale'
LANGUAGE C STRICT;
В этом примере создается функция grayscale (имеющая три float параметра и возращающая float), которая находится в динамической библиотеке utils. Ключевое слово STRICT используется для того, чтобы функция возвращала NULL, если хотя бы один из аргументов равен NULL Если не указан абсолютный путь, подразумевается каталог, указанный в переменной dynamic_library_path, просмотреть значение которой можно вот так:
SELECT current_setting ( 'dynamic_library_path' );
При этом если значение переменной или пути к динамической библиотеке начинается с $libdir, то $libdir заменяется на путь к каталогу, содержащему библиотеки PostgreSQL, узнать который можно, используя консольную команду:
pg_config --pkglibdir
Так как загрузка библиотеки производится с правами пользователя, под которым запущен демон PostgreSQL (как правило, это postgres), то этот пользователь должен иметь права на доступ к библиотеке.
Для функций существуют два типа соглашения: версии 0 (устаревшая) и версии 1. Функции версии 0 не портабельны и имеют ограниченный функционал, поэтому далее подразумеваются функции версии 1. Для указания, что мы используем версию 1, функцию, перед ее определением, необходимо пометить специальным макросом:
PG_FUNCTION_INFO_V1(grayscale);
Структура динамический библиотеки
Каждая библиотека обязана иметь некий магический блок (один, вне зависимости от количества файлов), чтобы была возможность обнаружить несоответствия, к примеру старшей версии сервера PostgreSQL и версии PostgreSQL, с которой собрана библиотека. Этот блок объявляется так:
#include <fmgr.h>
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
При необходимости можно определить функцию инициализации _PG_init (не имеющую параметров и возращающую void), которая вызывается после загрузки библиотеки, и функцию завершения _PG_fini (имеющую такую же сигнатуру как и _PG_init), которая вызывается перед выгрузкой библиотеки. Обратите внимание, в документации указано, что на данный момен библиотеки не выгружаются, поэтому функция _PG_fini никогда не будет вызвана. Пример функций:
void _PG_init()
{
createLog();
}
void _PG_fini()
{
destroyLog();
}
Функции в библиотеке имеют определенный вид, для параметров, получения аргументов, возрата результата и некоторых других операций предусмотрены специальные макросы (описаны подробней ниже):
Datum grayscale(PG_FUNCTION_ARGS)
{
float8 r = PG_GETARG_FLOAT8(0);
float8 g = PG_GETARG_FLOAT8(1);
float8 b = PG_GETARG_FLOAT8(2);
PG_RETURN_FLOAT8(0.299 * r + 0.587 * g + 0.114 * b);
}
Типы данных
Базовые типы данных, используемые в функциях делятся на три вида:
- с фиксированнной длиной, передаваемые по значению
- с фиксированнной длиной, передаваемые по указателю
- с переменной длиной, передаваемые по указателю
Типы первого вида могут иметь размер 1, 2 или 4 байта (или же 8, если sizeof(Datum) на вашей платформе равен 8). При определении собственных типов (к примеру, через typedef) вы должны быть уверены, что их размер одинаков на всех архитектурах.
Типы с фиксированной длиной, передаваемые по указателю, представляют собой структуры. Выделять память для них (и типов третьего вида), необходимо, используя palloc, к примеру:
typedef struct
{
float r, g, b, a;
} Color;
Color *color = (Color*)palloc(sizeof(Color));
Для типов третьего вида необходимо определить поле (4 байта) под хранение размера всего типа (размер данных + размер поля) и, собственно, сами данные, непрерывно расположеные за этим полем. Сделать это можно, используя структуру, вида:
typedef struct
{
int32 length;
char data[1];
} text;
Значение поля с размером типа устанавливается неявно, с помощью макроса SET_VARSIZE. Другие макросы для работы с типами переменной длины, передаваемыми по указателю:
char data[10];
...
text *string = (text*)palloc(VARHDRSZ + 20); // VARHDRSZ - размер поля с длиной данных
SET_VARSIZE(string, VARHDRSZ + 20); // SET_VARSIZE - установка размера типа
memcpy(VARDATA(string), data, 10); // VARDATA - указатель на данные типа
Соответствие между типами в функциях на C и SQL указаны в этой таблице.
Касательно типов, передаваемых по указателю, нужно отметить, что нельзя изменять данные, на которые указывает этот указатель, так как это могут быть данные, непосредственно находящиеся на диске, что может привести к их повреждению. Последствия будут не совсем радостные.
Структура функции
Сигнатура функции должна иметь такой вид:
Datum grayscale(PG_FUNCTION_ARGS);
Datum — это специальный тип для возращаемого значения функции, по сути являющийся typedef указателя. Макрос PG_FUNCTION_ARGS разворачивается в указатель на структуру, содержащую метаинформацию про параметры функции: контекст вызова, является ли значение NULL и прочее. Доступ к аргументам функции осуществляется при помощи макросов PG_GETARG_*:
float8 r = PG_GETARG_FLOAT8(0); // первый аргумент с типом float8
int32 x = PG_GETARG_INT32(1); // второй аргумент с типом int32
text *msg = PG_GETARG_TEXT_P(2); // третий аргумент с типом text*
Если SQL-функция объявлена без STRICT, с помощью PG_ARGISNULL можно проверить, является ли значение аргумента NULL. Вернуть же результат функции как NULL можно, через PG_RETURN_NULL. В качестве примера, посмотрим, как выглядит реализация функции без STRICT:
Datum grayscale(PG_FUNCTION_ARGS)
{
if (PG_ARGISNULL(0) || PG_ARGISNULL(1) || PG_ARGISNULL(2))
{
PG_RETURN_NULL();
}
float8 r = PG_GETARG_FLOAT8(0);
float8 g = PG_GETARG_FLOAT8(1);
float8 b = PG_GETARG_FLOAT8(2);
PG_RETURN_FLOAT8(0.299 * r + 0.587 * g + 0.114 * b);
}
Пример функции
Теперь, зная, как написать хранимую функцию на C, подведем итог и посмотрим на пример. Окружение у нас будет такое:
- операционная система: Ubuntu 12.10
- версия PostgreSQL: 9.3
- компилятор: gcc 4.7.2/
Создадим файл utils.c с таким содержимым:
#include <postgres.h>
#include <fmgr.h>
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(grayscale);
Datum grayscale(PG_FUNCTION_ARGS)
{
float8 r = PG_GETARG_FLOAT8(0);
float8 g = PG_GETARG_FLOAT8(1);
float8 b = PG_GETARG_FLOAT8(2);
PG_RETURN_FLOAT8(0.299 * r + 0.587 * g + 0.114 * b);
}
Далее, скомпилируем utils.c в объектный файл, при этом он должен быть с позиционно-независимым кодом (для gcc, это опция fpic):
cc -I/usr/local/pgsql/include/server -fpic -c utils.c
Команда pg_config --includedir-server подскажет расположение каталога с заголовочными файлами. Слинкуем объектный файл как динамическую библиотеку (если всё в порядке, у нас должна появится динамическая библиотека utils.so, скопируем ее в /usr/local/pgsql/lib):
cc -shared -L/usr/local/pgsql/lib -lpq -o utils.so utils.o
Теперь, соединимся с нашей БД и создадим в ней функцию grayscale_с, указав некоторые опции:
CREATE OR REPLACE FUNCTION grayscale_c ( r double precision, g double precision, b double precision )
RETURNS double precision AS
'utils', 'grayscale'
LANGUAGE C STRICT VOLATILE COST 100;;
Проверим ее работоспособность:
SELECT grayscale_c ( 0.6, 0.5, 0.5 ); -- результат: 0.5299
Но это еще не всё. Сравним эту функцию с аналогичной, но на plpgsql. Назовём ее grayscale_plpgsql:
CREATE OR REPLACE FUNCTION grayscale_plpgsql ( r double precision, g double precision, b double precision )
RETURNS double precision AS
$BODY$
BEGIN
RETURN 0.299 * r + 0.587 * g + 0.114 * b;
END
$BODY$
LANGUAGE plpgsql STRICT VOLATILE COST 100;
И проведём некоторый тест:
CREATE TABLE color AS
SELECT random () AS r, random () AS g, random () AS b
FROM generate_series ( 1, 1000000 );
SELECT grayscale_c ( r, g, b ) FROM color; -- время выполнения запроса: 926 мс
SELECT grayscale_plpgsql ( r, g, b ) FROM color; -- время выполнения запроса: 3679 мс
Небольшая проверка:
SELECT * FROM color WHERE grayscale_c ( r, g, b ) != grayscale_plpgsql ( r, g, b ); -- 0 записей
Очень даже неплохой результат.
Как мы убедились, создание динамически загружаемых функций на C не такая уже и сложная штука. Сложности, как правило, таятся в их реализации.
P.S. Спасибо за внимание.
Ссылки:
Автор: blackmaster
