Из Oracle да в Postgres

Так уж случилось, что с Oracle организация наша работает давно и плотно. Сам я познакомился с Oracle Database ещё во времена 6-ой версии и, с тех пор, какого либо дискомфорта не испытывал. Всё испортили рыночные отношения. С недавних пор, мы начали замечать, что Заказчик гораздо благосклоннее смотрит на наши проекты если в них используются бесплатные СУБД. О портации одного из таких проектов и будет мой рассказ... 

Выбор бесплатной СУБД был, для меня, делом долгим и непростым, но, в конечном итоге, свёлся к двум всем известным альтернативам. PostgreSQL привлекал богатой (и всё ещё развивающейся) функциональностью, в то время как MySQL заманивал производительностью и «нулевым» администрированием. Поскольку Oracle нас разбаловал и SQL мы все знали и любили, многочисленные и новомодные NoSQL варианты отпали ещё в полуфинале.

Сложно сказать, на какой из СУБД я бы остановился в конечном итоге, если бы не одна шабашка, давшая мне возможность «вживую» пощупать и PostgreSQL и MySQL, не спеша сравнить их и принять, на мой взгляд, вполне обоснованное решение. Помимо функционала, разумеется, сравнивалась и производительность. Я не буду рассказывать деталей, но одним из принципиальных моментов «шабашки» была возможность быстро и надёжно (ACID, да) вставлять в базу данных большое количество записей. На эту тему и был проведён тест:

По оси ординат отложено количество записей фиксированной длины, сохраняемых в БД ежесекундно. Число в «легенде» означает размер транзакции. Здесь следует заметить, что MySQL замерялся «как есть», а PostgreSQL с использованием небольшой нашлёпки, обеспечивавшей возможность работы с привычными мне партиционированными таблицами и материализованными представлениями. Поскольку речь шла о «надёжном» хранении, MyISAM на графике представлен исключительно для полноты картины и понимания того, где находится «теоретический максимум» искомой производительности на используемом «железе».

Поскольку само тестирование проводилось довольно давно и никакими SSD на имеющемся в наличии железе даже не пахло, к абсолютным значениям, показанным на графике, не стоит относиться как к догме. Безусловно, можно сохранять данные ещё быстрее, но меня интересовало соотношение производительности различных СУБД, работавших в (почти) одинаковых условиях. Для меня стало сюрпризом, что PostgreSQL, даже утяжелённый триггерами партиционирования, работает почти также быстро как MySQL, с использованием InnoDB, а на больших транзакциях (1000 записей и больше) начинает догонять MyISAM!

Как легко догадаться, показанный выше график окончательно убедил меня в том, что переходить следует на PostgreSQL. Пересоздание таблиц с переопределением типов столбцов (number в numeric и integer, varchar2 в varchar и text ...) было делом тривиальным. В переносе данных помог XML и XSLT.

О пользе XML

Строго говоря, XML помог ещё раньше. Одной из особенностей нашего продукта является хранение в БД описаний бизнес-сущностей в форме обычных табличных данных (не думаю, что он сильно оригинален в этом). Сравнение таких «метаданных» для двух различных схем было настоящей головной болью, до тех пор, пока я не написал небольшой пакет, выгружающий их в XML-описания. Сортировка тегов внутри описаний позволяла сравнивать их как обычные текстовые файлы. XSLT дополнил картину, обеспечив автоматическую генерацию SQL-скриптов из файлов описаний.

Осталось обеспечить работоспособность всего SQL-кода, написанного для Oracle. Большая часть запросов работала, часть — заработала после небольших косметических изменений. Первым делом, я создал таблицу dual:

create table dual (
  x    varchar(1) not null
);

insert into dual(x) values('x');

Не то, чтобы без неё нельзя было обойтись, но в наших запросах она использовалась так часто, что переписывать их было просто нецелесообразно. Чтобы PostgreSQL «был доволен», пришлось добавить в запросы больше строгости:

Oracle-версия

select b.id id, b.name name
from   ( select list_value
         from   acme_obj_list_value
         group by list_value ), acme_list_value b
where b.id = list_value

PostgreSQL-версия

