Использование oracle_fdw

Евгения Кузина

Обзор

Расширение oracle_fdw представляет собой обертку сторонних данных для доступа к базам данных Oracle. С его помощью можно напрямую обращаться к таблицам и представлениям в базе данных Oracle из интерфейса PostgreSQL, используя стандартные SQL-запросы. Можно выделить следующие основные функции расширения:

  • Чтение и запись — можно не только читать данные (команда SELECT), но и изменять их с помощью команд INSERT, UPDATE и DELETE на стороне Oracle.

  • Кеширование соединений — расширение удерживает открытые сессии с Oracle во время длительных транзакций, чтобы повторно не тратить время на авторизацию. Все соединения закрываются по завершении сессии ADP/PostgreSQL.

  • Преобразование типов — расширение автоматически сопоставляет типы данных Oracle с аналогичными типами данных PostgreSQL.

Наиболее частые сценарии использования:

  • Миграция — при переносе ИТ-систем с Oracle на ADP/PostgreSQL расширение позволяет настроить двусторонний обмен данными, чтобы приложения могли работать одновременно с обеими базами во время переходного периода.

  • Интеграция систем — если в компании часть данных хранится в Oracle, а новые сервисы пишутся под ADP/PostgreSQL, oracle_fdw позволяет строить отчеты и объединять таблицы из двух СУБД.

Установка

Пакет, требуемый для установки oracle_fdw, поставляется с ADP. Чтобы использовать oracle_fdw, выполните команду CREATE EXTENSION в той базе данных, в которую необходимо поставить расширение:

CREATE EXTENSION oracle_fdw;
ПРИМЕЧАНИЕ
Если расширение oracle_fdw создано в базе данных template1, используемой в качестве шаблона БД по умолчанию, во всех вновь создаваемых базах данных будет установлено это расширение.

ADP использует версию 2.8.0 расширения oracle_fdw. Чтобы это проверить, вызовите функцию oracle_diag:

SELECT oracle_diag();
                      oracle_diag
---------------------------------------------------------------
oracle_fdw 2.8.0, PostgreSQL 16.13, Oracle client 23.26.2.0.0

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

Изменение параметров обертки

Обертка сторонних данных oracle_fdw имеет необязательный параметр nls_lang, который позволяет установить переменную окружения NLS_LANG для Oracle. Если вам необходима эта функциональность, можно воспользоваться командой ALTER FOREIGN DATA WRAPPER:

ALTER FOREIGN DATA WRAPPER oracle_fdw
    OPTIONS (ADD nls_lang 'AMERICAN_AMERICA.AL32UTF8');

Обратите внимание, что при таком подходе все изменения будут потеряны при создании дампа и восстановлении. Чтобы этого избежать, можно определить новую обертку сторонних данных. Расширение oracle_fdw включает в себя функции обработчика (handler) и валидатора (validator), необходимые для создания обертки сторонних данных. Можно создать новую обертку сторонних данных следующим образом:

CREATE FOREIGN DATA WRAPPER custom_oracle_fdw
    HANDLER oracle_fdw_handler
    VALIDATOR oracle_fdw_validator
    OPTIONS (nls_lang 'AMERICAN_AMERICA.AL32UTF8');

Создание стороннего сервера

Использование oracle_fdw предполагает операции со сторонними таблицами. Создайте сторонний сервер (foreign server), используя для этого команду CREATE SERVER. Замените <dbserver.address.com> адресом сервера Oracle и укажите системный идентификатор Oracle вместо <SID>:

CREATE SERVER ora_server FOREIGN DATA WRAPPER oracle_fdw
    OPTIONS (dbserver '//<dbserver.address.com>:1521/<SID>');

В команде CREATE SERVER также можно указать дополнительные параметры в поле OPTIONS, описанные ниже.

Параметры стороннего сервера
Название Описание Значение по умолчанию

dbserver

Строка подключения Oracle к удаленной базе данных. Обязательный параметр

 — 

isolation_level

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

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

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

serializable

nchar

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

