[PostgreSQL: Refresh All Materialized Views In Proper Order]Refresh All Materialized Views - Modified Postgresql Wiki #psql

psql-refresh-all-materialized-views-in-proper-order.sql

-- Refresh All Materialized Views In Proper Order
--
-- original: [Refresh All Materialized Views - PostgreSQL wiki](https://wiki.postgresql.org/wiki/Refresh_All_Materialized_Views)
-- modified: removed ownername and pg_authid due to error "permission denied for table pg_authid" on the Google cloud PostgreSQL 11.14
--
-- It is best to put these views into a specific "operations" realated schema, like:
--
--    CREATE SCHMEMA IF NOT EXISTS ops;
--    SET search_path = ops, pg_catalog;
--
CREATE OR REPLACE VIEW mat_view_dependencies AS
WITH RECURSIVE s(start_schemaname,start_relname,start_relkind,
		 schemaname,relname,relkind,reloid,owneroid,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,
c2.relowner AS owneroid,
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

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
, c2.relowner AS owneroid
, 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
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, depth
FROM mat_view_dependencies
WHERE relkind='m'
ORDER BY schemaname, relname, depth DESC
)
-- Reorder appropriately
SELECT schemaname, relname, depth AS refresh_order
FROM b
ORDER BY depth, schemaname, relname
;


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