Миграция кода с Oracle на PostgreSQL: особенности и пути обхода, средства конвертации, вспомогательные модули

Эта статья завершает цикл о миграции с СУБД Oracle на СУБД PostgreSQL. В первых двух статьях рассматривались вопросы и устоявшиеся способы переноса данных из одной СУБД в другую (часть 1, часть 2). В третьей статье представлена часть особенностей, которые нужно учесть при переводе хранимого кода с PL/SQL на PL/pgSQL. В сегодняшнем материале рассматривается оставшаяся часть особенностей, адаптация и конвертация кода, включая выбор средств для конвертации.

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

Для таких структур рекомендуется использовать модуль pg_variables. Он позволяет сохранять как скалярные значения, так и множество записей, массивы.

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

Пример: у одного клиента процессы СУБД тратили большое количество времени на планирование запросов, поскольку они многократно пытались прочитать данные  pg_statistic и pg_class и при этом взять соответствующие блокировки. pg_statistic и pg_class являются одними из наиболее часто используемых объектов СУБД, поэтому время ожидания получения блокировки было существенным. Соответственно, от создания и удаления временных таблиц на каждую транзакцию пришлось отказаться.

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

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

В языке PL/SQL разработчик может создать свои собственные пользовательские исключения для обработки ошибок. Они могут быть созданы в блоке объявлений подпрограммы, при этом использовать их можно только внутри неё. Вызвать исключение нужно явно с помощью оператора RAISE или процедуры RAISE_APPLICATION_ERROR пакета DBMS_STANDARD. Ниже приведён пример:

CREATE TABLE customers (     id INTEGER,     name VARCHAR2(100),     address VARCHAR2(2000) );  INSERT INTO customers VALUES(1, 'Тестовый пользователь 1', 'Тестовый адрес 1'); INSERT INTO customers VALUES(2, 'Тестовый пользователь 2', 'Тестовый адрес 2'); INSERT INTO customers VALUES(3, 'Тестовый пользователь 3', 'Тестовый адрес 3');  ALTER TABLE customers ADD CONSTRAINT customers_pk PRIMARY KEY (id);  DECLARE     c_id customers.id%type := &cc_id;     c_name customers.name%type;     c_addr customers.address%type;          ex_invalid_id EXCEPTION;  BEGIN     IF c_id <= 0 THEN         RAISE ex_invalid_id;     END IF;          SELECT c.name, c.address INTO c_name, c_addr       FROM customers c      WHERE c.id = c_id;      dbms_output.put_line('Имя: '||  c_name);            dbms_output.put_line('Адрес: ' || c_addr);        EXCEPTION     WHEN ex_invalid_id THEN         dbms_output.put_line('Идентификатор клиента не может быть отрицательным');     WHEN no_data_found THEN         dbms_output.put_line('Клиент не найден'); END;

Ниже приведён пример перевода кода на СУБД PostgreSQL:

CREATE TABLE customers (     id bigint,     name VARCHAR(100),     address VARCHAR(2000) );  INSERT INTO customers VALUES(1, 'Тестовый пользователь 1', 'Тестовый адрес 1'); INSERT INTO customers VALUES(2, 'Тестовый пользователь 2', 'Тестовый адрес 2'); INSERT INTO customers VALUES(3, 'Тестовый пользователь 3', 'Тестовый адрес 3');  ALTER TABLE customers ADD CONSTRAINT customers_pk PRIMARY KEY (id);  CREATE OR REPLACE FUNCTION ex_invalid_id() RETURNS text AS $$     SELECT '06502'; $$ LANGUAGE SQL IMMUTABLE;   DO $$ DECLARE     c_id customers.id%type := 5;     c_name customers.name%type;     c_addr customers.address%type; BEGIN     IF c_id <= 0 THEN         RAISE EXCEPTION USING errcode = ex_invalid_id();     END IF;          SELECT c.name, c.address INTO STRICT c_name, c_addr       FROM customers c      WHERE c.id = c_id;           RAISE NOTICE 'Имя: %', c_name;     RAISE NOTICE 'Адрес: %', c_addr;    EXCEPTION     WHEN no_data_found THEN         RAISE NOTICE 'Клиент не найден';     WHEN others THEN         CASE SQLSTATE             WHEN ex_invalid_id() THEN                 RAISE NOTICE 'Идентификатор клиента не может быть отрицательным';         END CASE; END $$ LANGUAGE plpgsql;

