【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;