Особенности при миграции из Oracle в PostgreSQL (Life Hacks)

1 — Constraints (ограничения)

Хотя Oracle позволяет пользователям отключать и включать ограничения сколь угодно часто, это обычно не рекомендуется для любой СУБД, поскольку при неправильном выполнении это может привести к повреждению данных. В PostgreSQL ограничения вместо этого создаются как отложенные, и для их отсрочки можно использовать команду SET CONSTRAINTS. Параметр отложенного выполнения указывает время по умолчанию для активации ограничения. Если ограничение в Oracle нельзя отложить, его нужно будет отбросить и воссоздать как откладываемое, хотя иногда можно изменить ограничение, не отбрасывая его.

2 — Delete

При удалении данных в PostgreSQL для указания таблицы необходимо использовать синтаксис FROM, в Oracle же не требуется.

3 — Удаление объектов базы данных

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

4 — Dual Table

Поскольку предложение FROM является обязательным в Oracle для каждого оператора SELECT, FROM DUAL используется для операторов SELECT, где имя таблицы не требуется. PostgreSQL не требует предложения FROM, поэтому FROM DUAL не требуется и обычно его можно опустить. Если в PostgreSQL требуется dual table, ее можно сгенерировать как представление.

5 — Пустые строки и NULL

В Oracle пустые строки имеют значения NULL, но они не считаются NULL в PostgreSQL. В Oracle можно проверить, пуста ли строка или нет, с помощью оператора IS NULL, но в PostgreSQL он вернет FALSE для пустой строки (и TRUE для NULL).

6 — Federation to Foreign Data Wrappers

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

7 — GRANT

Команда GRANT действует аналогично в Oracle и PostgreSQL. Есть два основных варианта — они могут использоваться для предоставления привилегий для объекта базы данных и для предоставления членства роли.

При этом, не все привилегии Oracle доступны в PostgreSQL. Например, предоставление привилегии триггера для таблицы позволяет пользователям создавать триггеры, но в отличие от Oracle только владелец таблицы может удалять триггеры.

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

PostgreSQL не поддерживает START WITH. . .CONNECT BY, который Oracle использует для иерархических запросов. Вместо этого PostgreSQL использует WITH RECURSIVE.

9 — Joins with (+)

В Oracle есть специальный оператор (+) для выполнения левого и правого outer joins. В PostgreSQL эта функция отсутствует, поэтому необходимо указывать команду JOIN.

10 — Проверка NOT NULL

Для определения какие столбцы в таблице Oracle НЕ являются NULL, необходимо использовать команду CHECK (<column_name> IS NOT NULL). В PostgreSQL же вместо этого имеется столбец «attnotnull» в pg_attribute, в котором хранится информация о столбцах таблицы, в том числе – о столбце с Constraint NOT NULL.

11 — Преобразование PL / SQL в PL / pgSQL

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

12 — Remote objects

Для доступа к remote objects можно использовать оболочку внешних данных (Oracle_fdw) для доступа к любой другой базе данных. Столбцы ROWID, CTID и IdentityPostgreSQL не имеет точного эквивалента псевдостолбцу ROWID в Oracle, который предоставляет адрес строки в таблице. CTID в PostgreSQL аналогичен, за исключением того, что его значение изменяется каждый раз при выполнении VACUUM. Вместо этого используются столбцы идентификаторов, значения которых создаются автоматически при создании строки и никогда не изменяется. Значение можно указать, чтобы оно создавалось ВСЕГДА или ПО УМОЛЧАНИЮ. GENERATED BY DEFAULT позволяет пользователю вставить или обновить значение, а не использовать значение, сгенерированное системой.

13 — Sequences

Sequences имеют другой синтаксис в Oracle и PostgreSQL, и их необходимо обновлять вручную или с помощью скрипт.

14 — SUBSTR

Функция SUBSTR по-разному работает в Oracle и PostgreSQL. В Oracle оператор SELECT SUBSTR (‘ABC’, — 1) FROM DUAL; возвращает «C», а эквивалентный SELECT SUBSTR (‘ABC’, — 1); в PostgreSQL вернет ABC.

15 — MERGE INTO

В PostgreSQL отсутствует оператор MERGE INTO, в отличие от Oracle.

16 — Синонимы

PostgreSQL не поддерживает синонимы. Вместо CREATE SYNONYM Oracle для доступа к удаленным объектам в PostgreSQL можно использовать SET search_path для включения удаленного определения.

17 — SYSDATE

Функция Oracle SYSDATE возвращает дату и время (в часовом поясе сервера). PostgreSQL не имеет соответствующей функции, но существует ряд методов для получения даты и времени для различных целей: statement_timestamp () дает текущую дату и время с начала текущего оператора; now () и transaction_timestamp () дают дату и время с начала текущей транзакции, а clock_timestamp () дает текущую дату и время с момента выполнения функции.

18 — TO_DATE

Функция to_date () как в Oracle, так и в PostgreSQL возвращает тип данных даты. Однако тип данных даты PostgreSQL предоставляет дату (год, месяц, день), а значение типа данных даты Oracle предоставляет дату и время (год, месяц, день, час, минута, секунда). Чтобы избежать этой несовместимости, используйте to_timestamp () PostgreSQL. Решением этой несовместимости является преобразование TO_DATE () в TO_TIMESTAMP ().

19 — Транзакции

Oracle всегда использует транзакции, но в PostgreSQL их нужно активировать. В Oracle выполнение любого оператора запускает транзакцию и заканчивается оператором COMMIT. В PostgreSQL транзакция начинается с оператора BEGIN, а также заканчивается оператором COMMIT. Уровни изоляции транзакций одинаковы в PostgreSQL и Oracle, и Read Committed является уровнем изоляции по умолчанию для обоих.

20 — Обработка ошибок транзакции

PostgreSQL создан для облегчения управления транзакциями и обработки ошибок с полной поддержкой ACID и уровнями изоляции. Он также способен обрабатывать ошибки времени выполнения и предоставляет надежные коды ошибок и сообщения для PL/pgSQL или кода приложения. Однако они обрабатываются иначе, чем Oracle, поэтому изменения все же необходимо внести. Вот несколько советов по оптимизации обработки ошибок в PostgreSQL:

• Контроль транзакций внутри PL / pgSQL не разрешен: вы не можете зафиксировать или откатить транзакцию внутри хранимой процедуры.
• Когда во время транзакции возникает исключительная ситуация во время выполнения, транзакция должна быть отменена, перед выполнением другой операции, потому что транзакция прерывается, когда обнаруживает ошибку. Журнал приложения покажет следующее сообщение об ошибке:
• Используйте блок BEGIN… EXCEPTION… END для обработки исключений, чтобы код улавливал любые возникающие ошибки. Это автоматически устанавливает точку сохранения перед блоком и откатывается к ней при возникновении исключения. Имейте в виду, что, поскольку блоки исключений создают точку сохранения, они дороги, поэтому добавляйте их осторожно.
• Сопоставьте коды ошибок и типы исключений из Oracle в PostgreSQL. Хотя некоторые коды ошибок одинаковы в обоих случаях, другие различаются. Язык программирования также влияет на это — например, специфичные для Oracle исключения JDBC должны быть заменены либо общими исключениями между базами данных, либо специфичными для PostgreSQL.
• Обеспечение правильной обработки транзакций и ошибок в базе данных PostgreSQL является важной частью процесса миграции и обычно требует тщательного анализа базы данных и кода приложения.