Но есть один нюанс, касающийся исключений и их обработки. Во время выполнения BEGIN создаётся точка сохранения (SAVEPOINT), т.е. подтранзакция. Если главная транзакция успешно завершена, то будут подтверждены изменения всех её подтранзакций. Но если подтранзакция по какой-то причине прервалась, то её изменения отменяются. При обработке блока EXCEPTION неявно вызывается команда ROLLBACK TO SAVEPOINT.

При этом у каждой транзакции есть свой кеш подтранзакций, в котором хранятся их статусы. При создании снимка данных и проверки того, видно ту или иную строку или нет, нужно посмотреть статусы транзакции и её подтранзакций. В Postgres у каждого процесса кеш из 64 подтранзакций.

Если у хотя бы одной транзакции кэш переполнится, все процессы будут обращаться к структуре pg_subtrans. Был случай, когда все процессы ждали, когда в общий буферный кэш запишется нужная информация из pg_subtrans. То есть вместо выполнения расчётов все процессы ждали, это негативно сказалось на производительности приложения. Чтобы не попасть в такую ситуацию, разработчику приложения придётся думать над тем, сколько точек сохранения будет создано при обработке PL/pgSQL кода.

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

Операторы MERGE, INSERT FIRST и INSERT ALL

В СУБД Oracle часто применяется оператор MERGE для добавления новых записей и изменения существующих. Ниже приведён пример:

CREATE TABLE members (     member_id NUMBER PRIMARY KEY,     first_name VARCHAR2(50) NOT NULL,     last_name VARCHAR2(50) NOT NULL,     rank VARCHAR2(20) );  CREATE TABLE member_staging AS  SELECT * FROM members;  INSERT INTO members(member_id, first_name, last_name, rank)  VALUES(1, 'Имя1', 'Фамилия1', 'Золото');  INSERT INTO members(member_id, first_name, last_name, rank)  VALUES(2, 'Имя2', 'Фамилия2', 'Платина');  INSERT INTO member_staging(member_id, first_name, last_name, rank)  VALUES(1, 'Имя1', 'Фамилия1', 'Золото1');  INSERT INTO member_staging(member_id, first_name, last_name, rank)  VALUES(2, 'Имя2', 'Фамилия2', 'Платина1');  INSERT INTO member_staging(member_id, first_name, last_name, rank)  VALUES(3, 'Имя3', 'Фамилия3', 'Серебро1');  MERGE INTO member_staging x USING (SELECT member_id, first_name, last_name, rank FROM members) y    ON (x.member_id  = y.member_id)  WHEN MATCHED THEN      UPDATE          SET x.first_name = y.first_name           , x.last_name = y.last_name           , x.rank = y.rank       WHERE x.first_name <> y.first_name OR              x.last_name <> y.last_name OR              x.rank <> y.rank   WHEN NOT MATCHED THEN       INSERT (x.member_id, x.first_name, x.last_name, x.rank)       VALUES (y.member_id, y.first_name, y.last_name, y.rank);

В СУБД PostgreSQL оператор MERGE добавлен в 15-ую версию, в ранних выпусках вместо него в ряде случаев возможно использовать INSERT ON CONFLICT DO UPDATE:

