oracle_fdw: Oracle 的外部数据包装器

十二月 19, 2023

摘要oracle_fdw是一个 PostgreSQL 插件,它提供了一个外部数据包装器,可轻松高效地访问 Oracle 数据库,包括 WHERE 条件和所需列的下推,以及全面的 EXPLAIN 支持。

本文包含以下部分:

  1. 快速上手
  2. 插件创建的对象
  3. 选项
  4. 用法

快速上手

这是一个如何使用 oracle_fdw 的简单示例。
更详细的信息将在“选项”和“用法”部分提供。您还应该阅读有关外部数据的 PostgreSQL 文档,和其中引用的命令。

在本例中,假设您可以使用以下命令,以操作系统用户postgres(或启动 PostgreSQL 服务器的任何用户)进行连接:

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 配置为 PostgreSQL 超级用户,如下所示:

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;

然后,您可以用pguser连接到 PostgreSQL,并定义:

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

请记住,表和模式名称(模式名是可选的)通常必须为大写。

现在,您可以像使用常规的 PostgreSQL 表一样使用该外部表了。

插件创建的对象

FUNCTION oracle_fdw_handler() RETURNS fdw_handler
FUNCTION 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包装器,但在转储/还原后所有修改都会丢失)。

FUNCTION oracle_close_connections() RETURNS void

此函数可用于关闭此会话中所有打开的 Oracle 连接。有关进一步说明,请参阅“用法”部分。

FUNCTION oracle_diag(name DEFAULT NULL) RETURNS text

此函数仅用于诊断目的。它将返回 oracle_fdw、PostgreSQL 服务器和 Oracle 客户端的版本信息。 如果在不带参数或传入 NULL 的情况下调用,它将额外返回一些用于建立 Oracle 连接的环境变量的值。如果使用一个外部服务器的名称来调用,它将额外返回 Oracle 服务器版本信息。

FUNCTION oracle_execute(server name, stmt text) RETURNS void

此函数可用于在远程 Oracle 服务器上执行任意 SQL 语句。这仅适用于不返回结果的语句(通常是 DDL 语句)。

使用该函数时要小心,因为它可能会干扰oracle_fdw的事务管理。请记住,在 Oracle 中运行 DDL 语句将发出隐式 COMMIT。建议您在多语句事务以外使用此函数。

选项

外部数据包装器选项

注意:如果修改默认的外部数据包装器oracle_fdw,则任何更改都将在转储/还原时丢失。如果希望选项持久化,请创建新的外部数据包装器。插件附带的 SQL 脚本中包含您可以使用的 CREATE FOREIGN DATA WRAPPER 语句。

  • nls_lang(可选)

    将 Oracle 的 NLS_LANG 环境变量设置为此值。NLS_LANG 的格式为 “language_territory.charset”(例如 AMERICAN_AMERICA.AL32UTF8)。这必须与您的数据库编码匹配。当未设置此值时,如果可能的话,oracle_fdw会自动选择正确的值,否则会发出警告。仅当您知道自己在做什么时,才应该设置该值。

