Postgres Pro Enterprise : Документация: 16: H.3. oracle_fdw — доступ к базам данных Oracle : Компания Postgres Professional

H.3. oracle_fdw — доступ к базам данных Oracle
Пред.	Наверх	Приложение H. Сторонние модули и расширения, поставляемые в виде отдельных пакетов	Начало	След.

H.3. oracle_fdw — доступ к базам данных Oracle #

H.3.1. Установка
H.3.2. Внутреннее устройство
H.3.3. Простой пример
H.3.4. Использование
H.3.5. Справка
H.3.6. Проблемы
H.3.7. Авторы

Модуль oracle_fdw – это расширение Postgres Pro, которое предоставляет обёртку сторонних данных для простого и эффективного доступа к базам данных Oracle, включая вынесение наружу условий WHERE и обращений к столбцам, а также полноценную поддержку EXPLAIN.

H.3.1. Установка #

oracle_fdw поставляется вместе с Postgres Pro Enterprise в виде отдельного пакета oracle-fdw-ent-16 (подробные инструкции по установке приведены в Главе 17). Для oracle_fdw требуется Oracle Instant Client версии 19.5.

Для систем на базе RPM (RHEL, SUSE, РЕД ОС, ROSA, Альт Линукс) загрузите RPM-пакет oracle-instantclient и установите его с помощью rpm. Если вы используете систему на базе Debian (Ubuntu, Astra Linux), нужно либо преобразовать RPM-пакет в Debian-пакет с помощью alien, а затем установить его с помощью dpkg, либо загрузить ZIP-архив с клиентом и извлечь содержимое архива в каталог /opt/oracle.

После установки Postgres Pro Enterprise создайте расширение oracle_fdw:

CREATE EXTENSION oracle_fdw;

При этом определяются необходимые функции и создаётся оболочка сторонних данных.

Обратите внимание, что версия расширения, выводимая командой psql \dx или в системном каталоге pg_available_extensions, не является установленной версией oracle_fdw. Чтобы узнать версию oracle_fdw, используйте функцию oracle_diag.

H.3.2. Внутреннее устройство #

oracle_fdw задаёт для параметра MODULE сеанса Oracle значение postgres, а для параметра ACTION — номер внутреннего процесса. Это помогает идентифицировать сеанс Oracle и позволяет отслеживать его с помощью DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE.

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

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

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

Транзакция Oracle фиксируется непосредственно перед фиксацией локальной транзакции, поэтому завершённая транзакция Postgres Pro гарантирует завершение транзакции Oracle. Однако остаётся небольшая вероятность, что транзакция Postgres Pro не сможет завершиться, даже если транзакция Oracle уже была зафиксирована. Избежать этого можно только с использованием двухфазных транзакций и менеджера транзакций, что выходит за рамки возможностей обёртки сторонних данных. Подготовленные операторы с участием Oracle не поддерживаются по той же причине.

H.3.3. Простой пример #

Ниже представлен пример использования oracle_fdw. Для получения более детальной информации обратитесь к разделам Параметры и Использование. Также следует ознакомиться с документацией по работе со сторонними данными и упомянутыми в ней командами.

В данном примере предполагается, что вы можете подключиться к Oracle, выполнив следующую команду от имени пользователя операционной системы, который может запускать сервер Postgres Pro (например, postgres):

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

Если вы успешно подключились, значит клиент Oracle и среда настроены правильно. Также предполагается, что пакет с расширением oracle_fdw уже установлен (см. раздел Установка).

Необходим доступ к такой таблице:

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

Настройте oracle_fdw от имени суперпользователя Postgres Pro следующим образом:

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

Можно использовать другие варианты указания имени или локальные соединения, см. описание dbserver ниже.

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

pgdb=# GRANT USAGE ON FOREIGN SERVER oradb TO pguser;

Затем можно подключиться к Postgres Pro от имени pguser и задать следующие параметры:

pgdb=> CREATE USER MAPPING FOR pguser SERVER oradb
          OPTIONS (user 'orauser', password 'orapwd');

Чтобы не хранить пароли Oracle, можно использовать внешнюю аутентификацию.

pgdb=> 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');

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

Теперь можно обращаться к этой таблице, как к обычной таблице Postgres Pro.

H.3.4. Использование #

H.3.4.1. Разрешения Oracle #

Пользователю Oracle потребуется право CREATE SESSION, а также доступ к рассматриваемой таблице или представлению. Обратите внимание, что oracle_fdw обращается к таблице Oracle во время планирования запроса, чтобы получить её определение. Это происходит до проверки прав для внешней таблицы. Поэтому если вы попытаетесь получить доступ к внешней таблице без соответствующих прав в Postgres Pro, может возникнуть ошибка Oracle. Это ожидаемо и не представляет угрозы безопасности.

Для EXPLAIN VERBOSE пользователю также потребуются права SELECT для V$SQL и V$SQL_PLAN.

H.3.4.2. Соединения #

oracle_fdw кеширует соединения Oracle, поскольку создавать сеанс Oracle для каждого отдельного запроса слишком дорого. Все соединения автоматически закрываются по завершении сеанса Postgres Pro.

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

H.3.4.3. Столбцы #

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

oracle_fdw включит в запрос к Oracle только те столбцы, которые действительно необходимы запросу Postgres Pro.

В таблице Postgres Pro может быть больше или меньше столбцов, чем в таблице Oracle. Если в ней больше столбцов, и эти столбцы используются, выводится предупреждение и возвращаются значения NULL.

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

H.3.4.4. Типы данных #

Столбцы в Postgres Pro должны быть определены с типами данных, которые oracle_fdw может преобразовывать (см. таблицу преобразования ниже). Это ограничение действует только для используемых столбцов, поэтому можно определить «фиктивные» столбцы для непреобразуемых типов данных, пока к ним не производится обращение (этот подход работает только для SELECT, но не при изменении сторонних данных). Если значение Oracle превышает размер столбца Postgres Pro (например, длину столбца varchar или максимальное значение integer), это приведёт к ошибке времени выполнения.

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

Тип Oracle              | Возможные типы PostgreSQL
-------------------------+--------------------------------------------------
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           | 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
XMLTYPE                  | xml, char, varchar, text
MDSYS.SDO_GEOMETRY       | geometry (см. "поддержка PostGIS" ниже)

При преобразовании типа NUMBER в boolean значение 0 интерпретируется как false, а любое другое значение — как true.

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

В настоящее время тип NCLOB не поддерживается, так как Oracle не выполняет автоматическое преобразование этого типа в клиентскую кодировку.

Если требуется преобразовать TIMESTAMP WITH LOCAL TIMEZONE в timestamp, рекомендуется установить параметр set_timezone на стороннем сервере.

Если требуются преобразования, не указанные выше, определите соответствующее представление в Oracle или Postgres Pro.

H.3.4.5. Условия `WHERE` и предложения `ORDER BY` #

