Работа с хранимым кодом приложения при миграции с Oracle на PostgreSQL: особенности, сложности и способы их преодоления

В предыдущих статьях о миграции с Oracle на Postgres мы рассматривали перенос данных из одной системы управления базами данных в другую (часть 1, часть 2). Сегодня разговор пойдёт об особенностях работы с серверным хранимым кодом приложения при необходимости смены СУБД. В частности, будут рассмотрены следующие вопросы:

1. Какой код стоит переносить.

2. Основные особенности, затрудняющие перевод на PostgreSQL.

3. Варианты замены особенностей исходной СУБД при конвертации хранимого кода в СУБД-источник.

Прежде всего, необходимо принять решение о целесообразности переноса хранимого кода PL/SQL на PL/pgSQL. Код на PL/SQL можно скомпилировать в байт-код перед выполнением, а код на PL/pgSQL нельзя. Процесс компиляции преобразует хранимые процедуры PL/SQL в нативный код общих библиотек, связанных с ядром ОС, что может привести к увеличению производительности процедурного кода.

Степень повышения производительности зависит от содержимого PL/SQL. Процесс компиляции не влияет на скорость извлечения данных из базы, только на процедурную логику вокруг них (циклы, условные операторы, математические операции).

В отличие от PL/SQL языка PL/pgSQL является интерпретируемым языком программирования, в этом можно убедиться при просмотре содержимого столбца prosrc таблицы словаря данных pg_proc. Исходный текст процедуры или функции в точности такой, какой он был при выполнении команд CREATE FUNCTION и/или CREATE PROCEDURE. Когда сессия в первый раз выполняет процедуру или функцию, то:

1. Её нужно разобрать.

2. Составить дерево разбора.

3. Обойти его и выполнить указанные действия.

В результате интерпретируемый язык будет по скорости проигрывать компилируемому.

Возникает также вопрос о масштабировании решения, в котором весь прикладной код содержится на стороне СУБД-приёмника. Если вынести код на сторону приложения, то можно:

1. Добавить новые сервера приложений.

2. Установить «умный» балансировщик, который в зависимости от требований приложения будет перенаправлять запросы пользователя на тот или иной сервер приложений.

3. Разделить монолитное решение на микросервисы, каждый из которых будет работать со своей БД.

Кроме того, часть прикладной логики, в частности, взаимодействие с внешними подсистемами, удобнее реализовать на языке прикладного кода, а не на PL/pgSQL.

К сожалению, в настоящий момент ситуация такая: при переходе на другую СУБД многие стараются взять исходный код, сконвертировать его так, чтобы он был синтаксически корректен для конечной СУБД, и пытаются работать с новой СУБД так, как уже привыкли с прежней. Для получения максимальной производительности нужно проектировать систему с пониманием внутренних механизмов функционирования новой СУБД. Если такую ошибку допустить, то проект, вероятнее всего, не станет успешным.

Именно поэтому не стоит копировать код бездумно из Oracle в Postgres, а изучать особенности новой СУБД («приёмника»). У любой СУБД есть свои преимущества и недостатки, но можно с уверенностью утверждать, что Postgres решает свои задачи не хуже Oracle, а иногда даже лучше - например, задачи полнотекстового поиска или работы с геоданными. Следует понимать, насколько сложен код PL/SQL – расчёты биллинговой системы или более простой код, которого много.

Выше был описан первый из четырёх основных этапов перевода кода. Перечислим их все:

1. Оценка целесообразности миграции хранимого кода.

2. Поиск затрудняющих перенос особенностей в хранимом коде.

3. Выбор средств конвертации кода.

4. Конвертация и адаптация кода.

На что стоит обратить пристальное внимание в процессе переноса?

1. На часто выполняемые PL/SQL функции и процедуры со сложной логикой. Стоит рассмотреть возможность их переноса на сторону прикладного приложения, если речь идёт о:

   • Многократном использовании коллекций.

   • Выполнении функций несколькими процессами одновременно.

   • Помещении задач в очередь с ожиданием их выполнения для продолжения работы.

   PL/pgSQL - интерпретируемый язык, так что на нём всё перечисленное выше будет работать медленнее, чем на PL/SQL.

2. На тяжеловесные вычисления с применением параллелизма. Допустим, в системе реализован какой-то сложный биллинг. В этом случае стоит думать о выносе на сторону приложения, поскольку прикладной компилируемый язык программирования, скорее всего, будет работать быстрее.

