В новой версии своего флагманского дистрибутива Postgres Pro 15 Enterpise, компания Postgres Pro добавила поддержку функциональности пакетов: расширен синтаксис PL/pgSQL и добавлены новые механизмы в среду выполнения хранимых процедур.

В данной статье описана поддержка функциональности пакетов в Postgres Pro 15 Enterpise:

• группировка типов переменных процедур и функций в пакет;

• поддержка секции инициализации пакетов;

• поддержка глобальных переменных пакета доступных в течении всей «жизни» сессии.

Также в статье будут рассмотрены особенности использования пакетов в СУБД Postgres Pro Enterprise.

Введение

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

Для решения вышеописанной проблемы, то есть для упрощения миграции с Oracle, в СУБД  Postgres Pro 15 Enterprise была добавлена поддержка функциональности пакетов (packages) «в стиле Oracle». Необходимо отметить, что пакеты в Postgres Pro 15 Enterprise не обеспечивают полную поддержку синтаксиcа Oracle PL/SQL, то есть ручная доработка кода при миграции все равно потребуется. Тем не менее, поддержка функциональности пакетов позволяет в разы уменьшить затраты на изменение кода при миграции.

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

Что такое пакет в СУБД Oracle 

Прежде чем перейти к описанию функциональности пакетов в СУБД Postgres Pro 15 Enterprise, кратко напомним, что такое пакет в СУБД Oracle.

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

Пакет состоит из двух частей, спецификации пакета и тела пакета, хотя в общем случае, тело пакета может отсутствовать. 

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

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

Ниже приведен пример спецификации PL/SQL-пакета counter в СУБД Oracle, содержащего глобальную переменную n и функцию inc:

create or replace package   counter is

    n int;

    function inc return int;

end; 

В теле пакета counter в СУБД Oracle присутствует объявление приватной глобальной переменной k (доступна только в теле пакета) и реализация функции inc:

create or replace package body counter is

    k int := 3; -- переменная доступна только в теле пакета

    function inc return int is

    begin

        n := n + 1;

        return k + n;

    end;

   

begin -- Секция инициализации пакета

    n := 1;

    for i in 1..10 loop

        n := n + n;

    end loop;

end;

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

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

set serveroutput on

begin

    dbms_output.put_line(counter.n);

    dbms_output.put_line(counter.inc());

end;

/

1024

1028

Поддержка функциональности пакетов в Postgres Pro 15 Enterprise

Для реализации  функциональности пакетов СУБД Oracle были добавлены новые команды, а также дополнительные директивы (прагмы) интерпретатора PL/pgSQL.

Следует отметить, что это именно реализация основной функциональности пакетов, а не полное копирование синтаксиса и логики из СУБД Oracle. Главное назначение этой новой возможности – упрощение миграции кода Oracle PL/SQL при переходе в PL/pgSQL Postgres Pro.

Текущий подход к поддержке функциональности пакетов подразумевает небольшие изменения в языке PL/pgSQL и основан на использовании схем (schema) PostgreSQL в качестве функциональных аналогов пакетов. Введены дополнительные соглашения о функции инициализации и допустимом содержимом схемы, при выполнении которых схема может рассматриваться как пакет. Также добавлены дополнительные команды для работы с пакетами, которые соответствующим образом работают с такими особыми схемами.

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

Соглашения о пакетах в Postgres Pro Enterprise

Определение пакета:

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

• Функцией инициализации пакета (init-функцией) называется содержащаяся в соответствующей схеме функция на языке PL/pgSQL с именем __init__, не имеющая параметров и возвращающая тип void.

• Глобальными переменными пакета являются переменные, объявленные в функции инициализации.

Таким образом, переменные, определенные в блоке declare функции инициализации пакета, считаются пакетными и доступны для обращения из других функций пакета по имени, а извне его – при импортировании (см. раздел "Новые синтаксические элементы") – через квалифицированные имена (с указанием имени пакета через точку). Например: переменная bar, определенная в declare-блоке init-функции пакета foo, доступна извне пакета как foo.bar.

