Refresh All Materialized Views

From PostgreSQL wiki

Jump to: navigation, search

Views to determine proper refresh order

Snippets

Refresh All Materialized Views

Works with PostgreSQL

9.5

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.

--------------------------------------------------
--- A view giving the list of mat views that depend
--- on each mat view
--------------------------------------------------
CREATE OR REPLACE VIEW mat_view_dependencies AS
WITH RECURSIVE s(start_schemaname,start_mvname,schemaname,mvname,relkind,
     	       mvoid,depth) AS (
-- List of mat views -- with no dependencies
SELECT n.nspname AS start_schemaname, c.relname AS start_mvname,
n.nspname AS schemaname, c.relname AS mvname, c.relkind,
c.oid AS mvoid, 0 AS depth
FROM pg_class c JOIN pg_namespace n ON c.relnamespace=n.oid
WHERE c.relkind='m'
UNION
-- Recursively find all things depending on previous level
SELECT s.start_schemaname, s.start_mvname,
n.nspname AS schemaname, c.relname AS mvname,
c.relkind,
c.oid AS mvoid, depth+1 AS depth
FROM s
JOIN pg_depend d ON s.mvoid=d.refobjid
JOIN pg_rewrite r ON d.objid=r.oid
JOIN pg_class c ON r.ev_class=c.oid AND (c.relkind IN ('m','v'))
JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE s.mvoid <> c.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,mvname) schemaname, mvname, depth
FROM mat_view_dependencies
WHERE relkind='m'
ORDER BY schemaname, mvname, depth DESC
)
-- Reorder appropriately
SELECT schemaname, mvname, depth AS refresh_order
FROM b
ORDER BY depth, schemaname, mvname
;

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 toe execute and then execute that code. This can be done in psql using variables as follows:

WITH a AS (
SELECT 'REFRESH MATERIALIZED VIEW "' || schemaname || '"."' || mvname || '";' AS r
FROM mat_view_refresh_order
ORDER BY refresh_order
)
SELECT string_agg(r,E'\n') AS script FROM a \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.

WITH a AS (
SELECT 'REFRESH MATERIALIZED VIEW "' || schemaname || '"."' || mvname || '";' AS r
FROM mat_view_refresh_order
WHERE schemaname='myschema'
ORDER BY refresh_order
)
SELECT string_agg(r,E'\n') AS script FROM a \gset
 
-- Visualize the script
\echo :script
 
-- Execute the script
:script
Personal tools