off

set_timezone

Если значение on, часовой пояс сессии Oracle устанавливается в текущее значение параметра ADP timezone в момент установления подключения к Oracle. Это имеет смысл только в том случае, если вы планируете использовать в Oracle столбцы типа TIMESTAMP WITH LOCAL TIME ZONE и хотите преобразовывать их в timestamp без указания часового пояса в ADP.

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

Если Oracle не распознает указанный часовой пояс, подключения будут завершаться ошибкой ORA-01882: timezone region not found. В этом случае используйте другой часовой пояс или укажите значение off и установите переменную окружения ORA_SDTZ равной подходящему значению на сервере ADP

off

Создание сопоставления пользователей

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

CREATE USER pguser;

GRANT USAGE ON FOREIGN SERVER ora_server TO pguser;

Для доступа к сторонним данным требуется аутентификация на стороне Oracle. Например, пользователя Oracle можно создать следующим образом:

CREATE USER <ora_user> IDENTIFIED BY <ora_password>;

GRANT CREATE SESSION TO <ora_user>;

где:

  • <ora_user> — имя пользователя;

  • <ora_password> — пароль пользователя.

Подключитесь к ADP как pguser и создайте объект сопоставления пользователей (user mapping), чтобы указать имя пользователя и пароль, которые можно использовать для аутентификации на стороне Oracle для текущей роли ADP/PostgreSQL. Для этого выполните команду CREATE USER MAPPING:

CREATE USER MAPPING FOR pguser SERVER ora_server
    OPTIONS (user '<ora_user>', password '<ora_password>');

Для использования сторонней аутентификации передайте в качестве значения user (<ora_user>) пустую строку. В этом случае пользователь операционной системы postgres должен иметь доступ к серверу Oracle.

Создание сторонней таблицы

Например, имеется следующая таблица на сервере Oracle:

CREATE TABLE ORA_USER.ORA_BOOKS (
    id NUMBER PRIMARY KEY,
    author_id NUMBER,
    title VARCHAR2(255 char),
    genre VARCHAR2(50 char),
    price NUMBER);

Выполните команду CREATE FOREIGN TABLE, чтобы создать стороннюю таблицу. При определении типов полей сторонней таблицы необходимо учитывать возможность преобразования типов (см. Преобразование типов данных):

CREATE FOREIGN TABLE books_oracle (
    id integer OPTIONS (key 'true') NOT NULL,
    author_id integer,
    title VARCHAR(255) NOT NULL,
    genre VARCHAR(50),
    price NUMERIC(10, 2)
) SERVER ora_server OPTIONS (schema 'ORA_USER', table 'ORA_BOOKS');

где:

  • ORA_USER — название схемы (обычно совпадает с именем пользователя Oracle);

  • ORA_BOOKS — название таблицы Oracle.

Имя таблицы и схемы Oracle обычно указываются в верхнем регистре.

Теперь вы можете использовать таблицу books_oracle как обычную таблицу ADP/PostgreSQL.

Параметры сторонней таблицы
Название Описание Значение по умолчанию

table

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

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

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

В этом случае не указывайте параметр schema.

Команды INSERT, UPDATE и DELETE работают со сторонними таблицами, определенными простыми запросами. Если вы хотите этого избежать, используйте для сторонней таблицы параметр readonly

required

dblink

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

optional

schema

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

optional

max_long

Максимальная длина столбцов типов LONG, LONG RAW и XMLTYPE в таблице Oracle. Допустимые значения — целые числа от 1 до 1073741823 (максимальный размер типа bytea в ADP/PostgreSQL). Этот объем памяти будет выделен как минимум дважды, поэтому большие значения потребляют много памяти.

Если значение max_long меньше длины самого длинного извлеченного значения, возникнет сообщение об ошибке ORA-01406: fetched column value was truncated

32767

readonly

Команды INSERT, UPDATE и DELETE разрешены только для таблиц, в которых этот параметр имеет значение no / off / false. Допустимые значения: yes/no, on/off, true/false

false