Отдельно стоит обратить внимание на то, что пакетные переменные доступны только из кода на языке PL/pgSQL. В хранимых процедурах, разработанных на других языках, например на PL/Perl, PL/V8 – они недоступны. Для получения значений глобальных переменных пакета из SQL-запроса (оператор SELECT) или DML-оператора (INSERT, UPDATE, DELETE) в пакет необходимо добавить функцию, которая должна возвращать значение глобальной переменной. Подобное поведение аналогично СУБД Oracle – в ней точно также глобальные переменные пакетов недоступны в SQL-запросах.  Например:

SQL> select counter.n from dual;

select counter.n from dual

       *

ERROR at line 1:

ORA-06553: PLS-221: 'N' is not a procedure or is undefined

Пакетные переменные могут быть константами – синтаксис определения и поведение таких констант стандартны для PL/pgSQL.

Функция инициализации пакета вызывается автоматически при первом (в текущей сессии) обращении к какой-либо его функции или процедуре, имеющей статус пакетной (см. раздел "Новые синтаксические элементы"), к любой глобальной переменной пакета, или же при выполнении любого блока кода (анонимному, процедуры или функции) импортирующего данный пакет. Ручной вызов функции инициализации не требуется, но при желании может быть выполнен, что приведет к сбросу значений пакетных переменных. Также предусмотрена специальная функция plpgsql_reset_packages() для  сброса глобальных переменных всех пакетов в текущей сессии – аналог процедуры DBMS_SESSION.RESET_PACKAGES в СУБД Oracle.

Вышеописанный пример пакета counter в СУБД Oracle, при его портировании в СУБД Postgres Pro Enterprise, примет следующий вид (синтаксис оператора создания пакета аналогичен как в СУБД Oracle):

create or replace package counter
 
    create function __init__() returns void as $$ -- "cекция" инициализации пакета
    declare
        n int := 1;
        k int := 3; -- переменная стала публичной! 

    begin
        for i in 1..10 loop
            n := n + n;
        end loop;
    end;
    $$
 
    create function inc() returns int as $$
    begin
        n := n + 1;
        return k+n;
    end;
    $$
;

Использование этого пакета в СУБД Postgres Pro Enterprise будет выглядеть следующим образом:

do $$
#import counter
begin
    raise notice '%', counter.n;
    raise notice '%', counter.inc();
end;
$$;
 
NOTICE:  1024
NOTICE:  1028

Отдельно отметим нюансы использования пакетов:

1. Пакет не может содержаться в схеме, т.к. сам является схемой.

2. Пакет не может иметь одинаковое имя с какой-либо еще схемой.

3. Пакет может содержать только  типы, глобальные переменные, константы, курсоры, процедуры и функции. НЕ поддерживается cоздание таблиц, представлений и последовательностей в пакете-схеме. Это ограничение точно также есть и в СУБД Oracle - невозможно в пакете PL/SQL вставить операто создания таблицы (CREATE TABLE), представления (CREATE VIEW), последовательности (CREATE SEQUENCE) и т.д.

4. Все элементы пакета являются публичными, то есть все элементы пакета доступны из любого импортировавшего пакет блока кода. Спецификация и тело пакета отдельно НЕ определяются.

5. Как функции пакета, так и импортирующие его блоки кода должны быть написаны на языке PL/pgSQL. 

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

7. Инициализация глобальной переменной пакета может производиться как при ее объявлении (declare x int := 42;), так и позже – в теле функции инициализации __init__(). Для использующего пакет внешнего кода эти варианты эквивалентны.

8. Объявления типов в пакете должны располагаться в начале пакета, до определения первой функции или процедуры пакета.

9. Функция инициализации пакета должна быть первой подпрограммой, определенной в пакете.

Новые синтаксические элементы

Для поддержки функциональности пакетов, в СУБД Postgres Pro Enterprise были добавлены следующие нестандартные синтаксические элементы:

1. Модификаторы (прагмы функций).  Прагма – это директива интерпретатора PL/pgSQL

   1. #package

   2. #import

2. Встроенные функции:

   1. plpgsql_reset_packages();

3. SQL команды:

   1. CREATE [OR REPLACE] PACKAGE

   2. DROP PACKAGE

