A nightly job refreshes the PostgreSQL materialized view behind a sales leaderboard; which role should run it, and which failure modes must the job handle?
answer
- a privilege narrower than ownership
- runs as the owner anyway
- search_path is pinned during refresh
- timeouts, overlap and dependency order
basics
~20 sSince PostgreSQL 17, grant the job role MAINTAIN on that one materialized view instead of ownership; the query still runs as the owner with search_path pinned. The job must handle lock waits, overlapping runs, timeouts and dependent views.
solid answer
~40 sIn PostgreSQL 17 and 18, `REFRESH MATERIALIZED VIEW` needs the `MAINTAIN` privilege, so `GRANT MAINTAIN ON reporting.sales_leaderboard` to a dedicated role is narrower than ownership or `pg_maintain`, which covers every relation. Whoever runs it, the query executes as the **view owner** in a security-restricted context, with `search_path` temporarily set to `pg_catalog, pg_temp`, so functions it calls must schema-qualify names or set their own `search_path`. The job should set `lock_timeout` so a plain refresh's `ACCESS EXCLUSIVE` request does not wait indefinitely, and `statement_timeout` to cap runtime. Overlapping runs queue on the lock, so guard against them. Refresh dependent materialized views in order. A failed refresh rolls back and leaves the old contents. Record the refresh time in a separate table, not as a column in the view.
code
sql · 13 lines-- One-time setup, as the view owner (PostgreSQL 17+):
GRANT MAINTAIN ON reporting.sales_leaderboard TO leaderboard_refresher;
GRANT UPDATE ON reporting.refresh_log TO leaderboard_refresher;
-- Nightly, run by leaderboard_refresher via psql -v ON_ERROR_STOP=1 -f:
SET lock_timeout = '5s';
SET statement_timeout = '20min';
BEGIN;
REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.sales_leaderboard;
UPDATE reporting.refresh_log
SET refreshed_at = now()
WHERE view_name = 'sales_leaderboard';
COMMIT;go deeper
Recall that a PostgreSQL materialized view only changes when something runs REFRESH, so a schedule has to exist outside the view.
Explain that MAINTAIN lets a non-owner refresh since 17, and that the query still runs with the owner's rights.
Demonstrate a production-ready job: lock_timeout, statement_timeout, overlap handling, dependency order and the pinned search_path after an upgrade.
Weigh refresh cadence against source load and staleness, and decide when a nightly batch should become a different design.
## Who may run REFRESH Refreshing is a maintenance operation, and PostgreSQL 17 added a privilege for exactly that. | Release | Who can run `REFRESH MATERIALIZED VIEW` | |---|---| | 16 and earlier | The owner, or a member of the owning role | | 17 and 18 | Anyone with `MAINTAIN` on the view, or a member of `pg_maintain` | `MAINTAIN` is granted per relation, `GRANT MAINTAIN ON reporting.sales_leaderboard TO leaderboard_refresher`, and also covers `VACUUM`, `ANALYZE`, `CLUSTER`, `REINDEX` and `LOCK TABLE` on that relation. `pg_maintain` grants the same on **every** relation, far broader than a refresh job needs. On 16 the alternative was membership in the owning role, which also lets the job alter or drop the view. ## Whose rights the query runs with Whoever issues the command, PostgreSQL switches to the **materialized view's owner** for the duration and marks it a security-restricted operation. So: - the owner, not the job role, needs `SELECT` on `sales.orders` and any other source; - the job role needs no privilege on the base tables at all; - changing who schedules the job never changes what data the view can see. ## The pinned search_path Since 17, `search_path` is temporarily set to `pg_catalog, pg_temp` while the refresh runs. The view's own query is safe, because its table and function references were resolved when it was created. The risk is inside **functions it calls**: a SQL or PL/pgSQL function body that names `fx_rates` without a schema now fails during refresh, though it works in an interactive session. Fix the function by schema-qualifying its references or attaching `SET search_path` to it; upgrading from 16 is when this tends to surface. ## Locks, overlap and timeouts A robust job script does the following: 1. **Bounds the lock wait.** A plain refresh asks for `ACCESS EXCLUSIVE`; if a long report is reading, it waits, and while it waits new readers queue behind it. `SET lock_timeout` makes it give up and retry later instead. `CONCURRENTLY`, which needs a unique index, takes only `EXCLUSIVE` and lets readers through. 2. **Bounds the runtime** with `statement_timeout`, so a pathological plan does not run into business hours. 3. **Handles overlap.** Two refreshes of one view never run together; the second waits on the lock. If a run can exceed the schedule interval, let the lock timeout fail the late one, or take an advisory lock first. 4. **Keeps the transaction short.** The lock is held until commit, so do not bundle the refresh with unrelated work. 5. **Respects dependencies.** A materialized view built on another reads that one's current contents, so refresh the inner view first. 6. **Relies on atomicity.** A refresh that fails or is cancelled rolls back, and readers keep the previous contents. ## Scheduling and observing it - Scheduling lives outside the server's core: an OS cron entry or systemd timer running `psql` with `ON_ERROR_STOP`, or an in-database scheduler extension such as pg_cron, which is not part of core PostgreSQL. - PostgreSQL records no last-refresh time for a materialized view. Write one to a small log table in the **same transaction** as the refresh. - Do not add a column such as `now() AS refreshed_at` to the view: every row then differs on every run, so a concurrent refresh deletes and re-inserts the whole view. - `pg_matviews.ispopulated` shows whether the view is scannable. ## Errors the job should tell apart | SQLSTATE | Typical cause | What the job should do | |---|---|---| | `55P03` `lock_not_available` | `lock_timeout` expired waiting for the view's lock | Retry later; the old contents are still served | | `57014` `query_canceled` | `statement_timeout` hit, or an operator cancelled it | Investigate the query plan before retrying | | `42501` `insufficient_privilege` | The job role lacks `MAINTAIN` | Fix the grant, not the schedule | | `42P01` `undefined_table` | A called function relies on the caller's `search_path` | Schema-qualify the function, then retry | | `55000` `object_not_in_prerequisite_state` | `CONCURRENTLY` without a usable unique index | Add the index; never fall back silently | Alerting on the first two as transient and on the rest as defects keeps a nightly job from paging someone for a routine lock wait.
- After upgrading from PostgreSQL 16 to 17, a nightly REFRESH fails with a relation-does-not-exist error that never occurs interactively; why?From 17, `REFRESH MATERIALIZED VIEW` runs with `search_path` set to `pg_catalog, pg_temp`. The view's own references were bound at creation, but a function it calls resolves unqualified names at run time and no longer finds them. Schema-qualify the names in that function or give it `SET search_path`.
- Why should the leaderboard's last-refresh time not be a now() column inside the PostgreSQL materialized view?A concurrent refresh compares whole rows to find differences. A per-run timestamp makes every row differ, so every row is deleted and re-inserted, which is slower and leaves the maximum number of dead tuples. A one-row log table updated in the refresh transaction records the same fact for free.
- The refresh job's role needs SELECT on every base table the view reads.
- Only the owner can ever refresh a materialized view, even on PostgreSQL 18.
- Granting pg_maintain is the least-privilege way to let one job refresh one view.
- A failed refresh leaves the materialized view empty until the next run.
- REFRESH uses the caller's search_path, so functions in the view need no changes after an upgrade.
Assumes PostgreSQL 18; the MAINTAIN privilege, pg_maintain and the restricted search_path during REFRESH arrived in 17, so on 16 and earlier only the owner could refresh.