CREATE TABLE members (     member_id bigint,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     rank VARCHAR(20) );  CREATE TABLE member_staging (     member_id bigint,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     rank VARCHAR(20) );  INSERT INTO members(member_id, first_name, last_name, rank)  VALUES(1, 'Имя1', 'Фамилия1', 'Золото');  INSERT INTO members(member_id, first_name, last_name, rank)  VALUES(2, 'Имя2', 'Фамилия2', 'Платина');  INSERT INTO member_staging(member_id, first_name, last_name, rank)  VALUES(1, 'Имя1', 'Фамилия1', 'Золото1');  INSERT INTO member_staging(member_id, first_name, last_name, rank)  VALUES(2, 'Имя2', 'Фамилия2', 'Платина1');  INSERT INTO member_staging(member_id, first_name, last_name, rank)  VALUES(3, 'Имя3', 'Фамилия3', 'Серебро1');  ALTER TABLE members ADD CONSTRAINT members_pk PRIMARY KEY(member_id);  ALTER TABLE member_staging    ADD CONSTRAINT member_staging_pk PRIMARY KEY(member_id);  INSERT INTO member_staging AS x SELECT m.member_id      , m.first_name      , m.last_name      , m.rank   FROM members m     ON CONFLICT (member_id)     DO UPDATE           SET first_name = EXCLUDED.first_name             , last_name = EXCLUDED.last_name             , rank = EXCLUDED.rank         WHERE x.member_id = EXCLUDED.member_id            AND (x.first_name <> EXCLUDED.first_name OR                 x.last_name <> EXCLUDED.last_name OR                 x.rank <> EXCLUDED.rank);

В СУБД PostgreSQL при одновременном выполнении нескольких команд вида INSERT ON CONFLICT DO UPDATE не происходит нарушений целостности данных, в отличии от СУБД Oracle. Там при одновременном выполнении нескольких команд MERGE возможно появление дубликатов, что приводит к нарушениям ограничений первичного ключа.

В СУБД Oracle конструкция INSERT ALL позволяет добавлять данные в несколько таблиц одновременно при выполнении одного запроса. При этом можно добавлять условия, что позволяет добавлять строки в определённую таблицу:

CREATE TABLE orders_t (     order_id NUMBER(12) PRIMARY KEY,     customer_id NUMBER(6) NOT NULL,     status VARCHAR2(20) NOT NULL,     salesman_id NUMBER(6),     order_date DATE NOT NULL );   CREATE TABLE order_items_t (     order_id NUMBER(12),     item_id NUMBER(12),     product_id NUMBER(12) NOT NULL,     quantity NUMBER(8) NOT NULL,     unit_price NUMBER(8, 2) NOT NULL );  CREATE TABLE small_orders (     order_id NUMBER(12) NOT NULL,     customer_id NUMBER(6) NOT NULL,     amount NUMBER(8,2)  );  CREATE TABLE medium_orders AS SELECT *   FROM small_orders;  CREATE TABLE big_orders AS SELECT *   FROM small_orders;  INSERT ALL   WHEN amount < 10000 THEN INTO small_orders   WHEN amount BETWEEN 10000 AND 30000 THEN INTO medium_orders   WHEN amount > 30000 THEN INTO big_orders SELECT o.order_id      , o.customer_id      , oi.quantity * oi.unit_price AS amount   FROM orders_t o   JOIN order_items_t oi      ON oi.order_id = o.order_id;

Ниже приведён пример команды INSERT FIRST, при выполнении которой каждая строка подзапроса проверяется на выполнение условий WHEN. При выполнении условия строка запишется в соответствующую таблицу, остальные условия проверяться не будут.

INSERT FIRST   WHEN amount > 30000 THEN INTO big_orders   WHEN amount >= 10000 THEN INTO medium_orders   WHEN amount > 0 THEN INTO small_orders SELECT o.order_id      , o.customer_id      , oi.quantity * oi.unit_price AS amount   FROM orders_t o   JOIN order_items_t oi      ON oi.order_id = o.order_id;

В СУБД PostgreSQL применяются общие табличные выражения (CTE) и, при необходимости, ключевое слово RETURNING.