外部服务器选项

  • dbserver(必需)

    远程数据库的 Oracle 数据库连接字符串。这可以是 Oracle 支持的任何形式,只要您的 Oracle 客户端已相应地配置。对于本地(“BEQUEATH”)连接,请将其设置为空字符串。

  • isolation_level(可选,默认为serializable

    要在 Oracle 数据库中使用的事务隔离级别。该值可以是serializableread_committedread_only

    请注意,在单个 PostgreSQL 的语句执行期间(例如,在嵌套循环连接期间),可以多次查询 Oracle 表。如要确保不会因并发事务的竞争条件而导致不一致现象的发生,事务隔离级别必须保证读取稳定性。这只能通过 Oracle 的 SERIALIZABLE 或 READ ONLY 隔离级别来保证。

    不幸的是,Oracle 的 SERIALIZABLE 实现相当糟糕,并且在某些意外情况下会导致序列化错误(ORA-08177),例如插入数据到表中的时候。使用 READ COMMITTED 事务可以解决此问题,但存在不一致的风险。如果您想使用它,请检查您的执行计划,其中的外部扫描是否可能会多次执行。

  • nchar(布尔型,可选,默认为off

    设置此选项为on,会在 Oracle 端选择一种更昂贵的字符转换方式。如果您使用的是单字节 Oracle 数据库字符集,则这是必需的。但 NCHAR 或 NVARCHAR2 列包含无法在数据库字符集中表示的字符。

    设置ncharon会带来明显的性能影响,在 UPDATE 语句将字符串设置为超过 2000 字节(如果您设置了 MAX_STRING_SIZE 参数为 EXTENDED,则为 16383)长度时,它会导致 ORA-01461 错误。该错误看起来是一个 Oracle 的 bug。

  • set_timezone(布尔型,可选,默认为off

    设置此选项为on,可在与 Oracle 建立连接时,将 Oracle 会话时区设置为 PostgreSQL 参数timezone的当前值。仅当您计划使用 TIMESTAMP WITH LOCAL TIME ZONE 类型的 Oracle 列,并希望将它们转换为 PostgreSQL 中的timestamp without time zone时,该设置才是有用的。

    请注意,如果在建立 Oracle 连接后更改timezone,oracle_fdw 不会更改 Oracle 会话时区。在这种情况下,您可以调用oracle_close_connections(),以便在下次访问外表时重新建立新连接。

    如果 Oracle 无法识别时区,连接会失败,并显示如下错误

    ORA-01882: timezone region not found
    

    在这种情况下,请使用其他的timezone值,或让选项设置为off,并将环境变量 ORA_SDTZ 设置为 PostgreSQL 服务器环境中的一个适当值。

用户映射选项

  • user(必需)

    会话的 Oracle 用户名。如果不想要将 Oracle 访问凭据存储在 PostgreSQL 数据库中,请将其设置为空字符串,以进行外部身份验证。一种简单的方法是使用外部密码存储

  • password(必需)

    Oracle 用户的密码。

外部表选项

  • 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(可选,默认为“32767”)

    Oracle 表中任何 LONG、LONG RAW 和 XMLTYPE 列的最大长度。可能的值是介于 1 和 1073741823(PostgreSQL 中bytea值的最大长度)之间的整数。该大小的内存至少会分配两次,因此较大的值会消耗大量内存。如果 max_long 小于检索的最长值的长度, 您会接收到错误消息ORA-01406: fetched column value was truncated

  • readonly(可选,默认为“false”)

    只有在此选项未设置为 yes/on/true 的表上,才允许执行 INSERT、UPDATE 和 DELETE。

  • sample_percent(可选,默认为“100”)

    此选项仅影响 ANALYZE 的处理,可用于在合理的时间内对非常大的表进行 ANALYZE。

    该值必须介于 0.000001 和 100 之间,用于定义将随机选择的 Oracle 表块的百分比,以计算 PostgreSQL 表统计信息。这是使用 Oracle 中的SAMPLE BLOCK (x)子句完成的。

    对于使用 Oracle 查询定义的表,ANALYZE 会失败报错 ORA-00933,对于使用复杂 Oracle 视图定义的表,ANALYZE 可能会失败报错 ORA-01446。

  • prefetch(可选,默认为“50”)

    该选项用于设置,在外部表扫描期间,在 PostgreSQL 和 Oracle 之间进行单次往返获取的行数。该值必须介于 1 和 1000 之间。

    较高的值可以提高性能,但会在 PostgreSQL 服务器上占用更多内存。

    请注意,如果 Oracle 表中包含了MDSYS.SDO_GEOMETRY类型的列,则不会进行预取。

  • lob_prefetch(可选,默认为“1048576”)

    设置在读取 BLOB、CLOB 和 BFILE 值时,预取的字节数。超过该大小的 LOB,需要在 PostgreSQL 和 Oracle 之间进行额外的往返交互,因此将此值设置为大于典型 LOB 的大小,将有利于提高性能。为此选项选择较大的值,会在服务器端分配更多内存,但可以提升大型 LOB 值的访问性能。

列选项

  • key(可选,默认为“false”)

    如果设置为 yes/on/true,则将外部 Oracle 表上的相应列视为主键列。要使 UPDATE 和 DELETE 正常工作,必须在属于表主键的所有列上设置此选项。

  • strip_zeros(可选,默认为“false”)

    如果设置为 yes/on/true,则在传输过程中会从字符串中删除 ASCII 0 字符。此类字符在 Oracle 中是有效的,但在 PostgreSQL 中无效,因此当 oracle_fdw 读取时会导致错误。此选项仅对charactercharacter varyingtext列有意义。

用法

Oracle 权限

Oracle 用户显然需要有 CREATE SESSION 权限,和查询相关表或视图的权限。请注意,oracle_fdw 会在查询规划时访问 Oracle 表以获取其定义信息。这会在检查外表的访问权限之前发生。因此,如果您尝试在 PostgreSQL 中访问一个外部表,所使用的 Oracle 用户并没有该表的访问权限,您可能会接收到 Oracle 的错误消息。这是意料之中的,没有安全问题。

对于 EXPLAIN VERBOSE,用户还需要对视图 V$SQL 和 V$SQL_PLAN 的 SELECT 权限。

连接

oracle_fdw 会缓存 Oracle 连接,因为为每个单独的查询去创建 Oracle 会话的成本很高。当 PostgreSQL 会话结束时,所有连接都会自动关闭。

函数oracle_close_connections()可用于关闭所有缓存的 Oracle 连接。这对于没有一直访问外部表的长时间运行的会话,并希望避免阻塞资源(其他 Oracle 连接需要访问的资源)时非常有用。不能在修改 Oracle 数据的事务中调用此函数。

表列

定义一个外表时,Oracle 表的列将按其定义的顺序映射到 PostgreSQL 列。

oracle_fdw 将在 Oracle 查询中仅包含 PostgreSQL 查询实际需要的列。

PostgreSQL 表的列数可以比 Oracle 表多或少。如果它有更多列,并且使用了这些列,您会接收到警告消息,并将返回 NULL 值。

如果要 UPDATE 或 DELETE,请确保在属于表主键的所有列上设置了key选项。否则会导致错误。

数据类型

您必须使用 oracle_fdw 可以转换的数据类型定义 PostgreSQL 列(请参阅下面的转换表)。仅当实际使用该列时,才会强制执行此限制。 因此,只要您不访问它们,就可以为不可转换的数据类型定义“虚拟”列(此技巧仅适用于 SELECT,不适用于修改外部数据)。如果 Oracle 值超过 PostgreSQL 列的大小(例如,varchar 列的长度或整数列的最大值),您会接收到运行时错误。

这些转化由 oracle_fdw 自动处理:

Oracle type              | Possible PostgreSQL types
-------------------------+--------------------------------------------------
CHAR                     | char, varchar, text
NCHAR                    | char, varchar, text
VARCHAR                  | char, varchar, text
VARCHAR2                 | char, varchar, text, json
NVARCHAR2                | char, varchar, text
CLOB                     | char, varchar, text, json
LONG                     | char, varchar, text
RAW                      | uuid, bytea
BLOB                     | bytea
BFILE                    | bytea (read-only)
LONG RAW                 | bytea
NUMBER                   | numeric, float4, float8, char, varchar, text
NUMBER(n,m) with m<=0    | numeric, float4, float8, int2, int4, int8,
                         |    boolean, char, varchar, text
FLOAT                    | numeric, float4, float8, char, varchar, text
BINARY_FLOAT             | numeric, float4, float8, char, varchar, text
BINARY_DOUBLE            | numeric, float4, float8, char, varchar, text
DATE                     | date, timestamp, timestamptz, char, varchar, text
TIMESTAMP                | date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH TIME ZONE | date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH           | 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)

如果将 NUMBER 值转换为布尔类型,则 0 表示false,其他所有值都表示true

要插入或更新 XMLTYPE 值,仅适用于不超过 VARCHAR2 数据类型最大长度的值(4000 或 32767,取决于MAX_STRING_SIZE参数)。

当前还不支持 NCLOB,因为 Oracle 无法自动将其转换为客户端编码。

如果要将 TIMESTAMP WITH LOCAL TIME ZONE 转换为timestamp,请考虑在外部服务器上设置set_timezone选项。

如果需要超出上述的转换,请在 Oracle 或 PostgreSQL 中定义一个适当的视图。

WHERE 条件和 ORDER BY 子句

PostgreSQL 会使用 WHERE 子句的所有适用部分,作为扫描的过滤器。oracle_fdw 构造的 Oracle 查询,会包含与这些筛选条件对应的 WHERE 子句,只要此类条件可以安全地转换成 Oracle 中的 SQL。此功能也称为 WHERE 子句的下推,它可以大大减少从 Oracle 检索到的行数,并能让 Oracle 的优化器选择一个好的计划来访问所需的表。

同样,ORDER BY 子句也会尽可能向下推送到 Oracle。 请注意,不会向下推送按字符串排序的 ORDER BY 条件,因为不能保证 PostgreSQL 和 Oracle 中的字符串排序顺序相同。

要利用好下推能力,请尝试对外部表使用简单的条件。请选择与 Oracle 类型相对应的 PostgreSQL 列数据类型,否则条件无法被转换。

表达式now()transaction_timestamp()current_timestampcurrent_datelocaltimestamp可被正确转换。

EXPLAIN 的输出会显示使用的 Oracle 查询,因此您可以查看哪些条件已转换为 Oracle 的形式,以及如何转换的。

外表之间的连接

从 PostgreSQL 9.6 开始,oracle_fdw 可以将连接向下推送到 Oracle 服务器。也就是说,两个外部表之间的连接,可以转换成在 Oracle 端执行连接的单个 Oracle 查询。

要让连接下推到 Oracle 执行,会存在一些限制:

  • 这两个表必须在同一台外部服务器上定义。
  • 三个或更多表之间的连接不会被下推。
  • 连接必须位于 SELECT 语句中。
  • oracle_fdw 必须能够下推所有连接条件和 WHERE 子句。
  • 没有连接条件的交叉连接不会被下推。
  • 如果下推了一个连接,则不会下推 ORDER BY 子句。

使用 ANALYZE 收集两个外部表的表统计信息,对 PostgreSQL 确定最佳连接策略非常重要。

修改外部数据

oracle_fdw 支持对外表执行 INSERT、UPDATE 和 DELETE 操作。默认情况下,这是允许的(在从 PostgreSQL 早期版本升级过来的数据库中也是如此),并且可以通过设置 readonly 表选项来禁用它。

要使 UPDATE 和 DELETE 正常工作,与 Oracle 表的主键列对应的外表列必须设置 key 列选项。这些列用于标识外表行,因此请确保在属于主键的所有列上设置了该选项。

如果在 INSERT 期间省略外表列,则该列将设置为 PostgreSQL 外表的 DEFAULT 子句中定义的值,如果没有 DEFAULT 子句,则设置为 NULL。Oracle 表列上的 DEFAULT 子句不会使用到。如果 PostgreSQL 外表不包含 Oracle 表的所有列,则 Oracle 表列上的 DEFAULT 子句,将会用于外表定义中未包含的列。

INSERT、UPDATE 和 DELETE 上的 RETURNING 子句是支持的,但具有 Oracle 数据类型 LONG 和 LONG RAW 的列除外(Oracle 在 RETURNING 子句中不支持这些数据类型)。

PostgreSQL 9.4 及以上版本支持外部表上的触发器。使用 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 的应用程序应该重试因为该错误而失败的事务。

可以使用不同的事务隔离级别,有关讨论,请参阅外部服务器选项

EXPLAIN

PostgreSQL 的 EXPLAIN 会显示实际发送给 Oracle 的查询。EXPLAIN VERBOSE 将显示 Oracle 的执行计划(但不适用于 Oracle 服务器 9i 或更早版本)。

ANALYZE

您可以使用 ANALYZE 收集外表的统计信息。这是 oracle_fdw 所支持的。

如果没有统计信息,PostgreSQL 就无法估计外表上查询的行数,这可能会导致选择错误的执行计划。

PostgreSQL 不会像普通表那样使用 autovacuum 守护进程自动收集外表的统计信息,因此在外表创建后以及远程的 Oracle 表发生重大更改时,对外表运行 ANALYZE 尤为重要。

请记住,分析 Oracle 外表会导致完整的顺序表扫描。您可以使用表选项 sample_percent,仅对 Oracle 表进行采样来加快该操作。

PostGIS 支持

数据类型geometry仅在安装了 PostGIS 插件时可用。

只有二维和三维的 POINT、LINE、POLYGON、MULTIPOINT、MULTILINE 和 MULTIPOLYGON,才是受支持的几何类型。空的 PostGIS 几何图形不受支持,因为它们在 Oracle Spatial 中没有等效的几何图形。

Oracle SRID 的 NULL 值将会转换为 0,反之亦然。对于 Oracle SRID 和 PostGIS SRID 之间的其他转换,请在 PostgreSQL 的share目录中创建一个文件srid.map。此文件中的每一行,都应包含一个 Oracle SRID 和相应的 PostGIS SRID,并用空格分隔。请保持文件尽量的小,以获得良好的性能。

对 IMPORT FOREIGN SCHEMA 的支持

从 PostgreSQL 9.5 开始,支持 IMPORT FOREIGN SCHEMA,来批量导入一个 Oracle 模式中所有表的表定义。除了 IMPORT FOREIGN SCHEMA 相关文档中的描述外,还要考虑以下事项:

  • IMPORT FOREIGN SCHEMA 将会为在 ALL_TAB_COLUMNS 中找到的所有对象创建外部表。这包括表、视图和物化视图,但不包括同义词。

  • 以下是 IMPORT FOREIGN SCHEMA 支持的选项:

    • case:在导入过程中控制表名和列名的大小写转换

      可能的值为:

      • keep:保留名称在 Oracle 中的样子,通常为大写。
      • lower:将所有表名和列名转换为小写。
      • smart:仅转换 Oracle 中全部大写的名称(这是默认行为)。
    • collation:用于指定 case 选项lowersmart进行大小写转换时的排序规则

      默认值为default,这是数据库的默认排序规则。只有pg_catalog模式中的排序规则才受支持。有关可能值的列表,请参阅pg_collation系统表中的collname值。

    • dblink:用于访问模式的 Oracle 数据库链接

      此名称必须与 Oracle 系统表中的名称完全相同。所以通常只由大写字母组成。

    • readonly:在所有导入的表上设置 readonly 选项

      有关详细信息,请参阅“选项”部分。

    • max_long:在所有导入的表上设置 max_long 选项

      有关详细信息,请参阅“选项”部分。

    • sample_percent:在所有导入的表上设置 sample_percent 选项

      有关详细信息,请参阅“选项”部分。

    • prefetch:在所有导入的表上设置 prefetch 选项

      有关详细信息,请参阅“选项”部分。

    • lob_prefetch:在所有导入的表上设置 lob_prefetch 选项

      有关详细信息,请参阅“选项”部分。

    • nchar:在所有导入的表上设置 nchar 选项

      有关详细信息,请参阅“选项”部分。

    • set_timezone:在所有导入的表上设置 set_timezone 选项

      有关详细信息,请参阅“选项”部分。

  • Oracle 模式名称必须与 Oracle 中的名称完全相同,因此通常为大写。由于 PostgreSQL 在处理之前将名称转换为小写,因此您必须使用双引号保护模式名称(例如"SCOTT")。

  • LIMIT TO 或 EXCEPT 子句中的表名,必须编写为它们在大小写转换后出现在 PostgreSQL 中的名称。

请注意,IMPORT FOREIGN SCHEMA 不适用于 Oracle 服务器 8i。