Рассмотрим перечисленные элементы.

Модификаторы функций

Модификаторы функций начинаются с символа # и располагаются между заголовком функции и необязательным блоком declare (см. пример в разделе "CREATE [OR REPLACE] PACKAGE").

Более подробная информация про модификаторы функций приведена в документации. См. раздел «Модификаторы функций».

#package

Модификатор #package в функции или процедуре схемы сообщает, что данная функция должна трактоваться как пакетная. Это означает следующее:

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

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

Функция инициализации (__init__) не должна содержать модификатора #package. Создание функции с модификатором #package возможно только в схеме, уже содержащей функцию инициализации. Например:

create schema if not exists logger;

create or replace function logger.__init__ () returns void as $body$
declare
  v_gcurrentuser varchar(32);
begin
  v_gcurrentuser := current_user;
end;
$body$
language plpgsql;

create or replace procedure logger.trace(v_pMessage inout text) as $body$
#package
begin autonomous
  insert into log_messages_tab(level,message) values (600,v_pMessage);
end
$body$
language plpgsql;

В приведенном выше примере создается пакетная процедура trace в пакете logger, которая в автономной транзакции записывает информацию в лог-таблицу.

Модификатор #package можно опускать при создании функции в рамках команды CREATE [OR REPLACE] PACKAGE.

#import

Модификатор #import сообщает, что данная функция собирается работать с переменными некоторого внешнего пакета (набора пакетов). Это называется импортированием пакета и означает следующее:

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

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

Формат:

#import <packages_list>

, где обязательный  параметр <packages_list> представляет собой список имен импортируемых пакетов, разделенных запятыми. Вместо списка может использоваться последовательность нескольких прагм. Например:

#import foo, bar

эквивалентно:

#import foo

#import bar

Ниже в примере в процедуре showValues используется обращение к двум пакетам: htp и dbms_application_info

create or replace procedure showValues(p_Str varchar) as $$
#import htp, dbms_application_info
begin
  call dbms_application_info.set_action('Show hello');
 
  call htp.p('<p>' || p_Str || '</p>');
end;
$$language plpgsql;

Также следует отметить, что пакеты, импортированные в функции __init__(), автоматически импортируются во всех остальных функциях данного пакета.

Встроенная функция plpgsql_reset_packages

Функция plpgsql_reset_packages() предопределена в расширении plpgsql и приводит к сбросу всех пакетов в состояние "не инициализирован". Т.е. в отношении инициализации пакетов (но только инициализации!) вызов

select plpgsql_reset_packages();

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

Эта функция аналогична процедуре dbms_session.reset_package в СУБД Oracle. 

Новые SQL-команды

CREATE [OR REPLACE] PACKAGE

Более подробно о новых SQL-командах можно почитать в документации. См. раздел «Команды SQL»

Команда позволяет создать или изменить весь пакет одной командой. Формат вызова:

CREATE [OR REPLACE] PACKAGE <package_name> <package_body>;

Для наглядности удобнее разбивать вызов на несколько строк, что и будет делаться далее в тексте. 

В приведенном формате <package_name> – обычное имя схемы, удовлетворяющее всем стандартным требованиям, а <package_body> – последовательность создающих пакет команд с рядом особенностей:

1. Опускается команда CREATE SCHEMA – она выполняетcя автоматически.

2. Модификатор #package не используется:  все определенные в рамках команды функции получают его автоматически.

3. При создании функций не указывается "language plpgsql".

4. Имена создаваемых функций и типов указываются без квалификатора имени пакета.

5. В конце подкоманд создания функций и типов не ставится точка с запятой.

Например, так могло бы выглядеть создание простого пакета посредством стандартных команд работы со схемами:

create schema foo;
 
create type foo.footype as (a int, b int);
 
create function foo.__init__() returns void as $$
declare
  x int := 1;
begin
  raise notice 'foo inited';
end;
$$ language plpgsql;
 
create function foo.get() returns int as $$
#package
begin
  return x;
end;
$$ language plpgsql;
 
create function foo.inc() returns void as $$
#package
begin
  x := x + 1;
end;
$$ language plpgsql;