CREATE TABLE orders_t (     order_id bigint PRIMARY KEY,     customer_id int NOT NULL,     status VARCHAR(20) NOT NULL,     salesman_id int,     order_date timestamp NOT NULL );   CREATE TABLE order_items_t (     order_id bigint,     item_id bigint,     product_id bigint NOT NULL,     quantity int NOT NULL,     unit_price numeric(8,2) NOT NULL );  CREATE TABLE small_orders (     order_id bigint NOT NULL,     customer_id int NOT NULL,     amount numeric(8,2)  );  CREATE TABLE medium_orders AS SELECT * FROM small_orders;  CREATE TABLE big_orders AS SELECT * FROM small_orders LIKE small_orders(all);  WITH orders AS ( SELECT o.order_id      , o.customer_id      , oi.quantity * oi.unit_price AS amount   FROM orders_t o   JOIN order_items_t oi      ON oi.order_id = o.order_id ), so AS ( INSERT INTO small_orders SELECT s.*   FROM orders s  WHERE s.amount < 10000  ), mo AS ( INSERT INTO medium_orders SELECT s.*   FROM orders s  WHERE s.amount BETWEEN 10000 AND 30000 ) INSERT INTO big_orders SELECT s.*   FROM orders s  WHERE s.amount > 30000;

Стоит отметить, что начиная с 12-ой версии СУБД PostgreSQL поведение CTE можно контролировать в плане материализации полученного множества. Также запросы в WITH в общем случае могут выполняться параллельно и потому порядок их выполнения непредсказуем. В частности, для контроля порядка выполнения и используется конструкция RETURNING.

Пользуясь случаем, остановимся подробнее на INSERT ON CONFLICT DO UPDATE. Часто возникают ситуации, при которых разработчики применяют конструкции INSERT INTO TABLE VALUES следующим образом.

insert into "user_sequence" ("user_id","seq","timestamp","rkey","mapping") values ($1,$2,$3,$4,$5),($6,$7,$8,$9,$10),($11,$12,$13,$14,$15), ($16,$17,$18,$19,$20),($21,$22,$23,$24,$25),($26,$27,$28,$29,$30), ($31,$32,$33,$34,$35),($36,$37,$38,$39,$40),($41,$42,$43,$44,$45), ($46,$47,$48,$49,$50),($51,$52,$53,$54,$55),($56,$57,$58,$59,$60), ($61,$62,$63,$64,$65),($66,$67,$68,$69,$70),($71,$72,$73,$74,$75), ($76,$77,$78,$79,$80),($81,$82,$83,$84,$85),($86,$87,$88,$89,$90), ($91,$92,$93,$94,$95),($96,$97,$98,$99,$100), ($101,$102,$103,$104,$105),($106,$107,$108,$109,$110), ($111,$112,$113,$114,$115),($116,$117,$118,$119,$120), ($121,$122,$123,$124,$125),($126,$127,$128,$129,$130), ($131,$132,$133,$134,$135),($136,$137,$138,$139,$140), ($141,$142,$143,$144,$145),($146,$147,$148,$149,$150), ($151,$152,$153,$154,$155),($156,$157,$158,$159,$160);

При этом разработчики используют связанные переменные (bind variables) и надеются на хорошую производительность. Но её не будет, поскольку текст запроса меняется в зависимости от количества строк. PostgreSQL будет постоянно разбирать и планировать эти запросы, производительность будет не очень высокой.

Существуют два подхода, которые позволяют избежать этой проблемы.

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

Во-вторых, если требуется INSERT ON CONFLICT DO UPDATE, можно поступить так:

1. Взять промежуточную таблицу.

2. Опустошить её с помощью TRUNCATE.

3. Сохранить туда данные командой COPY.

4. Сделать INSERT INTO (таблица) SELECT FROM (промежуточная таблица) DO UPDATE.

Конструкция DECODE

Конструкция DECODE очень распространена в мире Oracle, для её замены в Postgres можно использовать CASE. В работе DECODE есть нюансы.

Предположим, что в DECODE есть 12 условий и четвёртое из них первой прошла проверку. В этом случае будет вычислено выражение, соответствующее ему, а все остальные условия от 5-го до 12-го не будут вычисляться.

В модуле orafce есть DECODE, но в виде функции. Это означает, что до вызова функции нужно вычислить все её аргументы. В процессе функционального тестирования одной из конструкций DECODE выяснилось, что её последним выражением является деление единицы на ноль. В Oracle ошибки не было, потому что до этого выражения DECODE бы не дошёл. PostgreSQL же считает все аргументы функции до её выполнения ,поэтому возникла ошибка деления на ноль. Пришлось переписывать конструкцию, используя CASE. Код стал чуть более громоздким, но ошибки при этом перестали появляться.

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

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

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

