《postgresql指南--内幕探索》第四章 外部数据包装器

本文主要是介绍《postgresql指南--内幕探索》第四章 外部数据包装器，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

2003年，在SQL标准中增加了一个访问远程数据的规范，称为SQL外部数据管理(SQL/MED)。自9.1版本以来，PostgreSQL开发了FDW，实现了SQL/MED的一部分特性。
在SQL/MED中，远程服务器上的表称为外部表。PostgreSQL 的外部数据包装器(Foreign Data Wrappers，FDW)使用与本地表类似的方式，通过SQL/MED来管理外部表。

图1：FDW的基本概念

安装必要的插件并进行适当的设置后, 您可以访问远程服务器上的外部表。例如, 假设有两个远程服务, 分别是 postgresql 和 mysql, 它们分别 foreign_pg_tbl表和foreign_my_tbl表。这个例子中, 您可以通过发出 SELECT 查询来访问本地服务器上的外部表, 如下所示。

localdb=# -- foreign_pg_tbl is on the remote postgresql server. 
localdb-# SELECT count(*) FROM foreign_pg_tbl;
 count 
-------
 20000

localdb=# -- foreign_my_tbl is on the remote mysql server. 
localdb-# SELECT count(*) FROM foreign_my_tbl;
 count 
-------
 10000

此外, 还可以执行连接操作，类似操作本地表一样的操作这些外部表。

localdb=# SELECT count(*) FROM foreign_pg_tbl AS p, foreign_my_tbl AS m WHERE p.id = m.id;
count 
-------
10000

许多FDW插件已经开发并在Postgres wiki中列出。尽管, 几乎所有插件都没有得到好的维护, 但 postgres_fdw 除外, 该插件是由 PostgreSQL 全球开发组正式开发和维护的, 作为访问远程 PostgreSQL 服务器的扩展。

概览

要使用 FDW 功能, 您需要安装相应的扩展并执行相关的命令，CREATE FOREIGN TABLE, CREATE SERVER和CREATE USER MAPPING (更加详细的描述请参考官方文档)。

执行完创建相关的命令后, 在执行查询处理的过程中，会调用插件中定义的函数以访问外部表。

(1) analyzer/analyser创建输入 SQL的查询树。
(2) 规划器 (或执行器) 连接到远程 server。
(3) 如果use_remote_estimate选项处于打开状态 (默认值为关闭)，规划器执行EXPLAIN命令, 以估算每个计划路径的成本。
(4) 规划器从计划树中创建纯文本 SQL 语句, 该语句在内部称为deparesing。
(5) 执行器将纯文本 SQL 语句发送到远程服务器并接收结果。

然后, 执行器在必要时处理接收到的数据。例如, 如果执行多表查询, 则执行器将执行处理完的数据和其他表的连接。
以下各节介绍了每个处理的详细信息。

创建查询树

analyzer/analyser使用外部表的定义创建输入SQL的查询树,在使用CREATE FOREIGN TABLE或IMPORT FOREIGN SCHEMA命令时， 这些表的相关信息存储在了pg_catalog.pg_class和pg_catalog.pg_foreign_table中.

连接远程服务器

要连接到远程服务器, 规划器 (或执行器) 使用特定库连接到远程数据库服务器。例如, 要连接到远程PostgreSQL服务器, postgres_fdw使用 libpq。要连接到 mysql 服务器, 则使用由interpriseDB开发的mysql_fdw,mysql_fdw使用libmysqlclient库。

连接参数 (如用户名、服务器IP地址和端口号) ，在执行CREATE USER MAPPING和CREATE SERVER命令后，存储在pg_catalog.pg_user_mapping和pg_catalog.pg_foreign_server

使用EXPLAIN命令创建一棵计划树 (可选)

PostgreSQL的FDW支持获取外部表的统计信息来估算查询的计划树, 这些值被一些FDW扩展使用, 如 postgres_fdw、mysql_fdw、tds_fdw和 jdbc2_fdw。
如果在使用ALTER_SERVER时，use_remote_estimate的值设为on，则规划器通过执行EXPLAIN向远程服务器获取查询计划的成本;否则, 默认情况使用嵌入的常量值。

localdb=# ALTER SERVER remote_server_name OPTIONS (use_remote_estimate 'on');

虽然某些扩展使用 EXPLAIN 命令的值, 但只有postgres fdw 才能反映 EXPLAIN 命令的结果, 因为PostgreSQL的EXPLAIN命令同时返回启动和总成本。

其他DBMS fdw扩展无法使用 EXPLAIN 命令的结果进行规划。例如, mysql的 EXPLAIN 命令只返回评估的行数;但是, PostgreSQL 的规划器需要更多的信息来估算第3章中所述的成本。

Deparesing

若要生成计划树, 规划器将从外部表的计划树扫描路径创建纯文本 SQL语句。例如, 图3 显示了以下 SELECT 语句的计划树。

localdb=# SELECT * FROM tbl_a AS a WHERE a.id < 10;

图3 显示了从 PlannedStmt的计划树链接的 “外部扫描” 节点,存储了SELECT纯文本。在这里, postgres_fdw通过语法分析和语义分析创建的查询树中重新创建了一个纯SELECT文本, 该文本在 PostgreSQL 中称为 “deparesing”。

使用mysql_fdw从查询树中重新创建MySQL的SELECT文本。使用redis_fdw 或rw_redis_fdw创建一个SELECT命令。

