oracle_fdw. Обертка сторонних данных для работы с СУБД Oracle

Версия: 2.5.0.

В исходном дистрибутиве установлено по умолчанию: нет.

Связанные компоненты: отсутствуют.

Схема размещения: ext.

Расширение представляет собой внешнюю обертку данных (fdw – foreign-data wrapper) для простого и эффективного доступа к базам данных Oracle из СУБД Pangolin. Возможности включают отображение условий WHERE и требуемых столбцов, а также всестороннюю поддержку EXPLAIN. Расширение позволяет получать доступ к таблицам и представлениям Oracle (включая материализованные представления) через сторонние таблицы.

Объекты расширения

Функции

Для обработки и проверки создаваемого расширения oracle_fdw используются функции oracle_fdw_handler и oracle_fdw_validator. Описание всех функций расширения в разделе «Функции функциональности FOREIGN DATA WRAPPER для БД Oracle» документа «Справочная информация».

Параметры

Параметры обертки внешних данных (FOREIGN DATA WRAPPER)

Внимание!

Рекомендуем всегда создавать новую обертку внешних данных (SQL CREATE FOREIGN DATA WRAPPER), если необходимо, чтобы параметры были постоянными. При изменении обертки внешних данных oracle_fdw, созданной по умолчанию, любые изменения будут потеряны при сбросе или восстановлении.

Для расширения oracle_fdw используется необязательный параметр nls_lang.

Когда клиент СУБД обращается к внешней таблице базы данных Oracle, oracle_fdw обращается к соответствующим данным во внешней базе данных Oracle через библиотеку интерфейса вызовов Oracle (OCI) на сервере СУБД Pangolin.

Чтобы получить доступ к библиотеке OCI из oracle_fdw, установите переменную окружения NLS_LANG для Oracle в параметр nls_lang. Параметр NLS_LANG находится в форме language_territory.charset (например, AMERICAN_AMERICA.AL32UTF8). Этот параметр должен соответствовать кодировке базы данных. Если это значение не задано, расширение oracle_fdw автоматически определит правильную кодировку либо выдаст предупреждение, если определить кодировку не удалось.

Параметры сервера – источника внешних данных (foreign server options)

Далее перечислены параметры для работы с внешним сервером (foreign server):

• dbserver (обязательный параметр) – строка подключения к сторонней БД Oracle может быть в любой из форм, поддерживаемых Oracle.

  Например:

  CREATE SERVER ora_test FOREIGN DATA WRAPPER oracle_fdw
  OPTIONS (dbserver 'host_ora:1521/EPXDB01');
  CREATE SERVER

  Где:

  • host_ora:1521 – имя и номер порта сервера Oracle;
  • EPXDB01 – имя сервера базы данных.

  Для локальных подключений в значение параметра dbserver установите пустую строку (протокол BEQUEATH).

• isolation_level (необязательно) – уровень изоляции транзакций используемый в БД Oracle. Допустимые значения serializable (по умолчанию), read_committed или read_only.

  примечание

  Обратите внимание, что таблица Oracle может быть запрошена более одного раза во время одной транзакции СУБД Pangolin (например, во время объединения (JOIN) таблиц). Чтобы убедиться, что не может возникнуть несоответствий, вызванных конкурентными транзакциями, уровень изоляции транзакций должен гарантировать стабильность чтения. Это возможно только с помощью уровней изоляции Oracle SERIALIZABLE или READ ONLY.

  Реализация наиболее строгого уровня изоляции SERIALIZABLE в Oracle довольно нестабильна и вызывает ошибки сериализации ORA-08177 в неожиданных ситуациях, например, вставки в таблицу. Использование READ COMMITTED позволяет обойти эту проблему, но существует риск возникновения несоответствий. В случае его использования рекомендуем узнать, может ли внешнее сканирование выполняться более одного раза. Для этого нужно проверить планы выполнения запросов (EXPLAIN).

• nchar (boolean, необязательно, значение по умолчанию off):

  • Установка этого параметра в значение on позволяет выбрать более дорогостоящее преобразование символов на стороне Oracle. Это необходимо, если используется однобайтовый набор символов базы данных Oracle, но при этом присутствуют столбцы NCHAR или NVARCHAR2, содержащие символы, которые не могут быть представлены в наборе символов базы данных.
  • Установка nchar в on оказывает заметное влияние на производительность и вызывает ошибки ORA-01461 с операторами UPDATE, которые устанавливают строки более 2000 байт (или 16383, если MAX_STRING_SIZE = EXTENDED). Эта ошибка, по-видимому, является ошибкой Oracle.