Иногда возникает потребность проверки строк на фонетическую схожесть. Для этого существуют различные алгоритмы, такие как:

• Jaro-Winkler.

• Levenshtein.

• Soundex distance.

Схожий функционал предлагает модуль pg_similarity

Алгоритм Levenshtein также поддерживается модулем fuzzystrmatch, входящий в стандартную поставку СУБД PostgreSQL.

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

Для создания аналога автономных транзакций Oracle в СУБД PostgreSQL существуют три способа:

1.  dblink. Создаётся отдельное подключение и отдельная независимая транзакция. Но стоит помнить, что при подключении к СУБД создаётся обслуживающий процесс, что является дорогой операцией.

2. Использование фоновых процессов при помощи pg_background. Потребуется оформить бизнес-логику в виде функции и поручить фоновому процессу её выполнить с помощью функции pg_background_launch(). Проверить результат выполнения можно с помощью функции pg_background_result().

3. Механизм автономных транзакций уже реализован в Postgres Pro Enterprise с версии 9.6.

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

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

Что касается pgtap, то это набор PL/pgSQL функций для написания тестов. В частности, если есть функция и некоторые значения параметров, то можно прописать, что данная функция при таких-то условиях должна возвращать определённое значение или выбрасывать исключения.

Если говорить о pg_prove, то это написанная на Perl утилита для запуска разработанных с помощью pgtap регрессионных тестов.

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

Здесь тоже есть ряд решений на выбор:

• pg_cron.

• pgAgent.

• Модуль СУБД Postgres Pro Enterprise pgpro_scheduler.

• Любой планировщик заданий прикладного ЯП.

В третьей части цикла было указано, что pgAgent можно устанавливать и использовать отдельно от pgAdmin. Он используется только для того, чтобы задавать задания для pgAgent в графическом интерфейсе. Для корректной работы pgAgent совсем необязательно использовать pgAdmin, можно обойтись обычным SQL-интерфейсом.

Если Вы клиент Postgres Professional и у Вас установлена версия Postgres Pro Enterprise, можно пользоваться pgpro_scheduler.

Можно  также использовать любой реализованный на прикладном языке программирования планировщик заданий и адаптировать его под свои требования.

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

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

pg_hint_plan позволяет управлять:

• Методами доступа к строкам таблицы.

• Методами соединения множеств.

• Порядком соединения множеств (этого же можно добиться с помощью параметрами join_collapse_limit и from_collapse_limit).

• Методами исправления количества возвращаемых строк.

• Параметрами GUC во время работы планировщика.

Утилиты для автоматизации конвертации хранимого кода

Поговорим о решениях, позволяющих обеспечить автоматическую конвертацию хранимого кода:

• ora2pg и производные от него решения (LUI4ORA2PG).

• ANTLR4 и грамматика для PL/SQL кода.

Уже упоминавшийся в предыдущих статьях модуль ora2pg может преобразовать часть хранимого кода, но при работе со сложными вещами возможны нюансы, о которых будет рассказано далее. LUI4ORA2PG — разработка компании «ФОРС». Это дорабатываемый инструмент, в котором можно создавать свои проекты миграции, в качестве основы разработчики используют ora2pg.

Пожалуй, наиболее правильным подходом является использование грамматики для PL/SQL кода и техники ANTLR4. В этом случае исходный код фактически преобразуется в дерево разбора. Разработчику придётся разработать логику обхода этого дерева самостоятельно, в результате, хранимый код исходной СУБД будет преобразован в соответствующий код целевой СУБД.

Платные конвертеры сделаны на основе упомянутой выше технологии. В данном докладе описан процесс автоматического перевода кода с PL/SQL на PL/pgSQL с помощью ANTLR4.

Особенности конвертации кода утилитой ora2pg

Инструмент ora2pg не покрывает всевозможные конструкции хранимого кода. Приведём несколько примеров:

1. Не поддерживается конвертация старого синтаксиса соединения таблиц в СУБД Oracle.

2. Частичная конвертация конструкции DECODE в CASE.

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

4. Не поддерживается конвертация старого и нового синтаксиса рекурсивных запросов.

