F.33. postgres_fdw #
postgres_fdw 模块提供外部数据包装器
postgres_fdw，可用于访问存储在外部
PostgreSQL 服务器中的数据。
本模块提供的功能与较旧的 dblink 模块在很大程度上重叠。但 postgres_fdw 为访问远程表提供了更透明且符合标准的语法，并且在许多情况下性能更好。
要准备通过 postgres_fdw 进行远程访问：
安装
postgres_fdw扩展，可使用 CREATE EXTENSION。使用 CREATE SERVER 创建外部服务器对象，用来表示每个要连接的远程数据库。将除
user和password之外的连接信息指定为服务器对象的选项。对于每个需要获准访问各个外部服务器的数据库用户，使用 CREATE USER MAPPING 创建用户映射。将要使用的远程用户名和密码指定为用户映射的
user和password选项。对于每个要访问的远程表，使用 CREATE FOREIGN TABLE 或 IMPORT FOREIGN SCHEMA 创建外部表。外部表的列必须与被引用的远程表匹配。不过，如果在外部表对象的选项中指定正确的远程名称，也可以使用与远程表不同的表名和/或列名。
现在，只需对外部表执行 SELECT，即可访问其底层远程表中存储的数据。也可以使用 INSERT、UPDATE、DELETE 或 COPY 修改远程表。（当然，在用户映射中指定的远程用户必须拥有执行这些操作的权限。）
请注意，postgres_fdw 当前不支持带有
ON CONFLICT DO UPDATE 子句的
INSERT 语句。不过，在省略唯一索引推断规范的前提下，支持
ON CONFLICT DO NOTHING 子句。还要注意，postgres_fdw 支持在分区表上执行的
UPDATE 语句所引发的行移动，但当前尚不能处理这样一种情况：为插入被移动行而选择的远程分区，同时也是之后将被更新的
UPDATE 目标分区。
通常建议将外部表的列声明为与被引用远程表的对应列具有完全相同的数据类型，并在适用时具有相同的排序规则。尽管 postgres_fdw
目前在按需执行数据类型转换方面相当宽容，但当类型或排序规则不匹配时，仍可能出现令人意外的语义异常，因为远程服务器对查询条件的解释可能与本地服务器不同。
请注意，外部表的声明可以比其底层远程表少一些列，或者使用不同的列顺序。与远程表列的匹配是按名称而不是按位置进行的。
F.33.1. postgres_fdw 的 FDW 选项
F.33.1.1. 连接选项
使用 postgres_fdw 外部数据包装器的外部服务器，可以使用与 libpq 连接字符串所接受的相同选项，详见第 34.1.2 节，但以下选项不被允许：
user和password（应改为在用户映射中指定）client_encoding（会根据本地服务器编码自动设置）fallback_application_name（始终设置为postgres_fdw）
只有超级用户才能不使用密码认证连接到外部服务器，因此应始终为属于非超级用户的用户映射指定 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该选项可为外部服务器指定，是一个数值，用作该服务器上外部表扫描的每个元组的额外代价。它表示服务器之间数据传输的额外开销。可以增大或减小该数值，以反映到远程服务器更高或更低的网络延迟。默认值为
0.01。
当 use_remote_estimate 为真时，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该选项是一个以逗号分隔的 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 FOREIGN SCHEMA 命令中给出）自定义导入行为：
import_collate该选项控制从外部服务器导入的外部表定义中是否包含列的
COLLATE选项。默认值为true。如果远程服务器的排序规则名称集合与本地服务器不同，则可能需要关闭此选项；如果远程服务器运行在不同操作系统上，这种情况尤其可能发生。不过，如果这样做，导入表列的排序规则就存在与底层数据不匹配的严重风险，从而导致查询行为异常。即使将此参数设置为
true，导入排序规则为远程服务器默认值的列仍可能有风险。这些列会以COLLATE "default"导入，这将选择本地服务器的默认排序规则，而它可能并不相同。import_default该选项控制从外部服务器导入的外部表定义中是否包含列的
DEFAULT表达式。默认值为false。如果启用此选项，需要警惕那些在本地服务器上的计算结果可能与远程服务器不同的默认值；nextval()是常见的问题来源。如果导入的默认值表达式使用了本地不存在的函数或操作符，则整个IMPORT将失败。import_not_null该选项控制从外部服务器导入的外部表定义中是否包含列的
NOT NULL约束。默认值为true。
请注意，除 NOT NULL 之外的约束永远不会从远程表导入。虽然 PostgreSQL 确实支持在外部表上定义
CHECK 约束，但由于约束表达式在本地和远程服务器上可能求值不同，系统不会自动导入它们。此类行为不一致的 CHECK 约束，可能导致查询优化中难以发现的错误。因此，如果希望导入 CHECK 约束，必须手工完成，并应仔细核实每一个约束的语义。有关外部表上 CHECK 约束处理方式的更多细节，请参见 CREATE FOREIGN TABLE。
作为其他表分区的表或外部表会被自动排除。分区表会被导入，除非它本身也是其他表的分区。由于所有数据都可以通过作为分区层次根的分区表访问，因此只导入分区表即可访问全部数据，而无需创建额外对象。
F.33.2. 连接管理
postgres_fdw 在首次执行使用与某个外部服务器关联的外部表的查询时，会建立到该外部服务器的连接。该连接会在同一会话中保留并供后续查询重用。如果使用多个用户标识（用户映射）访问该外部服务器，则会为每个用户映射建立一个连接。
F.33.3. 事务管理
在引用某个外部服务器上任意远程表的查询期间，如果当前本地事务尚未在该远程服务器上打开对应的事务，postgres_fdw 就会在该远程服务器上打开一个事务。本地事务提交或中止时，远程事务也会提交或中止。保存点也会通过创建对应的远程保存点进行类似管理。
当本地事务的隔离级别为 SERIALIZABLE 时，远程事务使用
SERIALIZABLE；否则使用
REPEATABLE READ 隔离级别。这一选择确保如果一个查询在远程服务器上执行多次表扫描，所有扫描都能获得快照一致的结果。其结果是，同一事务中的后续查询会看到来自远程服务器的相同数据，即使远程服务器由于其他活动正在发生并发更新。对于使用 SERIALIZABLE 或
REPEATABLE READ 隔离级别的本地事务，这种行为本来就符合预期；但对于 READ COMMITTED 本地事务，则可能令人意外。未来的 PostgreSQL 版本可能会修改这些规则。
请注意，postgres_fdw 当前不支持为两阶段提交预备远程事务。
F.33.4. 远程查询优化
postgres_fdw 会尽力优化远程查询，以减少从外部服务器传输的数据量。这是通过将查询的 WHERE 子句发送到远程服务器执行，以及不获取当前查询不需要的表列来实现的。为降低查询被错误执行的风险，只有当 WHERE 子句使用的所有数据类型、操作符和函数都是内置的，或属于外部服务器 extensions
选项列出的扩展时，才会将该子句发送到远程服务器。这类子句中的操作符和函数还必须是
IMMUTABLE。对于 UPDATE 或
DELETE 查询，postgres_fdw 会在查询中不存在无法发送到远程服务器的 WHERE 子句、没有本地连接操作、目标表上没有行级本地 BEFORE 或
AFTER 触发器，也没有来自父视图的
CHECK OPTION 约束时，尝试将整个查询发送到远程服务器以优化执行。在 UPDATE 中，为了降低查询被错误执行的风险，赋给目标列的表达式也必须只使用内置数据类型、IMMUTABLE 操作符或 IMMUTABLE 函数。
当 postgres_fdw 遇到同一外部服务器上的外部表之间的连接时，除非由于某种原因它认为分别从各表取回行会更高效，或者相关表引用受不同用户映射约束，否则会将整个连接发送到远程服务器。在发送
JOIN 子句时，它也会采取与前述
WHERE 子句相同的预防措施。
可以使用 EXPLAIN VERBOSE 查看实际发送给远程服务器执行的查询。
F.33.5. 远程查询执行环境
在 postgres_fdw 打开的远程会话中，search_path 参数会被设置为仅包含
pg_catalog，这样无需模式限定就只能看到内置对象。这对 postgres_fdw 自身生成的查询不是问题，因为它总是提供这种限定。然而，这可能会对那些通过远程表上的触发器或规则在远程服务器上执行的函数带来风险。例如，如果远程表实际上是一个视图，则该视图中使用的任何函数都会在受限的搜索路径下执行。建议在这类函数中对所有名称都写成带模式限定的形式，或者为这类函数附加
SET search_path 选项（见 CREATE FUNCTION），以建立其预期的搜索路径环境。
postgres_fdw 还会为远程会话设置以下参数：
TimeZone 被设置为
UTCDateStyle 被设置为
ISOIntervalStyle 被设置为
postgres对于 9.0 及以上的远程服务器，extra_float_digits 被设置为
3；对于更早版本，则被设置为2
这些设置通常不像 search_path 那样容易出问题，但如果有需要，也可以通过函数的 SET 选项处理。
不建议通过修改这些参数的会话级设置来覆盖这种行为；这很可能导致 postgres_fdw 工作异常。
F.33.6. 跨版本兼容性
postgres_fdw 可用于最早追溯到
PostgreSQL 8.3 的远程服务器。只读能力可追溯到
8.1。不过有一个限制是，postgres_fdw 通常假定：如果外部表的 WHERE 子句中出现不可变的内置函数和操作符，那么把它们发送到远程服务器执行是安全的。因此，某个在远程服务器所属发行版本之后才加入的内置函数，可能仍会被发送到该远程服务器执行，从而导致“function does not exist”或类似错误。可以通过重写查询绕过这类失败，例如把外部表引用放入一个带
OFFSET 0 的子 SELECT 中，作为优化栅栏，并将有问题的函数或操作符放到子 SELECT 之外。
F.33.7. 示例
下面是使用 postgres_fdw 创建外部表的一个示例。首先安装扩展：
CREATE EXTENSION postgres_fdw;
然后使用 CREATE SERVER 创建外部服务器。在本示例中，希望连接到一台 PostgreSQL 服务器，它运行在主机 192.83.123.89 上并监听
5432 端口。要连接的数据库在远程服务器上名为
foreign_db：
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');
还需要用 CREATE USER MAPPING 定义一个用户映射，以标识在远程服务器上使用哪个角色：
CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'foreign_user', password 'password');
现在可以通过 CREATE FOREIGN TABLE 创建外部表。在本例中，要访问远程服务器上的表 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 选项，指明它们在远程表中的名称。在许多情况下，使用 IMPORT FOREIGN SCHEMA 优于手工构造外部表定义。
F.33.8. 作者
Shigeru Hanada <shigeru.hanada@gmail.com>