Параметры прав доступа (USER MAPPING)

• user (обязательно). Имя пользователя Oracle для подключения.
• password (необязательно). Пароль пользователя Oracle для подключения. В СУБД Pangolin возможно использование засекреченного хранилища паролей (утилита pg_auth_config). Если пароль не задан в параметре password, он будет взят из хранилища паролей по соответствию имени хоста, порта и имени БД из строки подключения, указанной в используемом foreign server.

Параметры внешней таблицы (FOREIGN TABLE)

• table (обязательно):

  Имя таблицы Oracle. Должно быть написано точно так же, как оно было сохранено в системном каталоге Oracle. Как правило, имя содержит только символы в верхнем регистре.

  Чтобы определить внешнюю таблицу на основе произвольного запроса Oracle, установите этот параметр для запроса, заключенного в круглые скобки, например:

  OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')')

  Параметр schema в данном случае указывать не нужно.

  Инструкции INSERT, UPDATE и DELETE будут работать со сторонней таблицей, основанной на таких запросах, если необходимо избежать этого (или путаницы с сообщениями об ошибках Oracle для более сложных запросов), используйте опцию readonly.

• dblink (необязательно):

  Oracle database link, через который доступна таблица. Должно быть написано точно так же, как оно было сохранено в системном каталоге Oracle. Как правило, оно содержит только символы в верхнем регистре.

• schema (необязательно):

  Схема с таблицей (или владелец). Полезно для доступа к таблицам, которые не принадлежат подключающемуся пользователю Oracle. Название схемы должно быть написано точно так же, как оно было сохранено в системном каталоге Oracle. Как правило, оно содержит только символы в верхнем регистре.

• max_long (необязательно, значение по умолчанию «32767»):

  Максимальный размер полей с типом LONG, LONG RAW и XMLTYPE в таблице Oracle. Возможными значениями являются целые числа от 1 до 1073741823 (максимальный размер bytea в PostgreSQL). Этот объем памяти будет выделен как минимум дважды, поэтому большие значения будут занимать много памяти. Если max_long меньше размера самого большого из полученных значений, будет получена ошибка ORA-01406: fetched column value was truncated.

• readonly (необязательно, значение по умолчанию «false»):

  Операции INSERT, UPDATE и DELETE доступны только для таблиц, где данный параметр не равен yes/on/true.

• sample_percent (необязательно, значение по умолчанию «100»):

  Данный параметр влияет только на выполнение операции ANALYZE и может пригодиться для выполнения ANALYZE очень больших таблиц за разумное время. Значение должно быть в диапазоне от 0,000001 до 100, оно определяет процент блоков таблицы Oracle, которые будут выбраны случайным образом для вычисления статистики таблицы базы данных Pangolin. Это достигается с помощью выражения SAMPLE BLOCK(x) в Oracle. Построение ANALYZE завершится с ошибкой ORA-00933 для таблиц, определенных с помощью запросов Oracle, и может завершиться с ошибкой ORA-01446 для таблиц, определенных со сложными представлениями (view) Oracle.

• prefetch (необязательно, значение по умолчанию «200»):

  Задает количество строк, которые будут извлечены с помощью одного кругового перехода между СУБД Pangolin и Oracle во время сканирования внешней таблицы. Реализовано с помощью предварительной выборки строк Oracle. Значение должно находиться в диапазоне от 0 до 10240, где нулевое значение отключает предварительную выборку. По умолчанию строки выбираются порциями по 200 штук. Высокие значения могут повысить производительность, но будут использовать больше памяти сервера СУБД Pangolin.

  примечание

  Если запрос Oracle включает столбец BLOB, CLOB или BFILE, предварительная выборка строк не будет работать (ограничения Oracle). Как следствие, запросы к таким столбцам во внешней таблице будут выполняться долго при извлечении большого количества строк.

Параметры полей

• key (необязательно, значение по умолчанию «false»):

  Если значение равно yes/on/true, соответствующее поле в таблице Oracle является первичным ключом. Чтобы работали операции UPDATE и DELETE, установите этот параметр для всех столбцов, относящихся к первичному ключу таблицы.

• strip_zeros (необязательно, значение по умолчанию "false"):

  Если значение равно yes/on/true, ASCII 0 символы будут удалены из строки перед передачей. Такие символы допустимы в Oracle, но не в PostgreSQL, поэтому они вызовут ошибку при чтении oracle_fdw. Данная опция актуальна только для типов character, character varying и text.

Внутреннее устройство