select b.id id, b.name as name
from   ( select list_value
         from   acme_obj_list_value
         group by list_value ) a, acme_list_value b
where b.id = a.list_value

Все inline view необходимо именовать, а перед псевдонимами столбцов крайне желательно использовать ключевое слово 'as'. Для большинства столбцов его можно опускать, но при использовании таких имён как 'name' или 'value' это ведёт к ошибке. Следующим шагом, стала замена платформозависимого кода на соответствующие конструкции, поддерживаемые как в Oracle, так и в PostgreSQL. Речь идёт об nvl и decode, а также об устаревшем синтаксисе внешнего соединения. Первые две легко заменяются на стандартные (и более гибкие) coalesce и case, в случае же использования внешнего соединения, запрос должен быть переписан:

Oracle-версия

select ot.name, mv.str_value
from   acme_object o, acme_meta_value mv, acme_obj_type ot
where  o.id = :object_id
and    ot.id = o.obj_type_id
and    mv.owner_id(+) = ot.id
and    mv.param_id(+) = 9520

PostgreSQL-версия

select ot.name, mv.str_value
from   acme_object o 
left   join acme_meta_value mv on (mv.owner_id = ot.id and mv.param_id = 9520)
inner  join acme_obj_type ot on (ot.id = o.obj_type_id)
where  o.id = :object_id

ANSI Join поддерживается Oracle с 9-ой версии и, на мой взгляд, является более удобным (хотя и менее лаконичным), чем устаревший вариант с использованием (+). Не стоит пытаться сочетать различные формы соединения в одном SQL-запросе. Если мы использовали outer join, то для внутренних соединений вполне логично использовать inner join, а не перечисление таблиц в фразе from через запятую.

Основная часть работы по миграции SQL-кода оказалась связана с переписыванием иерархических запросов. Фраза connect by в PostgreSQL, естественно, не поддерживается. Между тем, в наличии имелось большое количество запросов следующего вида:

Пример использования иерархического запроса

select t.id as value 
from   acme_object t, acme_obj_ref_value rv
where  rv.object_id = t.id
and    rv.attr_id = 220102
and    rv.ref_value = :object_id
and    t.obj_type_id in ( select  ot.id 
                          from    acme_obj_type ot 
                          connect by prior ot.id = ot.parent_id 
                          start   with ot.id = 200335 )

Простое переписывание таких запросов, с использованием CTE не позволило бы привести их к платформонезависимому виду. Хотя Oracle (начиная с версии 11.2) поддерживает рекурсивные запросы, синтаксис их отличается от используемого в PostgreSQL. В частности, в PostgreSQL, использование ключевого слова recursive является обязательным, Oracle же его «не понимает». К счастью, в большинстве случаев, иерархическую часть запроса удавалось «спрятать» в представление.

Oracle-версия

create  or replace view acme_arm(id) as
select  ot.id 
from    acme_obj_type ot 
connect by prior ot.id = ot.parent_id 
start   with ot.id = 200335

PostgreSQL-версия

create or replace view acme_arm(id) as
with recursive t(id) as (
     select id
     from   acme_obj_type
     where  id = 200335
     union  all
     select a.id
     from   acme_obj_type a
     inner  join t on (t.id = a.parent_id)
)
select id from t

Переписывание операторов merge оказалось более «головоломным» (к счастью, они использовались не так часто, как иерархические запросы). PostgreSQL этот оператор не поддерживает, но зато он поддерживает использование фраз from и returning в операторе update, причём последняя — возвращает полноценный resultset (аналогично оператору select), что позволяет использовать его в фразе with. Я просто оставлю это здесь:

Oracle-версия

merge into acme_obj_value d
using ( select object_id
        from   acme_state_tmp
      ) s
on (d.object_id = s.object_id)
  when matched then
    update set d.date_value = least(l_dt, d.date_value)
  when not matched then
    insert (d.id, d.object_id, d.date_value)
    values (acme_param_sequence.nextval, s.object_id, l_dt)

PostgreSQL-версия

with s as (
     select object_id
     from   acme_state_tmp
),
upd as (
     update acme_obj_value
     set    date_value = least(l_dt, d.date_value)
     from   s
     where  acme_obj_value.object_id = s.object_id
     returning acme_obj_value.object_id
)
insert into acme_obj_value(id, object_id,  date_value)
select nextval('acme_param_sequence'), s.object_id, l_dt
from   s
where  s.object_id not in (select object_id from upd)

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

