1. 应用场景

在软件开发或数据库运维过程，每一次数据库同步都是运维人员的痛苦的过程。

如果每次都是无脑全量更新，则工作量较大
如果每次都是增量更新，则需要知道哪些视图谁在什么时候修改了，增加了什么信息（因为往往时团队去搭建数据库，根据业务模块去构建数据库中的视图和物化视图）。

2. 写一个存储过程，获取每一次的增量更新的信息

将每一个时间段之内发生的视图和物化视图发生修改的信息，通过版本和一定的机制获取出来，然后通过列表去编写增量更新的脚本。

2.1. 每次增量更新表单信息

CREATE SEQUENCE tb_sys_mvv_definittion_id_seqINCREMENT 1MINVALUE 1MAXVALUE 9223372036854775807START 1CACHE 1 CYCLE;CREATE TABLE "tb_sys_mvw_definition" ("id" int4 NOT NULL DEFAULT nextval('tb_sys_mvv_definittion_id_seq'::regclass),"view_kindtype" varchar(30) COLLATE "pg_catalog"."default","view_name" varchar(100) COLLATE "pg_catalog"."default","view_definition" text COLLATE "pg_catalog"."default","view_lastversion_definition" text COLLATE "pg_catalog"."default","view_changestatus" varchar(20) COLLATE "pg_catalog"."default","view_time" timestamp(6),"view_hasindexs" bool
)
;
COMMENT ON COLUMN "tb_sys_mvw_definition"."id" IS 'ID';
COMMENT ON COLUMN "tb_sys_mvw_definition"."view_kindtype" IS '视图类型';
COMMENT ON COLUMN "tb_sys_mvw_definition"."view_name" IS '视图名称';
COMMENT ON COLUMN "tb_sys_mvw_definition"."view_definition" IS '视图定义';
COMMENT ON COLUMN "tb_sys_mvw_definition"."view_lastversion_definition" IS '历史版本视图定义';
COMMENT ON COLUMN "tb_sys_mvw_definition"."view_changestatus" IS '视图变化状态';
COMMENT ON COLUMN "tb_sys_mvw_definition"."view_time" IS '视图信息记录时点信息';
COMMENT ON COLUMN "tb_sys_mvw_definition"."view_hasindexs" IS '视图是否存在索引信息';-- ----------------------------
-- Primary Key structure for table tb_sys_mvw_definition
-- ----------------------------
ALTER TABLE "tb_sys_mvw_definition" ADD CONSTRAINT "tb_sys_mvv_definittion_pkey" PRIMARY KEY ("id");

2.2. 历史版本视图定义表

CREATE TABLE "tb_sys_mvw_definition_lastversion" ("id" int4 NOT NULL,"view_kindtype" varchar(30) COLLATE "pg_catalog"."default","view_name" varchar(100) COLLATE "pg_catalog"."default","view_definition" text COLLATE "pg_catalog"."default","view_lastversion_definition" text COLLATE "pg_catalog"."default","view_changestatus" varchar(20) COLLATE "pg_catalog"."default","view_time" timestamp(6),"view_hasindexs" bool
)
;
COMMENT ON COLUMN "tb_sys_mvw_definition_lastversion"."id" IS 'ID';
COMMENT ON COLUMN "tb_sys_mvw_definition_lastversion"."view_kindtype" IS '视图类型';
COMMENT ON COLUMN "tb_sys_mvw_definition_lastversion"."view_name" IS '视图名称';
COMMENT ON COLUMN "tb_sys_mvw_definition_lastversion"."view_definition" IS '视图定义';
COMMENT ON COLUMN "tb_sys_mvw_definition_lastversion"."view_lastversion_definition" IS '历史版本视图定义';
COMMENT ON COLUMN "tb_sys_mvw_definition_lastversion"."view_changestatus" IS '视图变化状态';
COMMENT ON COLUMN "tb_sys_mvw_definition_lastversion"."view_time" IS '视图信息记录时点信息';
COMMENT ON COLUMN "tb_sys_mvw_definition_lastversion"."view_hasindexs" IS '视图是否存在索引信息';-- ----------------------------
-- Primary Key structure for table tb_sys_mvw_definition_lastversion
-- ----------------------------
ALTER TABLE "tb_sys_mvw_definition_lastversion" ADD CONSTRAINT "tb_sys_mvv_definition_lastversion_pkey" PRIMARY KEY ("id");