Как правило, на практике встречаются следующие случаи:

1. Кода много, но он сам по себе простой, его можно перенести в СУБД-приёмник.

2. Большая часть функционала выносится на сторону приложения, СУБД перестаёт быть сервером приложения и становится хранилищем данных.

3. Монолитная система логически разбивается на несколько частей. Тогда, вероятно, нужно переносить не всё, а лишь небольшие фрагменты кода, относящиеся к переносимой части.

Преимущество расширяемости PostgreSQL

PostgreSQL - расширяемая СУБД, предоставляющая широкие возможности добавления нового функционала без внесения изменений в код ядра.

Можно реализовать часть логики системы на языке C. Поначалу это может быть не совсем удобно, зато это оправдывает себя с точки зрения производительности. В документации PostgreSQL приведён пример разработки триггера на языке C. А вот здесь приводится сравнение производительности работы, из которого следует, что разработанный на языке С триггер будет работать быстрее. Также рекомендуется ознакомиться с докладом "Пример использования GiST в решении нестандартной поисковой задачи", в котором описывается, как с помощью разработанной на языке С функции можно ускорить выполнение запроса с 10 минут до 110 мс.

Можно создавать новые типы данных, методы доступа к ним. В качестве примера можно привести модуль для работы с геометрией PostGIS.

Также существует возможность написания своих агрегатных функций. В частности, есть модуль first_last_agg, позволяющий заменить конструкцию KEEP (DENSE_RANK LAST ORDER BY). С помощью агрегатных функций можно значительно сократить количество подзапросов в основном запросе и существенно улучшить его производительность. Например, был случай, когда модуль first_last_agg позволил убрать большую часть подзапросов, и запрос, работавший в СУБД Oracle 6 часов, в СУБД PostgreSQL стал выполняться за 15 минут.

Существует возможность создания процедурных языков программирования, позволяющих разрабатывать пользовательские функции не только на SQL и С, но и на других языках. В настоящее время в состав СУБД PostgreSQL включены четыре процедурных языка:

1. PL/pgSQL.

2. PL/Perl.

3. PL/Python.

4. PL/Tcl.

Особенности, затрудняющие перенос хранимого кода приложения

Ниже приведена часть особенностей кода приложения исходной СУБД, затрудняющих перевод в целевую СУБД:

1. Cтарый синтаксис соединения таблиц.

2. Иерархические запросы.

3. Параллельная обработка данных.

4. Условия фильтрации, вычисляемые на этапе выполнения.

5. Использование механизма очередей.

6. Технологии построения веб-приложений (APEX).

7. Взаимодействие с внешними сервисами по протоколу HTTP.

8. Использование PL/SQL коллекций и объектов.

9. Пользовательские константы.

10. Ref-курсор.

11. Глобальные структуры данных уровня пакета.

12. Пользовательские исключения.

13. Конструкции MERGE и INSERT ALL.

14. Конструкции DECODE.

15. Вложенные определения функций.

16. Функции определения схожести строк.

17. Автономные транзакции.

18. Регрессионное тестирование хранимого кода.

19. Выполнение задач по расписанию.

20. Конструкции-подсказки для планировщика.

Старый синтаксис соединения таблиц

Теперь поговорим о том, как обойти вышеназванные ограничения при миграции. Что касается старого синтаксиса соединения таблиц, его следует заменить на новый:

• LEFT OUTER JOIN.

• JOIN.

• RIGHT OUTER JOIN.

• FULL OUTER JOIN.

То есть нужно использовать стандарт языка ANSI SQL, либо, если у кого-то есть доступ в СУБД Oracle, можно там заменить код на поддерживаемый стандартом синтаксис.

Иерархические запросы

Ниже приведён типичный пример иерархии с «родителями» и «потомками»:

CREATE TABLE t_hierarchy ( 
    id INT NOT NULL PRIMARY KEY, 
    parent INT NOT NULL, 
    value VARCHAR(50) NOT NULL
);   

INSERT INTO t_hierarchy (id, parent, VALUE) 
SELECT s
     , s / 5 
     , 'Value ' || s 
  FROM generate_series(1, 1000) s; 

CREATE INDEX ix_hierarchy_parent ON t_hierarchy (parent);

Продемонстрируем, как можно написать иерархический запрос с использованием СУБД Postgres. Стоит отметить, что старый синтаксис СУБД Oracle известен не только своими конструкциями START WITH, CONNECT BY PRIOR, но и псевдостолбцами, позволяющими определить:

1. Признак листового узла, у которого нет дочерних элементов.

2. Путь от корня к текущему узлу.

3. Уровень вложенности и так далее.

Ниже приведена реализация иерархического запроса в СУБД PostgreSQL:

WITH RECURSIVE ds(id, parent, value, lvl, path) AS (
SELECT h.id
     , h.parent
     , h.value
     , 1 AS lvl
     , ARRAY[h.id] AS path
  FROM t_hierarchy h
 WHERE h.parent = 0

 UNION ALL

SELECT h.id
     , h.parent
     , h.value
     , 1 AS lvl
     , ARRAY[h.id] AS path
  FROM ds d
  JOIN t_hierarchy h
    ON h.parent = d.id
)
SELECT REPEAT('  ', d.lvl) || d.id AS id
     , d.parent
     , d.value
     , d.lvl
     , d.path::text AS path
     , CASE WHEN d.lvl - LEAD(d.lvl) OVER(ORDER BY d.path) < 0 THEN false ELSE true END AS is_leaf 
  FROM ds d
 ORDER BY d.path;

Видно, что реализация представляет собой рекурсивный запрос. Поле path здесь является определяющим, т.к., c помощью сортировки по этому полю получается правильная иерархическая структура с точки зрения визуального представления. Также path важен для определения листовых узлов:

1. Если у узла есть потомки, то он не является листовым.

2. Если у узла нет потомков, то он является листовым.

Массив path показывает путь от корня к текущему элементу. Уровень вложенности можно представить путём добавления единицы к первоначальному уровню.

Зацикливание рекурсивных запросов в PostgreSQL

Тем не менее, у рекурсивных запросов в PostgreSQL есть некоторые неприятные нюансы. В СУБД Oracle предусмотрена так называемая максимальная глубина рекурсии - если она превышена, то Oracle считает, что запрос зациклился, и его выполнение прерывается. В СУБД Postgres до 14-ой версии это было не так - запрос мог войти в бесконечный цикл, продолжали копиться временные файлы, очищавшиеся только после прерывания запроса. А он прерывался не раньше, чем закончится место на диске.

Ниже приведён пример множества, при обработке которого произойдёт зацикливание иерархического запроса, поскольку поля dss_key и dss_parent_key имеют одинаковое значение.

Данное множество получилось из-за того, что в СУБД PostgreSQL NULL и пустая строка это не одно и то же (на недавней PGConf.Russia был представлен доклад о NULL-значениях).

Стоит помнить, что условие на выход из цикла должно быть тщательно прописано, потому что такие ошибки достаточно тяжело отловить, особенно при очень объёмных запросах на тысячи строк кода.

К счастью, в 14-ой версии появился синтаксис CYCLE SET USING, позволяющий упростить выявление циклов. В этой конструкции:

1. В CYCLE объявляется набор столбцов, значения которых могут зациклиться.

2. В SET указывается имя столбца с признаком выявления цикла.

3. В USING указывается столбец, в котором будет отслеживаться путь.

Подробнее об этой особенности можно прочитать в:

1. PostgreSQL 14: Часть 5 или «весенние заморозки» (Коммитфест 2021-03).

2. Фразы CYCLE и SEARCH для рекурсивных запросов.

Параллельная обработка данных в Oracle

В СУБД Oracle есть три способа параллельной обработки данных:

1. Подсказка для планировщика PARALLEL.  (В этом случае данные распределяются по дочерним процессам главного процесса-координатора).

2. DBMS_PARALLEL_EXECUTE. (Появился в Oracle 11, использует в своей работе DBMS_SCHEDULER).

3. DBMS_SCHEDULER.Часто использовался в Oracle 10 до появления пакета DBMS_PARALLEL_EXECUTE. Данные разбивали на задания, они ставились в расписание и выполнялись, ожидалось их завершение. Переход к следующим заданиям происходил, если все предыдущие успешно выполнились. Иногда требовалось повторно выполнить завершившиеся с ошибкой задачи.

Параллельная обработка данных в PostgreSQL

В СУБД Oracle подсказка для планировщика PARALLEL позволяет ускорить выполнение части запроса, будет задействовано несколько фоновых процессов, каждому из которых передаётся часть данных для обработки.

Можно обернуть часть запроса в общее табличное выражение и указать для него подсказку PARALLEL. В результате, только она будет выполняться несколькими процессами, а все остальные части запроса нет.

