Checking for problems

get_mv_problems() scans all registered views and reports any issues it finds:

SELECT * FROM pgauto_mv.get_mv_problems();

To check a specific problem code:

SELECT * FROM pgauto_mv.get_mv_problems('missing_unique_index');

To suppress a problem code you do not care about:

UPDATE pgauto_mv.mv_problem_codes
SET report = false
WHERE problem_code = 'some_code';

Finding unregistered materialised views:

-- Show MVs that exist in the database but are not registered with pg_auto_mv
SELECT mv_schema, mv_name, is_populated
FROM pgauto_mv.list_mv(p_status => 'missing');

Finding registrations whose view was dropped:

If someone dropped a materialised view without calling unregister_mv(), the registration becomes stale:

SELECT auto_mv_id, mv_schema, mv_name
FROM pgauto_mv.list_mv(p_status => 'invalid');

Clean up stale registrations:

SELECT pgauto_mv.unregister_mv('reporting', 'dropped_view_name');

---