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

社区PostgreSQL对于SQL/MED标准的支持情况见下图：

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标准主要有如下不同：

AnalyticDB for PostgreSQL 6 新特性解析 - FDWAnalyticDB for PostgreSQL 6 新特性解析 - FDW1. SQL/MED2. FDW for PostgreSQL3. FDW in ADBPG v6.04. 参考

语法上扩展了HANDLER和VALIDATOR从句

标准里定义的LIBRARY和LANGUAGE从句并未实现

AnalyticDB for PostgreSQL 6 新特性解析 - FDWAnalyticDB for PostgreSQL 6 新特性解析 - FDW1. SQL/MED2. FDW for PostgreSQL3. FDW in ADBPG v6.04. 参考

绝大部分兼容 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。
语法上，，和基本与社区PostgreSQL一致，只是额外增加mpp_execute属性，用于mpp query时，决定通过哪个主机获取外部数据，默认为‘master’。mpp_execute属性的优先级为：FOREIGN TABLE > FOREIGN SERVER > FOREIGN DATA WARPPER

AnalyticDB for PostgreSQL 6 新特性解析 - FDWAnalyticDB for PostgreSQL 6 新特性解析 - FDW1. SQL/MED2. FDW for PostgreSQL3. FDW in ADBPG v6.04. 参考

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)

AnalyticDB for PostgreSQL 6 新特性解析 - FDWAnalyticDB for PostgreSQL 6 新特性解析 - FDW1. SQL/MED2. FDW for PostgreSQL3. FDW in ADBPG v6.04. 参考

AnalyticDB for PostgreSQL 6 新特性解析 - FDW

1. SQL/MED

2. FDW for PostgreSQL

2.1 演进

2.2 概况

2.3 核心

2.4 不同

3. FDW in ADBPG v6.0

3.1 概况

3.2 示例

4. 参考

继续阅读

JDBC连接数据库（statement）

MyBatis-Plus 之AR模式

ASP编程经典例子

ASP编程中20个非常有用的例子

龙珠训练营task04

阿里云天池龙珠计划SQL训练营打卡

阿里云天池龙珠计划SQL训练营day1

实验楼sql进阶之成绩管理系统的数据操作(window)

Oracle的基本操作

为什么要选择UniDAC

SQL优化SQL语句优化的目的

JAVA高效编程指南

关于SQL语言

SQL语言基础：常用的数据查询语句

neo4j之cypher使用文档

sqlServer根据经纬查距离