В предыдущей статье я рассказал о выходе Solid12 — версии IEM-платформы для PostgreSQL. Как и обещал, рассказываем более детально о том, с чем придется столкнуться разработчикам (и с чем столкнулись мы при миграции).

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

Итак, вот список отличий, который мы признали наиболее значимыми:

• Имена таблиц, колонок, хранимых процедур по умолчанию имеют нижний регистр (кроме идентификаторов в кавычках). Это влияет и на имена колонок в результатах SQL-запросов.
• Пакеты не поддерживаются. Ядро Solid12 использует схемы для группировки функций в пакеты, поэтому используется несколько ядерных схем.
• Вместо переменных пакетов используются переменные сессии. Семантика их похожа, хотя и имеются некоторые отличия.
• Временные таблицы в PostgreSQL всегда локальны и не могут быть привязаны к схемам, в отличие от Oracle. Solid12 располагает набором функций, предоставляющий эмуляцию глобальных временных таблиц в стиле Oracle, которые полностью поддерживают стандартный синтаксис для работы с глобальными временными таблицами.
• Язык хранимых процедур PL/PgSQL похож, но все же отличается от ораклового PL/SQL во многих деталях.
• Хранимые процедуры всегда используют динамическую привязку (т.е. символы интерпретируются во время выполнения). Например, если хранимая процедура выполняет запрос наподобие «SELECT * FROM USERS», поиск таблицы по имени «USERS» будет запущен во время выполнения, а не при компиляции. Динамическая привязка делает процедуры PostgreSQL весьма гибкими, но подверженными гораздо большему числу ошибок времени выполнения по сравнению с Oracle.
• По умолчанию, функции PostgreSQL запускаются с набором разрешений текущего пользователя (в Oracle по умолчанию используются разрешения создателя функции). Эту опцию можно явно переопределить в каждой функции, где это требуется.
• Пустые строки — не то же самое, что NULL. В отличие от Oracle, конкатенация PostgreSQL значений varchar или text с NULL всегда возвращает NULL. Всегда инициализируйте локальные переменные пустыми строчками, чтобы избежать непреднамеренного обнуления результата.
• Операции DDL в PostgreSQL транзакционны. Создание таблиц и функций, изменение типов колонок, очистку таблиц (TRUNCATE) и т. д. — нужно коммитить.
• Замена функции новой версией завершится ошибкой, если сигнатура функции отличается от оригинала. В этой ситуации выполнить «CREATE OR REPLACE FUNCTION» недостаточно: потребуется удалить (DROP) старую версию функции и создать ее заново.
• Любая ошибка базы данных помечает текущую транзакцию как ошибочную, так что транзакция не может выполнять никаких команд, кроме ROLLBACK. Однако такую транзакцию можно откатить к последней именованной точке сохранения (SAVEPOINT), выполненной до возникновения ошибки. После отката к точке сохранения, транзакция может продолжить работу и зафиксироваться (этот прием используется в интеграционных тестах, которые должны продолжать выполнение, невзирая на ошибки).
• Не поддерживается несколько одновременно активных DataReader-ов на одном соединении с базой данных (так называемый режим MARS — Multiple Active Result Sets). Чтобы прочитать несколько наборов данных, нужно либо открыть несколько соединений к базе данных, по одному на каждый набор данных, либо выполнять запросы по очереди.

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

Ниже приводятся кое-какие инструкции, которые могут помочь справиться с некоторыми из описанных трудностей.

Как обойти динамическую привязку в коде PL/PgSQL

Динамическая привязка — это мощный механизм, который в некоторых случаях может заменить динамическое выполнение запросов (EXECUTE sql). Обратной стороной медали является хрупкость конструкции, отсутствие проверок во время компиляции. Компилятор не может статически проверить, ссылается ли данный символ на какой-нибудь объект базы данных.

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

Чтобы отключить динамическую привязку, мы придерживаемся двух простых правил:

• Добавляем строчку «set search_path to (current schema name)»  ко всем определениям функций и
• Квалифицируем все таблицы вне текущей схемы именами их схем.

Это не делает привязку статической (PostgreSQL все равно не проверяет валидность символов), но просто отключает возможность непреднамеренной привязки символа к чему-нибудь не тому.

Вот пример исходного кода функции PL/PgSQL, которая больше не страдает от динамической привязки:

-- current search_path = my_schema 
create or replace function my_func(my_arg text) returns void as $$ 
declare v_id bigint; 
begin 
perform another_func(my_arg); -- same as perform my_schema.another_func(my_arg); 
select id into v_id from kernel.users -- table name is qualified with kernel schema name where login = my_arg; -- the rest is skipped... 
end $$ language plpgsql set search_path to my_schema;

