Spaces:
Paused
Paused
File size: 7,137 Bytes
5a81b95 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 | -- refresh-all-views.sql
-- Materialized View Refresh Script with Validation and Error Handling
--
-- Usage:
-- psql -U postgres -d cia_dev -f service.data.impl/src/main/resources/refresh-all-views.sql
--
-- Description:
-- Refreshes all materialized views in correct dependency order with:
-- - Validation that all views exist before attempting refresh
-- - Error handling to continue on individual failures
-- - Timing information for each view refresh
-- - Progress logging and summary report
\set ON_ERROR_STOP off
\timing on
\echo '======================================='
\echo 'Materialized View Refresh Script'
\echo 'Started at:' `date`
\echo '======================================='
-- ===========================================================================
-- PHASE 1: VALIDATION - Check that all views exist
-- ===========================================================================
\echo ''
\echo '--- VALIDATION PHASE ---'
\echo 'Checking that all materialized views exist...'
DO $$
DECLARE
v_views TEXT[] := ARRAY[
'view_worldbank_indicator_data_country_summary',
'view_riksdagen_politician_document',
'view_riksdagen_org_document_daily_summary',
'view_riksdagen_document_type_daily_summary',
'view_riksdagen_committee_decisions',
'view_riksdagen_vote_data_ballot_summary',
'view_riksdagen_vote_data_ballot_summary_daily',
'view_riksdagen_committee_ballot_decision_summary',
'view_riksdagen_vote_data_ballot_party_summary',
'view_riksdagen_vote_data_ballot_party_summary_daily',
'view_riksdagen_vote_data_ballot_party_summary_monthly',
'view_riksdagen_vote_data_ballot_party_summary_weekly',
'view_riksdagen_vote_data_ballot_party_summary_annual',
'view_riksdagen_vote_data_ballot_summary_annual',
'view_riksdagen_vote_data_ballot_summary_monthly',
'view_riksdagen_vote_data_ballot_summary_weekly',
'view_riksdagen_vote_data_ballot_politician_summary',
'view_riksdagen_vote_data_ballot_politician_summary_daily',
'view_riksdagen_vote_data_ballot_politician_summary_annual',
'view_riksdagen_vote_data_ballot_politician_summary_monthly',
'view_riksdagen_vote_data_ballot_politician_summary_weekly',
'view_riksdagen_committee_ballot_decision_party_summary',
'view_riksdagen_committee_ballot_decision_politician_summary',
'view_riksdagen_committee_decision_type_org_summary',
'view_riksdagen_committee_decision_type_summary',
'view_riksdagen_party_document_daily_summary',
'view_riksdagen_politician_document_daily_summary',
'view_riksdagen_politician_document_summary'
];
v_view TEXT;
v_exists BOOLEAN;
v_missing INT := 0;
BEGIN
FOREACH v_view IN ARRAY v_views
LOOP
SELECT EXISTS (
SELECT 1 FROM pg_matviews
WHERE schemaname = 'public' AND matviewname = v_view
) INTO v_exists;
IF NOT v_exists THEN
RAISE WARNING 'Materialized view does not exist: %', v_view;
v_missing := v_missing + 1;
ELSE
RAISE NOTICE '✓ View exists: %', v_view;
END IF;
END LOOP;
IF v_missing > 0 THEN
RAISE EXCEPTION 'Validation failed: % materialized views missing', v_missing;
ELSE
RAISE NOTICE 'Validation passed: All % views exist', array_length(v_views, 1);
END IF;
END $$;
-- ===========================================================================
-- PHASE 2: REFRESH - Refresh materialized views with error handling
-- ===========================================================================
\echo ''
\echo '--- REFRESH PHASE ---'
\echo 'Refreshing materialized views in dependency order...'
\echo ''
DO $$
DECLARE
v_views TEXT[] := ARRAY[
-- TIER 1: Base materialized views (no dependencies on other materialized views)
'view_worldbank_indicator_data_country_summary',
'view_riksdagen_politician_document',
'view_riksdagen_org_document_daily_summary',
'view_riksdagen_document_type_daily_summary',
'view_riksdagen_committee_decisions',
'view_riksdagen_vote_data_ballot_summary',
'view_riksdagen_committee_ballot_decision_summary',
'view_riksdagen_vote_data_ballot_party_summary',
'view_riksdagen_vote_data_ballot_politician_summary',
-- TIER 2: Daily/summary views that depend on base materialized views
'view_riksdagen_vote_data_ballot_summary_daily',
'view_riksdagen_vote_data_ballot_summary_weekly',
'view_riksdagen_vote_data_ballot_summary_monthly',
'view_riksdagen_vote_data_ballot_summary_annual',
'view_riksdagen_vote_data_ballot_party_summary_daily',
'view_riksdagen_vote_data_ballot_party_summary_weekly',
'view_riksdagen_vote_data_ballot_party_summary_monthly',
'view_riksdagen_vote_data_ballot_party_summary_annual',
'view_riksdagen_vote_data_ballot_politician_summary_daily',
'view_riksdagen_vote_data_ballot_politician_summary_weekly',
'view_riksdagen_vote_data_ballot_politician_summary_monthly',
'view_riksdagen_vote_data_ballot_politician_summary_annual',
'view_riksdagen_committee_ballot_decision_party_summary',
'view_riksdagen_committee_ballot_decision_politician_summary',
'view_riksdagen_committee_decision_type_org_summary',
'view_riksdagen_committee_decision_type_summary',
'view_riksdagen_party_document_daily_summary',
'view_riksdagen_politician_document_daily_summary',
'view_riksdagen_politician_document_summary'
];
v_view TEXT;
v_success INT := 0;
v_failed INT := 0;
v_start TIMESTAMP;
v_duration INTERVAL;
BEGIN
FOREACH v_view IN ARRAY v_views
LOOP
BEGIN
v_start := clock_timestamp();
RAISE NOTICE 'Refreshing: %...', v_view;
EXECUTE format('REFRESH MATERIALIZED VIEW %I', v_view);
v_duration := clock_timestamp() - v_start;
RAISE NOTICE '✓ Refreshed % in %', v_view, v_duration;
v_success := v_success + 1;
EXCEPTION WHEN OTHERS THEN
RAISE WARNING '✗ Failed to refresh %: %', v_view, SQLERRM;
v_failed := v_failed + 1;
END;
END LOOP;
RAISE NOTICE '';
RAISE NOTICE '===========================================';
RAISE NOTICE '--- REFRESH SUMMARY ---';
RAISE NOTICE '===========================================';
RAISE NOTICE 'Total views: %', array_length(v_views, 1);
RAISE NOTICE 'Successful: %', v_success;
RAISE NOTICE 'Failed: %', v_failed;
RAISE NOTICE '===========================================';
IF v_failed > 0 THEN
RAISE WARNING 'Some view refreshes failed. Check logs above for details.';
END IF;
END $$;
\echo ''
\echo '======================================='
\echo 'Refresh script completed'
\echo 'Finished at:' `date`
\echo '======================================='
|