How to refresh all materialized views in Postgresql 9.3 at once?

Question

I am loading a bunch of data into a PostgresQL 9.3 database and then I want to refresh all materialized views that depend on the updated tables. Is there a way to do it automatically instead of going through each view and refreshing them one by one? I know that Oracle can do that rather easily but I did not find anything after combing through PostgreSQL documentation.

Answer 1

Looks like current version of PostgreSQL (9.3.1) does not have such functionality, have had to write my own function instead:

CREATE OR REPLACE FUNCTION RefreshAllMaterializedViews(schema_arg TEXT DEFAULT 'public')
RETURNS INT AS $$
DECLARE
    r RECORD;
BEGIN
    RAISE NOTICE 'Refreshing materialized view in schema %', schema_arg;
    FOR r IN SELECT matviewname FROM pg_matviews WHERE schemaname = schema_arg 
    LOOP
        RAISE NOTICE 'Refreshing %.%', schema_arg, r.matviewname;
        EXECUTE 'REFRESH MATERIALIZED VIEW ' || schema_arg || '.' || r.matviewname; 
    END LOOP;

    RETURN 1;
END 
$$ LANGUAGE plpgsql;

(on github: https://github.com/sorokine/RefreshAllMaterializedViews)

Answer 2

The above answers work fine if the materialized views do not depend on each other. If that is not the case, then the order in which the materialized views are refreshed is important (i.e., you need to refresh the materialized views that don't depend on any other materialized views before you refresh those that do). The code below will generate an ordered list of materialized views so that they can be updated in the correct order.

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;

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
;

This can be used in psql to refresh all views in the appropriate order 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

\echo :script
:script

This final part can, alternatively, be converted into a function as has been done in the previous solutions.