PostgreSQL之 异地物化视图（基于外部表）

作者：瀚高PG实验室 （Highgo PG Lab）- 波罗
首先在192.168.100.200上创建extension

postgres=# create extension postgres_fdw;
CREATE EXTENSION

在200上创建server

postgres=# create server pg154 foreign data wrapper postgres_fdw options(hostaddr '192.168.100.154', dbname 'postgres', port '5432');
CREATE SERVER

创建user mapping

postgres=# create user mapping for postgres server pg154 options(user 'postgres', password 'postgres');
CREATE USER MAPPING

在另一台机器（pg v9.3.1 IP192.168.100.154）创建测试表基表

postgres=# create table userinfo(id int primary key, info text, crt_time timestamp);
CREATE TABLE

插入测试记录

postgres=# insert into userinfo select generate_series(1,100000), md5(random()::text), clock_timestamp();
INSERT 0 100000

在100.200创建外部表

postgres=# create foreign table ft_userinfo(id int, info text, crt_time timestamp) server pg154 options(schema_name 'public', table_name 'userinfo');
CREATE FOREIGN TABLE

查询：

postgres=# select * from ft_userinfo limit 2;
 id |               info               |          crt_time          
----+----------------------------------+----------------------------
  1 | 846b0023e543879ff827efb0f15ecbd4 | 2018-01-02 10:21:42.203501
  2 | 95a04631dae89fce67441e2d2af4e88f | 2018-01-02 10:21:42.204296
(2 rows)

创建物化视图

postgres=# create materialized view mv_userinfo as select * from ft_userinfo with no data;
SELECT 0

全量刷新物化视图

postgres=# refresh materialized view mv_userinfo with data;
REFRESH MATERIALIZED VIEW

创建唯一索引

postgres=# create unique index idx_mv_userinfo_id on mv_userinfo(id);
CREATE INDEX

增量刷新物化视图
postgres=# refresh materialized view concurrently mv_userinfo with data;
REFRESH MATERIALIZED VIEW

postgres=# \d tbl
                 Table "public.tbl"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 id       | integer                     | not null
 info     | text                        | 
 crt_time | timestamp without time zone | 
Indexes:
    "tbl_pkey" PRIMARY KEY, btree (id)

postgres=# alter table tbl drop constraint tbl_pkey;
ALTER TABLE

此时也是可以增量刷新物化视图
postgres=# refresh materialized view concurrently mv_tbl1 ;

REFRESH MATERIALIZED VIEW

增量刷新能否刷新判断的是物化视图上有没有唯一索引，并不关注基表上有没有唯一索引

物化视图不允许refresh、vacuum、analyze以及select以外的操作。
在pg_rewrite中限制了：hasForUpdate false

postgres=# delete from mv_tbl;
ERROR:  cannot change materialized view "mv_tbl"
postgres=# analyze mv_tbl;
ANALYZE
postgres=# vacuum mv_tbl;
VACUUM

postgres=# update mv_tbl set id=1 where id=2;
ERROR:  cannot change materialized view "mv_tbl"
postgres=# insert into mv_tbl(id) values(1);
ERROR:  cannot change materialized view "mv_tbl"

postgres=# select * from pg_rewrite where ev_class='mv_tbl'::regclass;
-[ RECORD 1 ]
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
rulename   | _RETURN
ev_class   | 16445
ev_type    | 1
ev_enabled | O
is_instead | t
ev_qual    | <>
ev_action  | ({QUERY :commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs false :hasWindowFuncs false :hasSubLinks false :hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :cteList <> :rtable ({RTE :alias {ALIAS :aliasname old :colnames <>} :eref {ALIAS :aliasname old :colnames ("id" "info" "crt_time")} :rtekind 0 :relid 16445 :relkind m :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :modifiedCols (b) :securityQuals <>} {RTE :alias {ALIAS :aliasname new :colnames <>} :eref {ALIAS :aliasname new :colnames ("id" "info" "crt_time")} :rtekind 0 :relid 16445 :relkind m :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :modifiedCols (b) :securityQuals <>} false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :modifiedCols (b) :securityQuals <>} {RTE :alias {ALIAS :aliasname new :colnames <>} :eref {ALIAS :aliasname new :colnames ("id" "info" "crt_time")} :rtekind 0 :relid 16445 :relkind m :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :modifiedCols (b) :securityQuals <>} {RTE :alias <> :eref {ALIAS :aliasname tbl :colnames ("id" "info" "crt_time")} :rtekind 0 :relid 16435 :relkind r :lateral false :inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 9 10 11) :modifiedCols (b) :securityQuals <>}) :jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 3}) :quals {OPEXPR :opno 97 :opfuncid 66 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 1 :location 59} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 62 :constvalue 4 [ -24 3 0 0 0 0 0 0 ]}) :location 61}} :targetList ({TARGETENTRY :expr {VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 1 :location 42} :resno 1 :resname id :ressortgroupref 0 :resorigtbl 16435 :resorigcol 1 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :varattno 2 :vartype 25 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnoold 3 :varoattno 2 :location 42} :resno 2 :resname info :ressortgroupref 0 :resorigtbl 16435 :resorigcol 2 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :varattno 3 :vartype 1114 :vartypmod -1 :varcollid0 :varlevelsup 0 :varnoold 3 :varoattno 3 :location 42} :resno 3 :resname crt_time :ressortgroupref 0 :resorigtbl 16435 :resorigcol 3 :resjunk false}) :withCheckOptions <> :returningList <> :groupClause <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :rowMarks <> :setOperations <> :constraintDeps <>})