F.33. postgres_fdw

• F.33.1. postgres_fdw 的 FDW 选项

• F.33.2 连接 Management

• F.33.3TransactionManagement

• F.33.4. 远程查询优化

• F.33.5. 远程查询执行环境

• F.33.6. 跨版本兼容性

• F.33.7. Examples

• F.33.8. Author

postgres_fdw模块提供了外部数据包装器postgres_fdw，可用于访问存储在外部 PostgreSQL 服务器中的数据。

该模块提供的功能与旧版dblink模块的功能基本重叠。但是postgres_fdw为访问远程表提供了更加透明和符合标准的语法，并且在许多情况下可以提供更好的性能。

准备使用postgres_fdw进行远程访问：

• 使用CREATE EXTENSION安装postgres_fdwextensions。

• 使用CREATE SERVER创建一个外部服务器对象，以表示您要连接的每个远程数据库。指定连接信息(user和password除外)作为服务器对象的选项。

• 使用创建用户 Map为要允许访问每个外部服务器的每个数据库用户创建一个用户 Map。指定用作用户 Map 的user和password选项的远程用户名和密码。

• 使用创建外表或导入外国模式为要访问的每个远程表创建一个外部表。外部表的列必须与引用的远程表匹配。但是，如果您指定正确的远程名称作为外部表对象的选项，则可以使用与远程表不同的表名和/或列名。

现在，您只需要来自外部表的SELECT即可访问存储在其基础远程表中的数据。您还可以使用INSERT，UPDATE或DELETE修改远程表。 (当然，您在用户 Map 中指定的远程用户必须具有执行这些操作的权限.)

请注意，postgres_fdw当前不支持带有ON CONFLICT DO UPDATE子句的INSERT语句。但是，如果省略了唯一的索引推断规范，则支持ON CONFLICT DO NOTHING子句。还要注意，postgres_fdw支持由在分区表上执行的UPDATE语句调用的行移动，但是当前无法处理以下情况：选择向其中插入已移动的行的远程分区也是UPDATE目标分区，稍后将对其进行更新。

通常建议使用与远程表的引用列完全相同的数据类型和排序规则来声明外部表的列。尽管postgres_fdw目前对于在需要时执行数据类型转换非常宽容，但是当类型或排序规则不匹配时，由于远程服务器对WHERE子句的解释与本地服务器的解释略有不同，因此可能会出现令人惊讶的语义异常。

请注意，与基础远程表相比，可以用更少的列或不同的列 Sequences 声明一个外部表。列与远程表的匹配是按名称而不是位置进行的。

F.33.1. postgres_fdw 的 FDW 选项

F.33.1.1. 连接选项

使用postgres_fdw外部数据包装器的外部服务器可以具有与 libpq 在连接字符串中接受的选项相同，如Section 34.1.2中所述，但不允许使用以下选项：

• user和password(请在用户 Map 中指定)

• client_encoding(这是根据本地服务器编码自动设置的)

• fallback_application_name(始终设置为postgres_fdw)

只有超级用户可以在没有密码身份验证的情况下连接到外部服务器，因此请始终为属于非超级用户的用户 Map 指定password选项。

F.33.1.2. 对象名称选项

这些选项可用于控制发送到远程 PostgreSQL 服务器的 SQL 语句中使用的名称。使用与基础远程表名称不同的名称创建外部表时，需要使用这些选项。

• schema_name

  • 可以为外部表指定的此选项提供了用于远程服务器上的外部表的架构名称。如果省略此选项，则使用外部表的架构名称。

• table_name

  • 可以为外部表指定的此选项提供表名，以供远程服务器上的外部表使用。如果省略此选项，则使用外部表的名称。

• column_name

  • 可以为外部表的列指定此选项，它提供了要用于远程服务器上的列的列名。如果省略此选项，则使用列的名称。

F.33.1.3. 成本估算选项

postgres_fdw通过对远程服务器执行查询来检索远程数据，因此理想情况下，扫描外部表的估计成本应等于在远程服务器上完成的成本，再加上一些通信开销。进行估算的最可靠方法是先询问远程服务器，然后添加一些开销—但是对于简单查询，可能不值得为获得成本估算而进行额外的远程查询。因此postgres_fdw提供了以下选项来控制成本估算的方式：

• use_remote_estimate

  • 可以为外部表或外部服务器指定此选项，它控制postgres_fdw是否发出远程EXPLAIN命令来获取成本估算。外部表的设置会覆盖其服务器的所有设置，但仅适用于该表。默认值为false。

• fdw_startup_cost

  • 可以为外部服务器指定的此选项是一个数字值，该值被添加到该服务器上任何外部表扫描的估计启动成本中。这表示构建连接，解析和计划远程查询等的额外开销。默认值为100。

• fdw_tuple_cost

  • 可以为外部服务器指定的此选项是一个数值，用作该服务器上的外部表扫描的每 Tuples 额外费用。这代表了服务器之间数据传输的额外开销。您可以增加或减少此数字，以反映远程服务器的较高或较低的网络延迟。默认值为0.01。