А так его можно полностью аналогично переписать при помощи команды CREATE [OR REPLACE] PACKAGE:

create package foo
  create type footype as (a int, b int)
 
  create function __init__() returns void as $$
  declare
    x int := 1;
  begin
    raise notice 'foo inited';
  end;
  $$
 
  create function get() returns int as $$
  begin
    return x;
  end;
  $$
 
  create function inc() returns void as $$
  begin
    x := x + 1;
  end;
  $$
;

Строгая форма CREATE PACKAGE <package_name> успешно  выполнится только при отсутствии в БД схемы <package_name>. Необязательная вставка [OR REPLACE], также как и в СУБД Oracle, позволяет заменить уже существующий пакет, стараясь при этом сохранить внешние зависимости. 

Правила работы замещающей функции CREATE OR REPLACE PACKAGE:

1. Замещать можно только схемы, содержащие функции, процедуры и типы. Любые другие элементы придется предварительно удалить при помощи команд DROP.

2. Элементы старого пакета, отсутствующие в новом определении, будут удалены, если нет внешних объектов, зависящих от них. Если такие объекты есть, команда завершится с ошибкой.

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

4. Замена типа возможна только при отсутствии внешних зависимостей от него. Если такие зависимости есть, и новое определение типа отличается от старого, команда завершится с ошибкой. Если новое определение типа не отличается от старого, то подкоманда CREATE TYPE игнорируется. В случае недеструктивного изменения типа (например, просто добавляется атрибут), cледует рассмотреть изменение определения типа отдельно, c помощью команды ALTER TYPE.

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

Команда CREATE [OR REPLACE] PACKAGE выполняется как транзакция, т.е. ошибка в описании одного элемента приводит к откату изменений всего пакета.

DROP PACKAGE

Команда позволяет удалить пакет (схему) с учетом пакетных зависимостей. Формат вызова:

DROP PACKAGE <package_names_list> [CASCADE] [IF EXISTS];

, где обязательный параметр <package_names_list> представляет собой список удаляемых пакетов. Команда выполняется транзакционно, т.е. либо удаляются все перечисленные пакеты, либо – при ошибке – ни один. Поведение команды DROP PACKAGE ... [CASCADE] [IF EXISTS] сходно с поведением DROP SCHEMA ... [CASCADE] [IF EXISTS] за исключением ряда особенностей:

1. Команду DROP PACKAGE ... [CASCADE] [IF EXISTS] можно применить только к схеме, являющейся пакетом. Попытка удаления c ее помощью схемы, не удовлетворяющей определению пакета, приведет к ошибке.

2. Если команда DROP PACKAGE завершилась с ошибкой из-за наличия внешних зависимостей от элементов удаляемого пакета, то зависимые внешние объекты, входящие в состав других пакетов не будут перечисляться по отдельности, а вместо них будет указано имя зависимого пакета.

3. Если команда DROP PACKAGE CASCADE находит объект, зависящий от удаляемого, и определяет, что этот объект входит в состав другого пакета, то зависимый пакет удаляется целиком.

Удаление схемы являющейся пакетом, при помощи команды DROP SCHEMA ... [CASCADE] [IF EXISTS] работает так же, как и для обычных схем.

Следует отметить, что все упомянутые в описании команд CREATE [OR REPLACE] PACKAGE и DROP PACKAGE «зависимости» являются зависимостями в стандартном для PostgreSQL смысле, т.е. зависимыми по сигнатурам (имя функции и ее параметры). Например, использование одной функции в сигнатуре другой формирует такую зависимость, а просто вызов одной функции в теле другой – нет. Аналогично не формирует зависимостей и обращение к пакетным переменным в теле сторонней функции – в т.ч. и через функцию-геттер.

Добавленные DDL-команды CREATE [OR REPLACE] PACKAGE и DROP PACKAGE делают работу с пакетами более удобной и прозрачной, однако, весь основной функционал поддержки пакетов доступен и без них – при помощи описанных выше прагм и при соблюдении соглашений. 

Ограничения в текущем релизе 

