Rebuilding triggers — clean_mv()

clean_mv() is the maintenance function for recovering from trigger drift: situations where triggers or trigger functions have been renamed, orphaned, or corrupted.

When to use clean_mv()

• Someone manually renamed a trigger function.
• Trigger setup failed partway through a previous operation.
• You want to verify the trigger setup is correct and rebuild it cleanly.

Running clean_mv()

Rebuild triggers for all registered MVs:

SELECT auto_mv_id, mv_name, watch_table, action
FROM pgauto_mv.clean_mv();

Rebuild for one specific MV:

SELECT *
FROM pgauto_mv._clean(
    p_auto_mv_id => (
        SELECT auto_mv_id FROM pgauto_mv.materialised_views
        WHERE mv_schema = 'reporting' AND mv_name = 'daily_revenue'
    )
);

Interpreting the result

action value	Meaning
triggers_recreated	Triggers were dropped and recreated successfully.
mv_missing	The OID stored in the registry no longer exists. The MV was dropped without calling unregister_mv(). Run sync_mv() to deactivate the stale registration.
recovered_function: pgauto_mv.some_func()	A trigger function was found by its embedded ID tag (renamed away from its canonical name). It was dropped and the canonical name recreated.

How clean_mv() finds trigger functions

Every generated trigger function has the comment -- pgauto_mv_id:{uuid} embedded in its body. clean_mv() searches for this tag in addition to the canonical name pattern — so even a manually renamed function is found and recovered.

---