Общее устройство языков

Oracle PL/SQL

PL/SQL — декларативный язык, который впервые появился в 6-й версии Oracle в 1988 году. По современным меркам этот язык не совсем привычный, потому что большинство языков программирования, на которых мы сейчас пишем (не считая Python), относится к языковой ветке Си. PL/SQL же был создан на основе языка Ада, который разработали для нужд Министерства обороны США. Сейчас он постепенно уходит в прошлое, но для своего времени был очень продуманным и мощным — с исключениями и модульной организацией.

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

Нужно понимать, что сам код на PL/SQL — совершенно отдельный язык, он не похож на SQL, и у него свой движок. Другими словами, если код выполняется на SQL и вызывается какая-то функция, то внутри происходит переключение контекста, запускается исполнитель PL/SQL, а потом возвращается обратно.

Код PL/SQL выглядит как текст, хранится внутри базы данных и там же интерпретируется при исполнении. Это значит, что можно написать процедуру в виде «черного ящика» для пользователя, тем самым качественно защищая базу данных. Именно поэтому на таком серверном процедурном коде долгое время было принято делать защищенные системы.

Поскольку PL/SQL — язык программирования, а не запросов, в нем существуют специальные типы данных, такие как BINARY_INTEGER, %ROWTYPE, TABLE INDEX OF и другие. Среди других плюсов PL/SQL — наличие внутри языка специальных механизмов типа курсоров и возможность подключать внешний код на Си и Java.

PostgreSQL PL/pgSQL

Почти десять лет спустя после PL/SQL, в 1997 году, на свет появился PL/pgSQL. Его создатели старались облегчить переход с Oracle, поэтому синтаксис языков очень близок. Единственное существенное отличие — в PL/pgSQL модульность организована через схемы, а пакеты — и то ограниченные — появились только в 15 версии PostgreSQL, в 2022 году.

В остальном, уже знакомая нам картина:
✔️ хранение кода в базе данных;
✔️ интерпретация;
✔️ специальные типы данных (правда, с другими названиями — RECORD, SETOF, TABLE и другие);
✔️ курсоры;
✔️ возможность подключать внешний код на Си;
✔️ целое семейство родственных языков: pl/Python, pl/Perl, pl/Tcl.

Расхождения в типах данных

Как видно из истории вопроса, языки максимально похожи, поэтому я не буду подробно описывать все сходства, а лучше подсвечу самые неприятные моменты, которые могут возникнуть при миграции с Oracle на PostgreSQL. Часть этих проблем связана с SQL, то есть с устройством таблиц, а часть — с миграцией серверного кода с PL/SQL.

Строки

Историческая особенность языков: пустая строка в PostgreSQL не равна NULL, это два совершенно разных значения, а в Oracle — равна. Простой способ решить эту проблему при миграции СУБД — во всех местах, где в Oracle было сравнение с пустой строкой, нужно добавить сравнение с NULL (IS NULL).

Другая историческая особенность: в Oracle для строк традиционно используют тип данных VARCHAR2, который в действительности идентичен стандартному типу VARCHAR. PostgreSQL поддерживает только VARCHAR, поэтому код придется исправлять. Кроме того, в PostgreSQL для текстов есть свой тип данных — TEXT. В общем, что называется, соответствие вилки и розетки — 99%.

Целые числа

С численными типами данных все еще веселее. Популярный тип в Oracle называется NUMBER, а в стандарте и в PostgreSQL — NUMERIC. При этом Oracle позволяет обрабатывать большие целые числа с 38 десятичными разрядами. При переходе на PostgreSQL можно, конечно, использовать NUMERIC, но это будет неэффективно, поскольку суммирование таких чисел будет происходить в сотни раз дольше, чем суммирование простых целых типов, таких как bigint, smallint, integer. Здесь имеет смысл подумать, какие ограничения на данные у нас есть. Если, например, количество товаров не больше миллиона — точно хватит простого integer.

Кроме этого, в самом PL/SQL есть разные типы данных: BINARY_INTEGER, PLS_INTEGER, BINARY_FLOAT и BINARY_DOUBLE. Фактически они более эффективны и соответствуют тому, что есть в PostgreSQL: integer, float, double precision.