Когда клиент СУБД обращается к внешней таблице, расширение oracle_fdw обращается к соответствующим данным во внешней базе данных Oracle через библиотеку интерфейса вызовов Oracle (OCI) на сервере PostgreSQL в СУБД Pangolin. Это расширение PostgreSQL предоставляет доступ к базам данных Oracle из СУБД Pangolin.

Расширение oracle_fdw устанавливает значение параметра postgres в параметр MODULE в сессии Oracle и pid бэкенд-процесса в параметр ACTION. Это позволяет идентифицировать сессию Oracle и делает возможной ее трассировку с помощью DBMS_MONITOR, SERV_MOD_ACT_TRACE_ENABLE.

Расширение oracle_fdw использует предварительную выборку результатов Oracle, чтобы избежать ненужных обходов между клиентом и сервером. Количество строк предварительной выборки можно настроить с помощью опции таблицы prefetch, по умолчанию оно равно 200.

В Oracle при планировании запросов функция EXPLAIN PLAN помещает планы выполнения запросов в таблицу PLAN_TABLE. Вместо того, чтобы использовать PLAN_TABLE для формирования плана запроса Oracle (что потребовало бы создания такой таблицы в базе данных Oracle), oracle_fdw использует планы выполнения, хранящиеся в кеше библиотеки. Для этого явно описывается запрос Oracle, что заставляет Oracle анализировать запрос. Сложность заключается в том, чтобы найти SQL_ID и CHILD_NUMBER запроса в V$SQL, потому что столбец SQL_TEXT содержит только первые 1000 байт запроса. Поэтому oracle_fdw добавляет комментарий, содержащий MD5-хеш текста запроса, к самому запросу. Данный способ используется для поиска в V$SQL. Фактический план выполнения или информация о затратах извлекаются из V$SQL_PLAN.

oracle_fdw использует уровень изоляции транзакций SERIALIZABLE на стороне Oracle, что соответствует REPEATABLE READ в PostgreSQL. Это необходимо, поскольку один запрос PostgreSQL может привести к нескольким запросам в Oracle (например, во время вложенного JOIN), и результаты должны быть согласованными.

Транзакция Oracle фиксируется непосредственно перед фиксацией локальной транзакции, так что завершенная транзакция PostgreSQL гарантирует завершение транзакции Oracle. Однако существует небольшая вероятность того, что транзакция PostgreSQL не сможет завершиться, даже если транзакция Oracle зафиксирована. Этого нельзя избежать без использования двухфазных транзакций и менеджера транзакций, что выходит за рамки того, что может разумно предоставить внешняя обертка данных.

Подготовленные запросы, передаваемые в Oracle, не поддерживаются по той же причине.

Доработка

Совместимость с хранилищем паролей

Доработка: Добавлена совместимость с защищенным засекреченным хранилищем паролей pg_auth_config.

Версия: 4.5.0.

Интеграция с хранилищем паролей (получение пароля из хранилища)

Доработка: Добавляется интеграция с хранилищем паролей. Расширение может получить пароль из хранилища по набору параметров host, port, database, username получаемых из настроек подключения к FOREIGN SERVER и USER MAPPING.

Если в хранилище паролей для записи добавлены значения roles и/или appnames будут проверены также текущий пользователь и имя приложения (oracle_fdw). В случае несовпадения пароль получен не будет.

Описание данной доработки приведено в подразделе «Доработка хранилища паролей для обеспечения безопасного взаимодействия с расширениями и внешними утилитами» раздела «Сценарии администрирования» в документе «Руководство администратора».

Ограничения

Ограничения отсутствуют.

Установка

Для установки расширения выполните действия:

1. Установите rpm/deb-пакет расширения, в зависимости от окружения:

   SberLinux, РЕД ОС, CentOS

   sudo dnf install pangolin-dbms-{base_version}-oracle-fdw-{product_version}-{OS}.x86_64.rpm

   Astra Linux

   sudo apt install pangolin-dbms-{base_version}-oracle-fdw-{product_version}_amd64.deb

   Альт СП

   sudo apt-get install pangolin-dbms-{base_version}-oracle-fdw-{product_version}-{OS}.x86_64.rpm

2. Добавьте в систему библиотеки клиента:

   1. Добавьте строку /usr/lib/oracle/19.18/client64/lib/ в файл /etc/ld.so.conf:

      sudo vi /etc/ld.so.conf

   2. Сохраните файл и выйдите из редактора.

   3. Создайте связки и кеш динамических библиотек:

      sudo ldconfig -N