5. Не всегда корректно преобразуются запросы с использованием двойных кавычек. Возможна потеря регистра. Иногда ora2pg меняет регистр идентификаторов, заключённых в двойные кавычки. В некоторых случаях регистр имеет значение.

6. Не поддерживается конвертация конструкции KEEP (DENSE_RANK LAST ORDER BY) OVER(PARTITION BY ), нужно использовать расширение first_last_agg.

7. Не всегда корректно преобразуется код динамических запросов. Иногда в хранимом коде требуется динамически формировать команды внутри функций, поскольку при каждом выполнении могут использоваться разные таблицы или типы данных. В СУБД Oracle для этого используется команда EXECUTE IMMEDIATE, а в СУБД PostgreSQL — EXECUTE.

   Были случаи, когда динамический код просто оставался в исходном виде, нужно было его переписывать. В частности, в СУБД Postgres нет таких типов, как BLOB, VARCHAR, CLOB.

8. Не конвертируется код с использованием пакетов dbms_xmldom, dbms_lob и.т.д.

Модуль orafce

Модуль orafce предоставляет функции и операторы для замены части функций и пакетов:

• Тип date для хранения значений «дата + время”.

• Функции для работы с типом date (add_months, next_day, last_day, months_between, trunc).

• Пакеты dbms_output, utl_file, dbms_pipe, dbms_alert.

• PLVstr и PLVchr для работы со строками.

Использование вспомогательных функций при работе планировщика

Стоит сказать пару слов и о функциях, которые возвращают множества. В Oracle речь идёт о конвейерной (pipeline) функции, которая на выходе возвращает множество. Рассмотрим пример.

CREATE OR REPLACE TYPE varchar_t AS TABLE OF VARCHAR2(4000);  CREATE OR REPLACE FUNCTION split_str(p_str VARCHAR, p_delim VARCHAR)  RETURN varchar_t PIPELINED AS BEGIN FOR v_row IN  (SELECT regexp_substr(p_str, '[^' || p_delim || ']+', 1, level) AS elem       , '[^' || p_delim || ']+' AS delim     FROM DUAL CONNECT BY regexp_substr(p_str, '[^' || p_delim || ']+', 1, level) is not null     ORDER BY LEVEL ) LOOP   PIPE ROW(v_row.elem); END LOOP; END;  SELECT * FROM TABLE(split_str('XXX,Y,ММ,AAAAA,B,CCC,D,E,F,GGG', ','));

Как правило, данный код заменяется в СУБД PostgreSQL на конструкцию вида:

SELECT * FROM regexp_split_to_table('XXX,Y,ММ,AAAAA,B,CCC,D,E,F,GGG', ',');

Ниже приведён её план выполнения.

                                  QUERY PLAN -----------------------------------------------------------------------------  Function Scan on regexp_split_to_table  (cost=0.00..10.00 rows=1000 width=32)   (actual time=0.025..0.025 rows=10 loops=1)

Видно, что расчётное количество строк в 100 раз превышает фактическое. До 12-ой версии СУБД PostgreSQL с точки зрения планировщика функции были чёрным ящиком, в качестве расчётного количества строк бралось значение prorows из pg_proc. Для функции regexp_split_to_table данное значение = 1000. При дальнейшем использовании этого расчётного количества, например, при соединении с другим множеством существует возможность выбора некорректного метода соединения, что приведёт к деградации работы запроса в целом.

В 12-ой версии команда CREATE FUNCTION была расширена следующим образом:

CREATE FUNCTION name (...) RETURNS ... SUPPORT supportfunction AS

Т.е, появилась возможность предоставления вспомогательной функции, обладающей информацией об основной функции и способствующей выбору лучшего плана выполнения. При работе с функцией, возвращающей множество, PostgreSQL вызывает вспомогательную функцию для оценки количества получаемых строк. Это было разработано для функции unnest(), что позволяет заменить regex_split_to_table() на следующую конструкцию:

SELECT * FROM unnest(regexp_split_to_array('XXX,Y,ММ,AAAAA,B,CCC,D,E,F,GGG', ','));

Ниже приведён план выполнения.

