Closed eljhkrr closed 1 year ago
Previously used in BRAC
DECLARE
matview RECORD;
start_time timestamp with time zone;
BEGIN
start_time := transaction_timestamp();
EXECUTE 'CREATE TABLE IF NOT EXISTS refresh_log(view_name varchar, duration interval, logged_on timestamp with time zone)';
RAISE NOTICE 'Refreshing base metaviews';
-- other matviews rely on contactview_metadata, which is a matview
-- so load this first
REFRESH MATERIALIZED VIEW CONCURRENTLY public.contactview_metadata;
EXECUTE format('INSERT INTO refresh_log VALUES (''%s'', ''%s''::interval, ''%s'')', 'public.contactview_metadata', clock_timestamp()- start_time, transaction_timestamp());
FOR matview IN SELECT matviewname, schemaname FROM pg_catalog.pg_matviews LOOP
IF matview.matviewname = 'contactview_metadata' THEN
-- this one is already done, skip it.
CONTINUE;
END IF;
RAISE NOTICE 'Refreshing %', matview.matviewname;
EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %s', CONCAT(matview.schemaname, '.', matview.matviewname));
EXECUTE format('INSERT INTO refresh_log VALUES (''%s'', ''%s''::interval, ''%s'')', CONCAT(matview.schemaname, '.', matview.matviewname), clock_timestamp() - start_time, transaction_timestamp());
END LOOP;
RAISE NOTICE 'Materialized views refreshed.';
RETURN 1;
END;
First step is to commit the current cron file in #53
Can be completed together with #52 (concurrent refresh)
Add widget to refresh tab