3. Выполните настройку необходимых переменных окружения:

   примечание

   Переменные среды необходимо устанавливать для обертки, в которой запускается сервер PostgreSQL.

   При работе экземпляра под управлением Pangolin Manager игнорируется переменная окружения LD_LIBRARY_PATH, инициализированная в .bash_profile в домашней директории пользователя postgres. Это происходит потому, что в systemd unit жестко прописано новое значение этой переменной.

   Для того чтобы переменная применилась, ее необходимо вносить непосредственно в systemd unit Pangolin Manager, расположение которого можно узнать командой:

   systemctl status pangolin-manager.service

   По умолчанию он расположен в каталоге /usr/lib/systemd/system/pangolin-manager.service.

   1. Для использования расширения достаточно переменной окружения LD_LIBRARY_PATH присвоить значение, равное пути, где расположены библиотеки oracle client для установленной версии, например, /usr/lib/oracle/19.18/client64/lib.

      vim ~/.bash_profile
      # добавить путь к переменной
      export LD_LIBRARY_PATH=/opt/pangolin-dbms-server/lib:/usr/lib/oracle/19.18/client64/lib
      export ORACLE_HOME="/usr/lib/oracle/19.14/client64"
      export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"

   2. Если в качестве Environment уже задана переменная PG_LD_LIBRARY_PATH, добавьте в нее существующий путь к библиотекам Oracle Client, например, /usr/lib/oracle/19.18/client64/lib. В результате строка будет выглядеть примерно так:

      Environment="PG_LD_LIBRARY_PATH=/opt/pangolin-dbms-server/lib:/usr/lib/oracle/19.18/client64/lib"

   3. Для обновления версии Pangolin необходимо создать символьную ссылку на библиотеку libclntsh.so.18.1 в каталоге системных библиотек /usr/lib64:

      ln -s /usr/lib/oracle/19.18/client64/lib/libclntsh.so.18.1 /usr/lib64/libclntsh.so.18.1

   4. Обновите конфигурацию systemd:

      sudo systemctl daemon-reload

   5. Остановите сервис Pangolin Manager:

      sudo systemctl stop pangolin-manager

   6. Запустите сервис Pangolin Manager:

      sudo systemctl start pangolin-manager

4. Перезагрузите сервер базы данных для применения переменных среды. Пример для инсталляции standalone:

   pg_ctl restart

   После выполнения описанных действий необходимые библиотеки и файлы клиента Oracle появятся в системе.

5. Добавьте расширение в БД (необходимы права суперпользователя):

   CREATE EXTENSION oracle_fdw SCHEMA ext;

   примечание

   В случае получения ошибки следующего вида проверьте правильность установки переменных среды (п. 2 – 4).

   ERROR:  could not load library "{$PGHOME}/lib/oracle_fdw.so": libclntsh.so.18.1: cannot open shared object file: No such file or directory

6. Дайте права на использование расширения oracle_fdw для необходимой роли:

   GRANT USAGE ON FOREIGN DATA WRAPPER oracle_fdw TO <role_name>;

7. При необходимости обновите расширение:

   ALTER EXTENSION oracle_fdw UPDATE;

Настройка

Настройка не требуется.

Управление

Привилегии Oracle

Учетная запись в Oracle должна иметь привилегию CREATE SESSION и права на чтение таблиц или представлений в запросе.

Для выполнения EXPLAIN VERBOSE пользователь также должен обладать привилегией SELECT на представлениях V$SQL и V$SQL_PLAN.

Подключение к внешней БД

Расширение oracle_fdw кеширует соединения Oracle, поскольку создание сеанса Oracle для каждого отдельного запроса обходится дорого. Все соединения автоматически закрываются по окончании сеанса со стороны СУБД Pangolin.

Функция oracle_close_connections() может быть использована для закрытия кешированных соединений Oracle. Это может быть полезно для длительных сеансов, которые не обращаются к внешним таблицам постоянно и хотят избежать блокировки ресурсов, необходимых для открытых подключений к Oracle. Нельзя вызвать эту функцию внутри транзакции, которая модифицирует данные в Oracle.

Поля и столбцы внешних таблиц

При определении внешней таблицы, столбцы таблицы базы данных Oracle сопоставляются столбцам СУБД Pangolin в порядке, в котором они указаны.

Расширение oracle_fdw будет включать в запрос Oracle только те столбцы, которые действительно необходимы для запроса СУБД Pangolin.

Таблица СУБД Pangolin может содержать больше или меньше столбцов, чем таблица базы данных Oracle. Если в ней больше столбцов, и эти столбцы используются, появится предупреждение и будут возвращены значения NULL.