EXPLAIN(ANALYZE)  SELECT * FROM unnest(regexp_split_to_array('XXX,Y,ММ,AAAAA,B,CCC,D,E,F,GGG', ','));                                                QUERY PLAN -----------------------------------------------------------------------------  Function Scan on unnest  (cost=0.00..0.10 rows=10 width=32)  (actual time=0.012..0.014 rows=10 loops=1)  Planning Time: 2.591 ms  Execution Time: 0.353 ms

В этом случае расчётное количество строк не отличается от фактического.

Расширенная статистика в СУБД PostgreSQL

В СУБД Postgres предусмотрено три вида расширенной статистики:

1. dependencies (функциональные зависимости).

2. n_distinct (многовариантное число различных значений).

3. mcv (многовариантные списки MCV).

Первый тип, dependencies, появился ещё в PostgreSQL 10 и служит для отслеживания функциональных зависимостей между столбцами. Например, если город — Казань, то его код 843, если город Москва, код  — 495. Эта расширенная статистика подходит для операций равенства, сравнивающих значения столбцов с константами, и условий IN с константами. Она не используется при:

1. Проверке равенства двух столбцов или сравнении столбца с выражением.

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

3. Проверке условий LIKE.

Для чего нужен ndistinct? Изначально в статистике СУБД PostgreSQL хранится информация о количестве уникальных значений столбца, но не для нескольких столбцов. Данный вид расширенной статистики используется в оценке количества уникальных групп, полученных при выполнении GROUP BY.

Статистика mcv — список наиболее часто встречающихся значений. Она доступна в PostgreSQL с версии 12 и позволяет определить наиболее часто встречающиеся значения для комбинации столбцов. Раньше они хранились только по одиночным столбцам. Если до этого в запросе было много условий фильтрации, объединённых логическими И (AND), то планировщик, как правило, ошибался в оценке расчётного количества строк. mcv позволяет планировщику оценить количество строк более точно.

Расширенные статистики можно комбинировать между собой. Пусть есть запрос с несколькими условиями фильтрации, объединённых операцией И (AND), с конструкцией GROUP BY по нескольким столбцам. Тогда можно создать расширенную статистику типа mcv и ndistinct, планировщик воспользуется ими для определения расчётного количества строк.

Также в PostgreSQL 14 появилась возможность создавать статистику на набор вычисляемых столбцов. Подробнее можно прочитать об этом здесь.

Поиск некорректного PL/pgSQL кода и зависимостей

Существуют средства для проверки PL/pgSQL кода. Модуль plpgsql_check позволяет отследить:

1. Некорректное использование типов параметров функций. Из-за этого, например, может использоваться полное сканирование таблицы вместо сканирования по индексу.

2. Неиспользуемые переменные и функции.

3. Отсутствие команды возврата значения.

4. Недостижимый код.

Ниже приведён пример вывода результата проверки функции.

Также можно определять зависимости между объектами.

Модуль plprofiler

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

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

Во-вторых, он генерирует HTML-отчёты, которые можно проанализировать. Они создаются для профилей производительности PL/pgSQL функций и процедур.

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

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

Ещё один визуальный пример. Видно, что у tpcb_upd_accounts() есть некие проблемы, да и у tpcb_fetch_abalance() тоже, потому что её выполнение занимает половину общего времени. Причина также заключается в отсутствии индекса в таблице pgbench_accounts, в котором столбец aid является лидирующим.

Стоит отметить, что модуль plprofiler можно использовать в рамках трёх сценариев:

1. Проверка и замер скорости работы определённой функции.

2. Замер скорости работы пользовательских процессов, оценка общего профиля нагрузки.

3. Использование модуля в промышленном окружении. Стоит отметить, что в промышленном окружении нельзя включить замеры работы для всех сессий, иначе просядет производительность. Тем не менее, профилирование можно включить на время. Есть риск, что часть нужной информации не будет записана. Некоторые функции могут начать свою работу до включения замеров.

Поэтому для точного анализа сессий, которые выбиваются из общей картины, можно использовать модуль pg_query state. Он покажет запрос, выполняемый в настоящий момент времени, и его план выполнения.

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