Огромный блок проблем с типами данных для дат и времени в рамках данной статьи я даже не буду затрагивать — эти типы данных во всех системах сделаны по-своему. Где-то есть тип date, где-то — datetime, где-то — timestamp.

Большие объекты

Следующая особенность Oracle — хранение больших объектов отдельно от остальных полей записи. Бинарные данные хранятся в типах BLOB, а текстовые — в CLOB. Для обращения к ним используют специальные функции и процедуры. Способ хранения больших объектов в Oracle с годами менялся, в 11-й версии были встроены возможности автоматической дедубликации, сжатия и шифрования.

У PostgreSQL есть похожие механизмы, но с ограничениями. В PostgreSQL для каждой базы есть своя таблица pg_largeobject. Максимальный размер таблицы — 32 Тб, одной записи — 4 Тб, а количества записей — около 4 млрд (integer). Для бытовых задач хватит, но для крупного бизнеса может оказаться проблемой.

Временные таблицы, автономные транзакции и прочие расхождения

Временные таблицы

На практике бывает удобно создать какую-нибудь таблицу на ходу: взять откуда-то данные, сгруппировать, почистить и так далее. При этом в отдельных случаях — например, когда запрос пишут разные люди — бывает лучше выполнить все это по частям и разбить задачу на этапы. Тогда те временные таблицы, которые заполняются данными в процессе выполнения огромного запроса, впоследствии окажутся не нужны.

В Oracle существуют глобальные и с недавних пор — сессионные временные таблицы. В PostgreSQL глобальных временных таблиц нет вообще — есть только сессионные таблицы с разными режимами:

✔️ удаление таблицы после коммита (инвалидация кеша словаря данных);

✔️ удаление записей из таблицы после коммита;

✔️ сохранение записей после коммита.

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

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

Автономные транзакции были придуманы в PL/SQL для того, чтобы часть операций — INSERT, UPDATE и тому подобные — выполнялась отдельно, а не в контексте основной транзакции. Это удобно, например, для того, чтобы записи в журнале аудита гарантированно сохранились даже при ее откате.

В PL/pgSQL это всегда было проблемой. Хороший обходной путь — расширение для выполнения запросов к другим СУБД или в отдельном сеансе:

PERFORM dblink_exec('dbname=' || current_database(), ‘INSERT …’);

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

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

SELECT pgv_set('vars', 'int1', 101);

BEGIN;

SELECT pgv_set('vars', 'int2', 102);

ROLLBACK;

SELECT * FROM pgv_list() order by package, name;

 package | name | is_transactional

---------+------+------------------

 vars    | int1 | f

 vars    | int2 | f

Через этот механизм можно хранить как просто скалярные переменные, так и коллекции.

Наконец, автономные транзакции все-таки добавили в платном варианте PostgresPro. Выглядят они так:

CREATE OR REPLACE FUNCTION myaudit() RETURNS boolean AS $$

BEGIN AUTONOMOUS

        INSERT INTO audit_schedule VALUES ('new audit',now());...

        RETURN true;

END;

$$ LANGUAGE plpgsql;

Средства отказоустойчивости

Переходим к следующему различию СУБД. В корпоративных версиях Oracle есть встроенный механизм Oracle Data Guard, который позволяет завести резервный горячий экземпляр базы данных и автоматически переключаться на него в случае сбоя системы.

В PostgreSQL есть альтернативный механизм потоковой репликации, а также дополнительные внешние системы, которые занимаются обслуживанием резервирования и автоматическим переключением: Patroni, Stolon и Pg_auto_failover.

Список прочих несовместимостей

Какие еще неприятности возникают при переезде с PL/SQL на PL/pgSQL и требуют ручной настройки:

✔️ иерархические запросы (в Oracle обычно реализуются специальным синтаксисом, уникальным для этой СУБД, а PostgreSQL следует стандарту);

✔️ параллельная обработка данных (в Oracle это устроено сложнее, а в PostgreSQL больше ограничений, что влияет на производительность);