Если необходимо выполнить операцию UPDATE или DELETE, убедитесь, что параметр key установлен для всех столбцов, принадлежащих первичному ключу таблицы. Невыполнение этого требования приведет к ошибкам.

Типы данных

Необходимо определить столбцы таблиц СУБД Pаngolin с типами данных, которые может преобразовать oracle_fdw (смотрите таблицу преобразования ниже). Это ограничение применяется только в том случае, если столбец действительно используется, поэтому можно определять «фиктивные» столбцы для непереводимых типов данных до тех пор, пока к ним не будет получен доступ (этот трюк работает только с SELECT, а не при изменении внешних данных). Если значение из Oracle превышает размер столбца СУБД Pаngolin (например, длину столбца varchar или максимальное целочисленное значение), будет выведено сообщение об ошибке во время выполнения.

Данные преобразования типов автоматически выполняются расширением oracle_fdw:

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 <=0	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 LOCAL TIME ZONE	date, timestamp, timestamptz, char, varchar, text
INTERVAL YEAR TO MONTH	interval, char, varchar, text
INTERVAL DAY TO SECOND	interval, char, varchar, text
XMLTYPE	xml, char, varchar, text
MDSYS.SDO_GEOMETRY	geometry (see "PostGIS support" below)

Если тип NUMBER привести к boolean, тогда "0" превратится в "false", все остальное в "true".

Вставка или обновление XMLTYPE работает только со значениями, которые не превышают максимальную длину типа данных VARCHAR2 (4000 или 32767, в зависимости от параметра MAX_STRING_SIZE).

Тип данных NCLOB в настоящее время не поддерживается, поскольку Oracle не может автоматически преобразовать его в кодировку клиента. Если нужны преобразования, отличающиеся от вышеуказанных, определите соответствующее представление (view) в Oracle или Pangolin.

Инструкции WHERE и ORDER BY

СУБД Pаngolin будет использовать все применимые части предложения WHERE в качестве фильтра для проверки. Запрос Oracle, который создает oracle_fdw, будет содержать предложение WHERE, соответствующее этим критериям фильтрации, всякий раз, когда такое условие может быть безопасно переведено в Oracle SQL. Эта функция, также известная как выдвижение предложений WHERE, может значительно сократить количество строк, извлекаемых из Oracle, и может позволить оптимизатору Oracle выбрать подходящий план доступа к требуемым таблицам.

Аналогично, предложения ORDER BY будут перенесены в Oracle там, где это возможно. Обратите внимание, что ORDER BY, сортирующее по символьной строке, будет пропущено, поскольку нет гарантий, что порядок сортировки в СУБД Pаngolin и Oracle будет одинаковым.

Для использования попробуйте писать простые условия для внешней таблицы. Выберите типы данных столбцов СУБД Pаngolin, соответствующие типам Oracle, поскольку в противном случае условия не могут быть переведены.

Выражения now(), transaction_timestamp(), current_timestamp, current_date и localtimestamp будут переведены правильно.

В выводе EXPLAIN будет показан используемый запрос Oracle, чтобы была возможность увидеть, какие условия были переведены в Oracle и как.

Объединения (JOIN) между внешними таблицами

Расширение oracle_fdw может передавать инструкцию join на сервер Oracle — join между двумя внешними таблицами приведет к одному запросу Oracle, который выполняет join на стороне Oracle.

Необходимые условия:

• обе таблицы должны относиться к одному внешнему серверу Oracle;
• JOIN между 3 и более таблицами не произойдет;
• JOIN должен использоваться в выражении SELECT;
• oracle_fdw должен быть способен выполнить все JOIN и условия WHERE;
• перекрестный JOIN без условий не будет выполнен;
• если выполнен JOIN, ORDER BY выполнен не будет.

Важно, чтобы статистика для обеих внешних таблиц была собрана с помощью ANALYZE для определения наилучшей стратегии объединения.

Изменение внешних данных

Расширение oracle_fdw поддерживает операции INSERT, UPDATE и DELETE на внешних таблицах. Данные операции доступны по умолчанию (также и в БД, обновленной с ранних версий PostgreSQL) и могут быть отключены опцией readonly на внешней таблице.

Чтобы операции UPDATE и DELETE работали, столбцы, относящиеся к первичному ключу в таблице Oracle, должны иметь установленную опцию key. Значения данных столбцов используются для идентификации строк внешней таблицы, поэтому убедитесь, что опция установлена на ВСЕХ столбцах, относящихся к первичному ключу.

