Типы данных oracle postgresql
For those of you out there working with PostgreSQL in a professional way, migrating from Oracle to PostgreSQL might be one of the most beloved tasks available. One of the first things most people will notice, however, is that those datatypes available in Oracle might not be quite the same in PostgreSQL. This blog will try to shed some light and show, how things work.
Обрабатываем boolean
Для них требуется удалить если есть значение по умолчанию, поменять тип и вернуть значение по умолчанию
Временные таблицы
В PostgreSQL в отличии от Oracle, временные таблицы создаются каждый раз и живут в рамках сессии. В Oracle только содержимое временных таблиц живет в каких-либо рамках, а сами таблицы созданы постоянно.
Из этого проистекают следующие проблемы:
1) При создании каждой сессии придется создавать временную таблицу. (данный момент можно упростить используя возможность сервера приложений выполнять инициализационный sql-блок при создании новой сессии (connection pool → advanced → Init SQL)
2) Валидация сущностей в hibernate производится в рамках настроенной схемы (если не указать схему, валидация будет производиться по всем доступным схемам). Т.к. в Postgre временные таблицы создаются в отдельных схемах, валидация провалится.
(Можно обойти создав реальную таблицу с такой же структурой. При нативных запросах работа будет производиться с временной таблицей, а не с обычной).
3) Работать с такой временной таблицей возможно только через нативные запросы, т.к. Hibernate во все генерируемые запросы добавляет название схемы (либо надо всю работу со временными таблицами вести через нативные запросы, либо отказываться от временных таблиц и использовать обычную с разграничением данных по уникальному ключу. Оба способа подразумевают переписывание функционала).
Чтобы продолжить работать с временными таблицами в Postgress, как в Oracle воспользуемся табличными функциями и созданием view на основе табличной функции. Подробности здесь
Datatypes in Oracle and PostgreSQL
While there are many similarities between Oracle and PostgreSQL there are a couple of differences, which are quite noteworthy. The first thing many people might notice is: PostgreSQL has many more data types than Oracle. As of version 10.0 an empty PostgreSQL database will expose the staggering number of 92 data types. Of course not all of them are useful and many of them are purely internal used for internal purposes.
Still: At the end of the day there are just more data types, which are can be used by applications, which of course
Let us take a look and see, which types can be matched. The following table contains a lot of potential options:
Oracle type | Possible PostgreSQL types |
CHAR | char, varchar, text |
NCHAR | char, varchar, text |
VARCHAR | char, varchar, text |
VARCHAR2 | char, varchar, text, json |
NVARCHAR2 | char, varchar, text |
CLOB | char, varchar, text, json |
LONG | char, varchar, text |
RAW | uuid, bytea |
BLOB | bytea |
BFILE | bytea (read-only) |
LONG RAW | bytea |
NUMBER | numeric, float4, float8, char, varchar, text |
NUMBER(n,m) with m | numeric, float4, float8, int2, int4, int8, |
boolean, char, varchar, text | |
FLOAT | numeric, float4, float8, char, varchar, text |
BINARY_FLOAT | numeric, float4, float8, char, varchar, text |
BINARY_DOUBLE | numeric, float4, float8, char, varchar, text |
DATE | date, timestamp, timestamptz, char, varchar, text |
TIMESTAMP | date, timestamp, timestamptz, char, varchar, text |
TIMESTAMP WITH TIME ZONE | date, timestamp, timestamptz, char, varchar, text |
TIMESTAMP WITH | date, timestamp, timestamptz, char, varchar, text |
LOCAL TIME ZONE | |
INTERVAL YEAR TO MONTH | interval, char, varchar, text |
INTERVAL DAY TO SECOND | interval, char, varchar, text |
MDSYS.SDO_GEOMETRY | geometry (see “PostGIS support” below) |
A second important thing is “varchar2”: On the PostgreSQL side it can easily be mapped to varchar or text. A small detail is that in Oracle varchar2 can be the number of bytes or the number of characters. Consider the following example: VARCHAR2(20 BYTE) vs.VARCHAR2(10 CHAR). You have to think twice, what to do on the PostgreSQL side because in Postgres we are always talking about “characters”.
Переносим данные
Перед переносом данных проверяем, что не совпадения типов остались только в колонках boolean и bytea(oid), а все колонки с численными значениями имеют правильную точность и не являются ‘double precision’ во избежание случайного округления.
По умолчанию процесс копирования идет в один поток. Меняем в конфигурации на нужное число
Запускаем процесс копирования данных
PostgreSQL pgBadger
pgBadger is a PostgreSQL log analyzer built for speed with fully reports from your PostgreSQL log file. It's a single and small Perl script that outperforms any other PostgreSQL log analyzer. It is written in pure Perl and uses a JavaScript library (flotr2) to draw graphs so that you don't need to install any additional Perl modules or other packages. Furthermore, this library gives us more features such as zooming. pgBadger also uses the Bootstrap JavaScript library and the FontAwesome webfont for better design. Everything is embedded. pgBadger is able to autodetect your log file format (syslog, stderr or csvlog). It is designed to parse huge log files as well as gzip compressed files. See a complete list of features below. Supported compressed format are gzip, bzip2 and xz. For the xz format you must have an xz version upper than 5.05 that supports the --robot option. All charts are zoomable and can be saved as PNG images. You can also limit pgBadger to only report err
substring
Script to find sessions that are blocking other sessions in PostgreSQL
In this table is very useful at the time of database migration here i differentiated between oracle data type and postgreSQL data types
How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?
Обрабатываем oid(bytea)
Создаем процедуру для конвертации bytea в oid
Создаем временную колонку
Удаляем старую колонку
Переименовываем временную колонку
How to Return a Result Set from a PostgreSQL Stored Procedure
Both stored procedures and user-defined functions are created with CREATE FUNCTION statement in PostgreSQL. To return one or more result sets (cursors in terms of PostgreSQL), you have to use refcursor return type. Quick Example : -- Procedure that returns a single result set (cursor) CREATE OR REPLACE FUNCTION show_cities ( ) RETURNS refcursor AS $$ DECLARE ref refcursor; -- Declare a cursor variable BEGIN OPEN ref FOR SELECT city , state FROM cities; -- Open a cursor RETURN ref; -- Return the cursor to the caller END ; $$ LANGUAGE plpgsql; Overview : Return Multiple Result Sets Cursor Lifetime Until the end of transaction Auto-commit Must be off Transaction must be active so the caller can see a result set Important Note : The cursor remains open until the end of transaction, and since PostgreSQL works
Исправление типов колонок
Смотрим в каких колонках надо произвести изменения
Замены для простых случаев
PostgreSQL pgBadger
pgBadger is a PostgreSQL log analyzer built for speed with fully reports from your PostgreSQL log file. It's a single and small Perl script that outperforms any other PostgreSQL log analyzer. It is written in pure Perl and uses a JavaScript library (flotr2) to draw graphs so that you don't need to install any additional Perl modules or other packages. Furthermore, this library gives us more features such as zooming. pgBadger also uses the Bootstrap JavaScript library and the FontAwesome webfont for better design. Everything is embedded. pgBadger is able to autodetect your log file format (syslog, stderr or csvlog). It is designed to parse huge log files as well as gzip compressed files. See a complete list of features below. Supported compressed format are gzip, bzip2 and xz. For the xz format you must have an xz version upper than 5.05 that supports the --robot option. All charts are zoomable and can be saved as PNG images. You can also limit pgBadger to only report err
Текстовые поля
В Oracle нет неограниченного текстового поля, вместо этого используется Lob. В PostgreSQL есть специальный тип — text. Чтобы можно было использовать обе базы аннотации текстовых полей должны быть следующими
Для Postgres также используем самописный диалект, знающий о добавленных функциях
Подготовка
В качестве утилиты для конвертации данных использовалась ora2pg. Процесс использования очень хорошо описан в статье.
Создаем проект в ora2pg, настраиваем проект, и генерируем схему.
Создаем в PostgreSQL схему 'ora_schema' и таблицы по файлу './schema/tables/table.sql'
Переключаем Hibernate в режим create и создаем еще одну эталонную схему 'hb_schema'. В случае использования view в проекте, число таблиц в разных схемах не сойдется. Hibernate сгенерирует полноценные таблицы вместо view и это следует учитывать.
How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database
In this post, I am sharing few important function for finding the size of database, table and index in PostgreSQL. Finding object size in postgresql database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in GB. The scripts have been formatted to work very easily with PUTTY SQL Editor. 1. Checking table size excluding table dependency: SELECT pg_size_pretty(pg_relation_size('mhrordhu_shk.mut_kharedi_audit')); pg_size_pretty ---------------- 238 MB (1 row) 2. Checking table size including table dependency: SELECT pg_size_pretty(pg_total_relation_size('mhrordhu_shk.mut_kharedi_audit')); pg_size_pretty ---------------- 268 MB (1 row) 3. Finding individual postgresql database size SELECT pg_size_pretty(pg_database_size('db_name')); 4. Finding individual table size for postgresql database -including dependency index: SELECT pg_size_pretty(pg_total_rel
How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?
How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database
In this post, I am sharing few important function for finding the size of database, table and index in PostgreSQL. Finding object size in postgresql database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in GB. The scripts have been formatted to work very easily with PUTTY SQL Editor. 1. Checking table size excluding table dependency: SELECT pg_size_pretty(pg_relation_size('mhrordhu_shk.mut_kharedi_audit')); pg_size_pretty ---------------- 238 MB (1 row) 2. Checking table size including table dependency: SELECT pg_size_pretty(pg_total_relation_size('mhrordhu_shk.mut_kharedi_audit')); pg_size_pretty ---------------- 268 MB (1 row) 3. Finding individual postgresql database size SELECT pg_size_pretty(pg_database_size('db_name')); 4. Finding individual table size for postgresql database -including dependency index: SELECT pg_size_pretty(pg_total_rel
Oracle → PostgreSQL: Automatic conversion
Hans-Jürgen Schönig
Hans-Jürgen Schönig has experience with PostgreSQL since the 90s. He is CEO and technical lead of CYBERTEC, which is one of the market leaders in this field and has served countless customers around the globe since the year 2000.
Так уж случилось, что с 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-описания. Сортировка тегов внутри описаний позволяла сравнивать их как обычные текстовые файлы. XSLT дополнил картину, обеспечив автоматическую генерацию SQL-скриптов из файлов описаний.
Осталось обеспечить работоспособность всего SQL-кода, написанного для Oracle. Большая часть запросов работала, часть — заработала после небольших косметических изменений. Первым делом, я создал таблицу dual:
Не то, чтобы без неё нельзя было обойтись, но в наших запросах она использовалась так часто, что переписывать их было просто нецелесообразно. Чтобы PostgreSQL «был доволен», пришлось добавить в запросы больше строгости:
Все inline view необходимо именовать, а перед псевдонимами столбцов крайне желательно использовать ключевое слово 'as'. Для большинства столбцов его можно опускать, но при использовании таких имён как 'name' или 'value' это ведёт к ошибке. Следующим шагом, стала замена платформозависимого кода на соответствующие конструкции, поддерживаемые как в Oracle, так и в PostgreSQL. Речь идёт об nvl и decode, а также об устаревшем синтаксисе внешнего соединения. Первые две легко заменяются на стандартные (и более гибкие) coalesce и case, в случае же использования внешнего соединения, запрос должен быть переписан:
ANSI Join поддерживается Oracle с 9-ой версии и, на мой взгляд, является более удобным (хотя и менее лаконичным), чем устаревший вариант с использованием (+). Не стоит пытаться сочетать различные формы соединения в одном SQL-запросе. Если мы использовали outer join, то для внутренних соединений вполне логично использовать inner join, а не перечисление таблиц в фразе from через запятую.
Основная часть работы по миграции SQL-кода оказалась связана с переписыванием иерархических запросов. Фраза connect by в PostgreSQL, естественно, не поддерживается. Между тем, в наличии имелось большое количество запросов следующего вида:
Простое переписывание таких запросов, с использованием CTE не позволило бы привести их к платформонезависимому виду. Хотя Oracle (начиная с версии 11.2) поддерживает рекурсивные запросы, синтаксис их отличается от используемого в PostgreSQL. В частности, в PostgreSQL, использование ключевого слова recursive является обязательным, Oracle же его «не понимает». К счастью, в большинстве случаев, иерархическую часть запроса удавалось «спрятать» в представление.
Переписывание операторов merge оказалось более «головоломным» (к счастью, они использовались не так часто, как иерархические запросы). PostgreSQL этот оператор не поддерживает, но зато он поддерживает использование фраз from и returning в операторе update, причём последняя — возвращает полноценный resultset (аналогично оператору select), что позволяет использовать его в фразе with. Я просто оставлю это здесь:
В этом примере можно заметить, что работа с последовательностями в PostgreSQL также отличается от принятой в Oracle. Конечно, в Oracle можно было определить функцию, аналогичную той, что получает значения из последовательностей в PostgreSQL, но переписывания Oracle-кода (также как и Java-кода) хотелось избежать. Кроме того, такой подход мог быть связан с дополнительными накладными расходами.
Много радости доставила работа с датой и временем. Дело в том, что широко используемый в Oracle тип date приучил к некоторой неряшливости при обращении с его значениями. Можно считать, что такое значение представляет собой число, целая часть определяет количество дней, прошедших с некоторой «магической» даты, а дробная — время, с точностью до секунды. После некоторого привыкания (как и к большинству особенностей Oracle), это довольно удобно, но PostgreSQL гораздо строже в том, что касается типов данных.
Добавить, таким образом, к дате константный интервал можно, но что делать, если необходимо добавить переменное значение? Искомое выражение совсем не очевидно:
Пробел в строке перед 'hour' обязателен! Также, можно заметить, что строгость PostgreSQL распространяется и на преобразование числовых значений в строковые (и наоборот, конечно). Маска обязательна, даже если она состоит из одних девяток. Неявные преобразования, столь привычные после работы с Oracle, не работают.
Оставшиеся запросы подверглись менее радикальным изменениям. Пересмотра потребовал весь код работающий со строками, просто потому, что соответствующие функции в Oracle и PostgreSQL выглядят по разному. Столбец rownum, там, где он ещё оставался, пришлось заменить на оконный row_number(). В тех случаях, когда условие на rownum использовалась для ограничения количества выводимых строк, запросы переписывались с использованием фразы limit.
Отдельно стоит рассказать о табличных функциях. И в Oracle и в PostgreSQL они есть. Реализация разумеется различна, но обращение к ним, из SQL-запроса выглядит сходным образом. К сожалению, как и в случае с рекурсивным CTE, всё портит наличие одного ключевого слова:
Осталось разобраться с пакетами. В PostgreSQL такого понятия нет, но, при ближайшем рассмотрении, оказывается, что ему оно не очень то и нужно. Действительно, для чего нужны пакеты в Oracle? Если отбросить в сторону глобальные переменные и инициализационный код (которыми мы не пользуемся), главным достоинством пакетов является то, что они разрывают цепочки зависимостей. При изменении объектов БД, инвалидируются лишь реализации зависимых пакетов, но не их заголовки. Возможность выполнения рекурсивных вызовов внутри пакетов является одним из следствий этого факта.
В PostgreSQL механизм зависимостей не реализован. С рекурсивными вызовами хранимых функций (процедур в PostgreSQL нет) также всё в порядке. Для того, чтобы в клиентский код пришлось вносить минимум изменений, достаточно обеспечить лишь видимость того, что мы продолжаем работать с пакетами. Схемы PostgreSQL подходят для этого как нельзя лучше. Разумеется, в таком «пакете», не удастся реализовать «приватные» функции, но это не очень большая проблема. Вот как будет выглядеть код:
Необходимость удаления всех объектов перед «пересозданием» схемы немного утомляет, но жить можно. Можно заметить в тексте непривычное слово 'strict'. Оно обеспечивает привычное по Oracle поведение, при попытке выборки нуля или более одной записи. Из других запомнившихся моментов, могу упомянуть странную конструкцию, вычисляющую количество строк, изменённых последним запросом:
Реализации всех пакетов пришлось, конечно, переписать, благо их оказалось не так много. Из моего предыдущего повествования можно понять, что весь переписанный SQL-код поделился на три категории:
- Запросы, которые путём небольшого переписывания удалось привести к платформонезависимому виду
- Запросы, в которых платформозависимые фрагменты удалось скрыть в представлениях
- Безусловно платформозависимый код
Было принято решение — хранить все платфомозависимые запросы в БД, загружая их в программный кэш, при первом обращении. Первоначально предполагалась, что каждая из БД будет хранить свои версии запросов, но оказалось удобнее хранить запросы одновременно во для всех используемых СУБД. В Oracle, для хранения текста запросов использовалось CLOB поле, в PostgreSQL — text. Для обеспечения единообразия, было использовано преобразование CLOB в varchar2, что ограничило максимальный размер запроса 4000 символов (один запрос всё-таки вылез за пределы этого размера, но поскольку он предназначался для PostgreSQL версии, «ужимать» его не пришлось). Само преобразование to_char пришлось скрыть с использованием представления:
Резюмируя, могу сказать, что работа оказалась совсем не такой страшной, какой она казалась в начале. Большая её часть была связана с переписыванием иерархических запросов и пакетов Oracle, а большая часть проблем — с более строгим синтаксисом SQL и отсутствием привычных неявных преобразований в PostgreSQL. Объем работ мог бы быть меньше если бы мы изначально использовали более строгий и платфомонезависимый код в Oracle.
Во время миграции из Oracle в PostgreSQL с помощью ora2pg встал вопрос с несоответствием типов данных между разными базами. По умолчанию не все колонки конвертируется правильно, а отсутствие в Oracle Boolean и вовсе порождает неоднозначность – часть колонок нужно перенести как числа, часть как логические значения. В тоже время hibernate знает все о типах данных и может создать эталонную схему.
Итоговый процесс переноса выглядел следующим образом: создание структуры таблиц через ora2pg, исправление структуры по эталонной схеме, перенос данных, конвертация blob и Boolean, добавление отсутствующих в PostgreSQL функций (nvl, nvl2, regexp_substr), создания оставшейся структуры — индексов, view и прочего.
Под катом накопившиеся за время конвертации sql скрипты для полуавтоматической миграции.
How to Return a Result Set from a PostgreSQL Stored Procedure
Both stored procedures and user-defined functions are created with CREATE FUNCTION statement in PostgreSQL. To return one or more result sets (cursors in terms of PostgreSQL), you have to use refcursor return type. Quick Example : -- Procedure that returns a single result set (cursor) CREATE OR REPLACE FUNCTION show_cities ( ) RETURNS refcursor AS $$ DECLARE ref refcursor; -- Declare a cursor variable BEGIN OPEN ref FOR SELECT city , state FROM cities; -- Open a cursor RETURN ref; -- Return the cursor to the caller END ; $$ LANGUAGE plpgsql; Overview : Return Multiple Result Sets Cursor Lifetime Until the end of transaction Auto-commit Must be off Transaction must be active so the caller can see a result set Important Note : The cursor remains open until the end of transaction, and since PostgreSQL works
Sequence
Oracle и Postgress имеют разный синтаксис nextval от Sequence.
Oracle
Приводим к общему виду. Для этого создаем функции в Postgres и Oracle и везде переписываем на использование этой функции.
Oracle
Использовать напрямую функцию Postgres nextval('my_seq') не получиться, так как хотя Oracle и позволит создать данную функцию, но не позволит выбирать значения.
Popular posts from this blog
Data Types
- Get link
- Other Apps
Добавление отсутствующих в PostgreSQL функций
Чтобы не было необходимости в переписывании кода, просто создадим, отсутствующие в PostgreSQL, но которые есть в Oracle и используются в проекте.
nvl(timestamp with time zone, timestamp with time zone)
nvl2(date, date, date)
nvl2(integer, integer, integer)
nvl2(numeric, numeric, numeric)
nvl2(text, text, text)
nvl(timestamp with time zone, timestamp with time zone)
nvl2(timestamp, timestamp, timestamp)
nvl2(varchar, varchar, varchar)
trunc(timestamp with time zone, varchar)
Sysdate
Transformers.ALIAS_TO_ENTITY_MAP
Если используется Transformers.ALIAS_TO_ENTITY_MAP то надо обязательно указывать возвращаемые типы. Oracle по умолчанию приведет ключи к верхнему регистру, PotgresSql к нижнему и несовпадение ключей исправляется только вручную.
Comments
Post a Comment
Читайте также: