Trigger Architecture

4.1 Generated Trigger Functions

_create_triggers(p_auto_mv_id uuid) generates one PL/pgSQL function per watch table per auto_mv. Each function is created via EXECUTE with dynamic SQL built using format() and %I/%L quoting — never string concatenation with raw identifier values.

Naming convention:

Function: pgauto_mv._trig_{id_safe}_{schema_safe}_{table_safe}()
DML trigger: pg_auto_mv_{id_safe}
TRUNCATE trigger: pg_auto_mv_{id_safe}_trunc

UUIDs contain hyphens which are illegal in unquoted identifiers. v_id_safe := replace(auto_mv_id::text, '-', '_') is applied to every generated name. Schema and table names are sanitised with regexp_replace(name, '[^a-zA-Z0-9]', '_', 'g').

ID tag in prosrc: Every generated function body contains the comment -- pgauto_mv_id:{uuid} (raw UUID with hyphens). This is the recovery anchor for clean_mv(): it can locate functions by scanning pg_proc.prosrc even after a DBA has manually renamed the function away from its canonical name.

Generated function body (all events, both trigger types):

1. v_event_at := clock_timestamp()
2. SELECT is_pending INTO v_was_pending FROM mv_state WHERE auto_mv_id = v_auto_mv_id
3. INSERT INTO mv_events (action = 'opened' or 'updated')
4. UPDATE mv_state:
     is_pending = true
     latest_event_at = v_event_at
     first_event_at = CASE WHEN NOT is_pending THEN v_event_at ELSE first_event_at END
     last_watch_schema / last_watch_table from TG_TABLE_SCHEMA / TG_TABLE_NAME
5. PERFORM pgrelay.notify('pg_auto_mv.refresh_materialised_view', v_auto_mv_id::text)
   [6. If fire_on_replica=true and TG_OP='TRUNCATE':
       INSERT INTO mv_truncate_signals (auto_mv_id, watch_schema, watch_table)]
7. RETURN NULL

No computed columns are written to mv_state from the trigger. The trigger writes only raw timestamps and flags. All timing derivations live in mv_state_v.

4.2 Trigger Modes

Controlled by fire_on_replica on the registry row:

fire_on_replica	DML trigger type	Enabled	TRUNCATE trigger
false (default)	FOR EACH STATEMENT	ORIGIN (default)	FOR EACH STATEMENT, default
true	FOR EACH ROW	ALWAYS (via ALTER TABLE ... ENABLE ALWAYS TRIGGER)	FOR EACH STATEMENT, default

TRUNCATE triggers are always statement-level — PostgreSQL does not permit FOR EACH ROW triggers on TRUNCATE. The ENABLE ALWAYS flag cannot override the statement-level TRUNCATE restriction during logical apply.

The TRUNCATE trigger (_trunc) is only created when watch_delete = true for that watch table row.

4.3 _drop_triggers(p_auto_mv_id uuid)

Iterates watch_tables for the auto_mv. For each watch table it:

1. Checks the table still exists in pg_class (watch table may have been dropped) before attempting trigger drops.
2. Executes DROP TRIGGER IF EXISTS for both the DML and TRUNCATE trigger names.
3. Executes DROP FUNCTION IF EXISTS for the generated function.

4.4 clean_mv(p_auto_mv_id uuid DEFAULT NULL)

Maintenance tool that rebuilds triggers from scratch. For each auto_mv (or one specific one when a UUID is supplied):

1. Checks mv_oid in pg_class. If missing, returns a mv_missing row and skips trigger work.
2. Finds all trigger functions via two independent searches:
3. Canonical name: starts_with(proname, '_trig_' || id_safe || '_')
4. ID tag in prosrc: prosrc LIKE '%-- pgauto_mv_id:{uuid}%' Functions found only by tag (renamed by a DBA) are reported as recovered_function.
5. For each found function, locates all triggers using it via pg_trigger.tgfoid and drops them, then drops the function.
6. Drops canonical-named triggers that may linger without a matching function.
7. Calls _create_triggers() to recreate from the current watch_tables rows.
8. Returns a triggers_recreated row.

---