Если во время вставки один из столбцов внешней таблицы пропущен, этому столбцу присваивается значение, определенное в предложении DEFAULT для внешней таблицы СУБД Pangolin (или NULL, если предложение DEFAULT отсутствует). Предложения DEFAULT в соответствующих столбцах Oracle не используются. Если внешняя таблица СУБД Pangolin не включает все столбцы таблицы Oracle, для столбцов, не включенных в определение внешней таблицы, будут использоваться предложения DEFAULT в Oracle.

Директива RETURNING в операцих INSERT, UPDATE или DELETE поддерживается, кроме столбцов Oracle с типами данных LONG или LONG RAW (сам Oracle не поддерживает данные типы данных в RETURNING).

Поддерживаются триггеры для внешних таблиц. Триггеры, определенные с использованием AFTER и FOR EACH ROW, требуют, чтобы во внешней таблице не было столбцов с типом данных Oracle LONG или LONG RAW. Это происходит потому, что такие триггеры используют предложение RETURN, упомянутое выше.

Во время изменения внешних данных производительность может снижаться, особенно когда затрагивается много строк, потому что (так работают внешние обертки данных) каждая строка должна обрабатываться индивидуально.

Транзакции пересылаются в Oracle, поэтому BEGIN, COMMIT, ROLLBACK и SAVEPOINT работают должным образом. Подготовленные запросы, относящиеся к Oracle, не поддерживаются.

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

ORA-08177: can't serialize access for this transaction

Это может произойти, если несколько транзакций одновременно изменяют таблицу и вероятность этого возрастает на длинных транзакциях. Такие ошибки могут быть идентифицированы по статусу SQLSTATE(40001). Приложение, использующее oracle_fdw, должно повторить транзакции, которые завершаются с этой ошибкой.

По возможности используйте другие уровни изоляции транзакций (READ COMMITTED или READ ONLY), чтобы избежать данной ошибки.

Планы запросов (EXPLAIN)

Команда EXPLAIN в СУБД Pаngolin покажет запрос, который фактически отправлен в Oracle. EXPLAIN VERBOSE покажет план выполнения Oracle (но это не будет работать с версиями Oracle 9i и ниже).

Сбор статистики (ANALYZE)

Можно использовать ANALYZE для сбора статистики внешней таблицы. oracle_fdw поддерживает такую возможность.

Без статистики СУБД Pаngolin не может оценить количество строк для запросов по внешней таблице, что может привести к выбору неправильных планов выполнения. СУБД Pаngolin не будет автоматически собирать статистику для внешних таблиц с помощью демона AUTOVACUUM, как это делается для обычных таблиц, поэтому особенно важно запускать ANALYZE для внешних таблиц после создания и всякий раз, когда внешняя таблица значительно изменилась.

примечание

Для внешней таблицы Oracle выполнение ANALYZE приведет к полному последовательному сканированию таблицы. Чтобы ускорить процесс, можно использовать параметр таблицы sample_percent.

Поддержка геоданных PostGIS

Особенности миграции с Oracle представлены в одноименном разделе документа «Руководство администратора».

Импорт определения сторонних таблиц (IMPORT FOREIGN SCHEMA)

Расширение oracle_fdw имеет функцию, которая позволяет массово создавать сторонние таблицы. Можно создать внешнюю таблицу, используя CREATE FOREIGN TABLE. При этом необходимо определить каждый столбец в соответствии с его определением на стороне БД Oracle. Это достаточно трудоемко, если количество таблиц велико.

С помощью IMPORT FOREIGN SCHEMA есть возможность создавать сторонние таблицы для каждой из таблиц, определенных в импортированной схеме, с возможностью импорта только некоторых из этих таблиц.

примечание

Предложение DEFAULT не импортируется, поэтому его необходимо добавить отдельно к столбцам позже.

В дополнение к документации по IMPORT FOREIGN SCHEMA, учитывайте следующее:

• IMPORT FOREIGN SCHEMA создаст внешние таблицы для всех объектов, найденных в ALL_TAB_COLUMNS, включая таблицы, представления и материализованные представления, но не синонимы.

• Поддерживаемые параметры для IMPORT FOREIGN SCHEMA:

• case – управляет регистром имен таблиц и столбцов при импорте; Возможные значения:

  • keep: оставить имена такими как в Oracle, как правило в верхнем регистре.
  • lower: преобразовать имена всех таблиц и столбцов в нижний регистр.
  • smart: преобразовать только имена, записанные полностью в верхнем регистре в Oracle (значение по умолчанию).

• collation: порядок сортировки, используемый при преобразовании регистра при выборе значения lower или smart для параметра case. По умолчанию используется порядок сортировки используемый в БД. Поддерживаются только значения, которые есть в схеме pg_catalog. Доступные параметры сортировки указаны в таблице pg_collation, значения collname.

