AnalyticDB for PostgreSQL 6 新特性解析 - FDW
随着AnalyticDB for PostgreSQL 6（下文简称 ADBPG v6）发布公测，ADBPG v6内核进入PostgreSQL 9.4时代，而PG内核的升级给ADBPG带来更多新的特性，本文简要解析Foreign Data Wrapper（FDW）。
1. SQL/MED
在正式说明FDW之前，我们需要了解FDW的由来，想要了解FDW，那就不得不说SQL/MED。
- SQL/MED ("SQL Management of External Data"，SQL标准ISO/IEC 9075-9)，是最早在2001年提出的用于集成管理外部数据的SQL标准ISO/IEC 9075-9:2001
- 最新发布版本为ISO/IEC 9075-9:2016
-
主要包含两部分主题：
- Foreign Table a transparent access method for external data
- DATALINK a special SQL type intended to store URLs in database
- Foreign Data Wrapper 是SQL/MED标准的一部分，定义访问外部数据源的接口，见下图：
2. FDW for PostgreSQL
2.1 演进
- 2008年，PostgreSQL为适配SQL/MED标准，提出设计草案：，并于PostgreSQL 8.4版本开始逐步实现。
- 2009年，PostgreSQL用户大会时，Peter Eisentraut的演讲说明当时PostgreSQL支持SQL/MED的现状与计划：
- 2011年，PostgreSQL 9.1支持只读方式访问外表（外表查询）
- 2013年，PostgreSQL 9.3支持可写方式访问外表（外表插入/更新/删除）
- ... ...
2.2 概况
- 直至今日，PostgreSQL Global Development Group (PGDG)仅支持：file_fdw 和 postgre_fdw。
- 更多的FDW可以在the PGXN website找到，但因不是官方支持的版本，需小心使用，更详细的说明见WIKI。
2.3 核心
大体来说，PostgreSQL外表功能的核心实现可以简要概括如下：
- 以 _USER MAPPING _用户（CREATE USER MAPPING）
- 通过 _FOREIGN DATA WRAPPER _方式（CREATE FOREIGN DATA WRAPPER）
- 操作在 _FOREIGN SERVER_（CREATE SERVER）上
- 定义的 _FOREIGN TABLE_（CREATE FOREIGN TABLE）
2.4 不同
在实现外表功能的语法上，PostgreSQL与ISO/IEC 9075-9标准主要有如下不同：
- CREATE FOREIGN DATA WRAPPER
- 语法上扩展了HANDLER和VALIDATOR从句
- 标准里定义的LIBRARY和LANGUAGE从句并未实现
- CREATE FOREIGN TABLE
- 绝大部分兼容 ISO/IEC 9075-9 (SQL/MED)标准
- 允许字段空值约束
- 允许无字段创建外表
- 字段允许设定默认值也是PostgreSQL的一个功能扩展
3. FDW in ADBPG v6.0
3.1 概况
- ADBPG v6中的FDW与社区PostgreSQL基本一致，可以像在PostgreSQL中一样在ADBPG v6中使用FDW功能。需要注意的是：postgres_fdw只能通过master而不能直接通过segment直接访问ADBPG。
- 语法上，CREATE FOREIGN TABLE，CREATE SERVER 和 CREATE FOREIGN DATA WRAPPER 基本与社区PostgreSQL一致，只是额外增加mpp_execute属性，用于mpp query时，决定通过哪个主机获取外部数据，默认为‘master’。mpp_execute属性的优先级为：FOREIGN TABLE > FOREIGN SERVER > FOREIGN DATA WARPPER
3.2 示例
多说无益，以postgres_fdw为例演示FDW在ADBPG v6.0中的使用。
- 编译postgres_fdw（当前默认不编译）。
- 在保证网络可服务的前提下，在10.101.194.174上搭建集群A，在100.81.152.195上搭建集群B，配置如下：
集群A：10.101.194.174
postgres=# select * from gp_segment_configuration ;
dbid | content | role | preferred_role | mode | status | port | hostname | address | datadir
------+---------+------+----------------+------+--------+-------+------------------+------------------+--------------------------------------------------------------
1 | -1 | p | p | n | u | 19600 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/qddir/demoDataDir-1
2 | 0 | p | p | s | u | 19650 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/dbfast1/demoDataDir0
5 | 0 | m | m | s | u | 19653 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/dbfast_mirror1/demoDataDir0
3 | 1 | p | p | s | u | 19651 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/dbfast2/demoDataDir1
6 | 1 | m | m | s | u | 19654 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/dbfast_mirror2/demoDataDir1
4 | 2 | p | p | s | u | 19652 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/dbfast3/demoDataDir2
7 | 2 | m | m | s | u | 19655 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/dbfast_mirror3/demoDataDir2
8 | -1 | m | m | s | u | 19610 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/standby
(8 rows)集群B：100.81.152.195
postgres=# select * from gp_segment_configuration ;
dbid | content | role | preferred_role | mode | status | port | hostname | address | datadir
------+---------+------+----------------+------+--------+-------+-------------------+-------------------+--------------------------------------------------------
1 | -1 | p | p | n | u | 15432 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/qddir/demoDataDir-1
2 | 0 | p | p | s | u | 18000 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/dbfast1/demoDataDir0
5 | 0 | m | m | s | u | 18003 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/dbfast_mirror1/demoDataDir0
3 | 1 | p | p | s | u | 18001 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/dbfast2/demoDataDir1
6 | 1 | m | m | s | u | 18004 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/dbfast_mirror2/demoDataDir1
4 | 2 | p | p | s | u | 18002 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/dbfast3/demoDataDir2
7 | 2 | m | m | s | u | 18005 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/dbfast_mirror3/demoDataDir2
8 | -1 | m | m | s | u | 16432 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/standby
(8 rows)- 以集群B（100.81.152.195）作为外部数据源，配置pg_hba.conf文件后reload。
local all all trust
host all all ::1/128 trust
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5- 设置集群B const用户的登陆密码（1234）。
postgres=# \password const
Enter new password:
Enter it again:- 在集群B（100.81.152.195）的public schema下分别创建表x，y。
postgres=# create table x(id int, value float8, des text) distributed by (id);
CREATE TABLE
postgres=# create table y(id int, value float8, des text) distributed by (id);
CREATE TABLE
postgres=# insert into x select r, r*random(), md5((r*random())::text) from generate_series(1,1000000)r;
INSERT 0 1000000
postgres=# insert into y select r, r*random(), md5((r*random())::text) from generate_series(1,1000)r;
INSERT 0 1000- 准备在集群A（10.101.194.174）中创建外表 foreign_x 和 foreign_y分别对应集群B中的表x和y。
-- 创建postgres_fdw
CREATE EXTENSION postgres_fdw;
-- 创建foreign server
create server foreign_server_195
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS(host '100.81.152.195', port '15432', dbname 'postgres');
-- 创建user mapping
CREATE USER MAPPING FOR current_user
SERVER foreign_server_195
OPTIONS (user 'const', password '1234');
-- 创建foreign table foreign_x
CREATE FOREIGN TABLE foreign_x
(
id int,
value float8,
des text
)
SERVER foreign_server_195
OPTIONS (schema_name 'public', table_name 'x');
-- 创建foreign table foreign_y
CREATE FOREIGN TABLE foreign_y
(
id int,
value float8,
des text
)
SERVER foreign_server_195
OPTIONS (schema_name 'public', table_name 'y');
-- 查看foreign server
postgres=# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
--------------------+-------+----------------------+-------------------+------+---------+----------------------------------------------------------+-------------
foreign_server_195 | const | postgres_fdw | | | | (host '100.81.152.195', port '15432', dbname 'postgres') |
(1 row)
-- 查看foreign table
postgres=# \det+
List of foreign tables
Schema | Table | Server | FDW Options | Description
--------+-----------+--------------------+----------------------------------------+-------------
public | foreign_x | foreign_server_195 | (schema_name 'public', table_name 'x') |
public | foreign_y | foreign_server_195 | (schema_name 'public', table_name 'y') |
(2 rows)
-- 查询foreign_x
postgres=# select count(1) from foreign_x;
count
--------
1000000
(1 row)
-- 设置mpp_execute = all segments
postgres=# alter foreign table foreign_x options (add mpp_execute 'all segments');
ALTER FOREIGN TABLE
postgres=# \det+
List of foreign tables
Schema | Table | Server | FDW Options | Description
--------+-----------+--------------------+----------------------------------------+-------------
public | foreign_x | foreign_server_195 | (schema_name 'public', table_name 'x', mpp_execute 'all segments') |
public | foreign_y | foreign_server_195 | (schema_name 'public', table_name 'y') |
(2 rows)
-- 再次查询foreign_x (3 segments)
postgres=# select count(1) from foreign_x;
count
--------
3000000
(1 row)4. 参考
- https://wiki.postgresql.org/wiki/Foreign_data_wrappers
- https://wiki.postgresql.org/wiki/SQL/MED
- https://www.pgcon.org/2009/schedule/events/142.en.html
- https://wiki.postgresql.org/wiki/DATALINK
- https://wiki.postgresql.org/wiki/SqlMedConnectionManager
- https://www.iso.org/standard/63476.html
- https://www.postgresql.org/docs/current/unsupported-features-sql-standard.html
- https://gpdb.docs.pivotal.io/6-0/admin_guide/external/g-foreign.html
- https://yq.aliyun.com/articles/713076
