刷新所有物化视图

确定正确刷新顺序的视图

代码片段

刷新所有物化视图

适用于 PostgreSQL

9.5 - 12

用

SQL

依赖

无

如果你像我一样，会创建很多物化视图 - 它们具有表的便捷性和速度，同时保留了创建方式的历史记录，允许在有新数据到达时轻松刷新。我倾向于创建相互依赖的物化视图。这使得刷新它们变得复杂，因为需要在刷新依赖它们的子物化视图之前刷新父物化视图。

不幸的是，目前没有 PostgreSQL 命令可以按正确顺序刷新所有视图。因此，我创建了几个简单的视图，它们使用系统表上的递归来确定视图和物化视图的层次结构，然后可以使用这些视图按正确顺序刷新这些物化视图。

CREATE OR REPLACE VIEW mat_view_dependencies AS
WITH RECURSIVE s(start_schemaname,start_relname,start_relkind,
		 schemaname,relname,relkind,reloid,owneroid,ownername,depth)
		 AS (
-- List of tables and views that mat views depend on
SELECT n.nspname AS start_schemaname, c.relname AS start_relname,
c.relkind AS start_relkind,
n2.nspname AS schemaname, c2.relname, c2.relkind,
c2.oid AS reloid,
au.oid AS owneroid,
au.rolname AS ownername,
0 AS depth
FROM pg_class c JOIN pg_namespace n
     ON c.relnamespace=n.oid AND c.relkind IN ('r','m','v','t','f', 'p')
JOIN pg_depend d ON c.oid=d.refobjid
JOIN pg_rewrite r ON d.objid=r.oid
JOIN pg_class c2 ON r.ev_class=c2.oid -- AND c2.relkind='m'
JOIN pg_namespace n2 ON n2.oid=c2.relnamespace
JOIN pg_authid au ON au.oid=c2.relowner

UNION

-- Recursively find all mat views depending on previous level
SELECT s.start_schemaname, s.start_relname, s.start_relkind,
n.nspname AS schemaname, c2.relname,
c2.relkind, c2.oid,
au.oid AS owneroid, au.rolname AS ownername,
s.depth+1 AS depth
FROM s
JOIN pg_depend d ON s.reloid=d.refobjid
JOIN pg_rewrite r ON d.objid=r.oid
JOIN pg_class c2 ON r.ev_class=c2.oid AND (c2.relkind IN ('m','v'))
JOIN pg_namespace n ON n.oid=c2.relnamespace
JOIN pg_authid au ON au.oid=c2.relowner

WHERE s.reloid <> c2.oid -- exclude the current MV which always depends on itself
)
SELECT * FROM s;

--------------------------------------------------
--- A view that returns the list of mat views in the
--- order they should be refreshed.
--------------------------------------------------
CREATE OR REPLACE VIEW mat_view_refresh_order AS
WITH b AS (
-- Select the highest depth of each mat view name
SELECT DISTINCT ON (schemaname,relname) schemaname, relname, ownername, depth
FROM mat_view_dependencies
WHERE relkind='m'
ORDER BY schemaname, relname, depth DESC
)
-- Reorder appropriately
SELECT schemaname, relname, ownername, depth AS refresh_order
FROM b
ORDER BY depth, schemaname, relname
;

刷新所有物化视图

可以创建一个使用这些视图一次刷新所有物化视图的 PL/PGSQL 函数，但由于这是一个相对少见的命令，而且执行时间可能很长，所以我认为最好只使用这些视图生成需要执行的代码，然后执行该代码。这可以在 psql 中使用变量完成，如下所示

SELECT string_agg(
       'REFRESH MATERIALIZED VIEW "' || schemaname || '"."' || relname || '";',
       E'\n' ORDER BY refresh_order) AS script
FROM mat_view_refresh_order \gset

-- Visualize the script
\echo :script

-- Execute the script
:script

刷新特定模式下的所有物化视图

可以使用类似上述的技术来完成许多有用的操作，例如按正确顺序删除物化视图，只刷新依赖特定父物化视图的物化视图等。例如，以下代码允许刷新特定模式下的所有物化视图。

SELECT string_agg(
       'REFRESH MATERIALIZED VIEW "' || schemaname || '"."' || relname || '";',
       E'\n' ORDER BY refresh_order) AS script
FROM mat_view_refresh_order WHERE schemaname='myschema' \gset

-- Visualize the script
\echo :script

-- Execute the script
:script

刷新依赖特定表的物化视图

如果某些表的新的数据到达（在下面的示例中为 schema1.table1 和 schema2.table2），那么可以使用以下命令刷新仅依赖于这些表的物化视图

WITH b AS (
-- Select the highest depth of each mat view name
SELECT DISTINCT ON (schemaname,relname) schemaname, relname, depth
FROM mat_view_dependencies
WHERE relkind='m' AND 
      (start_schemaname,start_relname) IN (('schema1','table1'),('schema2','table2'))
ORDER BY schemaname, relname, depth DESC
)
SELECT string_agg(
       'REFRESH MATERIALIZED VIEW "' || schemaname || '"."' || relname || '";',
       E'\n' ORDER BY depth) AS script
FROM b \gset

-- Visualize the script
\echo :script

-- Execute the script
:script