2.3. 版本比对存储过程

CREATE OR REPLACE FUNCTION "public"."fun_sync_operation_mvw_log"(OUT "runstate" bool)RETURNS "pg_catalog"."bool" AS $BODY$
declarecurrent_schema_name TEXT;
beginRunState=True;SELECT current_schema INTO current_schema_name;EXECUTE 'delete from tb_sys_mvw_definition_lastversion';EXECUTE 'insert into tb_sys_mvw_definition_lastversion select * from tb_sys_mvw_definition';EXECUTE 'delete from tb_sys_mvw_definition';EXECUTE 'insert into tb_sys_mvw_definition(view_kindtype,view_name,view_definition,view_lastversion_definition,view_changestatus,view_time,view_hasindexs)select ''MaterializedView'',matviewname,definition,'''',''Add'',now(),hasindexes from pg_matviews where schemaname='''||current_schema_name||''' and matviewname not in (select view_name from tb_sys_mvw_definition_lastversion where view_kindtype=''MaterializedView'')union allselect ''View'',viewname,definition,'''',''Add'',now(),false from pg_views where schemaname='''||current_schema_name||''' and viewname not in (select view_name from tb_sys_mvw_definition_lastversion where view_kindtype=''View'')union allselect view_kindtype,view_name,'''',view_definition,''Drop'',view_time,False from tb_sys_mvw_definition_lastversion where view_kindtype=''MaterializedView'' and view_name not in (select matviewname from pg_matviews)union allselect view_kindtype,view_name,'''',view_definition,''Drop'',view_time,False from tb_sys_mvw_definition_lastversion where view_kindtype=''View'' and view_name not in (select viewname from pg_views)union allselect view_kindtype,view_name,pg_matviews.definition,tb_sys_mvw_definition_lastversion.view_definition,case when pg_matviews.definition=tb_sys_mvw_definition_lastversion.view_definition then ''UnChanged'' else ''Alter'' end,view_time,pg_matviews.hasindexes from tb_sys_mvw_definition_lastversion,pg_matviews where view_kindtype=''MaterializedView'' and tb_sys_mvw_definition_lastversion.view_name =pg_matviews.matviewname union allselect view_kindtype,view_name,pg_views.definition,tb_sys_mvw_definition_lastversion.view_definition,case when pg_views.definition=tb_sys_mvw_definition_lastversion.view_definition then ''UnChanged'' else ''Alter'' end,view_time,Falsefrom tb_sys_mvw_definition_lastversion,pg_views where view_kindtype=''View'' and tb_sys_mvw_definition_lastversion.view_name =pg_views.viewname 'Return;
end;
$BODY$LANGUAGE plpgsql VOLATILECOST 100

3. 实际使用

3.1. 版本1：创建了一个视图，创建了一个物化视图

create MATERIALIZED view mvc__dic_aaa
as
select now();create view vw_dic_aaa
as
select now();select fun_sync_operation_mvw_log();select * from tb_sys_mvw_definition order by id

在这里插入图片描述

3.2. 版本2：修改视图的基本信息

drop  MATERIALIZED view mvc__dic_aaa;create  MATERIALIZED view mvc__dic_aaa
as
select now() as date_into1,now() as date_into2;drop view vw_dic_aaa;create  view vw_dic_aaa
as
select now() as date_into1,now() as date_into2;select fun_sync_operation_mvw_log();select * from tb_sys_mvw_definition where view_changestatus='Alter' order by id ;

在这里插入图片描述

3.3. 删除物化视图

drop  MATERIALIZED view mvc__dic_aaa;drop view vw_dic_aaa;select fun_sync_operation_mvw_log();select * from tb_sys_mvw_definition where view_changestatus='Drop' order by id ;