Refresh All Materialized Views

Views to determine proper refresh order

Snippets

Refresh All Materialized Views

Works with PostgreSQL

9.5 - 12

Written in

SQL

Depends on

Nothing

If you are like me, you create lots of materialized views - these have the convenience and speed of tables, while maintaining a history of how they were created permitting them to be easily refreshed when new data arrives. I tend to create materialized views that depend on each other. This complicates refreshing them because one needs to refresh parent materialized views before refreshing child materialized views that depend on them.

Unfortunately, there is currently no PostgreSQL command to refresh all views in the proper order. I therefore created a couple of simple views that use recursion on system tables to determine the hierarchy of views and materialized views, which can then be used to refresh those materialized views in the correct order.

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
;

Refreshing all materialized views

One could create a PL/PGSQL function that uses these views to refresh all materialized views at once, but as this is a relatively rare command to execute that can take a long time to run, I figured it was best just to use these views to generate the code one needs to execute and then execute that code. This can be done in psql using variables as follows:

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

Refreshing just the materialized views in a particular schema

One could use techniques similar to above to do lots of useful things with materialized views, such as dropping them in the correct order, refreshing just those materialized views that depend of a particular parent materialized view, etc. As an example, the code below allows refreshing just the materialized views in a particular schema.

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

Refreshing just the materialized views that depend on particular tables

If new data arrives for just certain tables (in the example below schema1.table1 and schema2.table2), then you can refresh just the materialized views that depend on those tables using:

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