Переопределение разрешений, которые применяются к функции

По умолчанию функции PostgreSQL вызываются с набором разрешений текущего пользователя СУБД, подобно опции Oracle «AUTHID CURRENT_USER» (по умолчанию в Oracle действует другой режим — «AUTHID DEFINER»).

Чтобы эмулировать поведение Oracle, функция должна переопределить «security option» вот так:

create or replace function my_secure_func() returns void as $$
begin  -- call here any functions available to the superuser 
end $$ language plpgsql security definer; -- default is security invoker

Эмуляция глобальных временных таблиц в стиле Oracle

Семантика временных таблиц в PostgreSQL существенно отличается от оракловой. Вот краткий обзор отличий:

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

Схема pack_temp содержит библиотеку для эмуляции временных таблиц в стиле Oracle. Нас интересуют всего две функции:

create_permanent_temp_table(table_name [, schema_name]);
drop_permanent_temp_table(table_name [, schema_name]);

Создание постоянной временной таблицы делается в два приема:

1. Создаем обычную временную таблицу PostgreSQL (ту самую, которая удаляется в конце транзакции).
2. Вызываем функцию create_permanent_temp_table, чтобы превратить эту временную таблицу в постояную:
   

   create temporary table if not exists another_temp_table (    first_name varchar,    last_name varchar,    date timestamp(0) with time zone,    primary key(first_name, last_name) ) on commit drop;
   -- create my_schema.another_temp_table select pack_temp.create_permanent_temp_table('another_temp_table', 'my_schema');
   -- or create another_temp_table in the current schema -- select create_permanent_temp_table('another_temp_table');
   -- don't forget to commit: PostgreSQL DDL is transactional commit;
   

   
   

При этом создается представление, которое ведет себя точь-в-точь как глобальная временная таблица Oracle. Удалить его можно функцией drop_permanent_temp_table.

Несколько активных DataReader-ов на одном соединении с базой данных

Это наиболее досадное ограничение PostgreSQL: каждое соединение с базой данных может иметь только один открытый DataReader в каждый момент времени.

Новый запрос нельзя выполнить, пока предыдущий не будет выполнен и обработан.

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

1. LINQ-запрос использует константу (или вызывает службу). Запрос открывает первый DataReader, а служба констант пытается открыть второй и получает ошибку. Чтобы избавиться от ошибки, нужно прочитать константу в локальную переменную перед выполнением запроса (или вызывать службу уже после чтения результатов запроса). Пример:
   
   

   // было 
   var query = from a in DataContext.GetTable<Agent>() 
                     where a.ID = Constants.TestAgentID select a;
   // стало 
   var testAgentId = Constants.TestAgentID; 
   var query = from a in DataContext.GetTable<Agent>() 
                     where a.ID = testAgentId select a;
   

   
   
2. Результаты LINQ-запроса обрабатываются в цикле, но тело цикла выполняет второй запрос, будь то LINQ или SQL. Как обойти ограничение: материализовать результаты запроса в список или массив, и пробежать по списку после того как первый запрос уже выполнен. Пример:
   
   

   // было 
   foreach (var langId in DataContext.GetTable<Language>().Select(x => x.ID)) 
   { 
     using (LanguageService.UseLanguage(langId)) 
    { 
       // do something language-specific 
    } 
   }
   // стало 
   foreach (var langId in DataContext.GetTable<Language>().Select(x => x.ID).ToIDList()) 
   { 
     using (LanguageService.UseLanguage(langId)) 
     { 
         // do something language-specific 
     } 
   }
   

   
   
3. Вызов ToArray/ToList/ToIDList внутри LINQ-зпроса. Чтобы исправить, нужно разбить запрос на части:
   
   

   // было 
   var dictionary = DataContext.GetTable<CalendarDayStatus>().Where(d => dates.Contains(d.DT)) .GroupBy(g => g.DT, e => e.StatusID) .ToDictionary(k => k.Key, e => e.ToIDList());
   // стало 
   var dictionary = DataContext.GetTable<CalendarDayStatus>() .Where(d => dates.Contains(d.DT)) .GroupBy(g => g.DT, e => e.StatusID) .ToDictionary(p => p.Key);
   var dict = dictionary.ToDictionary(p => p.Key, p => p.Value.ToIDList());
   

   
   К сожалению, этот вид ошибки очень сложно обнаружить статически. Поэтому каждый нетривиальный LINQ-запрос нужно тщательно протестировать, чтобы убедиться, что он не пытается открыть несколько DataReader-ов одновременно.
   

Что дальше?

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

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

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