• dblink: dblink на базу данных Oracle, через которую осуществляется доступ к схеме. Имя должно быть написано точно так же, как оно было сохранено в системном каталоге Oracle. Как правило, оно содержит только символы в верхнем регистре.

• readonly: устанавливает опцию readonly для всех импортируемых таблиц(смотрите описание опций таблиц).

• max_long: устанавливает опцию max_long для всех импортируемых таблиц (смотрите описание опций таблиц).

• sample_percent: устанавливает опцию sample_percent для всех импортируемых таблиц (смотрите описание опций таблиц).

• prefetch: устанавливает опцию prefetch для всех импортируемых таблиц (смотрите описание опций таблиц).

• Имя схемы должно быть написано точно так же, как оно сохранено в системном каталоге Oracle. Как правило, имя содержит только символы в верхнем регистре. Поскольку PostgreSQL переводит имена в нижний регистр перед обработкой, необходимо обернуть имя схемы двойными кавычками (например, "SCOTT").

• Имена таблиц в выражениях, использующих LIMIT TO или EXCEPT, должны быть написаны так, как они будут отображаться в PostgreSQL после преобразования регистра (смотрите параметр case).

Обратите внимание, что IMPORT FOREIGN SCHEMA не работает с СУБД Oracle версии 8i и ниже.

Использование модуля

Сценарий использования №1

Данный пример предполагает, что работа ведется под пользователем ОС postgres и он может подключиться к БД Oracle:

sqlplus orauser/orapwd@//dbserver.mydomain.com:1521/ORADB

Проверка подключения к БД необходима, чтобы убедиться, что Oracle клиент и окружение настроены корректно и все работает правильно. Кроме этого необходимо, чтобы расширение oracle_fdw присутствовало в инсталляции Pangolin (убедитесь, что версия Pangolin используется не ниже 4.5.0).

Предположим, что необходимо получить доступ к таблице Oracle, которая имеет вид:

SQL> DESCRIBE oratab

 Name                            Null?    Type
 ------------------------------- -------- ------------
 ID                              NOT NULL NUMBER(5)
 TEXT                                     VARCHAR2(30)
 FLOATING                        NOT NULL NUMBER(7,2)

Для этого:

1. Сконфигурируйте oracle_fdw под администратором СУБД:

   CREATE EXTENSION oracle_fdw;

   CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
           OPTIONS (dbserver '//dbserver.mydomain.com:1521/ORADB');

2. Выдайте права на использование стороннего сервера пользователю:

   GRANT USAGE ON FOREIGN SERVER oradb TO pguser;

3. Подключитесь к БД пользователем pguser и выполните:

   CREATE USER MAPPING FOR pguser SERVER oradb OPTIONS (user 'orauser');

4. Пароль пользователя Oracle должен быть сохранен в засекреченном хранилище паролей, утилита pg_auth_config;

5. Создайте внешнюю таблицу:

   CREATE FOREIGN TABLE oratab (
           id integer OPTIONS (key 'true') NOT NULL,
           text character varying(30),
           floating double precision NOT NULL
       ) SERVER oradb OPTIONS (schema 'ORAUSER', table 'ORATAB');

Теперь можно обращаться к данной таблице, как к обычной таблице PostgreSQL в СУБД Pangolin.

Сценарий использования №2

1. Создать «сервер» – атрибут, который содержит параметры подключения к серверу базы данных Oracle:

   CREATE SERVER ora_db FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//oracle_server:1521/oracle_db');

   Где:

   • ora_db – произвольное имя сервера;
   • dbserver – параметр подключения к серверу баз данных Oracle.

2. Создать сопоставление пользователя на внешнем сервере для подключения к созданному серверу ora_db:

   CREATE USER MAPPING FOR pguser SERVER ora_db OPTIONS (user 'ora_user');

   Где:

   • pguser – имя пользователя в базе данных Pangolin, который сможет пользоваться внешними данными;
   • ora_db – имя созданного сервера внешних данных;
   • ora_user – имя пользователя в базе данных Oracle, который имеет право на чтение внешних данных.

3. Выдать права для использования стороннего сервера Oracle для пользователя, которому было создано сопоставление (USER MAPPING):

   GRANT USAGE ON FOREIGN SERVER ora_db TO pguser;

   Где:

   • ora_db – имя созданного сервера внешних данных;
   • pguser – имя пользователя в базе данных Pangolin, которому предоставляются права на использование созданного сервера внешних данных.