Postgres Pro будет использовать все подходящие части предложения WHERE в качестве фильтра для сканирования. Запрос Oracle, сформированный oracle_fdw, будет включать предложение WHERE, соответствующее этим фильтрам, если такое условие можно без проблем преобразовать в SQL Oracle. Эта функция, известная как вынесение наружу предложений WHERE, может значительно сократить количество строк, извлекаемых из Oracle, а также помочь оптимизатору Oracle выбрать хороший план доступа к необходимым таблицам.

Предложения ORDER BY будут аналогично выноситься наружу в Oracle, когда это возможно. Обратите внимание, что условия ORDER BY, сортирующие по строкам символов, не будут выноситься наружу, поскольку невозможно гарантировать, что порядок сортировки в Postgres Pro совпадёт с порядком в Oracle.

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

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

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

H.3.4.6. Соединения сторонних таблиц #

Расширение oracle_fdw поддерживает вынос соединений на сервер Oracle. Это означает, что соединение сторонних таблиц будет выполнено в рамках одного запроса Oracle на стороне Oracle.

Однако для выноса соединений наружу есть следующие ограничения:

Обе таблицы должны быть определены на одном и том же стороннем сервере.
Соединения между тремя и более таблицами не выносятся наружу.
Соединение должно быть в рамках оператора SELECT.
Расширение oracle_fdw должно иметь возможность вынести наружу все условия соединения и все предложения WHERE.
Перекрёстные соединения без условий соединения не выносятся наружу.
Если соединение передано на сервер, предложения ORDER BY не выносятся наружу.

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

H.3.4.7. Изменение сторонних данных #

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

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

Если не указать столбец сторонней таблицы во время INSERT, для этого столбца устанавливается значение, определённое в предложении DEFAULT в сторонней таблице Postgres Pro (или NULL, если нет предложения DEFAULT). Предложения DEFAULT в соответствующих столбцах Oracle не используются. Если сторонняя таблица Postgres Pro не включает все столбцы таблицы Oracle, предложения DEFAULT из Oracle будут использоваться для столбцов, не включённых в определение сторонней таблицы.

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

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

Хотя изменение сторонних данных поддерживается, производительность может быть низкой, особенно при обработке большого количества строк. Это связано с особенностями работы обёртки сторонних данных, которая требует обработки каждой строки отдельно.

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

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

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

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

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

H.3.4.8. EXPLAIN #

Postgres Pro EXPLAIN показывает запрос, который фактически отправлен в Oracle. EXPLAIN VERBOSE показывает план выполнения Oracle (эта возможность недоступна для серверов Oracle 9i или более ранней версии, см. Проблемы).

H.3.4.9. ANALYZE #

Для сбора статистик по сторонним таблицам можно использовать ANALYZE, которая поддерживается в oracle_fdw.

Без статистики Postgres Pro не может оценить количество строк, возвращаемых запросами к сторонней таблице, что может привести к выбору неоптимальных планов выполнения.

Postgres Pro не будет автоматически собирать статистику для сторонних таблиц с помощью демона автоочистки, как это происходит с обычными таблицами. Поэтому особенно важно запускать ANALYZE для сторонних таблиц после их создания и каждый раз, когда удалённая таблица значительно изменяется.

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

H.3.4.10. Поддержка PostGIS #

Тип данных geometry доступен, только если установлено расширение PostGIS.

Поддерживаются следующие геометрические типы данных: POINT, LINE, POLYGON, MULTIPOINT, MULTILINE и MULTIPOLYGON в двухмерном и трёхмерном представлении. Пустые геометрии PostGIS не поддерживаются, поскольку для них нет аналогов в Oracle Spatial.

Значения NULL для SRID в Oracle будут преобразованы в 0 и наоборот. Для других преобразований SRID Oracle и PostGIS создайте файл srid.map в каталоге share в Postgres Pro. Каждая строка этого файла должна содержать SRID Oracle и соответствующий SRID PostGIS, разделённые пробелом. Для обеспечения высокой производительности файл рекомендуется держать небольшим.

H.3.4.11. Поддержка команды `IMPORT FOREIGN SCHEMA` #

Команда IMPORT FOREIGN SCHEMA поддерживает массовый импорт определений всех таблиц из схемы Oracle. В дополнение к документации команды IMPORT FOREIGN SCHEMA следует учитывать следующие особенности:

IMPORT FOREIGN SCHEMA создаёт внешние таблицы для всех объектов, найденных в ALL_TAB_COLUMNS. Сюда входят таблицы, представления и материализованные представления, но не синонимы.
Для команды IMPORT FOREIGN SCHEMA поддерживаются следующие параметры:
- Переменная case управляет регистром для имён таблиц и столбцов во время импорта.
  Возможные значения:
  - keep: оставить имена такими же, как в Oracle, обычно в верхнем регистре.
  - lower: перевести все имена таблиц и столбцов в нижний регистр.
  - smart: перевести только те имена, которые в Oracle полностью написаны в верхнем регистре (это значение по умолчанию).
- collation — это правило сортировки, используемое для управления регистром lower и smart параметра case.
  Значение по умолчанию — default, которое соответствует правилу сортировки по умолчанию для базы данных. Поддерживаются только правила сортировки в схеме pg_catalog. Для получения списка возможных значений обратитесь к полю collname в каталоге pg_collation .
- dblink — это ссылка на базу данных Oracle, через которую осуществляется доступ к схеме.
  Написание этого имени должно быть таким же, как в системном каталоге Oracle, которое обычно состоит только из заглавных букв.
- readonly устанавливает параметр readonly для всех импортируемых таблиц.
- skip_tables (по умолчанию false): не импортировать таблицы.
- skip_views (по умолчанию false): не импортировать представления.
- skip_matviews (по умолчанию false): не импортировать материализованные представления.
- max_long устанавливает параметр max_long для всех импортируемых таблиц.
- sample_percent устанавливает параметр sample_percent для всех импортируемых таблиц.
- prefetch устанавливает параметр prefetch для всех импортируемых таблиц.
- lob_prefetch устанавливает параметр lob_prefetch для всех импортируемых таблиц.
- nchar устанавливает параметр nchar для всех импортируемых таблиц.
- set_timezone устанавливает параметр set_timezone для всех импортируемых таблиц.
Имя схемы Oracle должно быть написано точно так же, как в Oracle, то есть, как правило, в верхнем регистре. Так как Postgres Pro переводит имена в нижний регистр перед обработкой, необходимо заключить имя схемы в двойные кавычки (например, "SCOTT").
Имена таблиц в предложении LIMIT TO или EXCEPT должны быть записаны в том виде, в котором они будут отображаться в Postgres Pro после применения правил учёта регистра, описанных выше.

Обратите внимание, что IMPORT FOREIGN SCHEMA не поддерживается для серверов Oracle версии 8i; за подробностями обратитесь к разделу Проблемы.

H.3.5. Справка #

H.3.5.1. Создаваемые расширением объекты #

oracle_fdw_handler() RETURNS fdw_handler oracle_fdw_validator(text[], oid) RETURNS void #

Эти функции представляют собой функцию-обработчик и функцию проверки, которые необходимы для создания обёртки сторонних данных.

