【postgresql 物化视图】自动刷新物化视图2种方法

普通视图就是一个虚拟表，不占内存。而物化视图是存在的，占内存。
物化视图，默认是手动刷新。下面是手动刷新的例子。我们来创建一个物化视图。

create MATERIALIZED VIEW dnh_analasis_view as 
select cjsj,a,b,c,d from table_1;
REFRESH MATERIALIZED  VIEW dnh_analasis_view;
1
2
3
手动刷新无法实现自动化，那么如何让postgresql 实现自动刷新物化视图呢。

方法1：实现思路：创建一个函数，函数实现刷新物化视图的功能。然后创建一个触发器，触发函数。最终达到实现自动刷选物化视图的目的。

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

create or replace function tri_dnh_analasis_func() returns trigger as $$ 
declare
begin 
  refresh materialized view concurrently dnh_analasis_view with data; 
  return null; 
end; 
$$ language plpgsql;
1
2
3
4
5
6
7
第二步：创建触发器，当表有插入更新删除操作时，触发函数。刷新物化视图。

create trigger tri_dnh_analasis
after insert or update or delete on dws_tag_value
for each statement 
  execute procedure tri_dnh_analasis_func();
1
2
3
4
方法2：定时任务（pg_cron）自动刷新物化视图。
pg_cron 是一个简单的基于 cron 的 PostgreSQL(9.5或更高版本)作业调度程序，它在数据库中作为扩展运行。它使用与常规 cron 相同的语法，但它允许你直接从数据库调度 PostgreSQL 命令。

pg_cron是基于cron的作业调度插件，语法与常规cron相同，但它可以直接从数据库执行PostgreSQL命令。
每一个定时任务分为两部分：
定时计划
规定使用插件的计划，例如每隔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呢？

第一步：安装pg_cron 插件。编译安装

git clone https://github.com/citusdata/pg_cron.git
cd pg_cron
source /home/postgres/.bash_profile
make
make install

第二步：
使用该插件前，需要将pg_cron加入到shared_preload_libraries参数中。

shared_preload_libraries = 'pg_cron' 
cron.database_name = 'mes'

注：配置了cron.database_name参数才能在对应数据库上安装pg_cron扩展。

第三步：开始在客服端如navicat 执行使用命令：

创建pg_cron 插件

create extension pg_cron;

删除插件

DROP EXTENSION pg_cron;

执行某个任务

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

查看当前任务列表

SELECT * FROM cron.job;