4. Добавить пароль для пользователя базы данных Oracle в засекреченное хранилище. Приведены два способа:

   • функция add_auth_record_to_storage требует явного ввода пароля в строке запуска, что небезопасно:

     SELECT add_auth_record_to_storage('FQDN_hostname-OR-IPaddress', 1521, 'oracle_db', 'ora_user', '<password>');

     Пример вывода результата:

     add_auth_record_to_storage
     ----------------------------
     
     (1 row)

   • утилита pg_auth_ config с интерактивным вводом пароля:

     pg_auth_config add --host <FQDN_hostname-OR-IPaddress> --port 1521 --database oracle_db --user ora_user

     По запросу утилиты ввести интерактивно дважды пароль пользователя:

     enter password:
     *******************
     confirm password:
     *******************

     В случае успешного завершения утилита выдает сообщение:

     Going to add auth record for user: "ora_user", host: "<FQDN_hostname-OR-IPaddress>", port: "1521", database: "oracle_db"
     new record added

   Внимание!

   При проверке хранилища паролей расширение не проверяет пароль пользователя БД Oracle.

5. Вывод содержимого целевой тестовой таблицы SCOPE_TEST1 в базе данных Oracle:

   SQL> SELECT * FROM SCOPE_TEST1;
   
         COL1 COL2
   ---------- -------------------------------------------------
         4034 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
          734 GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
   	    32 BUFFER_POOL DEFAULT FLASH_CACHE
       	74 POOL DEFAULT FLASH_CACHE
          297 GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
       	84 GROUPS POOL DEFAULT FLASH_CACHE
          532 BUFFER_POOL DEFAULT
          574 POOL DEFAULT
          297 GROUPS 1 BUFFER_POOL DEFAULT
          584 GROUPS POOL DEFAULT
   
   10 rows selected.

6. Структура целевой тестовой таблицы SCOPE_TEST1 в базе данных Oracle:

   SQL> DESCRIBE SCOPE_TEST1;
   
    Name						   Null?    Type
    ----------------------------- -------- ----------------------
    COL1						   NOT NULL NUMBER(19)
    COL2						   NOT NULL VARCHAR2(128 CHAR)

7. Создать внешнюю таблицу по отношению к существующей таблицы в базе данных Oracle (атрибуты и типы данных должны соответствовать):

   CREATE FOREIGN TABLE ext.ora_scope_test1(col1 numeric(19,0), col2 varchar(128)) SERVER ora_db OPTIONS (SCHEMA 'PG_USER', TABLE 'SCOPE_TEST1');

   Где:

   • ext.ora_scope_test1 – полное название внешней таблицы, создаваемой в базе данных Pangolin;
   • col1, col2 – столбцы таблицы, которые должны соответствовать по названию и типу данных структуре внешней таблице в базе данных Oracle;
   • ora_db – имя сервера внешних данных Oracle;
   • PG_USER – имя схемы в базе данных Oracle;
   • SCOPE_TEST1 – имя тестовой таблицы в базе данных Oracle.

8. Проверка работы расширения: можно обращаться к целевой таблице во внешней базе данных Oracle, как к обычной таблице PostgreSQL в СУБД Pangolin:

   SELECT * FROM ext.ora_scope_test1;

   Пример вывода результата запроса:

    col1 |                       col2
   ------+---------------------------------------------------
    4034 | FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
     734 | GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
      32 | BUFFER_POOL DEFAULT FLASH_CACHE
      74 | POOL DEFAULT FLASH_CACHE
     297 | GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
      84 | GROUPS POOL DEFAULT FLASH_CACHE
     532 | BUFFER_POOL DEFAULT
     574 | POOL DEFAULT
     297 | GROUPS 1 BUFFER_POOL DEFAULT
     584 | GROUPS POOL DEFAULT
   (10 rows)

9. Созданная внешняя таблица присутствует в списке системного каталога:

   \d+ ora_scope_test1

                                             Foreign table "ext.ora_scope_test1"
    Column |          Type          | Collation | Nullable | Default | FDW options | Storage  | Stats target | Description
   --------+------------------------+-----------+----------+---------+-------------+----------+--------------+-------------
    col1   | numeric(19,0)          |           |          |         |             | main     |              |
    col2   | character varying(128) |           |          |         |             | extended |              |
   Server: ora_db
   FDW options: (schema 'PG_USER', "table" 'SCOPE_TEST1')

10. Удалить внешнюю таблицу:

    DROP FOREIGN TABLE ext.ora_scope_test1;

Ссылки на документацию

Дополнительно поставляемый модуль oracle_fdw: https://github.com/laurenz/oracle_fdw.