Много радости доставила работа с датой и временем. Дело в том, что широко используемый в Oracle тип date приучил к некоторой неряшливости при обращении с его значениями. Можно считать, что такое значение представляет собой число, целая часть определяет количество дней, прошедших с некоторой «магической» даты, а дробная — время, с точностью до секунды. После некоторого привыкания (как и к большинству особенностей Oracle), это довольно удобно, но PostgreSQL гораздо строже в том, что касается типов данных.

date '2001-09-28' + interval '1 hour'

Добавить, таким образом, к дате константный интервал можно, но что делать, если необходимо добавить переменное значение? Искомое выражение совсем не очевидно:

date '2001-09-28' + (to_char(p_hours, '99') || ' hour')::interval

Пробел в строке перед 'hour' обязателен! Также, можно заметить, что строгость PostgreSQL распространяется и на преобразование числовых значений в строковые (и наоборот, конечно). Маска обязательна, даже если она состоит из одних девяток. Неявные преобразования, столь привычные после работы с Oracle, не работают.

Оставшиеся запросы подверглись менее радикальным изменениям. Пересмотра потребовал весь код работающий со строками, просто потому, что соответствующие функции в Oracle и PostgreSQL выглядят по разному. Столбец rownum, там, где он ещё оставался, пришлось заменить на оконный row_number(). В тех случаях, когда условие на rownum использовалась для ограничения количества выводимых строк, запросы переписывались с использованием фразы limit.

Отдельно стоит рассказать о табличных функциях. И в Oracle и в PostgreSQL они есть. Реализация разумеется различна, но обращение к ним, из SQL-запроса выглядит сходным образом. К сожалению, как и в случае с рекурсивным CTE, всё портит наличие одного ключевого слова:

Oracle-версия

select * from table(acme_table_fuction(...))

PostgreSQL-версия

select * from acme_table_fuction(...)

Осталось разобраться с пакетами. В PostgreSQL такого понятия нет, но, при ближайшем рассмотрении, оказывается, что ему оно не очень то и нужно. Действительно, для чего нужны пакеты в Oracle? Если отбросить в сторону глобальные переменные и инициализационный код (которыми мы не пользуемся), главным достоинством пакетов является то, что они разрывают цепочки зависимостей. При изменении объектов БД, инвалидируются лишь реализации зависимых пакетов, но не их заголовки. Возможность выполнения рекурсивных вызовов внутри пакетов является одним из следствий этого факта.

В PostgreSQL механизм зависимостей не реализован. С рекурсивными вызовами хранимых функций (процедур в PostgreSQL нет) также всё в порядке. Для того, чтобы в клиентский код пришлось вносить минимум изменений, достаточно обеспечить лишь видимость того, что мы продолжаем работать с пакетами. Схемы PostgreSQL подходят для этого как нельзя лучше. Разумеется, в таком «пакете», не удастся реализовать «приватные» функции, но это не очень большая проблема. Вот как будет выглядеть код:

Эмуляция пакетов в PostgreSQL

drop function acme_utils.get_str_res(numeric);
drop function acme_utils.c_str_res_ot();
drop function acme_utils.c_str_res_id_attr();

drop schema acme_utils;

create schema acme_utils;

create or replace function acme_utils.c_str_res_ot()
returns numeric
as $$
begin
  return 20069;
end;
$$ language plpgsql IMMUTABLE;

create or replace function acme_utils.c_str_res_id_attr()
returns numeric
as $$
begin
  return 20070;
end;
$$ language plpgsql IMMUTABLE;

create or replace function acme_utils.get_str_res(in p_res_id numeric)
returns text
as $$
declare
  res text;
begin
  select o.name
  into   strict res
  from   acme_object o
  inner  join acme_obj_value rid on (rid.object_id = o.id and rid.attr_id = acme_utils.c_str_res_id_attr())
  where  o.obj_type_id = acme_utils.c_str_res_ot()
  and    rid.num_value = p_res_id;
  return res;