sample_percent

Определяет процент блоков таблицы Oracle, которые будут случайным образом выбраны для расчета статистики таблицы PostgreSQL. Значение должно быть в диапазоне от 0.000001 до 100. Параметр влияет только на выполнение операции ANALYZE и может быть полезен для анализа больших таблиц за разумное время

100

prefetch

Задает количество строк, извлекаемых за один сетевой обмен между ADP и Oracle при сканировании сторонней таблицы. Значение должно быть в диапазоне от 1 до 10240.

Большие значения могут повысить производительность, но при этом используют больше памяти на сервере ADP и могут привести к ошибкам из‑за нехватки памяти.

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

50

lob_prefetch

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

1048576

При создании сторонней таблицы столбцы таблицы Oracle сопоставляются со столбцами таблицы ADP/PostgreSQL в том порядке, в котором они указаны в команде FOREIGN TABLE. Таблица ADP/PostgreSQL может содержать больше или меньше столбцов, чем таблица Oracle. Если в ней больше столбцов, и эти столбцы используются, вы получите предупреждение, и oracle_fdw вернет значения NULL для недостающих столбцов.

oracle_fdw включает в запрос Oracle только те столбцы, которые требуются запросу PostgreSQL.

Столбцы сторонней таблицы могут иметь следующие необязательные опции:

  • key — если установлено в yes/on/true, соответствующий столбец в сторонней таблице Oracle считается столбцом первичного ключа. Значение по умолчанию — false.

  • strip_zeros — если установлено в yes/on/true, символы ASCII 0 будут удалены из строки во время передачи. Такие символы допустимы в Oracle, но не в ADP/PostgreSQL. Эта опция имеет смысл только для столбцов типов character, character varying и text. Значение по умолчанию — false.

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

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

ALTER SERVER ora_server OPTIONS (ADD isolation_level 'read_committed');

Добавьте строки в стороннюю таблицу books_oracle:

 INSERT INTO books_oracle (id, author_id, title, genre, price) VALUES
    (1, 1, 'Mrs. Dalloway', 'novel', 360),
    (2, 1, 'To the Lighthouse', 'novel', 440),
    (3, 2, 'To Kill a Mockingbird', 'novel', 750),
    (4, 3, 'The Great Gatsby', 'novel', 900),
    (5, 4, 'The Lord of the Rings', 'fantasy', 1200);

Прочитайте строки из таблицы:

SELECT * FROM books_oracle WHERE genre = 'fantasy';
 id | author_id |         title         |  genre  |  price
----+-----------+-----------------------+---------+---------
  5 |         4 | The Lord of the Rings | fantasy | 1200.00

Преобразование типов данных

Столбцы ADP/PostgreSQL должны быть определены с такими типами данных, которые oracle_fdw может преобразовать. Расширение oracle_fdw автоматически обрабатывает следующие преобразования:

Тип Oracle Тип ADP/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

NCLOB

char, varchar, text, json

LONG

char, varchar, text

RAW

uuid, bytea

BLOB

bytea

BFILE

bytea (только для чтения)

LONG RAW

bytea

NUMBER

numeric, float4, float8, char, varchar, text

NUMBER(n,m) при 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

Если значение Oracle превышает размер столбца ADP/PostgreSQL, вы получите ошибку во время выполнения.

Если NUMBER преобразуется в boolean, значение 0 интерпретируется как false, все остальные значения — как true.

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

Если вы хотите преобразовать TIMESTAMP WITH LOCAL TIME ZONE в timestamp, рассмотрите возможность установки параметра set_timezone для стороннего сервера.

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

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

Выражения WHERE и ORDER BY

ADP/PostgreSQL использует все применимые части выражения WHERE как фильтр для сканирования. oracle_fdw формирует запрос Oracle, содержащий выражение WHERE, соответствующее этим критериям фильтрации. Поскольку выражение WHERE применяется на стороне Oracle, это может значительно сократить количество строк, извлекаемых Oracle. Такая функциональность также известна как "проталкивание" предикатов выражений WHERE (push-down of WHERE clauses).

