HighGoDB物化视图

HighGoDB物化视图

1.概述

物化视图的使用规则和视图是一样的，和视图不同的是，物化视图将结果集持久化在表中。

一个物化视图对应一个SQL语句，查询时去对应的结果集表查询。

2.创建表

create table table0(a varchar,b varchar,c varchar);
insert into table0 values ('1','11','12');

3.创建物化视图

create materialized view table0_view as select a,b,c from table0;
select * from table0_view;
 a | b  | c  
---+----+----
 1 | 11 | 12
(1 行记录)

4.物化视图手动刷新

insert into table0 values ('2','21','22');
refresh materialized view table0_view;
select * from table0_view;
 a | b  | c  
---+----+----
 1 | 11 | 12
 2 | 21 | 22
(2 行记录)

5.物化视图自动刷新

5.1.触发器实现

第一步：创建函数。新版的物化视图新增了 concurrently 参数，可以使在刷新视图时不会锁住该物化视图的查询工作。

create or replace function tri_table0_func() returns trigger as $$ 
declare
begin 
  refresh materialized view concurrently table0_view with data; 
  return null; 
end; 
$$ language plpgsql;

第二步：创建触发器，当表有插入更新删除操作时，触发函数。刷新物化视图。

create trigger tri_table0 after insert or update or delete on table0
for each statement execute procedure tri_table0_func();

测试

insert into table0 values ('3','31','32');
错误:  不能同时刷新物化视图 "sysdba.table0_view"
提示:  在物化视图的一个或多个列上创建不带WHERE子句的唯一索引.
背景:  SQL 语句 "refresh materialized view concurrently table0_view with data"
在SQL语句的第4行的PL/pgSQL函数tri_table0_func()

创建唯一索引
create unique index on table0_view(a);

insert into table0 values ('3','31','32');
INSERT 0 1
select * from table0_view;
 a | b  | c  
---+----+----
 1 | 11 | 12
 2 | 21 | 22
 3 | 31 | 32
(3 行记录)

5.2.定时任务（pg_cron）自动刷新物化视图

pg_cron 是一个简单的基于 cron 的作业调度程序，它在数据库中作为扩展运行。它使用与常规 cron 相同的语法，但它允许你直接从数据库调度HighGoDB命令。

每一个定时任务分为两部分：

定时计划

规定使用插件的计划，例如每隔1分钟执行一次该任务。

定时计划使用标准的cron语法，其中*表示任意时间都运行，特定数字表示仅在这个时间时运行。

---

┌───────────── 分钟： 0 ~ 59

│ ┌────────────── 小时： 0 ~ 23

│ │ ┌─────────────── 日期： 1 ~ 31

│ │ │ ┌──────────────── 月份： 1 ~ 12

│ │ │ │ ┌───────────────── 一周中的某一天 ：0 ~ 6，0表示周日。

│ │ │ │ │

│ │ │ │ │

│ │ │ │ │
* * * * *

---

例如每周六3:30am（GMT）的语法为：

30 3 * * 6

** 定时任务**

用户具体的任务内容，例如select * from some_table。

第一步：将pg_cron加入到shared_preload_libraries参数中

shared_preload_libraries = ‘pg_cron’

cron.database_name = ‘highgo’

第二步：创建扩展

create extension pg_cron;

第三步：创建任务

SELECT cron.schedule('<定时计划>', '<定时任务>')
-- 每个小时的23分执行指定脚本。
SELECT cron.schedule('23 * * * *', 'select 1;');
-- 每分钟执行指定脚本。
SELECT cron.schedule('* * * * *', 'select 1;');

SELECT cron.schedule(‘* * * * *’, ‘refresh materialized view concurrently table0_view with data;’);

查看当前任务列表

select * from cron.job;

highgo=# select * from cron.job;
-[ RECORD 1 ]--+--------------------------------------------------------------
jobid          | 1
nodeport       | 5866
is_hgjob       | f
schedule       | * * * * *
command        | refresh materialized view concurrently table0_view with data;
first_run_time | 
nodename       | localhost
database       | highgo
username       | sysdba
interval       | 
next_run_time  | 
active         | t
jobname        | 

测试

highgo=# insert into table0 values ('4','41','42');
INSERT 0 1
highgo=# select * from table0_view;
 a | b  | c  
---+----+----
 1 | 11 | 12
 2 | 21 | 22
 3 | 31 | 32
(3 行记录)
highgo=# select * from table0_view;
 a | b  | c  
---+----+----
 1 | 11 | 12
 2 | 21 | 22
 3 | 31 | 32
 4 | 41 | 42
(4 行记录)