В PostgreSQL есть возможность использования параллелизма, но оно даже при использовании модуля pg_hint_plan не действует на:

1. Внешние таблицы.

2. Табличные функции.

3. Предложения VALUES.

4. CTE.

5. Представления.

6. Вложенные запросы.

Тем не менее, можно попытаться воспроизвести поведение PARALLEL, если:

1. Часть запроса обернуть в функцию.

2. Установить в её определении max_parallel_workers_per_gather: SET max_parallel_workers_per_gather TO num_workers.

Таким образом, на этапе планирования запроса планировщик примет решении об использовании нескольких фоновых процессов для выполнения функции.

Для получения части возможностей пакета DBMS_PARALLEL_EXECUTE можно использовать модуль pg_background. В этом случае создаются фоновые процессы, каждый из которых подключается к требуемой базе данных и выполняет прикладной код. Модуль позволяет дождаться их завершения и проверить результаты работы. На основе этого принимается решение о дальнейшем выполнении той или иной части бизнес-логики.

Поскольку DBMS_PARALLEL_EXECUTE использует в своей работе DBMS_SCHEDULER, можно разделить данные на задания и обрабатывать их по расписанию. Для этого есть модуль pg_cron, для наших клиентов доступен модуль pgpro_scheduler, поставляемый в составе Postgres Pro Enterprise. Существует также множество планировщиков заданий на языке высокого уровня таких как Java, C#, Golang.

Также, пользуясь случаем, стоит развеять миф о pgAgent - есть ошибочное мнение о том, что его можно установить только с pgAdmin. Это отдельное решение, состоящее из расширения на языке PL/pgSQL и C++. Ниже приведены его основные функции:

1. Периодическая проверка расписаний заданий.

2. Поиск заданий на выполнение.

3. Выполнение найденного задания.

Агент - фоновый процесс, логика которого реализована на основе функционала набора библиотек libboost. На ОС Astra Linux Smolensk 1.6 доводилось ставить pgAgent и можно сказать, что он там вполне неплохо работает. Пакеты libboost там есть, поэтому проблем возникнуть не должно.

Условия фильтрации в секциях WHERE

Условия фильтрации в секциях WHERE могут быть разными. Допустим, что поле таблицы больше или равно значению некоторой функции. Функции в СУБД PostgreSQL бывают разными с точки зрения категории изменчивости.

Есть неизменяемые (IMMUTABLE) функции, возвращающие одинаковый результат при одном и том же наборе входных данных. Такие функции можно вычислить на этапе планирования. В этом случае планировщик может довольно точно определить расчётное количество строк того или иного узла плана выполнения запроса.

Есть случаи, когда значение функции нельзя вычислить на этапе планирования (STABLE и VOLATILE). Тогда планировщик часто возвращает значение по умолчанию, что часто приводит к некорректным методам доступа к данным и соединений множеств.

NULLIF до 14-ой версии вообще стоит заменять на col_name IS NOT NULL AND col_name != ‘’. Как правило, такую конструкцию используют для необязательных условий фильтрации. Если значение этого поля не указано, то по нему ничего фильтровать не нужно.

Если использовать предложенную конструкцию, то планировщик будет видеть прописанные необязательные значения параметров, условие фильтрации будет учтено в итоговом плане выполнения. Если там ничего не будет прописано, то в плане часть условий фильтрации будет убрано.

С 14-ой версии планировщик стал вычислять выражения NULLIF на этапе построения плана запроса:

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
SELECT * FROM tickets WHERE NULLIF(1, 2) = 2;

           QUERY PLAN           
--------------------------------
 Result (actual rows=0 loops=1)
   One-Time Filter: false

Как уже было сказано выше, поскольку некоторые функции не могут быть вычислены на этапе планирования, возможны расхождения между расчётным и фактическим количеством строк в определённых узлах плана. Это может привести не только к некорректному выбору метода доступа к данным, но и к некорректному соединению множеств. Есть много случаев, когда некорректный способ соединения не позволял запросу выполниться быстро, от этого замедлялась работа всего приложения.

Использование механизма очередей

Существует несколько подходов - можно использовать select for update skip locked, модуль pgq. Следует ответить, что нужно проверять скорость, соответствует ли она бизнес-требованиям.

Очередь также можно вынести на уровень приложения и использовать Apache Kafka, Apache ActiveMQ, RabbitMQ и Celery. Эти решения достаточно популярны на рынке и часто встречаются в высоко нагруженных системах.

Технологии построения веб-приложений

Технологии построения веб-приложений типа APEX в PostgreSQL отсутствуют. Эта задача достаточно сложна в реализации, там нужно много всего предусмотреть - в частности, хранение обработчиков событий в хранимом коде, JavaScript, написание интерфейсов, разработку выполняемой на серверах приложений логики. Требуется разработка отдельного приложения.

Взаимодействие с внешними сервисами по протоколу HTTP

Что касается взаимодействия с внешними сервисами по протоколу HTTP, то тут даже в СУБД Oracle есть нюанс. Начиная с версии 11, прежде чем пытаться кому-то что-то отправить, нужно прописать специальные настройки во внутреннем файерволе Oracle. В противном случае, любая попытка взаимодействия с внешним источником будет отклонена СУБД.

В PL/pgSQL так делать нельзя, по определению считается, что им может пользоваться любой пользователь, и при этом его действия не будут противоречить требованиям безопасности. Если аналогичную вещь нужно реализовать, можно использовать:

1. Модуль прикладного приложения.

2. pl/python.

3. pl/perl.

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

Кроме того, сам вызов внешнего сервиса может быть успешным, а породившая его транзакция может быть отменена. Это может привести к плохим последствиям, в частности, при переводе средств.

Использование PL/SQL коллекций и объектов

Вложенные таблицы (nested tables) можно заменить массивом пользовательских или встроенных типов.

Ассоциативные массивы - hstore или JSONB.

Стоит отметить, что в Postgres, в отличие от Oracle, нужно явно указывать переменную с типом record и использовать её в конструкциях типа for rec in (query) loop.

Также важно, что в PL/pgSQL не поддерживается объектно-ориентированное программирование (ООП). Если логика приложения завязана на объекты с большим количеством функций, то это придётся переписывать.

Bulk collect

bulk collect используется в СУБД Oracle для размещения данных в коллекцию и уменьшения количества переключений с PL/SQL-контекста на SQL-контекст. Чем больше переключений, тем медленнее скорость работы. Планировщику нужно будет проверить:

1. Наличие плана выполнения в локальном кеше процесса.

2. При отсутствие плана разобрать запрос, выбрать оптимальный план его выполнения, поместить в кэш и выполнить.

Ниже приведён пример замены bulk collect с помощью массива:

CREATE OR REPLACE FUNCTION bulk_test() RETURNS VOID AS
$$
DECLARE
   a tb_person[];
BEGIN
   a := array(SELECT tb_person FROM tb_person);
   ...
END;
$$
LANGUAGE plpgsql;

Однако для хранения элементов массива требуется память. СУБД PostgreSQL не даёт выделить больше 1 ГБ памяти за один раз. Были случаи, когда объём массива превышал 1 ГБ, в результате, в результате, часть логики переписывалась.

Пользовательские константы

Константы с течением времени не меняются, значит, их можно оформить в виде неизменяемых (IMMUTABLE) функций, значение которых вычисляется на этапе планирования.

Если константы используются только внутри процедуры/функции, то их можно объявить в секции DECLARE:

DO
$$
DECLARE
  msg constant text := 'Message 1';
BEGIN
  RAISE NOTICE '%', msg;
END
$$
LANGUAGE plpgsql;

Ref-курсоры

В СУБД PostgreSQL можно объявить переменную типа refcursor и передавать её в качестве возвращаемого значения приложению. В результате оно будет извлекать данные, на которые ссылается этот ref-курсор. Вот как это выглядит:

CREATE OR REPLACE FUNCTION get_employees_by_deptno (
    p_deptno IN emp.deptno%TYPE, 
    p_rset REFCURSOR
) 
RETURNS REFCURSOR AS 
$$ 
BEGIN 
    OPEN p_rset FOR
    SELECT empno
	       , ename
      FROM emp
     WHERE deptno = p_deptno
     ORDER BY ename;
 
    RETURN p_rset;
END;
$$
LANGUAGE plpgsql STABLE;

В следующей, четвёртой статье будут разобраны оставшиеся особенности кода приложения на СУБД Oracle, для которых нужно находить обходные пути из-за отсутствия прямых аналогов в СУБД PostgreSQL. Это завершающая статья цикла, в которой также будет рассмотрен выбор средств конвертации кода, конвертация и адаптация кода.