FOREIGN DATA WRAPPER oracle_fdw
  HANDLER oracle_fdw_handler
  VALIDATOR oracle_fdw_validator

Расширение автоматически создаёт обёртку сторонних данных с именем oracle_fdw. Обычно после этого можно перейти к определению сторонних серверов. Можно также создать дополнительные обёртки сторонних данных Oracle, например, если нужно установить параметр nls_lang. Хотя можно изменить существующую обёртку oracle_fdw, следует помнить, что в этом случае все изменения будут потеряны после выгрузки/восстановления базы.

oracle_close_connections() RETURNS void #

Эту функцию можно использовать для закрытия всех открытых соединений Oracle в этом сеансе. За дополнительные сведениями обратитесь к разделу Использование.

oracle_diag(name DEFAULT NULL) RETURNS text #

Данная функция полезна только для диагностических целей. Она возвращает версии сервера oracle_fdw, Postgres Pro и клиента Oracle. При вызове без аргументов или с NULL также возвращаются значения некоторых переменных среды, используемых для установления соединений с Oracle. При вызове с именем стороннего сервера также возвращается версия сервера Oracle.

oracle_execute(server name, stmt text) RETURNS void #

Эту функцию можно использовать для выполнения произвольных операторов SQL на удалённом сервере Oracle. Она будет работать только с операторами, которые не возвращают результаты (обычно операторы DDL).

Будьте осторожны при использовании этой функции, поскольку она может повлиять на процесс управления транзакциями oracle_fdw. Помните, что выполнение DDL-операторов в Oracle сопровождается неявной командой COMMIT. Не рекомендуется использовать эту функцию в транзакциях с несколькими операторами.

H.3.5.2. Параметры #

H.3.5.2.1. Параметры обёртки сторонних данных #

Важно

Если вы измените стандартную обёртку сторонних данных oracle_fdw, все изменения будут потеряны при выгрузке/восстановлении. Чтобы сохранить параметры, создайте новую обёртку сторонних данных. Установочный скрипт содержит оператор CREATE FOREIGN DATA WRAPPER, который можно взять за основу при создании собственной обёртки.

[nls_lang] #: Устанавливает указанное значение для переменной среды NLS_LANG в Oracle. Формат NLS_LANG — язык_территория.наборсимволов (например, AMERICAN_AMERICA.AL32UTF8). Значение должно соответствовать кодировке вашей базы данных. Если это значение не установлено, oracle_fdw автоматически определит его, если сможет, и выдаст предупреждение в противном случае. Устанавливайте это значение, только если понимаете, как оно работает. См. также раздел Проблемы.

H.3.5.2.2. Параметры стороннего сервера #

dbserver #

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

[isolation_level] #

Уровень изоляции транзакций, используемый в базе данных Oracle: serializable, read_committed или read_only. Значение по умолчанию — serializable.

Обратите внимание, что один оператор Postgres Pro может обращаться к таблице Oracle более одного раза (например, оператор соединения вложенным циклом). Чтобы избежать несогласованности данных в условиях гонки с параллельными транзакциями, уровень изоляции транзакций должен гарантировать стабильность чтения. Это возможно только с уровнями изоляции Oracle SERIALIZABLE или READ ONLY.

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

[nchar] #

Со значением on для этого параметра будет выбираться более дорогое преобразование символов на стороне Oracle. Это необходимо, когда в таблицах Oracle есть столбцы NCHAR или NVARCHAR2, содержащие символы, которые не могут быть представлены в наборе символов базы данных Oracle. Значение по умолчанию — off.

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

[set_timezone] #

При указании для этого параметра значения on часовой пояс сеанса Oracle при подключении будет соответствовать текущему значению параметра timezone в Postgres Pro. Это полезно, только если планируется использовать столбцы Oracle типа TIMESTAMP WITH LOCAL TIME ZONE и нужно преобразовать их в timestamp without time zone в Postgres Pro. Значение по умолчанию — off.

Обратите внимание, что если изменить timezone после подключения к Oracle, oracle_fdw не изменит часовой пояс сеанса Oracle. В этом случае, чтобы открыть новое соединение при следующем обращении к сторонней таблице, вызовите oracle_close_connections() RETURNS void .

Если Oracle не может распознать часовой пояс, попытки соединения завершаются ошибкой вида «ORA-01882: timezone region not found» (ORA-01882: регион часового пояса не найден).

В этом случае либо используйте другое значение timezone, либо оставьте значение off и задайте для переменной среды ORA_SDTZ соответствующее значение в конфигурации среды сервера Postgres Pro.

H.3.5.2.3. Параметры сопоставления пользователей #

user #: Имя пользователя Oracle для сеанса. Если вы не хотите хранить учётные данные Oracle в базе данных Postgres Pro, укажите пустую строку для внешней аутентификации (один из простых способов — внешнее хранилище паролей).
password #: Пароль пользователя Oracle.

H.3.5.2.4. Параметры сторонней таблицы #

table #

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

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

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

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

dblink #

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

[schema] #

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

[max_long] #

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

[readonly] #

Команды INSERT, UPDATE и DELETE поддерживаются только для таблиц, где значение этого параметра отличается от yes/on/true. По умолчанию используется false.

[sample_percent] #

Этот параметр влияет только на выполнение ANALYZE и может быть полезен для сокращения времени работы ANALYZE с очень большими таблицами.

Значение должно находиться в диапазоне от 0,000001 до 100. Оно определяет процент блоков таблицы Oracle, которые будут выбраны случайным образом для расчёта статистики таблицы Postgres Pro. В Oracle для выборки блоков используется предложение SAMPLE BLOCK (x). Значение по умолчанию: 100.

ANALYZE завершится ошибкой ORA-00933 для таблиц, определённых с помощью запросов Oracle, и может завершиться ошибкой ORA-01446 для таблиц, определённых с помощью сложных представлений Oracle.

[prefetch] #

Задаёт количество строк, которые будут извлечены за один цикл передачи данных между Postgres Pro и Oracle во время сканирования сторонней таблицы. Значение должно находиться в диапазоне от 1 до 1000, где нулевое значение отключает предварительную выборку. Значение по умолчанию — 50.

С более высокими значениями производительность может повыситься, но будет использоваться больше памяти на сервере Postgres Pro.

Обратите внимание, что предварительная выборка не выполняется, если таблица Oracle содержит столбцы типа MDSYS.SDO_GEOMETRY.

[lob_prefetch] #

Устанавливает число байт, которые предварительно выбираются для значений BLOB, CLOB и BFILE. Если значения LOB превышают этот размер, потребуются дополнительные циклы передачи данных между Postgres Pro и Oracle, поэтому установка значения, превышающего обычно используемый размер LOB, может повысить производительность. Выбор больших значений для этого параметра увеличит потребление памяти на стороне сервера, но повысит производительность при работе с большими LOB. Значение по умолчанию — 1048576.

H.3.5.2.5. Параметры столбцов #

