How to refresh all materialized views in Postgresq

问题:

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.

回答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)

回答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.

回答3:

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

回答4:

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;

回答5:

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;