Выражения ORDER BY по возможности также выполняются на стороне Oracle. Обратите внимание, что условие ORDER BY, сортирующее по символьной строке, не выносится наружу, поскольку невозможно гарантировать, что порядок сортировки в ADP/PostgreSQL будет совпадать с порядком сортировки в Oracle.

Чтобы "проталкивание" предикатов выражений ORDER BY выполнялось успешно, используйте простые условия для сторонней таблицы. Выбирайте типы данных столбцов ADP/PostgreSQL, соответствующие типам Oracle, иначе oracle_fdw не сможет преобразовать условия.

Выражения now(), transaction_timestamp(), current_timestamp, current_date и localtimestamp передаются корректно.

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

EXPLAIN SELECT * FROM books_oracle WHERE genre = 'fantasy';
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------------
Foreign Scan on books_oracle  (cost=10000.00..20000.00 rows=1000 width=658)
 Oracle query: SELECT /*7c63955190fa1d22*/ r1."ID", r1."AUTHOR_ID", r1."TITLE", r1."GENRE", r1."PRICE"
        FROM "ORA_USER"."ORA_BOOKS" r1 WHERE (r1."GENRE" = 'fantasy')

JOIN-операции между сторонними таблицами

Расширение oracle_fdw может выносить JOIN‑операции на сервер Oracle. Таким образом, JOIN между двумя сторонними таблицами реализуется как единственный Oracle-запрос, который выполняет JOIN на стороне Oracle. У данной операции JOIN существуют следующие ограничения:

  • Обе таблицы должны быть определены на одном и том же стороннем сервере.

  • Операция JOIN между тремя и более таблицами не выносится наружу.

  • JOIN должен находиться в операторе SELECT.

  • oracle_fdw должен иметь возможность вынести наружу все условия JOIN и выражения WHERE.

  • CROSS JOIN без условий объединения не выносится наружу.

  • Если JOIN выносится наружу, выражения ORDER BY не выносятся наружу.

Также рекомендуется использовать команду ANALYZE для сбора статистики по обеим сторонним таблицам для определения оптимальной стратегии.

Пример

Создайте еще одну таблицу на сервере Oracle:

CREATE TABLE ORA_USER.ORA_AUTHORS (
    id NUMBER PRIMARY KEY,
    author_name VARCHAR2(100 char)
);

INSERT INTO ORA_USER.ORA_AUTHORS (id, author_name) VALUES
    (1, 'Virginia Woolf'),
    (2, 'Harper Lee'),
    (3, 'F. Scott Fitzgerald'),
    (4, 'J.R.R. Tolkien');

Создайте соответствующую стороннюю таблицу в ADP:

CREATE FOREIGN TABLE authors_oracle (
    id integer OPTIONS (key 'true') NOT NULL,
    name VARCHAR(100)
) SERVER ora_server OPTIONS (schema 'ORA_USER', table 'ORA_AUTHORS');

Выполните команду ANALYZE:

ANALYZE authors_oracle;
ANALYZE books_oracle;

Выполните запрос JOIN:

SELECT authors_oracle.name, books_oracle.title
    FROM books_oracle INNER JOIN authors_oracle ON authors_oracle.id = books_oracle.author_id
    ORDER BY name;
        name         |         title
---------------------+-----------------------
 F. Scott Fitzgerald | The Great Gatsby
 Harper Lee          | To Kill a Mockingbird
 J.R.R. Tolkien      | The Lord of the Rings
 Virginia Woolf      | Mrs. Dalloway
 Virginia Woolf      | To the Lighthouse

Выведите план выполнения запроса:

EXPLAIN SELECT authors_oracle.name, books_oracle.title
    FROM books_oracle INNER JOIN authors_oracle ON authors_oracle.id = books_oracle.author_id
    ORDER BY name;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Sort  (cost=10200.43..10200.48 rows=20 width=531)
   Sort Key: authors_oracle.name
   ->  Foreign Scan  (cost=10000.00..10200.00 rows=20 width=531)
         Oracle query: SELECT /*39731fa1cb7295c3*/ r2."AUTHOR_NAME", r1."TITLE" FROM ("ORA_USER"."ORA_BOOKS" r1
            INNER JOIN "ORA_USER"."ORA_AUTHORS" r2 ON (r1."AUTHOR_ID" = r2."ID"))

В выводе видно, что операция JOIN выполняется на стороне Oracle.

Изменение сторонних данных

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

Если при выполнении INSERT столбец сторонней таблицы не указан, этому столбцу присваивается значение, определенное в выражении DEFAULT сторонней таблицы ADP/PostgreSQL (или NULL, если выражение DEFAULT отсутствует). Выражения DEFAULT в соответствующих столбцах Oracle при этом не используются. Если сторонняя таблица ADP/PostgreSQL не содержит всех столбцов таблицы Oracle, то Oracle-выражения DEFAULT будут применены для тех столбцов, которые не включены в определение сторонней таблицы.

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

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

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

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

Поскольку по умолчанию oracle_fdw использует сериализуемые транзакции, операторы изменения данных могут привести к ошибке сериализации: ORA-08177: can’t serialize access for this transaction. Это может произойти, если параллельные транзакции изменяют одну и ту же таблицу, и особенно вероятно в случае длительных транзакций. Такие ошибки можно распознать по коду SQLSTATE (40001). Приложение, использующее oracle_fdw, должно повторно выполнить транзакции, завершившиеся с этой ошибкой.

Также можно использовать другой уровень изоляции транзакций, см. Создание стороннего сервера.

Сбор статистики по сторонним таблицам

Для сбора статистики по сторонним таблицам можно использовать команду ANALYZE. Без статистики ADP не может оценить количество строк в результате запроса к сторонней таблице, что может привести к выбору неудачных планов выполнения.

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

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

Для того чтобы просмотреть запрос, который отправляется в Oracle, воспользуйтесь командой ADP/PostgreSQL EXPLAIN. Вызов EXPLAIN VERBOSE отображает план выполнения запроса на стороне Oracle. Выполните команду EXPLAIN VERBOSE с запросом JOIN из раздела JOIN-операции между сторонними таблицами:

EXPLAIN VERBOSE SELECT authors_oracle.name, books_oracle.title
    FROM books_oracle INNER JOIN authors_oracle ON authors_oracle.id = books_oracle.author_id
    ORDER BY name;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=10200.43..10200.48 rows=20 width=531)
   Output: authors_oracle.name, books_oracle.title
   Sort Key: authors_oracle.name
   ->  Foreign Scan  (cost=10000.00..10200.00 rows=20 width=531)
         Output: authors_oracle.name, books_oracle.title
         Oracle query: SELECT /*4bab0a18b3951d4f*/ r2."AUTHOR_NAME", r1."GENRE"
            FROM ("ORA_USER"."ORA_BOOKS" r1 INNER JOIN "ORA_USER"."ORA_AUTHORS" r2 ON (r1."TITLE" = r2."ID"))
         Oracle plan: SELECT STATEMENT
         Oracle plan:   HASH JOIN   (condition "R2"."ID"=TO_NUMBER("R1"."TITLE"))
         Oracle plan:     NESTED LOOPS
         Oracle plan:       NESTED LOOPS
         Oracle plan:         STATISTICS COLLECTOR
         Oracle plan:           TABLE ACCESS FULL ORA_BOOKS
         Oracle plan:         INDEX UNIQUE SCAN SYS_C008644 (condition "R2"."ID"=TO_NUMBER("R1"."TITLE"))
         Oracle plan:       TABLE ACCESS BY INDEX ROWID ORA_AUTHORS
         Oracle plan:     TABLE ACCESS FULL ORA_AUTHORS
 Query Identifier: -5125250772757756403