当use_remote_estimate为 true 时，postgres_fdw从远程服务器获取行数和成本估算，然后将fdw_startup_cost和fdw_tuple_cost添加到成本估算中。当use_remote_estimate为假时，postgres_fdw执行本地行计数和成本估算，然后将fdw_startup_cost和fdw_tuple_cost添加到成本估算中。除非可获得远程表统计信息的本地副本，否则此本地估计不太可能非常准确。在外部表上运行ANALYZE是更新本地统计信息的方法。这将执行对远程表的扫描，然后像表是本地表一样计算和存储统计信息。保留本地统计信息可能是减少远程表按查询进行计划的开销的有用方法-但是，如果远程表经常更新，则本地统计信息很快就会过时。

F.33.1.4. 远程执行选项

默认情况下，将仅考虑使用内置运算符和函数的WHERE子句在远程服务器上执行。提取行后，将在本地检查涉及非内置函数的子句。如果这些功能在远程服务器上可用，并且可以依靠它们产生与本地相同的结果，则可以通过发送此类WHERE子句以进行远程执行来提高性能。可以使用以下选项控制此行为：

• extensions

  • 此选项是逗号分隔的 PostgreSQLextensions 称的列表，这些 PostgreSQL 扩展的名称以兼容版本安装在本地和远程服务器上。属于所列扩展的不可变功能和运算符将被视为可移植到远程服务器。只能为外部服务器指定此选项，不能按表指定。

使用extensions选项时，用户必须负责列出的扩展存在并且在本地和远程服务器上具有相同的行为。否则，远程查询可能会失败或表现异常。

• fetch_size

  • 此选项指定每个提取操作应获得的行数postgres_fdw。可以为外部表或外部服务器指定它。在表上指定的选项将覆盖为服务器指定的选项。默认值为100。

F.33.1.5. 可更新性选项

默认情况下，假定所有使用postgres_fdw的外部表都是可更新的。可以使用以下选项来覆盖它：

• updatable

  • 此选项控制postgres_fdw是否允许使用INSERT，UPDATE和DELETE命令修改外部表。可以为外部表或外部服务器指定它。表级选项将覆盖服务器级选项。默认值为true。

当然，如果远程表实际上不可更新，则无论如何都会发生错误。使用此选项主要是允许在不查询远程服务器的情况下在本地引发错误。但是请注意，根据该选项的设置，information_schema视图将报告postgres_fdw外表可更新(或不可更新)，而无需检查远程服务器。

F.33.1.6. 导入选项

postgres_fdw能够使用导入外国模式导入外部表定义。此命令在本地服务器上创建与远程服务器上存在的表或视图匹配的外部表定义。如果要导入的远程表具有用户定义的数据类型的列，则本地服务器必须具有相同名称的兼容类型。

可以使用以下选项(在IMPORT FOREIGN SCHEMA命令中提供)自定义导入行为：

• import_collate

  • 此选项控制从外部服务器导入的外部表的定义中是否包含列COLLATE选项。默认值为true。如果远程服务器具有与本地服务器不同的排序规则名称集，则可能需要关闭此功能(如果它在不同的 os 上运行，则可能是这种情况)。

• import_default

  • 此选项控制从外部服务器导入的外部表的定义中是否包含列DEFAULT表达式。默认值为false。如果启用此选项，请警惕默认值，这些默认值在本地服务器上的计算方式可能与在远程服务器上的计算方式有所不同； nextval()是常见的问题来源。如果导入的默认表达式使用本地不存在的函数或运算符，则IMPORT将完全失败。

• import_not_null

  • 此选项控制从外部服务器导入的外部表的定义中是否包含列NOT NULL约束。默认值为true。

请注意，永远不会从远程表中导入NOT NULL以外的约束。尽管 PostgreSQL 确实支持对外部表的CHECK约束，但是由于约束表达式在本地和远程服务器上的评估结果可能不同，因此没有自动导入它们的规定。 CHECK约束的行为中的任何此类不一致都可能导致查询优化中难以检测到的错误。因此，如果您想导入CHECK约束，则必须手动进行，并且应仔细验证每个约束的语义。有关在外表上处理CHECK约束的更多详细信息，请参见创建外表。

作为某些其他表分区的表或外部表会自动排除。分区表被导入，除非它们是其他表的分区。由于可以通过作为分区层次结构根目录的分区表访问所有数据，因此该方法应允许访问所有数据而无需创建额外的对象。

F.33.2 连接 Management

postgres_fdw在使用与外部服务器关联的外部表的第一个查询期间构建与外部服务器的连接。该连接将保留并重新用于同一会话中的后续查询。但是，如果使用多个用户标识(用户 Map)来访问外部服务器，则会为每个用户 Map 构建连接。

F.33.3TransactionManagement

在引用外部服务器上任何远程表的查询期间，postgres_fdw如果尚未打开与当前本地事务相对应的事务，则会在远程服务器上打开事务。当本地事务提交或中止时，远程事务将被提交或中止。通过创建相应的远程保存点来类似地 Management 保存点。