end;
$$ language plpgsql STABLE;

Необходимость удаления всех объектов перед «пересозданием» схемы немного утомляет, но жить можно. Можно заметить в тексте непривычное слово 'strict'. Оно обеспечивает привычное по Oracle поведение, при попытке выборки нуля или более одной записи. Из других запомнившихся моментов, могу упомянуть странную конструкцию, вычисляющую количество строк, изменённых последним запросом:

Oracle-версия

insert into acme_resource_tmp(id, device_id, profile_id, owner_id, type_id, res_num, name)
select acme_main_sequence.nextval, t.device_id, t.profile_id, r.id, p.type_id, t.num, t.value
from   acme_state_tmp t
inner  join acme_profile_detail p on (p.profile_id = t.profile_id and p.param_id = t.param_id)
inner  join acme_resource r on (r.device_id = t.device_id and r.owner_id is null);
l_ic := sql%rowcount;

PostgreSQL-версия

insert into acme_resource_tmp(id, device_id, profile_id, owner_id, type_id, res_num, name)
select nextval('acme_main_sequence'), t.device_id, t.profile_id, r.id, p.type_id, t.num, t.value
from   acme_state_tmp t
inner  join acme_profile_detail p on (p.profile_id = t.profile_id and p.param_id = t.param_id)
inner  join acme_resource r on (r.device_id = t.device_id and r.owner_id is null);
get diagnostics l_ic = row_count;

Реализации всех пакетов пришлось, конечно, переписать, благо их оказалось не так много. Из моего предыдущего повествования можно понять, что весь переписанный SQL-код поделился на три категории:

1. Запросы, которые путём небольшого переписывания удалось привести к платформонезависимому виду
2. Запросы, в которых платформозависимые фрагменты удалось скрыть в представлениях
3. Безусловно платформозависимый код

С первыми двумя никаких сложностей нет. Последняя категория может доставить некоторые проблемы, если платформозависимые конструкции присутствуют в запросах, формируемых клиентом. Дело в том, что Java-код не хочется переписывать. Ещё меньше желания разделять исходники на две версии, работающие с различными СУБД. К сожалению, полностью исключить платформозависимые конструкции из клиентского кода не удалось. По большей части, мешало ключевое слово table в запросах к табличным функциям. Также имелись обращения к последовательностям и немного иерархических запросов.

Было принято решение — хранить все платфомозависимые запросы в БД, загружая их в программный кэш, при первом обращении. Первоначально предполагалась, что каждая из БД будет хранить свои версии запросов, но оказалось удобнее хранить запросы одновременно во для всех используемых СУБД. В Oracle, для хранения текста запросов использовалось CLOB поле, в PostgreSQL — text. Для обеспечения единообразия, было использовано преобразование CLOB в varchar2, что ограничило максимальный размер запроса 4000 символов (один запрос всё-таки вылез за пределы этого размера, но поскольку он предназначался для PostgreSQL версии, «ужимать» его не пришлось). Само преобразование to_char пришлось скрыть с использованием представления:

Oracle-версия

create or replace view acme_query(name, sql) as
select a.name, to_char(c.clob_value)
from   acme_object a
inner  join acme_obj_list_value b on (b.object_id = a.id and b.attr_id = 10061)
inner  join acme_obj_value c on (c.object_id = a.id and c.attr_id = 10062)
where  a.obj_type_id = 10004
and    b.list_value = 10061;

PostgreSQL-версия

create or replace view acme_query(name, sql) as
select a.name, c.clob_value
from   acme_object a
inner  join acme_obj_list_value b on (b.object_id = a.id and b.attr_id = 10061)
inner  join acme_obj_value c on (c.object_id = a.id and c.attr_id = 10062)
where  a.obj_type_id = 10004
and    b.list_value = 10062;

Резюмируя, могу сказать, что работа оказалась совсем не такой страшной, какой она казалась в начале. Большая её часть была связана с переписыванием иерархических запросов и пакетов Oracle, а большая часть проблем — с более строгим синтаксисом SQL и отсутствием привычных неявных преобразований в PostgreSQL. Объем работ мог бы быть меньше если бы мы изначально использовали более строгий и платфомонезависимый код в Oracle.