Из Oracle да в Postgres

Из Oracle да в Postgres

1050

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

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

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

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

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

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

Сортировка тегов снутри описаний позволяла ассоциировать их как обыденные текстовые файлы. О полезности XMLСтрого говоря, XML посодействовал ещё ранее. Сопоставление таковых «метаданных» для 2-ух разных схем было истинной головной болью, до тех пор, пока я не написал маленький пакет, выгружающий их в 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. 1-ые две просто заменяются на обычные (и наиболее гибкие) coalesce и case, в случае же использования наружного соединения, запрос должен быть переписан: Речь идёт о nvl и decode, а также о устаревшем синтаксисе наружного соединения.

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

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

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

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

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), это достаточно комфортно, но PostgreSQL еще строже в том, что касается типов данных. Дело в том, что обширно используемый в Oracle тип date приучил к некой неряшливости при обращении с его значениями. Много радости доставила работа с датой и временем. Можно считать, что такое значение представляет собой число, целая часть описывает количество дней, прошедших с некой «магической» даты, а дробная — время, с точностью до секунды.

date ‘2001-09-28’ + interval ‘1 hour’
Разыскиваемое выражение совершенно не разумеется: Добавить, таковым образом, к дате константный интервал можно, но что делать, раз нужно добавить переменное значение?

date ‘2001-09-28′ + (to_char(p_hours, ’99’) || ‘ hour’)::interval
Неявные преобразования, настолько обычные опосля работы с Oracle, не работают. Маска неотклонима, даже раз она состоит из одних девяток. Также, можно увидеть, что строгость PostgreSQL распространяется и на преобразование числовых значений в строковые (и напротив, естественно). Пробел в строке перед ‘hour’ обязателен!

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

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

Oracle-версияselect * from table(acme_table_fuction(…))

PostgreSQL-версияselect * from acme_table_fuction(…)

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

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

Эмуляция пакетов в PostgreSQLdrop 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;

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

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-код поделился на три категории: Реализации всех пакетов пришлось, естественно, переписать, благо их оказалось не так много.

Запросы, которые методом маленького переписывания удалось привести к платформонезависимому виду
Запросы, в которых платформозависимые фрагменты удалось скрыть в представлениях
Непременно платформозависимый код

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

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

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.
habrahabr.ru