我正在将一堆数据加载到PostgreSQL 9.3数据库中,然后我想刷新所有依赖于更新表的物化视图。有没有自动完成此操作的方法,而不是逐个刷新每个视图? 我知道Oracle可以很容易地做到这一点,但在阅读了PostgreSQL文档后未找到相关信息。
我正在将一堆数据加载到PostgreSQL 9.3数据库中,然后我想刷新所有依赖于更新表的物化视图。有没有自动完成此操作的方法,而不是逐个刷新每个视图? 我知道Oracle可以很容易地做到这一点,但在阅读了PostgreSQL文档后未找到相关信息。
看起来当前版本的 PostgreSQL(9.3.1)没有这样的功能,我不得不编写自己的函数代替:
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;
(在GitHub上:https://github.com/sorokine/RefreshAllMaterializedViews)
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
;
以下命令可在psql
中使用,以适当的顺序刷新所有视图:
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
REFRESH MATERIALIZED VIEW CONCURRENTLY
。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;
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;
同样的方法,增加了在所有模式下运行它的选项,可以选择并发运行。
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;
我也将它上传到GitHub:https://github.com/frankhommers/RefreshAllMaterializedViews
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;