[key] #: Если установлено значение yes/on/true, соответствующий столбец сторонней таблицы Oracle считается частью первичного ключа. Чтобы UPDATE и DELETE работали, необходимо установить этот параметр для всех столбцов, входящих в первичный ключ таблицы. Значение по умолчанию — false.
[strip_zeros] #: Если установлено значение yes/on/true, символы ASCII 0 будут удалены из строки во время передачи. Такие символы допустимы в Oracle, но не в Postgres Pro, поэтому они вызовут ошибку при чтении oracle_fdw. Этот параметр имеет смысл использовать только для столбцов character, character varying и text. Значение по умолчанию — false.

H.3.6. Проблемы #

H.3.6.1. Кодировка #

Символы, хранящиеся в базе данных Oracle, которые не могут быть преобразованы в кодировку базы данных Postgres Pro, будут автоматически заменены на подстановочные символы Oracle, обычно это прямые или обратные вопросительные знаки. При этом не будет выводиться никаких предупреждений или сообщений об ошибках.

Если используется кодировка базы данных Postgres Pro, которая не поддерживается Oracle (в настоящее время это EUC_CN, EUC_KR, LATIN10, MULE_INTERNAL, WIN874 и SQL_ASCII), символы, отличные от ASCII, не могут быть конвертированы. В этом случае будет выдано предупреждение, и символы будут заменены на подстановочные, как описано выше.

Можно установить параметр nls_lang внешней оболочки данных, чтобы принудительно использовать определённую кодировку Oracle, но итоговые символы в большинстве случаев будут некорректными и приведут к сообщениям об ошибках Postgres Pro. Вероятно, это может быть полезно только для кодирования SQL_ASCII, так что использовать это следует, только если вы точно понимаете, что делаете.

H.3.6.2. Ограниченная функциональность в старых версиях Oracle #

Определение системных каталогов Oracle V$QL и V$QL_PLAN изменилось в Oracle 10.1. Использование EXPLAIN VERBOSE со старыми версиями сервера Oracle приведёт к ошибкам, например:

ERROR:  error describing query: OCIStmtExecute failed to execute
        remote query for sql_id
DETAIL:  ORA-00904: "LAST_ACTIVE_TIME": invalid identifier

Это не планируется исправлять, поскольку расширенная поддержка Oracle 9i отсутствует с 2010 года, и эта функциональность не является существенной.

IMPORT FOREIGN SCHEMA выдаёт следующую ошибку с сервером Oracle 8i:

ERROR:  error importing foreign schema: OCIStmtExecute failed to execute
        column query
DETAIL:  ORA-00904: invalid column name

Это связано с тем, что в представлении ALL_TAB_COLUMNS отсутствует столбец CHAR_LENGTH, который был добавлен в Oracle 9i.

H.3.6.3. Библиотеки LDAP #

Общая библиотека клиента Oracle поставляется с собственной клиентской LDAP-реализацией, соответствующей RFC 1823, поэтому у этих функций те же имена, что и в OpenLDAP. Это приведёт к конфликту имён, если сервер Postgres Pro был сконфигурирован с параметром --with-ldap.

Конфликт имён не будет выявлен, поскольку oracle_fdw загружается во время выполнения. Однако если будет вызвана функция LDAP, возникнут проблемы. Как правило, сначала загружается OpenLDAP, поэтому если Oracle вызывает функцию LDAP (например, если используется разрешение имён по правилам именования каталогов), произойдёт сбой сервера. Это может привести к появлению в журнале сервера Postgres Pro сообщений подобного рода (например, в Linux):

../../../libraries/libldap/getentry.c:29: ldap_first_entry:
 Assertion `( (ld)->ld_options.ldo_valid == 0x2 )' failed.

Так как сборка Postgres Pro выполнена с указанием --with-ldap, она может работать, пока не используются какие-либо функциональные возможности клиента LDAP в Oracle. На некоторых платформах можно принудительно загрузить общую клиентскую библиотеку Oracle до запуска сервера Postgres Pro (LD_PRELOAD в Linux). Затем должны использоваться функции LDAP Oracle. В этом случае Oracle может использовать функциональные возможности LDAP, но использование LDAP из Postgres Pro приведёт к сбою сервера.

Использовать функциональность LDAP одновременно и в Postgres Pro, и в Oracle не допускается.

H.3.6.4. Ошибки сериализации #

В Oracle версии 11.2 или выше при вставке первой строки в созданную таблицу Oracle с oracle_fdw возникнет ошибка сериализации.

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

Это не серьёзная проблема. Её можно избежать, либо проигнорировав эту первую ошибку, либо создав таблицу с параметром SEGMENT CREATION IMMEDIATE.

Гораздо более серьёзная проблема заключается в том, что одновременные вставки могут иногда вызывать ошибки сериализации, когда разделение страницы индекса происходит одновременно с модифицируемой сериализуемой транзакцией.

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

H.3.6.5. Ошибки Oracle #

Это список ошибок Oracle, которые влияют или влияли на oracle_fdw в прошлом.

Ошибка 2728408 может вызвать «ORA-8177 cannot serialize access for this transaction» (невозможно сериализовать доступ для этой транзакции), даже если не предпринимается никаких попыток изменения удалённых данных. Она может возникнуть на сервере Oracle 8.1.7.4 (установите промежуточное исправление 2728408) или сервере Oracle 9.2 (установите набор исправлений 9.2.0.4 или выше).

Известно, что клиент Oracle 21c не поддерживает работу со столбцами CLOB (они отображаются пустыми). Нет прямых доказательств, что это ошибка Oracle, но другие версии работают нормально.

H.3.7. Авторы #

Лауренц Альбе, с заметным вкладом Винсента Мора из Oslandia и Тацуро Ямады из NTT OSS Center.

Пред.	Наверх	След.
H.2. hypopg — поддержка гипотетических индексов	Начало	H.4. pg_failover_slots — автоматическое создание и синхронизация слотов логической репликации на физических репликах

H.3. oracle_fdw — access to Oracle databases
Prev	Up	Appendix H. Third-Party Modules and Extensions Shipped as Individual Packages	Home	Next

H.3. oracle_fdw — access to Oracle databases #

H.3.1. Installation
H.3.2. Internals
H.3.3. Short Simple Example
H.3.4. Usage
H.3.5. Reference
H.3.6. Problems
H.3.7. Authors

The oracle_fdw module is a Postgres Pro extension that provides a Foreign Data Wrapper for easy and efficient access to Oracle databases, including pushdown of WHERE conditions and required columns as well as comprehensive EXPLAIN support.

H.3.1. Installation #

The oracle_fdw extension is provided with Postgres Pro Enterprise as a separate pre-built package oracle-fdw-ent-16 (for the detailed installation instructions, see Chapter 17). oracle_fdw requires Oracle Instant Client version 19.5.

For RPM-based systems (RHEL, SUSE, Red OS, ROSA, ALT Linux), download the oracle-instantclient RPM and install it using rpm. If you use a Debian-based system (Ubuntu, Astra Linux), you have to either convert the RPM package to a Debian package using alien, and then install it using dpkg, or download the ZIP archive of the client and extract the contents of the archive into the /opt/oracle directory.

Once you have Postgres Pro Enterprise installed, create the oracle_fdw extension:

CREATE EXTENSION oracle_fdw;

That will define the required functions and create a foreign data wrapper.

Note that the extension version as shown by the psql \dx command or the system catalog pg_available_extensions is not the installed version of oracle_fdw. To get the oracle_fdw version, use the oracle_diag function.

H.3.2. Internals #

oracle_fdw sets the MODULE of the Oracle session to postgres and the ACTION to the backend process number. This can help identifying the Oracle session and allows you to trace it with DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE.

oracle_fdw uses Oracle array interface to avoid unnecessary client-server round-trips. The batch size can be configured with the prefetch table option and is set to 50 by default.

Rather than using a PLAN_TABLE to explain an Oracle query (which would require such a table to be created in the Oracle database), oracle_fdw uses execution plans stored in the library cache. For that, an Oracle query is explicitly described, which forces Oracle to parse the query. The hard part is to find the SQL_ID and CHILD_NUMBER of the statement in V$SQL because the SQL_TEXT column contains only the first 1000 bytes of the query. Therefore, oracle_fdw adds a comment to the query that contains a hash of the query text. This is used to search in V$SQL. The actual execution plan or cost information is retrieved from V$SQL_PLAN.

oracle_fdw uses transaction isolation level SERIALIZABLE on the Oracle side, which corresponds to Postgres Pro's REPEATABLE READ. This is necessary because a single Postgres Pro statement can lead to multiple Oracle queries (e.g. during a nested loop join) and the results need to be consistent. Unfortunately, the Oracle implementation of SERIALIZABLE has certain quirks; see the Problems section for more.

The Oracle transaction is committed immediately before the local transaction commits, so that a completed Postgres Pro transaction guarantees that the Oracle transaction has completed. However, there is a small chance that the Postgres Pro transaction cannot complete even though the Oracle transaction is committed. This cannot be avoided without using two-phase transactions and a transaction manager, which is beyond what a foreign data wrapper can reasonably provide. Prepared statements involving Oracle are not supported for the same reason.

H.3.3. Short Simple Example #

This is an example how to use oracle_fdw. More detailed information is provided in the sections Options and Usage. You should also read the documentation on foreign data and the commands referenced there.

For the sake of this example, let's assume you can connect to Oracle as the operating system user postgres (or whoever starts the Postgres Pro server) with the following command:

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

That means that the Oracle client and the environment are set up correctly. It is also assumed that oracle_fdw has been installed (see the Installation section).

We want to access a table defined like this:

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

Then configure oracle_fdw as a Postgres Pro superuser like this:

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

You can use other naming methods or local connections, see the description of dbserver below.

It is a good idea to use a superuser only where really necessary, so let's allow a normal user to use the foreign server (this is not required for the example to work but recommended):

pgdb=# GRANT USAGE ON FOREIGN SERVER oradb TO pguser;

Then you can connect to Postgres Pro as pguser and define the following:

pgdb=> CREATE USER MAPPING FOR pguser SERVER oradb
          OPTIONS (user 'orauser', password 'orapwd');

You can use external authentication to avoid storing Oracle passwords.

pgdb=> 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');

Remember that the table and schema name (the latter is optional) must normally be in uppercase.

Now you can use the table like a regular Postgres Pro table.

H.3.4. Usage #

H.3.4.1. Oracle Permissions #

The Oracle user will need the CREATE SESSION privilege and the right to select from the table or view in question. Note that oracle_fdw accesses the Oracle table at query planning time to get its definition. This happens before permissions on the foreign table are checked. Consequently, you may receive an Oracle error if you try to access a foreign table on which you have no permissions in Postgres Pro. This is expected and no security problem.

For EXPLAIN VERBOSE, the user will also need SELECT privileges on V$SQL and V$SQL_PLAN.

H.3.4.2. Connections #

oracle_fdw caches Oracle connections because it is expensive to create an Oracle session for each individual query. All connections are automatically closed when the Postgres Pro session ends.

The close_connections function can be used to close all cached Oracle connections. This can be useful for long-running sessions that do not access foreign tables all the time and want to avoid blocking the resources needed by an open Oracle connection. You cannot call this function inside a transaction that modifies Oracle data.

H.3.4.3. Columns #

When you define a foreign table, the columns of the Oracle table are mapped to the Postgres Pro columns in the order of their definition.

oracle_fdw will only include those columns in the Oracle query that are actually needed by the Postgres Pro query.

The Postgres Pro table can have more or less columns than the Oracle table. If it has more columns, and these columns are used, you will receive a warning and NULL values will be returned.

If you want to UPDATE or DELETE, make sure that the key option is set on all columns that belong to the table's primary key. Failure to do so will result in errors.

H.3.4.4. Data Types #

You must define the Postgres Pro columns with data types that oracle_fdw can translate (see the conversion table below). This restriction is only enforced if the column actually gets used, so you can define “dummy” columns for untranslatable data types as long as you don't access them (this trick only works with SELECT, not when modifying foreign data). If an Oracle value exceeds the size of the Postgres Pro column (e.g., the length of a varchar column or the maximal integer value), you will receive a runtime error.

These conversions are automatically handled by 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           | 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
XMLTYPE                  | xml, char, varchar, text
MDSYS.SDO_GEOMETRY       | geometry (see "PostGIS support" below)

If a NUMBER is converted to a boolean, 0 means false, everything else true.

Inserting or updating XMLTYPE only works with values that do not exceed the maximum length of the VARCHAR2 data type (4000 or 32767, depending on the MAX_STRING_SIZE parameter).

NCLOB is currently not supported because Oracle cannot automatically convert it to the client encoding.

If you want to convert TIMESTAMP WITH LOCAL TIME ZONE to timestamp, consider setting the set_timezone option on the foreign server.

If you need conversions exceeding the above, define an appropriate view in Oracle or Postgres Pro.

H.3.4.5. `WHERE` Conditions and `ORDER BY` Clauses #

Postgres Pro will use all applicable parts of the WHERE clause as a filter for the scan. The Oracle query that oracle_fdw constructs will contain a WHERE clause corresponding to these filter criteria whenever such a condition can safely be translated to Oracle SQL. This feature, also known as push-down of WHERE clauses, can greatly reduce the number of rows retrieved from Oracle and may enable Oracle's optimizer to choose a good plan for accessing the required tables.

Similarly, ORDER BY clauses will be pushed down to Oracle wherever possible. Note that no ORDER BY condition that sorts by a character string will be pushed down as the sort orders in Postgres Pro and Oracle cannot be guaranteed to be the same.

To make use of that, try to use simple conditions for the foreign table. Choose Postgres Pro column data types that correspond to Oracle's types because otherwise conditions cannot be translated.

The expressions now(), transaction_timestamp(), current_timestamp, current_date, and localtimestamp will be translated correctly.

The output of EXPLAIN will show the Oracle query used so you can see which conditions were translated to Oracle and how.

H.3.4.6. Joins Between Foreign Tables #

oracle_fdw can push down joins to the Oracle server, that is, a join between two foreign tables will lead to a single Oracle query that performs the join on the Oracle side.

There are some restrictions when this can happen:

Both tables must be defined on the same foreign server.
Joins between three or more tables won't be pushed down.
The join must be in a SELECT statement.
oracle_fdw must be able to push down all join conditions and WHERE clauses.
Cross joins without join conditions are not pushed down.
If a join is pushed down, ORDER BY clauses will not be pushed down.

It is important that table statistics for both foreign tables have been collected with ANALYZE for Postgres Pro to determine the best join strategy.

H.3.4.7. Modifying Foreign Data #

oracle_fdw supports INSERT, UPDATE, and DELETE on foreign tables. This is allowed by default (also in databases upgraded from an earlier Postgres Pro release) and can be disabled by setting the readonly table option.

For UPDATE and DELETE to work, the columns corresponding to the primary key columns of the Oracle table must have the key column option set. These columns are used to identify a foreign table row, so make sure that the option is set on all columns that belong to the primary key.

If you omit a foreign table column during INSERT, that column is set to the value defined in the DEFAULT clause on the Postgres Pro foreign table (or NULL if there is no DEFAULT clause). DEFAULT clauses on the corresponding Oracle columns are not used. If the Postgres Pro foreign table does not include all columns of the Oracle table, the Oracle DEFAULT clauses will be used for the columns not included in the foreign table definition.

The RETURNING clause on INSERT, UPDATE and DELETE is supported except for columns with Oracle data types LONG and LONG RAW (Oracle doesn't support these data types in the RETURNING clause).

Triggers on foreign tables are supported. Triggers defined with AFTER and FOR EACH ROW require that the foreign table has no columns with Oracle data type LONG or LONG RAW. This is because such triggers make use of the RETURNING clause mentioned above.

While modifying foreign data works, the performance is not particularly good, specifically when many rows are affected, because (owing to the way foreign data wrappers work) each row has to be treated individually.

Transactions are forwarded to Oracle so BEGIN, COMMIT, ROLLBACK, and SAVEPOINT work as expected. Prepared statements involving Oracle are not supported. See the Internals section for details.

Since oracle_fdw uses serialized transactions by default, it is possible that data modifying statements lead to a serialization failure:

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

This can happen if concurrent transactions modify the table and gets more likely in long running transactions. Such errors can be identified by their SQLSTATE (40001). An application using oracle_fdw should retry transactions that fail with this error.

It is possible to use a different transaction isolation level, see Foreign Server Options for a discussion.

H.3.4.8. EXPLAIN #

Postgres Pro's EXPLAIN will show the query that is actually issued to Oracle. EXPLAIN VERBOSE will show Oracle's execution plan (that will not work with Oracle server 9i or older, see Problems).

H.3.4.9. ANALYZE #

You can use ANALYZE to gather statistics on a foreign table. This is supported by oracle_fdw.

Without statistics, Postgres Pro has no way to estimate the row count for queries on a foreign table, which can cause bad execution plans to be chosen.

Postgres Pro will not automatically gather statistics for foreign tables with the autovacuum daemon like it does for normal tables, so it is particularly important to run ANALYZE on foreign tables after creation and whenever the remote table has changed significantly.

Keep in mind that analyzing an Oracle foreign table will result in a full sequential table scan. You can use the table option sample_percent to speed this up by using only a sample of the Oracle table.

H.3.4.10. PostGIS Support #

The data type geometry is only available when PostGIS is installed.

The only supported geometry types are POINT, LINE, POLYGON, MULTIPOINT, MULTILINE, and MULTIPOLYGON in two and three dimensions. Empty PostGIS geometries are not supported because they have no equivalent in Oracle Spatial.

NULL values for Oracle SRID will be converted to 0 and vice versa. For other conversions between Oracle SRID and PostGIS SRID, create a file srid.map in the Postgres Pro share directory. Each line of this file shall contain an Oracle SRID and the corresponding PostGIS SRID, separated by a whitespace. Keep the file small for good performance.

H.3.4.11. Support for `IMPORT FOREIGN SCHEMA` #

IMPORT FOREIGN SCHEMA is supported to bulk import table definitions for all tables in an Oracle schema. In addition to the documentation of IMPORT FOREIGN SCHEMA, consider the following:

IMPORT FOREIGN SCHEMA will create foreign tables for all objects found in ALL_TAB_COLUMNS. That includes tables, views and materialized views, but not synonyms.
These are the supported options for IMPORT FOREIGN SCHEMA:
- case controls case folding for table and column names during import.
  The possible values are:
  - keep: leave the names as they are in Oracle, usually in upper case.
  - lower: translate all table and column names to lower case.
  - smart: only translate names that are all upper case in Oracle (this is the default).
- collation is the collation used for case folding for the lower and smart options of case.
  The default value is default, which is the database's default collation. Only collations in the pg_catalog schema are supported. See the collname values in the pg_collation catalog for a list of possible values.
- dblink is the Oracle database link through which the schema is accessed.
  This name must be written exactly as it occurs in Oracle's system catalog so normally consist of uppercase letters only.
- readonly sets the readonly option on all imported tables.
- skip_tables (default false): don't import tables.
- skip_views (default false): don't import views.
- skip_matviews (default false): don't import materialized views.
- max_long sets the max_long option on all imported tables.
- sample_percent sets the sample_percent option on all imported tables.
- prefetch sets the prefetch option on all imported tables.
- lob_prefetch sets the lob_prefetch option on all imported tables.
- nchar sets the nchar option on all imported tables.
- set_timezone sets the set_timezone option on all imported tables.
The Oracle schema name must be written exactly as it is in Oracle, so normally in upper case. Since Postgres Pro translates names to lower case before processing, you must protect the schema name with double quotes (for example, "SCOTT").
Table names in the LIMIT TO or EXCEPT clause must be written as they will appear in Postgres Pro after the case folding described above.

Note that IMPORT FOREIGN SCHEMA does not work with Oracle server 8i; see the Problems section for details.

H.3.5. Reference #

H.3.5.1. Objects Created by the Extension #

oracle_fdw_handler() RETURNS fdw_handler oracle_fdw_validator(text[], oid) RETURNS void #

These functions are the handler and validator functions necessary to create a foreign data wrapper.

FOREIGN DATA WRAPPER oracle_fdw
  HANDLER oracle_fdw_handler
  VALIDATOR oracle_fdw_validator

The extension automatically creates a foreign data wrapper named oracle_fdw. Normally, that's all you need, and you can proceed to define foreign servers. You can create additional Oracle foreign data wrappers, for example, if you need to set the nls_lang option (you can alter the existing oracle_fdw wrapper, but all modifications will be lost after a dump/restore).

oracle_close_connections() RETURNS void #

This function can be used to close all open Oracle connections in this session. See Usage for further description.

oracle_diag(name DEFAULT NULL) RETURNS text #

This function is useful for diagnostic purposes only. It will return the versions of oracle_fdw, Postgres Pro server, and Oracle client. If called with no argument or NULL, it will additionally return the values of some environment variables used for establishing Oracle connections. If called with the name of a foreign server, it will additionally return the Oracle server version.

oracle_execute(server name, stmt text) RETURNS void #

This function can be used to execute arbitrary SQL statements on the remote Oracle server. That will only work with statements that do not return results (typically DDL statements).

Be careful when using this function since it might disturb the transaction management of oracle_fdw. Remember that running a DDL statement in Oracle will issue an implicit COMMIT. You are best advised to use this function outside of multi-statement transactions.

H.3.5.2. Options #

H.3.5.2.1. Foreign Data Wrapper Options #

Important

If you modify the default foreign data wrapper oracle_fdw, any changes will be lost upon dump/restore. Create a new foreign data wrapper if you want the options to be persistent. The SQL script shipped with the software contains a CREATE FOREIGN DATA WRAPPER statement you can use.

[nls_lang] #: Sets the NLS_LANG environment variable for Oracle to this value. NLS_LANG is in the form language_territory.charset (for example, AMERICAN_AMERICA.AL32UTF8). This must match your database encoding. When this value is not set, oracle_fdw will automatically do the right thing if it can and issue a warning if it cannot. Set this only if you know what you are doing. See the Problems section.

H.3.5.2.2. Foreign Server Options #

dbserver #

The Oracle database connection string for the remote database. This can be in any of the forms that Oracle supports as long as your Oracle client is configured accordingly. Set this to an empty string for local (BEQUEATH) connections.

[isolation_level] #

The transaction isolation level to use at the Oracle database. The value can be serializable, read_committed, or read_only. The default is serializable.

Note that the Oracle table can be queried more than once during a single Postgres Pro statement (for example, during a nested loop join). To make sure that no inconsistencies caused by race conditions with concurrent transactions can occur, the transaction isolation level must guarantee read stability. This is only guaranteed with Oracle's SERIALIZABLE or READ ONLY isolation levels.

Unfortunately Oracle's implementation of SERIALIZABLE is rather bad and causes serialization errors (ORA-08177) in unexpected situations, like inserts into the table. Using READ COMMITTED transactions works around this problem, but there is a risk of inconsistencies. If you want to use it, check your execution plans if the foreign scan could be executed more than once.

[nchar] #

Setting this option to on chooses a more expensive character conversion on the Oracle side. This is required if Oracle tables have NCHAR or NVARCHAR2 columns that contain characters that cannot be represented in the Oracle database character set. The default is off.

Setting nchar to on has a noticable performance impact, and it causes ORA-01461 errors with UPDATE statements that set strings over 2000 bytes (or 16383 if you have MAX_STRING_SIZE = EXTENDED). This error seems to be an Oracle bug.

[set_timezone] #

Setting this option to on sets the Oracle session time zone to the current value of the Postgres Pro parameter timezone when the connection to Oracle is made. This is only useful if you plan to use Oracle columns of type TIMESTAMP WITH LOCAL TIME ZONE and want to translate them to timestamp without time zone in Postgres Pro. The default is off.

Note that if you change timezone after the Oracle connection has been established, oracle_fdw will not change the Oracle session time zone. You can call oracle_close_connections() RETURNS void in that case so that a new connection is opened the next time you access a foreign table.

If Oracle does not recognize the time zone, connections will fail with an error like “ORA-01882: timezone region not found”.

In that case, either use a different timezone or leave the option set to off and set the environment variable ORA_SDTZ to an appropriate value in the environment of the Postgres Pro server.

H.3.5.2.3. User Mapping Options #

user #: The Oracle user name for the session. Set this to an empty string for external authentication if you don't want to store Oracle credentials in the Postgres Pro database (one simple way is to use an external password store).
password #: The password for the Oracle user.

H.3.5.2.4. Foreign Table Options #

table #

The Oracle table name. This name must be written exactly as it occurs in Oracle's system catalog so normally consist of uppercase letters only.

To define a foreign table based on an arbitrary Oracle query, set this option to the query enclosed in parentheses:

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

Do not set the schema option in this case. INSERT, UPDATE, and DELETE will work on foreign tables defined on simple queries; if you want to avoid that (or confusing Oracle error messages for more complicated queries), use the table option readonly.

dblink #

The Oracle database link through which the table is accessed. This name must be written exactly as it occurs in Oracle's system catalog so normally consist of uppercase letters only.

[schema] #

The table's schema (or owner). Useful to access tables that do not belong to the connecting Oracle user. This name must be written exactly as it occurs in Oracle's system catalog so normally consist of uppercase letters only.

[max_long] #

The maximal length of any LONG, LONG RAW, and XMLTYPE columns in the Oracle table. Possible values are integers between 1 and 1073741823 (the maximal size of a bytea in Postgres Pro). This amount of memory will be allocated at least twice so large values will consume a lot of memory. If max_long is less than the length of the longest value retrieved, you will receive the error message “ORA-01406: fetched column value was truncated”. The default is 32767.

[readonly] #

INSERT, UPDATE, and DELETE are only allowed on tables where this option is not set to yes/on/true. The default is false.

[sample_percent] #

This option only influences ANALYZE processing and can be useful to ANALYZE very large tables in a reasonable time.

The value must be between 0.000001 and 100 and defines the percentage of Oracle table blocks that will be randomly selected to calculate Postgres Pro table statistics. This is accomplished using the SAMPLE BLOCK (x) clause in Oracle. The default is 100.

ANALYZE will fail with ORA-00933 for tables defined with Oracle queries and may fail with ORA-01446 for tables defined with complex Oracle views.

[prefetch] #

Sets the number of rows that will be fetched with a single round-trip between Postgres Pro and Oracle during a foreign table scan. The value must be between 1 and 1000, where a value of zero disables prefetching. The default is 50.

Higher values can speed up performance but will use more memory on the Postgres Pro server.

Note that there is no prefetching if the Oracle table contains columns of the type MDSYS.SDO_GEOMETRY.

[lob_prefetch] #

Sets the number of bytes that are prefetched for BLOB, CLOB, and BFILE values. LOBs that exceed that size will require additional round trips between Postgres Pro and Oracle so setting this value bigger than the size of your typical LOB will be good for performance. Choosing bigger values for this option can allocate more memory on the server side but will boost performance for large LOBs. The default is 1048576.

H.3.5.2.5. Column Options #

[key] #: If set to yes/on/true, the corresponding column on the foreign Oracle table is considered a primary key column. For UPDATE and DELETE to work, you must set this option on all columns that belong to the table's primary key. The default is false.
[strip_zeros] #: If set to yes/on/true, ASCII 0 characters will be removed from the string during transfer. Such characters are valid in Oracle but not in Postgres Pro so they will cause an error when read by oracle_fdw. This option only makes sense for character, character varying, and text columns. The default is false.

H.3.6. Problems #

H.3.6.1. Encoding #

Characters stored in an Oracle database that cannot be converted to the Postgres Pro database encoding will silently be replaced by replacement characters, typically a normal or inverted question mark, by Oracle. You will get no warning or error messages.

If you use a Postgres Pro database encoding that Oracle does not know (currently these are EUC_CN, EUC_KR, LATIN10, MULE_INTERNAL, WIN874, and SQL_ASCII), non-ASCII characters cannot be translated correctly. You will get a warning in this case, and the characters will be replaced by replacement characters as described above.

You can set the nls_lang option of the foreign data wrapper to force a certain Oracle encoding, but the resulting characters will most likely be incorrect and lead to Postgres Pro error messages. This is probably only useful for SQL_ASCII encoding if you know what you are doing.

H.3.6.2. Limited Functionality in Old Oracle Versions #

The definition of the Oracle system catalogs V$SQL and V$SQL_PLAN has changed with Oracle 10.1. Using EXPLAIN VERBOSE with older Oracle server versions will result in errors like:

ERROR:  error describing query: OCIStmtExecute failed to execute
        remote query for sql_id
DETAIL:  ORA-00904: "LAST_ACTIVE_TIME": invalid identifier

There is no plan to fix this, since Oracle 9i has been out of Extended Support since 2010 and the functionality is not essential.

IMPORT FOREIGN SCHEMA throws the following error with Oracle server 8i:

ERROR:  error importing foreign schema: OCIStmtExecute failed to execute
        column query
DETAIL:  ORA-00904: invalid column name

This is because the view ALL_TAB_COLUMNS lacks the column CHAR_LENGTH, which was added in Oracle 9i.

H.3.6.3. LDAP Libraries #

The Oracle client shared library comes with its own LDAP client implementation conforming to RFC 1823 so these functions have the same names as OpenLDAP's. This will lead to a name collision when the Postgres Pro server was configured --with-ldap.

The name collision will not be detected because oracle_fdw is loaded at runtime, but trouble will happen if anybody calls an LDAP function. Typically, OpenLDAP is loaded first so if Oracle calls an LDAP function (for example, if you use directory naming name resolution), the backend will crash. This can lead to messages like the following (seen on Linux) in the Postgres Pro server log:

../../../libraries/libldap/getentry.c:29: ldap_first_entry:
 Assertion `( (ld)->ld_options.ldo_valid == 0x2 )' failed.

Since Postgres Pro is built --with-ldap, it may work as long as you don't use any LDAP client functionality in Oracle. On some platforms, you can force Oracle's client shared library to be loaded before the Postgres Pro server is started (LD_PRELOAD on Linux). Then Oracle's LDAP functions should get used. In that case, Oracle may be able to use LDAP functionality, but using LDAP from Postgres Pro will crash the backend.

You cannot use LDAP functionality both in Postgres Pro and in Oracle.

H.3.6.4. Serialization Errors #

In Oracle 11.2 or above, inserting the first row into a newly created Oracle table with oracle_fdw will lead to a serialization error.

This is because of an Oracle feature called deferred segment creation, which defers allocation of storage space for a new table until the first row is inserted. This causes a serialization failure with serializable transactions.

This is no serious problem; you can work around it by either ignoring that first error or creating the table with SEGMENT CREATION IMMEDIATE.

A much nastier problem is that concurrent inserts can sometimes cause serialization errors when an index page is split concurrently with a modifying serializable transaction.

Oracle claims that this is not a bug, and the suggested solution is to retry the transaction that got a serialization error.

H.3.6.5. Oracle Bugs #

This is the list of Oracle bugs that affect or have affected oracle_fdw in the past.

Bug 2728408 can cause “ORA-8177 cannot serialize access for this transaction” even if no modification of remote data is attempted. It can occur with Oracle server 8.1.7.4 (install one-off patch 2728408) or Oracle server 9.2 (install Patch Set 9.2.0.4 or better).

Oracle client 21c is known not to work for CLOB columns (they appear empty). There is no ultimate proof that that is an Oracle bug, but other versions are working fine.

H.3.7. Authors #

Laurenz Albe, with notable contributions from Vincent Mora of Oslandia and Tatsuro Yamada of the NTT OSS Center.

Prev	Up	Next
H.2. hypopg — support for hypothetical indexes	Home	H.4. pg_failover_slots — automatic creation and synchronization of logical replication slots on physical replicas

H.3. oracle_fdw — доступ к базам данных Oracle #

H.3.1. Установка #

H.3.2. Внутреннее устройство #

H.3.3. Простой пример #

H.3.4. Использование #

H.3.4.1. Разрешения Oracle #

H.3.4.2. Соединения #

H.3.4.3. Столбцы #

H.3.4.4. Типы данных #

H.3.4.5. Условия WHERE и предложения ORDER BY #

H.3.4.6. Соединения сторонних таблиц #

H.3.4.7. Изменение сторонних данных #

H.3.4.8. EXPLAIN #

H.3.4.9. ANALYZE #

H.3.4.10. Поддержка PostGIS #

H.3.4.11. Поддержка команды IMPORT FOREIGN SCHEMA #

H.3.5. Справка #

H.3.5.1. Создаваемые расширением объекты #

