Из Oracle да в Postgres
Так уж случилось, что с Oracle организация наша работает давно и плотно. Сам я познакомился с Oracle Database ещё во времена 6-ой версии и, с тех пор, какого либо дискомфорта не испытывал. Всё испортили рыночные отношения. С недавних пор, мы начали замечать, что Заказчик гораздо благосклоннее смотрит на наши проекты если в них используются бесплатные СУБД. О портации одного из таких проектов и будет мой рассказ… Выбор бесплатной СУБД был, для меня, делом долгим и непростым, но, в конечном итоге, свёлся к двум всем известным альтернативам. PostgreSQL привлекал богатой (и всё ещё развивающейся) функциональностью, в то время как MySQL заманивал производительностью и «нулевым» администрированием. Поскольку Oracle нас разбаловал и SQL мы все знали и любили, многочисленные и новомодные NoSQL варианты отпали ещё в полуфинале.Сложно сказать, на какой из СУБД я бы остановился в конечном итоге, если бы не одна шабашка, давшая мне возможность «вживую» пощупать и PostgreSQL и MySQL, не спеша сравнить их и принять, на мой взгляд, вполне обоснованное решение. Помимо функционала, разумеется, сравнивалась и производительность. Я не буду рассказывать деталей, но одним из принципиальных моментов «шабашки» была возможность быстро и надёжно (ACID, да) вставлять в базу данных большое количество записей. На эту тему и был проведён тест:
По оси ординат отложено количество записей фиксированной длины, сохраняемых в БД ежесекундно. Число в «легенде» означает размер транзакции. Здесь следует заметить, что MySQL замерялся «как есть», а PostgreSQL с использованием небольшой нашлёпки, обеспечивавшей возможность работы с привычными мне партиционированными таблицами и материализованными представлениями. Поскольку речь шла о «надёжном» хранении, MySAM на графике представлен исключительно для полноты картины и понимания того, где находится «теоретический максимум» искомой производительности на используемом «железе».Поскольку само тестирование проводилось довольно давно и никакими 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-код поделился на три категории: Запросы, которые путём небольшого переписывания удалось привести к платформонезависимому виду Запросы, в которых платформозависимые фрагменты удалось скрыть в представлениях Безусловно платформозависимый код С первыми двумя никаких сложностей нет. Последняя категория может доставить некоторые проблемы, если платформозависимые конструкции присутствуют в запросах, формируемых клиентом. Дело в том, что 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.
