上篇 blog 介绍了外部表模式的导入，详见 PostgreSQL9.5: IMPORT FOREIGN SCHEMA ，9.5 版本还支持外部表继承本地表，也支持本地表继承外部表，这篇博客演示外部表继承本地表。
环境准备
外部表 test_1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| [pg95@db1 ~]$ psql fdb fdb
psql (9.5alpha1)
Type "help" for help.
fdb=> /dE test_1
List of relations
Schema | Name | Type | Owner
--------+--------+---------------+-------
fdb | test_1 | foreign table | fdb
(1 row)
fdb=> select count(*) from test_1;
count
---------
3000000
(1 row)
|
备注： fdb 库中已有表名为 test_1 的外部表。
外部表继承本地表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| fdb=> create table test_master ( like test_1 including all ) ;
CREATE TABLE
fdb=> ALTER TABLE test_1 INHERIT test_master;
ALTER TABLE
fdb=> /d+ test_master
Table "fdb.test_master"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
id | integer | | plain | |
name | text | | extended | |
Child tables: test_1
fdb=> select count(*) from test_master;
count
---------
3000000
(1 row)
|
备注： 创建本地表 test_master，并让 test_master 继承外部表 test_1。
在本地表 test_master 插入一条数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| fdb=> insert into test_master(id,name) values (1,'master');
INSERT 0 1
fdb=> select * from test_master where id=1;
id | name
----+--------
1 | master
1 | 1a
(2 rows)
fdb=> select count(*) from test_master;
count
---------
3000001
fdb=> select tableoid::regclass, * from test_master where id=1;
tableoid | id | name
-------------+----+--------
test_master | 1 | master
test_1 | 1 | 1a
(2 rows)
|
执行计划
1
2
3
4
5
6
7
8
9
10
11
| fdb=> explain analyze select tableoid::regclass, * from test_master where id=1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..128.71 rows=7 width=40) (actual time=0.034..0.816 rows=2 loops=1)
-> Append (cost=0.00..128.71 rows=7 width=40) (actual time=0.031..0.810 rows=2 loops=1)
-> Seq Scan on test_master (cost=0.00..2.59 rows=1 width=40) (actual time=0.029..0.031 rows=1 loops=1)
Filter: (id = 1)
-> Foreign Scan on test_1 (cost=100.00..126.12 rows=6 width=40) (actual time=0.772..0.773 rows=1 loops=1)
Planning time: 0.252 ms
Execution time: 1.520 ms
(7 rows)
|
备注：外部表的继承用法为 PostgreSQL 的分片方案提供了思路，有兴趣的同学可以看看下面这篇通过外部表扩展的博客。
参考
原创文章，作者：506227337，如若转载，请注明出处：https://blog.ytso.com/239628.html
