postgres_fdw

主要SQL语句

（来自PostgreSQL 10.3）

创建server

create server server_remote1 FOREIGN data wrapper postgres_fdw

OPTIONS(host '127.0.0.1', port '55100', dbname 'db1');

创建user mapping

create user mapping for wln server server_remote options(user 'wln',password 'password');

创建源表， 在 db1 中创建 表 t1：

create table t1(id1 int);

insert into t1 select generate_series(1,10);

创建外表

CREATE FOREIGN TABLE t_resource (id1 int) server server_remote options (table_name 't1');

测试

postgres=# select * from pg_foreign_server ;

srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions

---------------+----------+--------+---------+------------+--------+----------------------------------------

server_remote | 10 | 24639 | | | | {host=127.0.0.1,port=55100,dbname=db1}

(1 row)

postgres=# select * from pg_foreign_data_wrapper ;

fdwname | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions

--------------+----------+------------+--------------+--------+------------

postgres_fdw | 10 | 24637 | 24638 | |

(1 row)

postgres=# explain verbose select * from t_resource ;

QUERY PLAN

----------------------------------------------------------------------------

Foreign Scan on public.t_resource (cost=100.00..197.75 rows=2925 width=4)

Output: id1

Remote SQL: SELECT id1 FROM public.t2

(3 rows)

postgres=# explain verbose update t_resource set id1 = id1*10 where id1 <2;

QUERY PLAN

------------------------------------------------------------------------------------

Update on public.t_resource (cost=100.00..158.18 rows=803 width=10)

-> Foreign Update on public.t_resource (cost=100.00..158.18 rows=803 width=10)

Remote SQL: UPDATE public.t1 SET id1 = (id1 * 10) WHERE ((id1 < 2))

(3 rows)

postgres=# explain verbose insert into t_resource values(12);

QUERY PLAN

---------------------------------------------------------------

Insert on public.t_resource (cost=0.00..0.01 rows=1 width=4)

Remote SQL: INSERT INTO public.t1(id1) VALUES ($1)

-> Result (cost=0.00..0.01 rows=1 width=4)

Output: 12

(4 rows)

问题

创建和普通表同名的外表，会产生死循环。

参考

postgres 官方文档

https://www.postgresql.org/docs/current/static/postgres-fdw.html

PG&GP · 特性分析 · 外部数据导入接口实现分析

https://yq.aliyun.com/articles/51199?spm=5176.100239.blogcont3074.10.d6nVdg

PostgreSQL的postgres_fdw跨库使用

https://my.oschina.net/Kenyon/blog/214953