主页 / dba / 问题 / 264269
Accepted
Rino
Asked: 2020-04-04 08:51:37 +0800 CST

如何使用来自 PostgreSQL 的外部数据包装器更新/删除 Oracle 外部表？

  • 772

我需要通过 Oracle Foreign Data Wrapper (FDW) 将大量行从我的 PostgreSQL 数据库传输到远程 Oracle DB。到目前为止，所需的操作是插入到外部表 (FT)，但现在我也在考虑执行 DELETE/UPDATE。需要 UPDATE 来根据某些条件标记插入的行。

最初我遇到一个问题，即 INSERT to an Oracle FT 出现此错误：

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

并尝试使用不同类型的 Oracle 表显示以下模式：

在此处输入图像描述

那么我选择普通的 TABLE_4（没有任何 PK/索引）。但事实证明 DELETE/UPDATE 不喜欢它，如错误消息中所述：

ERROR:  no primary key column specified for foreign Oracle table
DETAIL:  For UPDATE or DELETE, at least one foreign table column must be marked as primary key column.
HINT:  Set the option "key" on the columns that belong to the primary key.

但是，当我使用 TABLE_6（带有 PK）时，DELETE/UPDATE 命令不断收到提示有 PK 的相同错误。

那么如何通过 Oracle FDW 更新/删除 Oracle 外部表？

此处的 Oracle FDW 文档在没有我需要的任何详细信息/示例的情况下讲述了以下内容：

如果要更新或删除，请确保在key属于表主键的所有列上都设置了该选项。不这样做会导致错误。

和

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

仅供参考，我的游乐场：

  • PostgreSQL 9.6.11 64 位与 PostGIS 2.5.1 
  • PostgreSQL 9.6.15 64 位与 PostGIS 2.5.3 
  • 每个 Postgres 都位于 Windows Server 2008 R2 Datacenter 之上，使用 Oracle FDW 1.1 版，连接到不同的 Oracle DB，均使用 11.2.0.4.0 64 位版本。
  • 尽管 PostGIS 在我的 PostgreSQL 数据库中至关重要，但要传输到 Oracle DB 的数据根本没有几何图形
oracle postgresql

1 个回答

  1. Best Answer

    正如文档和错误所说，您必须使用key列选项为属于主键的所有列定义外表。如果不知道外键是什么，就不可能确定远程表中应该更新或删除的行（对于INSERT，这不是必需的）。

    以下是设置key选项的表定义示例：

    CREATE FOREIGN TABLE atable (
   id    bigint OPTIONS (key 'true') NOT NULL,
   value text
) SERVER oraserver OPTIONS (table 'ATABLE');

    在这个例子id中是主键。

    您可以使用该IMPORT FOREIGN SCHEMA语句让 oracle_fdw 为您定义外部表，这将key根据需要自动设置选项。

    ORA-08177 是另一回事。可能是 oracle_fdw 在单个语句期间必须多次扫描 Oracle 表（例如，如果在嵌套循环连接的内侧扫描外部表），并且 oracle_fdw 必须确保在这些扫描期间看到的数据是一致的。

    默认READ COMMITTED事务隔离级别无法保证这一点，因此必须使用 oracle_fdw SERIALIZABLE（不可序列化，但保证读取稳定性）。

    现在，Oracle 在实现这种所谓的SERIALIZABLE隔离级别方面做得非常糟糕。它不是完全错误的，因为根据本书，它总是允许在可序列化事务中抛出序列化错误，但 Oracle 帽子非常自由地解释了这一点，并且只要正确实现读取稳定性被证明太麻烦，就会抛出序列化错误。

    例如，如果并发INSERT导致索引页面拆分，则尝试同时进行的可序列化事务INSERT将收到序列化错误。这当然是愚蠢的。同样，从并发事务接收数据修改的表上的任何数据修改都将导致序列化错误，即使它们没有触及相同的行。

    与所有序列化错误一样，您的响应应该是重试事务并希望下次有更多运气。

    因为这是一个常见的问题，并且要求不同解决方案的呼声变得如此响亮，所以我最近拉出了一个补丁isolation_level，可以让您在外国服务器上设置一个选项。您可以将此选项设置read_committed为将隔离级别更改为不安全值READ COMMITTED。

    这还没有发布版本，但是如果你想尝试的话，你可以安全地使用 Git HEAD。使用这个不安全的选项需要您自担风险。对于INSERTs，什么都不会出错，但是在存在并发数据修改的情况下，您不能避免不一致的查询结果。

    提醒一句：虽然批量数据修改适用于 oracle_fdw，但效率不高，因为每个受影响的行在PostgreSQL 和 Oracle 之间都有一个往返。原因在于外部数据包装器 API。很难解决这个问题，而且我不认为批量数据修改是一个如此重要的用例。

    • 2

相关问题