H.3.5.2. Параметры #

H.3.5.2.1. Параметры обёртки сторонних данных #

Важно

H.3.5.2.2. Параметры стороннего сервера #

H.3.5.2.3. Параметры сопоставления пользователей #

H.3.5.2.4. Параметры сторонней таблицы #

H.3.5.2.5. Параметры столбцов #

H.3.6. Проблемы #

H.3.6.1. Кодировка #

H.3.6.2. Ограниченная функциональность в старых версиях Oracle #

H.3.6.3. Библиотеки LDAP #

H.3.6.4. Ошибки сериализации #

H.3.6.5. Ошибки Oracle #

H.3.7. Авторы #

H.3. oracle_fdw — access to Oracle databases #

H.3.1. Installation #

H.3.2. Internals #

H.3.3. Short Simple Example #

H.3.4. Usage #

H.3.4.1. Oracle Permissions #

H.3.4.2. Connections #

H.3.4.3. Columns #

H.3.4.4. Data Types #

H.3.4.5. WHERE Conditions and ORDER BY Clauses #

H.3.4.6. Joins Between Foreign Tables #

H.3.4.7. Modifying Foreign Data #

H.3.4.8. EXPLAIN #

H.3.4.9. ANALYZE #

H.3.4.10. PostGIS Support #

H.3.4.11. Support for IMPORT FOREIGN SCHEMA #

H.3.5. Reference #

H.3.5.1. Objects Created by the Extension #

H.3.5.2. Options #

H.3.5.2.1. Foreign Data Wrapper Options #

Important

H.3.5.2.2. Foreign Server Options #

H.3.5.2.3. User Mapping Options #

H.3.5.2.4. Foreign Table Options #

H.3.5.2.5. Column Options #

H.3.6. Problems #

H.3.6.1. Encoding #

H.3.6.2. Limited Functionality in Old Oracle Versions #

H.3.6.3. LDAP Libraries #

H.3.6.4. Serialization Errors #

H.3.6.5. Oracle Bugs #

H.3.7. Authors #

H.3.4.5. Условия `WHERE` и предложения `ORDER BY` #

H.3.4.11. Поддержка команды `IMPORT FOREIGN SCHEMA` #

H.3.4.5. `WHERE` Conditions and `ORDER BY` Clauses #

H.3.4.11. Support for `IMPORT FOREIGN SCHEMA` #