Поддержка IMPORT FOREIGN SCHEMA

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

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

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

  • Имена таблиц в выражениях LIMIT TO или EXCEPT должны указываться так, как они будут выглядеть в ADP/PostgreSQL после применения правил изменения регистра, определенных параметрами IMPORT FOREIGN SCHEMA.

Нижеперечисленные параметры указываются в поле OPTIONS команды IMPORT FOREIGN SCHEMA.

Параметры IMPORT FOREIGN SCHEMA
Название Описание

case

Управляет преобразованием регистра имен таблиц и столбцов при импорте. Возможные значения:

  • keep — сохранять имена такими же, как в Oracle, обычно в верхнем регистре;

  • lower — переводить все имена таблиц и столбцов в нижний регистр;

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

collation

Сортировка (collation), используемая при изменении регистра для значений lower и smart опции case.

Значение по умолчанию — default, что соответствует правилу сортировки по умолчанию для базы данных. Поддерживаются только сортировки из схемы pg_catalog. Для получения списка возможных значений обратитесь к полю collname каталога pg_collation

dblink

Ссылка (database link) 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 для всех импортируемых таблиц

Следующий код создает схему schema_oracle и импортирует в нее схему ORA_USER с сервера Oracle:

CREATE SCHEMA schema_oracle;

IMPORT FOREIGN SCHEMA "ORA_USER" from SERVER ora_server into schema_oracle;

Чтобы проверить результат, отобразите имеющиеся сторонние таблицы:

SELECT foreign_table_schema AS schema_name,
       foreign_table_name AS table_name,
       foreign_server_name AS server_name
    FROM information_schema.foreign_tables
    WHERE foreign_table_schema = 'schema_oracle';
  schema_name  | table_name  | server_name
---------------+-------------+-------------
 schema_oracle | ora_authors | ora_server
 schema_oracle | ora_books   | ora_server

Функции, создаваемые расширением

oracle_fdw_handler и oracle_fdw_validator

Функции oracle_fdw_handler и oracle_fdw_validator необходимы для создания обертки сторонних данных (foreign data wrapper):

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

Пример вы можете найти выше.

oracle_close_connections

Эта функция используется для закрытия всех открытых подключений к Oracle в текущей сессии.

FUNCTION oracle_close_connections() RETURNS void

oracle_fdw кеширует подключения к Oracle, поскольку создание отдельной сессии Oracle для каждого запроса является дорогостоящей операцией. Все подключения закрываются при завершении сессии ADP/PostgreSQL.

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

Пример:

SELECT oracle_close_connections();

Вызывать эту функцию внутри транзакции, которая модифицирует данные в Oracle, невозможно.

oracle_diag

Эта функция используется только в диагностических целях.

FUNCTION oracle_diag(<server_name> DEFAULT NULL) RETURNS text

где <server_name> — имя стороннего сервера.

Функция возвращает версии oracle_fdw, сервера PostgreSQL и клиента Oracle. Если вызвать ее без аргумента или с аргументом NULL, она дополнительно вернет значения переменных окружения, используемых для установления подключений к Oracle:

SELECT oracle_diag(NULL);
                                        oracle_diag
--------------------------------------------------------------------------------------------
 oracle_fdw 2.8.0, PostgreSQL 16.13, Oracle client 23.26.2.0.0, ORACLE_HOME=/usr/lib/oracle

Если вызвать функцию с именем стороннего сервера, она также вернет версию сервера Oracle:

SELECT oracle_diag('ora_server');
                                       oracle_diag
-----------------------------------------------------------------------------------------
 oracle_fdw 2.8.0, PostgreSQL 16.13, Oracle client 23.26.2.0.0, Oracle server 23.0.0.0.0

oracle_execute

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

FUNCTION oracle_execute(<server_name>, <query_text>) RETURNS void

где:

  • <server_name> — имя стороннего сервера;

  • <query_text> — текст запроса.

Например, можно добавить столбец in_stock в таблицу ORA_BOOKS на сервере Oracle:

SELECT oracle_execute('ora_server', 'ALTER TABLE ORA_USER.ORA_BOOKS ADD in_stock NUMBER');

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

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней