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 13 → 14: 属性变化。

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

--- PostgreSQL 13
+++ PostgreSQL 14
@@ -10,18 +10,25 @@
     "EndForeignInsert": "postgresEndForeignInsert",
     "EndForeignModify": "postgresEndForeignModify",
     "EndForeignScan": "postgresEndForeignScan",
+    "ExecForeignBatchInsert": "postgresExecForeignBatchInsert",
     "ExecForeignDelete": "postgresExecForeignDelete",
     "ExecForeignInsert": "postgresExecForeignInsert",
+    "ExecForeignTruncate": "postgresExecForeignTruncate",
     "ExecForeignUpdate": "postgresExecForeignUpdate",
     "ExplainDirectModify": "postgresExplainDirectModify",
     "ExplainForeignModify": "postgresExplainForeignModify",
     "ExplainForeignScan": "postgresExplainForeignScan",
+    "ForeignAsyncConfigureWait": "postgresForeignAsyncConfigureWait",
+    "ForeignAsyncNotify": "postgresForeignAsyncNotify",
+    "ForeignAsyncRequest": "postgresForeignAsyncRequest",
     "GetForeignJoinPaths": "postgresGetForeignJoinPaths",
+    "GetForeignModifyBatchSize": "postgresGetForeignModifyBatchSize",
     "GetForeignPaths": "postgresGetForeignPaths",
     "GetForeignPlan": "postgresGetForeignPlan",
     "GetForeignRelSize": "postgresGetForeignRelSize",
     "GetForeignUpperPaths": "postgresGetForeignUpperPaths",
     "ImportForeignSchema": "postgresImportForeignSchema",
+    "IsForeignPathAsyncCapable": "postgresIsForeignPathAsyncCapable",
     "IsForeignRelUpdatable": "postgresIsForeignRelUpdatable",
     "IterateDirectModify": "postgresIterateDirectModify",
     "IterateForeignScan": "postgresIterateForeignScan",
@@ -48,11 +55,11 @@
       "name": "use_remote_estimate"
     },
     {
-      "definition": "This option, which can be specified for a foreign server, is a numeric value that is added to the estimated startup cost of any foreign-table scan on that server. This represents the additional overhead of establishing a connection, parsing and planning the query on the remote side, etc. The default value is 100 .",
+      "definition": "This option, which can be specified for a foreign server, is a floating point value that is added to the estimated startup cost of any foreign-table scan on that server. This represents the additional overhead of establishing a connection, parsing and planning the query on the remote side, etc. The default value is 100 .",
       "name": "fdw_startup_cost"
     },
     {
-      "definition": "This option, which can be specified for a foreign server, is a numeric value that is used as extra cost per-tuple for foreign-table scans on that server. This represents the additional overhead of data transfer between servers. You might increase or decrease this number to reflect higher or lower network delay to the remote server. The default value is 0.01 .",
+      "definition": "This option, which can be specified for a foreign server, is a floating point value that is used as extra cost per-tuple for foreign-table scans on that server. This represents the additional overhead of data transfer between servers. You might increase or decrease this number to reflect higher or lower network delay to the remote server. The default value is 0.01 .",
       "name": "fdw_tuple_cost"
     },
     {
@@ -64,8 +71,20 @@
       "name": "fetch_size"
     },
     {
+      "definition": "This option specifies the number of rows postgres_fdw should insert in each insert operation. It can be specified for a foreign table or a foreign server. The option specified on a table overrides an option specified for the server. The default is 1 . Note the actual number of rows postgres_fdw inserts at once depends on the number of columns and the provided batch_size value. The batch is executed as a single query, and the libpq protocol (which postgres_fdw uses to connect to a remote server) limits the number of parameters in a single query to 65535. When the number of columns * batch_size exceeds the limit, the batch_size will be adjusted to avoid an error.",
+      "name": "batch_size"
+    },
+    {
+      "definition": "This option controls whether postgres_fdw allows foreign tables to be scanned concurrently for asynchronous execution. It can be specified for a foreign table or a foreign server. A table-level option overrides a server-level option. The default is false . In order to ensure that the data being returned from a foreign server is consistent, postgres_fdw will only open one connection for a given foreign server and will run all queries against that server sequentially even if there are multiple foreign tables involved, unless those tables are subject to different user mappings. In such a case, it may be more performant to disable this option to eliminate the overhead associated with running queries asynchronously. Asynchronous execution is applied even when an Append node contains subplan(s) executed synchronously as well as subplan(s) executed asynchronously. In such a case, if the asynchronous subplans are ones processed using postgres_fdw , tuples from the asynchronous subplans are not returned until after at least one synchronous subplan returns all tuples, as that subplan is executed while the asynchronous subplans are waiting for the results of asynchronous queries sent to foreign servers. This behavior might change in a future release.",
+      "name": "async_capable"
+    },
+    {
       "definition": "This option controls whether postgres_fdw allows foreign tables to be modified using INSERT , UPDATE and DELETE commands. It can be specified for a foreign table or a foreign server. A table-level option overrides a server-level option. The default is true . Of course, if the remote table is not in fact updatable, an error would occur anyway. Use of this option primarily allows the error to be thrown locally without querying the remote server. Note however that the information_schema views will report a postgres_fdw foreign table to be updatable (or not) according to the setting of this option, without any check of the remote server.",
       "name": "updatable"
+    },
+    {
+      "definition": "This option controls whether postgres_fdw allows foreign tables to be truncated using the TRUNCATE command. It can be specified for a foreign table or a foreign server. A table-level option overrides a server-level option. The default is true . Of course, if the remote table is not in fact truncatable, an error would occur anyway. Use of this option primarily allows the error to be thrown locally without querying the remote server.",
+      "name": "truncatable"
     },
     {
       "definition": "This option controls whether column COLLATE options are included in the definitions of foreign tables imported from a foreign server. The default is true . You might need to turn this off if the remote server has a different set of collation names than the local server does, which is likely to be the case if it's running on a different operating system. If you do so, however, there is a very severe risk that the imported table columns' collations will not match the underlying data, resulting in anomalous query behavior. Even when this parameter is set to true , importing columns whose collation is the remote server's default can be risky. They will be imported with COLLATE \"default\" , which will select the local server's default collation, which could be different.",
@@ -82,6 +101,10 @@
     {
       "definition": "This option controls whether column NOT NULL constraints are included in the definitions of foreign tables imported from a foreign server. The default is true .",
       "name": "import_not_null"
+    },
+    {
+      "definition": "This option controls whether postgres_fdw keeps the connections to the foreign server open so that subsequent queries can re-use them. It can only be specified for a foreign server. The default is on . If set to off , all connections to this foreign server will be discarded at the end of each transaction.",
+      "name": "keep_connections"
     }
   ],
   "protocol_versions": {},

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