Dependency Tracking and Safe Deployment of Materialized Views

Materialized views depend on base tables and other views. Dropping or altering a dependency can silently break them. This lesson covers introspection and safe deployment practices.

-- Inspect all materialized views and their definitions
SELECT
    schemaname,
    matviewname,
    ispopulated,
    hasindexes,
    definition
FROM pg_matviews
WHERE schemaname = 'absolutelearning'
ORDER BY matviewname;

-- Find all dependencies of a materialized view
-- (what tables/views does it read from?)
SELECT
    dep.classid::regclass,
    dep.objid,
    ref.relname  AS depends_on,
    ref.relkind  AS kind    -- r=table, m=matview, v=view
FROM pg_depend dep
JOIN pg_class ref ON ref.oid = dep.refobjid
JOIN pg_class mat ON mat.oid = dep.objid
WHERE mat.relname = 'mv_daily_hashtag_stats'
  AND mat.relkind = 'm'
  AND dep.deptype = 'n';

-- Find everything that depends ON a table (what breaks if I alter it?)
SELECT
    dependent.relname  AS dependent_object,
    dependent.relkind  AS kind
FROM pg_depend d
JOIN pg_class source    ON source.oid = d.refobjid
JOIN pg_class dependent ON dependent.oid = d.objid
WHERE source.relname = 'tw_tweets'
  AND dependent.relkind IN ('m', 'v')
  AND d.deptype = 'n';