当本地事务具有SERIALIZABLE隔离级别时，远程事务使用SERIALIZABLE隔离级别；否则使用REPEATABLE READ隔离级别。此选择可确保如果查询在远程服务器上执行多个表扫描，它将为所有扫描获取快照一致的结果。结果是，即使由于其他活动而在远程服务器上发生并发更新，单个事务中的连续查询也将从远程服务器看到相同的数据。如果本地事务使用SERIALIZABLE或REPEATABLE READ隔离级别，则无论如何都会发生这种行为，但是对于READ COMMITTED本地事务，这可能会令人惊讶。将来的 PostgreSQL 版本可能会修改这些规则。

请注意，postgres_fdw目前不支持为两阶段提交准备远程事务。

F.33.4. 远程查询优化

postgres_fdw尝试优化远程查询以减少从外部服务器传输的数据量。这是通过将查询WHERE子句发送到远程服务器来执行的，而不是检索当前查询不需要的表列来完成的。为了减少查询被错误执行的风险，除非WHERE子句仅使用内置的数据类型，运算符和函数，或者属于外部服务器的extensions选项中列出的 extensions，否则它们不会发送到远程服务器。这些子句中的运算符和函数也必须为IMMUTABLE。对于UPDATE或DELETE查询，如果没有查询WHERE子句无法发送到远程服务器，没有查询的本地联接，没有行级，则postgres_fdw尝试通过将整个查询发送到远程服务器来优化查询执行。目标表上的本地BEFORE或AFTER触发器，并且父视图没有CHECK OPTION约束。在UPDATE中，要分配给目标列的表达式必须仅使用内置数据类型，IMMUTABLE运算符或IMMUTABLE函数，以减少执行查询的风险。

当postgres_fdw在同一外部服务器上的外部表之间遇到连接时，它将整个连接发送到外部服务器，除非出于某种原因，除非它认为从每个表中分别获取行会更有效，或者除非涉及到表引用服从不同的用户 Map。发送JOIN子句时，采取与上述WHERE子句相同的预防措施。

可以使用EXPLAIN VERBOSE检查实际发送到远程服务器以执行的查询。

F.33.5. 远程查询执行环境

在postgres_fdw打开的远程会话中，search_path参数仅设置为pg_catalog，因此只有内置对象可见而不需要模式限定。对于postgres_fdw本身生成的查询，这不是问题，因为它始终提供这种限定。但是，这可能会对通过远程表上的触发器或规则在远程服务器上执行的功能造成危害。例如，如果远程表实际上是一个视图，则该视图中使用的任何功能都将使用受限的搜索路径执行。建议对此类函数中的所有名称进行模式限定，或者将SET search_path选项(请参见CREATE FUNCTION)附加至此类函数以构建其预期的搜索路径环境。

postgres_fdw同样为各种参数构建远程会话设置：

• TimeZone设置为UTC

• DateStyle设置为ISO

• IntervalStyle设置为postgres

• 远程服务器 9.0 及更高版本的extra_float_digits设置为3，而较旧版本的extra_float_digits设置为2

与search_path相比，这些问题的可能性较小，但是如果需要，可以使用函数SET选项进行处理。

不建议您通过更改这些参数的会话级设置来覆盖此行为；可能会导致postgres_fdw发生故障。

F.33.6. 跨版本兼容性

postgres_fdw可与可 traceback 到 PostgreSQL 8.3 的远程服务器一起使用。只读功能可回溯到 8.1. 但是，一个限制是postgres_fdw通常假定不可变的内置函数和运算符可以安全地发送到远程服务器以供执行，如果它们出现在外部表的WHERE子句中。因此，自远程服务器发布以来添加的内置函数可能会发送给它执行，从而导致“该函数不存在”或类似的错误。可以通过重写查询来解决这种类型的故障，例如，将外部表引用嵌入到以OFFSET 0作为优化围栏的 SubSELECT中，然后将有问题的函数或运算符放在 SubSELECT之外。

F.33.7. Examples

这是使用postgres_fdw创建外部表的示例。首先安装扩展：

CREATE EXTENSION postgres_fdw;

然后使用CREATE SERVER创建外部服务器。在此示例中，我们希望连接到主机192.83.123.89上的 PostgreSQL 服务器，并监听端口5432。构建连接的数据库在远程服务器上名为foreign_db：

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');

还需要使用创建用户 Map定义的用户 Map，以标识将在远程服务器上使用的角色：

CREATE USER MAPPING FOR local_user
        SERVER foreign_server
        OPTIONS (user 'foreign_user', password 'password');

现在可以使用创建外表创建外部表。在此示例中，我们希望访问远程服务器上名为some_schema.some_table的表。它的本地名称为foreign_table：

CREATE FOREIGN TABLE foreign_table (
        id integer NOT NULL,
        data text
)
        SERVER foreign_server
        OPTIONS (schema_name 'some_schema', table_name 'some_table');

至关重要的是，在CREATE FOREIGN TABLE中声明的列的数据类型和其他属性必须与实际的远程表相匹配。列名称也必须匹配，除非您将column_name选项附加到各个列以显示它们在远程表中的命名方式。在许多情况下，使用导入外国模式优于手动构造外部表定义。

F.33.8. Author

花田繁<shigeru.hanada@gmail.com>