Refreshing on demand

pgauto_mv.refresh_materialised_view_now(schema, name) queues an immediate refresh from your application code, an ETL script, or a shell command.

SELECT pgauto_mv.refresh_materialised_view_now('reporting', 'daily_revenue');

From a shell script after a bulk load:

psql -d mydb -c "SELECT pgauto_mv.refresh_materialised_view_now('reporting', 'my_expensive_mview');"

What it does:

1. Validates the view is registered. Raises an error if not found.
2. Writes an audit record to the event log (so you can see who requested the refresh and when).
3. Queues the refresh immediately, bypassing refresh_lag and max_wait.
4. Returns immediately — it does not wait for the refresh to complete.

This also allows you to implement your own complex logic of when to refresh a materialised view.

What it does not do:

• Does not bypass cooldown. If the view refreshed 3 seconds ago with a 10-second cooldown, the refresh waits 7 seconds. This prevents conflicting concurrent refreshes.
• Does not block your transaction. The refresh happens asynchronously in the background.
• Does not advance the scheduled slot. If the view also has a schedule, the next scheduled refresh still fires at its normal time.

Multiple rapid calls are safe. If your application calls this function several times in quick succession (for example, from parallel processes), only one refresh runs — duplicates are suppressed automatically.

Bulk load pattern

The most common use case is a bulk load where per-row trigger overhead is unwanted:

-- No triggers installed — avoids per-row overhead during the load.
-- p_populate_if_empty => false: the view is still empty at registration time; don't
-- refresh it until the explicit call below, after the load completes.
SELECT pgauto_mv.register_mv(
    p_mv_name           => 'pricing_summary',
    p_mv_schema         => 'reporting',
    p_watch_tables      => NULL,
    p_populate_if_empty => false,
    p_cooldown          => 5.0
);

-- Load data into the source table
INSERT INTO products.pricing SELECT * FROM staging.pricing_import;  -- 500,000 rows

-- Signal pg_auto_mv to refresh once the load is complete
SELECT pgauto_mv.refresh_materialised_view_now('reporting', 'pricing_summary');

Combining with a schedule

Direct refresh and scheduled refresh are independent. Combine them to get both a reliable baseline (the scheduled slot) and the ability to refresh immediately after a critical load:

Caution: p_populate_if_empty => false only skips the automatic refresh at registration time — it does not stop a schedule from firing. If a schedule is attached and its first slot arrives before your bulk load finishes, that scheduled tick refreshes the view against whatever data currently exists (there is no not_populated skip — an unpopulated view always refreshes rather than being skipped). Make sure the schedule's first slot falls comfortably after your expected load window, or don't attach a schedule until after the initial load completes.

-- Nightly baseline + on-demand refresh during the day
SELECT pgauto_mv.register_mv(
    p_mv_name       => 'risk_summary',
    p_mv_schema     => 'finance',
    p_watch_tables  => NULL,
    p_schedule => ARRAY[pgrelay.on_dow('eve', '02:00')],
    p_cooldown      => 30.0
);

-- After a critical afternoon data load:
SELECT pgauto_mv.refresh_materialised_view_now('finance', 'risk_summary');

Access control

EXECUTE on this function is restricted — it is not available to all database users by default. Ask your DBA to grant your role access if you need it:

Ask your DBA to run:
GRANT EXECUTE ON FUNCTION pgauto_mv.refresh_materialised_view_now(text, text) TO <your_role>;

Audit trail

Every call writes one row to mv_events with action = 'direct'. The resulting refresh appears in mv_refresh_log with refresh_source = 'direct':

SELECT refresh_started, refresh_completed, duration_ms, success, refresh_source
FROM pgauto_mv.mv_refresh_log
JOIN pgauto_mv.materialised_views USING (auto_mv_id)
WHERE mv_schema = 'reporting' AND mv_name = 'pricing_summary'
  AND refresh_source = 'direct'
ORDER BY refresh_id DESC
LIMIT 10;

---