postgres_fdw

说明

postgres_fdw 模块提供外部数据包装器 postgres_fdw ，可用于访问存储在外部 PostgreSQL 服务器中的数据。 本模块提供的功能与较旧的 dblink 模块在很大程度上重叠。但 postgres_fdw 为访问远程表提供了更透明且符合标准的语法，并且在许多情况下性能更好。 要准备通过 postgres_fdw 进行远程访问： 现在，只需对外部表执行 SELECT ，即可访问其底层远程表中存储的数据。也可以使用 INSERT 、 UPDATE 、 DELETE 、 COPY 或 TRUNCATE 修改远程表。（当然，在用户映射中指定的远程用户必须拥有执行这些操作的权限。）

接口类别

外部数据包装器

处理函数或例程

postgres_fdw_handler

记录的回调

36

数据源

远程 PostgreSQL 服务器

接口与能力边界

矩阵记录此源码构建实际注册的回调。注册表明实现了相应接口钩子；操作是否允许还取决于选项、查询形式、权限和提供方规则。

下方保留同版本完整手册，包括配置、约束和示例。不声称已进行运行时能力测试。

异步执行选项

postgres_fdw 支持异步执行，它可以并发运行 Append 节点的多个部分，而不是串行运行，以提高性能。可以使用以下选项控制这种执行方式：

事务管理选项

如“事务管理”一节所述，在 postgres_fdw 中，事务是通过创建对应的远程事务来管理的，子事务则通过创建对应的远程子事务来管理。当当前本地事务涉及多个远程事务时，默认情况下， postgres_fdw 会在本地事务提交或中止时串行提交或中止这些远程事务。当当前本地子事务涉及多个远程子事务时，默认情况下， postgres_fdw 也会在本地子事务提交或中止时串行提交或中止这些远程子事务。使用以下选项可以改善性能：

如果启用了这些选项的多个外部服务器参与同一个本地事务，那么在本地事务提交或中止时，这些外部服务器上的多个远程事务会跨服务器并行提交或中止。

启用这些选项后，若某个外部服务器涉及很多远程事务，则在本地事务提交或中止时，该外部服务器上的性能可能会受到负面影响。

事务管理

在引用某个外部服务器上任意远程表的查询期间，如果当前本地事务尚未在该远程服务器上打开对应的事务， postgres_fdw 就会在该远程服务器上打开一个事务。本地事务提交或中止时，远程事务也会提交或中止。保存点也会通过创建对应的远程保存点进行类似管理。

当本地事务的隔离级别为 SERIALIZABLE 时，远程事务使用 SERIALIZABLE ；否则使用 REPEATABLE READ 隔离级别。这一选择确保如果一个查询在远程服务器上执行多次表扫描，所有扫描都能获得快照一致的结果。其结果是，同一事务中的后续查询会看到来自远程服务器的相同数据，即使远程服务器由于其他活动正在发生并发更新。对于使用 SERIALIZABLE 或 REPEATABLE READ 隔离级别的本地事务，这种行为本来就符合预期；但对于 READ COMMITTED 本地事务，则可能令人意外。未来的 PostgreSQL 版本可能会修改这些规则。

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

远程查询优化

postgres_fdw 会尽力优化远程查询，以减少从外部服务器传输的数据量。这是通过将查询的 WHERE 子句发送到远程服务器执行，以及不获取当前查询不需要的表列来实现的。为降低查询被错误执行的风险，只有当 WHERE 子句使用的所有数据类型、操作符和函数都是内置的，或属于外部服务器 extensions 选项列出的扩展时，才会将该子句发送到远程服务器。这类子句中的操作符和函数还必须是 IMMUTABLE 。对于 UPDATE 或 DELETE 查询， postgres_fdw 会在查询中不存在无法发送到远程服务器的 WHERE 子句、没有本地连接操作、目标表上没有行级本地 BEFORE 或 AFTER 触发器或存储生成列，也没有来自父视图的 CHECK OPTION 约束时，尝试将整个查询发送到远程服务器以优化执行。在 UPDATE 中，为了降低查询被错误执行的风险，赋给目标列的表达式也必须只使用内置数据类型、 IMMUTABLE 操作符或 IMMUTABLE 函数。

当 postgres_fdw 遇到同一外部服务器上的外部表之间的连接时，除非由于某种原因它认为分别从各表取回行会更高效，或者相关表引用受不同用户映射约束，否则会将整个连接发送到远程服务器。在发送 JOIN 子句时，它也会采取与前述 WHERE 子句相同的预防措施。

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

跨版本兼容性

postgres_fdw 可用于访问 PostgreSQL 8.3 及更新的远程服务器；只读能力可追溯到 8.1。

一个限制是，postgres_fdw 通常认为外部表 WHERE 子句中的不可变内置函数和运算符可以安全地发送到远程服务器执行。因此，远程服务器所用版本之后才加入的内置函数也可能被发送过去，导致“function does not exist”或类似错误。可以通过改写查询绕过此问题，例如将外部表引用放入带 OFFSET 0 的子 SELECT 中，以此形成优化屏障，并将有问题的函数或运算符放在子 SELECT 外部。

另一个限制是，在外部表上执行 INSERT 语句并带有 ON CONFLICT DO NOTHING 子句时，远程服务器必须运行 PostgreSQL 9.5 或更高版本，因为更早版本不支持此特性。

核心源码中注册的实现

{
	FdwRoutine *routine = makeNode(FdwRoutine);

	/* Functions for scanning foreign tables */
	routine->GetForeignRelSize = postgresGetForeignRelSize;
	routine->GetForeignPaths = postgresGetForeignPaths;
	routine->GetForeignPlan = postgresGetForeignPlan;
	routine->BeginForeignScan = postgresBeginForeignScan;
	routine->IterateForeignScan = postgresIterateForeignScan;
	routine->ReScanForeignScan = postgresReScanForeignScan;
	routine->EndForeignScan = postgresEndForeignScan;

	/* Functions for updating foreign tables */
	routine->AddForeignUpdateTargets = postgresAddForeignUpdateTargets;
	routine->PlanForeignModify = postgresPlanForeignModify;
	routine->BeginForeignModify = postgresBeginForeignModify;
	routine->ExecForeignInsert = postgresExecForeignInsert;
	routine->ExecForeignBatchInsert = postgresExecForeignBatchInsert;
	routine->GetForeignModifyBatchSize = postgresGetForeignModifyBatchSize;
	routine->ExecForeignUpdate = postgresExecForeignUpdate;
	routine->ExecForeignDelete = postgresExecForeignDelete;
	routine->EndForeignModify = postgresEndForeignModify;
	routine->BeginForeignInsert = postgresBeginForeignInsert;
	routine->EndForeignInsert = postgresEndForeignInsert;
	routine->IsForeignRelUpdatable = postgresIsForeignRelUpdatable;
	routine->PlanDirectModify = postgresPlanDirectModify;
	routine->BeginDirectModify = postgresBeginDirectModify;
	routine->IterateDirectModify = postgresIterateDirectModify;
	routine->EndDirectModify = postgresEndDirectModify;

	/* Function for EvalPlanQual rechecks */
	routine->RecheckForeignScan = postgresRecheckForeignScan;
	/* Support functions for EXPLAIN */
	routine->ExplainForeignScan = postgresExplainForeignScan;
	routine->ExplainForeignModify = postgresExplainForeignModify;
	routine->ExplainDirectModify = postgresExplainDirectModify;

	/* Support function for TRUNCATE */
	routine->ExecForeignTruncate = postgresExecForeignTruncate;

	/* Support functions for ANALYZE */
	routine->AnalyzeForeignTable = postgresAnalyzeForeignTable;

	/* Support functions for IMPORT FOREIGN SCHEMA */
	routine->ImportForeignSchema = postgresImportForeignSchema;

	/* Support functions for join push-down */
	routine->GetForeignJoinPaths = postgresGetForeignJoinPaths;

	/* Support functions for upper relation push-down */
	routine->GetForeignUpperPaths = postgresGetForeignUpperPaths;

	/* Support functions for asynchronous execution */
	routine->IsForeignPathAsyncCapable = postgresIsForeignPathAsyncCapable;
	routine->ForeignAsyncRequest = postgresForeignAsyncRequest;
	routine->ForeignAsyncConfigureWait = postgresForeignAsyncConfigureWait;
	routine->ForeignAsyncNotify = postgresForeignAsyncNotify;

	PG_RETURN_POINTER(routine);
}

相关条目

• Foreign-data wrapper 系统目录
• 既有扩展目录标识
• Foreign Scan 计划节点

文档与源码

• PostgreSQL 18 English manual
• contrib/postgres_fdw/postgres_fdw.c:557

版本比较

PostgreSQL 18 → 19: 属性变化。

以下差异保留原始字段名与英文源描述。

--- PostgreSQL 18
+++ PostgreSQL 19
@@ -28,6 +28,7 @@
     "GetForeignRelSize": "postgresGetForeignRelSize",
     "GetForeignUpperPaths": "postgresGetForeignUpperPaths",
     "ImportForeignSchema": "postgresImportForeignSchema",
+    "ImportForeignStatistics": "postgresImportForeignStatistics",
     "IsForeignPathAsyncCapable": "postgresIsForeignPathAsyncCapable",
     "IsForeignRelUpdatable": "postgresIsForeignRelUpdatable",
     "IterateDirectModify": "postgresIterateDirectModify",
