PostgreSQL 外部表

#PostgreSQL 外部表

1 背景知识

1.1 FDW 是什么？

1、FDW （foreing Data Wrappers)外部数据封装器。
2、外部异构的数据库进行交互。
3、用于数据迁移。
4、支持多表链接统计计数。
database
FDW
data dictionary
External TableMetadata
database
table
External Files
查询外部表

外部表支持的|400

1.2 FDW 发展历史

Version Description
9.3 postgres_fdw module is released.
9.6 Consider performing sorts on the remote server.
9.6 Consider performing joins on the remote server.
9.6 When feasible, perform UPDATE or DELETE entirely on the remote server.（只是针对pg ，Oracle 改不了
9.6 Allow the fetch size to be set as a server or table option.
10 Push aggregate functions to the remote server, when possible . （Oracle 不支持）

1.3 FDW 出现死锁

FDW 不会检测死锁，因为无法分布式锁机制是缺失的。

2 FDW原理

2.1 FDW SQL 执行步骤

外部表的SQL 解析步骤|400

  1. Parser解析器： 解析后生成解析树。
  2. Analyze/Analyser 分析器：根据语法树解析后生成查询树。
    1. 根据 pg_catalog.pg_class 和 pg_catalog.pg_foreign_table 在这个步骤去判断是否是外部表。
    2. 根据上面的信息，生成查询树。
  3. Rewriter 重写器：根据查询树重写语句。
  4. Planner 规划器 ：生成执行计划。
    1. 根据 pg_catalog.pg_foregin_server 和 pg_catalog.pg_user_mapping 去判断表存在哪里
    2. 发送重写后的语句到remote 服务器生成执行计划。
    3. 获得 remote server SQL 执行的代价。目的是为了获得最佳的执行计划。（是在本地执行数据处理，还是 remote 执行数据处理）。
      。
  5. 执行器：根据执行计划进行执行，发送执行语句到 remote ，返回数据。

执行计划的产生，需要判断在local 执行，还是在remote 执行。

  1. case1：remote server 将数据传回本地后再处理。
  2. case2： remote server 处理完之后再传回local。

3 FDW 单表查询

3.1 PG 9.5 以前

3.1.1 FDW 单表查询

9.5 FDW 详细步骤|400

3.1.2 FDW 后台执行原理

  1. （5-1）使用（REPEATABLE READ ）启动事务
  2. （5-2）声明一个游标
  3. （5-3）绑定游标变量
  4. （5-4）执行游标
  5. （5-5）获取数据（每次100条）
  6. （5-6） 返回数据
  7. （5-7）关闭游标
  8. （5-8）提交事务

3.2 PG 9.6 以后

3.2.1 FDW 单表查询

Local Sver 
Local Sver 
Local Sver 
Local Sver 
（5-1）START TRANSACTION ISOLATION LEVEL REPEATABLE READ
（5-1）START TRANSACTION ISOLATION LEVEL REPEATABLE READ
（5-2）EXPLAIN SELECT city_id, city, country_id, last_update FROM public.city
（5-2）EXPLAIN SELECT city_id, city, country_id, last_update FROM public.city
（5-3）DECLARE c1 CURSOR FOR
SELECT city_id, city, country_id, last_update FROM public.city
（5-3）DECLARE c1 CURSOR FOR...
（5-4）FETCH 100 FROM c1
（5-4）FETCH 100 FROM c1
（5-6）CLOSE c1
（5-6）CLOSE c1
（5-7）COMMIT TRANSACTION
（5-7）COMMIT TRANSACTION
（5-5）Send data
（5-5）Send dataText is not SVG - cannot display

3.2.2 后台执行原理

  1. （5-1）使用（REPEATABLE READ ）启动事务
  2. （5-2）生成执行计划
  3. （5-3）声明一个游标
  4. （5-4）获取数据（每次100条）
  5. （5-5） 返回数据
  6. （5-6）关闭游标
  7. （5-7）提交事务

4 FDW多表查询

4.1 PG 9.5 以前

9.5 FDW 多表查询|400

4.2 PG 9.6 以后

PG9.6 FDW 多表查询|400

5 FDW 排序（Sort)

5.1 PG 9.5 之前

Local Sver 
Local Sver 
Local Sver 
Local Sver 
（5-1）START TRANSACTION ISOLATION LEVEL REPEATABLE READ
（5-1）START TRANSACTION ISOLATION LEVEL REPEATABLE READ
（5-2）parse：DECLARE c1 CURSOR FOR
SELECT city_id, city, country_id, last_update FROM public.city
（5-2）parse：DECLARE c1 CURSOR FOR...
（5-5）FETCH 100 FROM c1
（5-5）FETCH 100 FROM c1
（5-7）CLOSE c1
（5-7）CLOSE c1
（5-8）COMMIT TRANSACTION
（5-8）COMMIT TRANSACTION
（5-6）Send data
（5-6）Send data
（5-3）bind：DECLARE c1 CURSOR FOR
SELECT city_id, city, country_id, last_update FROM public.city
（5-3）bind：DECLARE c1 CURSOR FOR...
（5-4）execute：DECLARE c1 CURSOR FOR
SELECT city_id, city, country_id, last_update FROM public.city
（5-4）execute：DECLARE c1 CURSOR FOR...
传输数据后，在本地进行排序
传输数据后，在本地进行排序Text is not SVG - cannot display

5.2 PG 9.6 以后

Local Sver 
Local Sver 
Local Sver 
Local Sver 
（5-1）START TRANSACTION ISOLATION LEVEL REPEATABLE READ
（5-1）START TRANSACTION ISOLATION LEVEL REPEATABLE READ
（5-4）parse：DECLARE c1 CURSOR FOR
SELECT city_id, city, country_id, last_update FROM public.city
（5-4）parse：DECLARE c1 CURSOR FOR...
（5-7）FETCH 100 FROM c1
（5-7）FETCH 100 FROM c1
（5-9）CLOSE c1
（5-9）CLOSE c1
（5-10）COMMIT TRANSACTION
（5-10）COMMIT TRANSACTION
（5-8）Send data
（5-8）Send data
（5-5）bind：DECLARE c1 CURSOR FOR
SELECT city_id, city, country_id, last_update FROM public.city
（5-5）bind：DECLARE c1 CURSOR FOR...
（5-6）execute：DECLARE c1 CURSOR FOR
SELECT city_id, city, country_id, last_update FROM public.city
（5-6）execute：DECLARE c1 CURSOR FOR...
传输数据后，在本地进行排序
传输数据后，在本地进行排序
（5-2）EXPLAIN SELECT country_id, country, last_update FROM public.country
（5-2）EXPLAIN SELECT country_id, country, last_update FROM public.country
（5-3）EXPLAIN SELECT country_id, country, last_update
 FROM public.country ORDER BY country ASC NULLS LAST
（5-3）EXPLAIN SELECT country_id, country, last_update...Text is not SVG - cannot display

6 PostgreSQL 实战

PostgreSQL FDW 安装与部署
PostgreSQL file Fdw