✔️ условия фильтрации, вычисляемые на этапе выполнения;

✔️ использование очередей;

✔️ использование PL/SQL-коллекций;

✔️ пользовательские константы и исключения;

✔️ глобальные структуры данных уровня пакета;

✔️ операторы MERGE, INSERT FIRST и INSERT ALL;

✔️ функция DECODE;

✔️ планировщик.

Инструменты миграции

Инструменты миграции с PL/SQL на PL/pgSQL можно сравнить с условным Google Translate: да, перевод с одного языка на другой будет осуществлен автоматически, но перед отправкой текста носителю желательно, чтобы профессиональный переводчик отредактировал его вручную. В противном случае можно сморозить глупость и опозориться. А в нашем случае — допустить критические ошибки в продуктивной среде.

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

ora2pg

Международное комьюнити разработчиков давно ведет проект ora2pg, который занимается именно переносом данных с Oracle на PostgreSQL. При этом подключается он к существующей базе данных Oracle, для PostgreSQL только генерирует скрипты. Сам проект написан на Perl, так что его нужно будет предварительно установить.

Инструмент очень хорошо переносит:

✔️ таблицы, представления, индексы, последовательности, ограничения РК/FK/unique/check;

✔️ права доступа;

✔️ данные, в том числе BLOB (в виде INSERT или файла для COPY);

✔️ код процедур/функций/пакетов/триггеров (требует ревью и ручной доработки).

Кроме того, ora2pg умеет:

✔️ создавать обертки внешних данных (fdw) для таблиц;

✔️ экспортировать данные представлений и материализованных представлений;

✔️ оценивать сложность и стоимость миграции.

При этом у ora2pg есть существенные ограничения:

✔️ в целом код PL/pgSQL требует ручной доработки;

✔️ инструмент не переносит: локальные функции, составные триггеры, переменные в пакетах, стандартные пакеты Oracle (DBMS_*, UTL_*) и рекурсивные запросы с CONNECT BY.

Таким образом, ora2pg закрывает в среднем около 80% задачи перевода кода с PL/SQL на PL/pgSQL. У решения много полезных возможностей и пользовательских настроек. Впрочем, нужно понимать, что «каждая несчастливая семья несчастлива по-своему»: проекты миграции отличаются сложностью и масштабом, и где-то от ora2pg не будет практически никакой пользы, а где-то она сделает плюс-минус все.

orafce

Другой опенсорсный проект для миграции — orafce. Он реализует в PostgreSQL различные пакеты и нестандартные возможности Oracle, в частности:

✔️ превдотаблицу DUAL;

✔️ тип данных date и функции для него;

✔️ пакеты DBMS_OUTPUT, UTL_FILE и некоторые другие;

✔️ триггеры для исправления сравнений с пустой строкой/NULL.

Подключив библиотеку, вам не придется переписывать тысячи объемных запросов — orafce добавит недостающие типы и функции и сгладит ключевые отличия в диалектах языка запросов SQL. В простых случаях инструмент также может автоматизировать большую долю задач по переезду.

ora2pgpro

ora2pgpro — расширенная платная версия ora2pg от Postgres Pro. Инструмент переносит данные не в PostgreSQL, а в Postgres Pro, используя ее дополнительные возможности, в частности упомянутые автономные транзакции.

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

Выбор инструмента для переезда

Серебряной пули здесь нет, поэтому выбор зависит главным образом от целевой платформы, имеющихся ресурсов и устройства исходного кода в вашем проекте. Если в вашем случае отсутствует серверный код, то с помощью orafce можно малой кровью перенести только запросы. Если же серверный код есть, то нужно будет выбирать между двумя версиями ora2pg. Версия pro рассчитана на перенос СУБД на платную платформу PostgresPro с платной поддержкой, которой пользуется не каждый бизнес. Если использовать ora2pgpro в паре со стандартной PostgreSQL, то инструмент придется настраивать дополнительно, исключая из обработки те или иные платные фичи платформы.

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

Оригинал статьи размещен на Хабре.