@@ -65,6 +66,10 @@
     {
       "definition": "This option, which can be specified for a foreign table or a foreign server, determines if ANALYZE on a foreign table samples the data on the remote side, or reads and transfers all data and performs the sampling locally. The supported values are off , random , system , bernoulli and auto . off disables remote sampling, so all data are transferred and sampled locally. random performs remote sampling using the random() function to choose returned rows, while system and bernoulli rely on the built-in TABLESAMPLE methods of those names. random works on all remote server versions, while TABLESAMPLE is supported only since 9.5. auto (the default) picks the recommended sampling method automatically; currently it means either bernoulli or random depending on the remote server version.",
       "name": "analyze_sampling"
+    },
+    {
+      "definition": "This option, which can be specified for a foreign table or a foreign server, determines if ANALYZE on a foreign table will instead attempt to fetch the existing statistics for the remote table, and import those statistics directly to the local server (without applying the foreign table columns' n_distinct option). If the attempt failed, statistics are collected by row sampling on the foreign table. This option is only useful if the remote table is one that can have regular statistics (tables, not inherited, and materialized views). When using this option, it is the user's responsibility to ensure that the existing statistics for the remote table are up-to-date. The default is false . If the foreign table is a partition of a partitioned table, analyzing the partitioned table will still result in row sampling on the foreign table regardless of this setting, though direct analysis of the foreign table would have attempted to fetch and import the remote statistics first if this option was enabled. Note that this option doesn't currently support the case where the remote table is (or was once) inherited. This is an implementation restriction that might be fixed in a future release.",
+      "name": "import_stats"
     },
     {
       "definition": "This option is a comma-separated list of names of PostgreSQL extensions that are installed, in compatible versions, on both the local and remote servers. Functions and operators that are immutable and belong to a listed extension will be considered shippable to the remote server. This option can only be specified for foreign servers, not per-table. When using the extensions option, it is the user's responsibility that the listed extensions exist and behave identically on both the local and remote servers. Otherwise, remote queries may fail or behave unexpectedly.",
@@ -119,7 +124,7 @@
       "name": "keep_connections"
     },
     {
-      "definition": "This option controls whether postgres_fdw will use the SCRAM pass-through authentication to connect to the foreign server. It can be specified for a foreign server or a user mapping. A user mapping setting overrides the foreign server setting. With SCRAM pass-through authentication, postgres_fdw uses SCRAM-hashed secrets instead of plain-text user passwords to connect to the remote server. This avoids storing plain-text user passwords in PostgreSQL system catalogs. To use SCRAM pass-through authentication: The remote server must request the scram-sha-256 authentication method; otherwise, the connection will fail. The remote server can be of any PostgreSQL version that supports SCRAM. Support for use_scram_passthrough is only required on the client side (FDW side). The user mapping password is not used. The server running postgres_fdw and the remote server must have identical SCRAM secrets (encrypted passwords) for the user being used on postgres_fdw to authenticate on the foreign server (same salt and iterations, not merely the same password). As a corollary, if FDW connections to multiple hosts are to be made, for example for partitioned foreign tables/sharding, then all hosts must have identical SCRAM secrets for the users involved. The current session on the PostgreSQL instance that makes the outgoing FDW connections also must also use SCRAM authentication for its incoming client connection. (Hence “ pass-through ” : SCRAM must be used going in and out.) This is a technical requirement of the SCRAM protocol.",
+      "definition": "This option controls whether postgres_fdw will use the SCRAM pass-through authentication to connect to the foreign server. It can be specified for a foreign server or a user mapping. A user mapping setting overrides the foreign server setting. With SCRAM pass-through authentication, postgres_fdw uses SCRAM-hashed secrets instead of plain-text user passwords to connect to the remote server. This avoids storing plain-text user passwords in PostgreSQL system catalogs. To use SCRAM pass-through authentication: The remote server must request the scram-sha-256 authentication method; otherwise, the connection will fail. The remote server can be of any PostgreSQL version that supports SCRAM. Support for use_scram_passthrough is only required on the client side (FDW side). The user mapping password is not used. The server running postgres_fdw and the remote server must have identical SCRAM secrets (encrypted passwords) for the user being used on postgres_fdw to authenticate on the foreign server (same salt and iterations, not merely the same password). As a corollary, if FDW connections to multiple hosts are to be made, for example for partitioned foreign tables/sharding, then all hosts must have identical SCRAM secrets for the users involved. The current session on the PostgreSQL instance that makes the outgoing FDW connections also must also use SCRAM authentication for its incoming client connection. (Hence “ pass-through ” : SCRAM must be used going in and out.) This is a technical requirement of the SCRAM protocol. The foreign server must not be used for subscription connections (see Section F.40.4 ).",
       "name": "use_scram_passthrough"
     }
   ],

比较已记录的接口与属性，排除来源指纹和构建元数据。某个样本中没有记录，不能据此判断实际引入或移除的版本。