发送 SQL 语句和接收结果

在deparesing之后，执行器将deparsed SQL语句发送到远程服务器并接收结果。

将 SQL 语句发送到远程服务器的方法取决于每个扩展的开发人员。例如, mysql_fdw 在不使用事务的情况下发送 SQL 语句。下面显示了在 mysql_fdw 中执行 SELECT 查询的 SQL 语句的典型顺序 (图 4)。

(5-1) 设置 SQL_MODE为 ‘ANSI_QUOTES’。
(5-2) 发送SELECT语句到远程服务。
(5-3)从远程服务接受结果。

在这里，mysql_fdw将结果转换为PostgreSQL的可读数据。

所有FDW扩展都实现了将结果转换为PostgreSQL可读数据的功能。

在postgres_fdw中，SQL命令的顺序很复杂。在postgres_fdw中执行SELECT查询的典型SQL语句序列如下所示（图4.5）

(5-1)开始一个远程事物
默认的远程事务隔离级别是REPEATABLE READ;如果本地事务的隔离级别设置为SERIALIZABLE，则远程事务也会设置为SERIALIZABLE。

(5-2)-(5-4) 声明一个游标
SQL语句基本上作为游标执行

(5-5)执行FETCH命令获取结果
默认，FETCH命令获取100行

(5-6) 从远端服务接受结果
(5-7) 关闭游标
(5-8) 提交远程事物

找到远程服务器的实际日志如下所示

LOG:  statement: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
LOG:  parse : DECLARE c1 CURSOR FOR SELECT id, data FROM public.tbl_a WHERE ((id < 10))
LOG:  bind : DECLARE c1 CURSOR FOR SELECT id, data FROM public.tbl_a WHERE ((id < 10))
LOG:  execute : DECLARE c1 CURSOR FOR SELECT id, data FROM public.tbl_a WHERE ((id < 10))
LOG:  statement: FETCH 100 FROM c1
LOG:  statement: CLOSE c1
LOG:  statement: COMMIT TRANSACTION

postgres_fdw中的默认远程事务隔离级别

官方文档中提供了有关默认远程事务隔离级别为REPEATABLE READ的原因的说明：

当本地事务使用SERIALIZABLE隔离级别时，远程事务也使用SERIALIZABLE隔离级别;否则它使用REPEATABLE READ隔离级别。此选择可确保在远程服务器上执行多表扫描查询，它将为所有扫描获得一致性快照的结果。结果是，一个事务中连续发出相同的查询，也会看到来自远程服务器的相同的数据，即使在远程服务器上有其他活动事物发生的并发更新。

Postgres_fdw扩展如何执行

postgres_fdw扩展是一个特殊的模块，由PostgreSQL全球开发小组正式维护，其源代码包含在PostgreSQL源代码树中。

postgres_fdw逐渐得到改善。表1列出了官方文档中与postgres_fdw相关的发行说明。

表1：与postgres_fdw有关的发行说明（引自官方文件）

鉴于前一节描述了postgres_fdw如何处理单表查询，以下章节将介绍postgres_fdw如何处理多表查询，排序操作和聚合函数。

本小节重点介绍SELECT语句；但是，postgres_fdw还可以处理其他DML语句（INSERT，UPDATE和DELETE），如下所示。

PostgreSQL的FDW没有检测到死锁

postgres_fdw和FDW功能不支持分布式锁管理器和分布式死锁检测功能。因此，很容易发生死锁。例如，如果在Client_A中更新本地表’tbl_local’和外部表’tbl_remote’，在Client_B中更新’tbl_remote’和’tbl_local’，则这两个事务处于死锁但PostgreSQL无法检测到。因此，无法提交这些事物。

localdb=# -- Client A
localdb=# BEGIN;
BEGIN
localdb=# UPDATE tbl_local SET data = 0 WHERE id = 1;
UPDATE 1
localdb=# UPDATE tbl_remote SET data = 0 WHERE id = 1;
UPDATE 1

localdb=# -- Client B
localdb=# BEGIN;
BEGIN
localdb=# UPDATE tbl_remote SET data = 0 WHERE id = 1;
UPDATE 1
localdb=# UPDATE tbl_local SET data = 0 WHERE id = 1;
UPDATE 1

多表查询

要执行多表查询，postgres_fdw使用单表SELECT语句获取每个外部表，然后在本地服务器上将它们连接起来。
在9.5或更早版本中，即使外部表存储在同一个远程服务器中，postgres_fdw也会分别获取它们并连接它们。
在版本9.6或更高版本中，postgres_fdw得到改进，并且当外部表都同一服务器上时,而且use_remote_estimate选项打开时，可以在远程服务器上执行远程连接操作。

排序操作

在9.5或更早版本中，在本地服务器上处理排序操作（例如ORDER BY），即本地服务器在排序操作之前从远程服务器获取所有目标行。
在版本9.6或更高版本中，postgres_fdw尽可能的在远程服务器上使用ORDER BY子句执行SELECT语句。

聚合函数

在版本9.6或更早版本中，类似于前一小节中提到的排序操作，AVG()和cont() 等聚合函数将在本地服务器上处理。
在版本10或更高版本中，postgres_fdw将尽可能的在远程服务器上执行带有聚合函数的SELECT语句。

整理自：
https://www.interdb.jp/pg/pgsql04.html

这篇关于《postgresql指南--内幕探索》第四章 外部数据包装器的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！