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

ID : 274318

viewed : 35

Tags : postgresqlmaterialized-viewspostgresql-9.3postgresql





Top 5 Answer for How to refresh all materialized views in Postgresql 9.3 at once?

vote vote

97

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)

vote vote

88

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.

vote vote

77

same method, added the option to run it on all schema's, optionally concurrently.

CREATE OR REPLACE FUNCTION RefreshAllMaterializedViews(_schema TEXT DEFAULT '*', _concurrently BOOLEAN DEFAULT false) RETURNS INT AS $$   DECLARE     r RECORD;   BEGIN     RAISE NOTICE 'Refreshing materialized view(s) in % %', CASE WHEN _schema = '*' THEN ' all schemas' ELSE 'schema "'|| _schema || '"' END, CASE WHEN _concurrently THEN 'concurrently' ELSE '' END;     IF pg_is_in_recovery() THEN        RETURN 0;     ELSE           FOR r IN SELECT schemaname, matviewname FROM pg_matviews WHERE schemaname = _schema OR _schema = '*'        LOOP         RAISE NOTICE 'Refreshing %.%', r.schemaname, r.matviewname;         EXECUTE 'REFRESH MATERIALIZED VIEW ' || CASE WHEN _concurrently THEN 'CONCURRENTLY ' ELSE '' END || '"' || r.schemaname || '"."' || r.matviewname || '"';        END LOOP;     END IF;     RETURN 1;   END  $$ LANGUAGE plpgsql; 

I also put it on GitHub: https://github.com/frankhommers/RefreshAllMaterializedViews

vote vote

64

same method, added standby check

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;     if pg_is_in_recovery()  then      return 1;     else     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;     end if;     RETURN 1; END  $$ LANGUAGE plpgsql; 
vote vote

57

The snippet below uses REFRESH MATERIALIZED VIEW CONCURRENTLY when a UNIQUE index exists for that view.

CREATE OR REPLACE FUNCTION public.refresh_materialized_views()   RETURNS void AS $BODY$   DECLARE       refresh_sql text;     BEGIN      WITH matviews AS (        SELECT t.oid,               relname AS view_name,               nspname AS schema_name          FROM pg_class t          JOIN pg_catalog.pg_namespace n ON n.oid = t.relnamespace         WHERE t.relkind = 'm'           AND nspname NOT LIKE 'pg-%'     ), unique_indexes AS (      SELECT m.oid,             view_name,             schema_name        FROM pg_class i,             pg_index ix,             matviews m       WHERE ix.indisunique = true         AND ix.indexrelid = i.oid         AND ix.indrelid = m.oid     ), refresh_concurrently AS (       SELECT 'REFRESH MATERIALIZED VIEW CONCURRENTLY ' || quote_ident(schema_name) || '.' || quote_ident(view_name) AS sql         FROM unique_indexes     ), refresh AS (       SELECT 'REFRESH MATERIALIZED VIEW ' || quote_ident(schema_name) || '.' || quote_ident(view_name) AS sql         FROM matviews        WHERE oid != all (SELECT oid FROM unique_indexes)     ), sql AS (       SELECT sql FROM refresh_concurrently       UNION ALL       SELECT sql FROM refresh     )      SELECT string_agg(sql, E';\n') || E';\n' FROM sql INTO refresh_sql;      EXECUTE refresh_sql;      END; $BODY$ LANGUAGE plpgsql VOLATILE; 

This snippet accepts a schema name to limit the views that are refreshed.

CREATE OR REPLACE FUNCTION public.refresh_materialized_views(_schema text)   RETURNS void AS $BODY$   DECLARE       refresh_sql text;     BEGIN      WITH matviews AS (        SELECT t.oid,               relname AS view_name,               nspname AS schema_name          FROM pg_class t          JOIN pg_catalog.pg_namespace n ON n.oid = t.relnamespace         WHERE t.relkind = 'm'           AND nspname NOT LIKE 'pg-%'           AND nspname = _schema     ), unique_indexes AS (      SELECT m.oid,             view_name,             schema_name        FROM pg_class i,             pg_index ix,             matviews m       WHERE ix.indisunique = true         AND ix.indexrelid = i.oid         AND ix.indrelid = m.oid     ), refresh_concurrently AS (       SELECT 'REFRESH MATERIALIZED VIEW CONCURRENTLY ' || quote_ident(schema_name) || '.' || quote_ident(view_name) AS sql         FROM unique_indexes     ), refresh AS (       SELECT 'REFRESH MATERIALIZED VIEW ' || quote_ident(schema_name) || '.' || quote_ident(view_name) AS sql         FROM matviews        WHERE oid != all (SELECT oid FROM unique_indexes)     ), sql AS (       SELECT sql FROM refresh_concurrently       UNION ALL       SELECT sql FROM refresh     )      SELECT string_agg(sql, E';\n') || E';\n' FROM sql INTO refresh_sql;      EXECUTE refresh_sql;      END; $BODY$ LANGUAGE plpgsql VOLATILE; 

Top 3 video Explaining How to refresh all materialized views in Postgresql 9.3 at once?







Related QUESTION?