1. В СУБД Oracle пакет является целостной и неделимой структурой: создается и меняется только целиком с помощью команды CREATE OR REPLACE PACKAGE. 

   В СУБД Postgres Pro 15 Enterprise пакет – это просто схема с точки зрения БД. Поэтому, можно менять элементы такого пакета по отдельности, помощью команд "CREATE OR REPLACE PROCEDURE/FUNCTION" и "ALTER TYPE".

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

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

2. Права на выполнение пакета.

   Поскольку пакет – это схема в СУБД Postgres Pro Enterprise, необходимо дать права конечному пользователю на использование этой схемы:

  GRANT USAGE ON SCHEMA foo, bar TO hr_user;

Если пакет должен выполняться с правами создателя (DEFINER RIGHT):

  grant all on schema hr_main to hr_user;

3. Если пакет должен выполняться  с правами вызывающего (INVOKER RIGHT), тогда необходимо дать этому пользователю конкретные права на таблицы и представления, которые он использует в пакетах, например:

grant select demo.employee_tab to hr_user;

4. Зависимости от пакета

В PostgreSQL не сохраняются зависимости на уровне объявления переменных и тела функции, поэтому при операциях изменения пакета такие зависимости отследить невозможно. Это существенное отличие от Oracle, где в словаре (Oracle Dictionary) сохраняется полная информация о зависимостях пакетов и вообще любых объектов PL/SQL (триггеров, функций, процедур и т.д.). 

5. Состояние пакета INVALID.

В СУБД Oracle пакет может находится в ошибочном состоянии (INVALID), если нарушены зависимости. Пакет присутствует в СУБД Oracle, но находится в неработоспособном состоянии.

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

Таким образом, если какая-либо операция в PostgreSQL (например, удаление таблицы, используемой в объявлении типа пакета) приводит к невалидному состоянию пакета, то она НЕ может быть выполнена – сначала этот пакет необходимо удалить. Пакет НЕ может быть оставлен в БД в ошибочном (INVALID) состоянии.

6. Изменение пакета в другой сессии.

Проблема связана с тем, что в PostgreSQL нет общего библиотечного кэша (library cache), как в Oracle, – в PostgreSQL каждый бэкэнд кэширует у себя код PL/pgSQL.

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

В настоящий момент глобальной блокировки на изменение пакета, который в данный момент выполняется (аналог соответствующего латча в СУБД Oracle), не реализовано. Также, на данный момент отсутствует механизм отслеживания факта изменения пакета (аналог исключения ORA-04068: existing state of packages has been discarded в СУБД Oracle).

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

После этого приложение стартует, и все сессии согласованно «увидят» новые актуальные версии пакетов.

7. Как уже было отмечено ранее, все элементы пакета являются публичными, то есть доступны снаружи пакета (в других пакетах, анонимных блоках, функции пакета также доступны в SQL-операторах ). Нет разделения на спецификацию и тело пакета, как в СУБД Oracle.

8. Модификаторы объявления процедур в пакете, которые использовались в СУБД Oracle (например: RESULT_CACHE, DETERMINISTIC и т.д.), не поддерживаются. Также не поддерживаются директивы компилятора Oracle PL/SQL (например: pragma inline, pragma udf и т.д.).

Заключение

Поддержка основной функциональности пакетов в СУБД Postgres Pro 15 Enterprise, позволяет значительно снизить объем работ по ручной модификации кода пакетов PL/SQL, при миграции из Oracle в Postgres Pro. Обеспечивается основной функционал работы с пакетами в языке PL/pgSQL:

• группировка типов переменных процедур и функций в пакет;

• поддержка секции инициализации пакетов;

• поддержка глобальных переменных пакета доступных в течении всей "жизни" сессии.

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

В следующей статье будут рассмотрены практические аспекты использования пакетов в СУБД Postgres Pro, а именно:

• решение проблемы с зависимостями при изменении пакета;

• использование новой утилиты Postgres Pro ORA2PGPRO, предназначенной для автоматической конвертации кода пакетов из СУБД Oracle в СУБД Postgres Pro Enterprise;

• миграция системных встроенных пакетов, например: utl_mail, dbms_application_info;

• особенности выдачи привилегий на выполнение пакетов.