ALTER MATERIALIZED VIEW
Changes a materialized view's refresh owner, or suspends and resumes its automatic refresh.
Syntax
ALTER MATERIALIZED VIEW <view> OWNER TO { <principal> | CURRENT_USER };
ALTER MATERIALIZED VIEW <view> { SUSPEND | RESUME };<view> is fully qualified as <workspace>.<collection>.<view_name>.
These are the only properties of a view you can alter. Everything else about it — its
columns, its contents, the tables it reads — follows from its defining SELECT, and changes
by redefining that with CREATE OR REPLACE MATERIALIZED VIEW.
OWNER TO
A materialized view refreshes as a pinned identity — not as whoever's write happened to
trigger it. That identity lives on each of the view's refresh triggers, pinned when the
trigger is created and shown as runs_as in information_schema.triggers; the view itself
carries none, exactly as a task carries none. Every automatic refresh runs with that
principal's permissions and is billed to them.
A view reading four tables has four refresh triggers. This statement repoints all of them at once, so the view never refreshes as two identities depending on which source was written to last. ALTER TRIGGER ... OWNER TO moves one trigger.
ALTER MATERIALIZED VIEW analytics.daily_orders OWNER TO 'etl@example.com';
ALTER MATERIALIZED VIEW analytics.daily_orders OWNER TO CURRENT_USER;Redefining a view with CREATE OR REPLACE MATERIALIZED VIEW does not move it: a trigger the
view already had keeps its identity, and only the trigger on a newly read source is pinned
to the editor. Fixing a colleague's view does not make you responsible for keeping it fresh,
and does not hand your permissions to whoever edits it next.
CURRENT_USER resolves to the identity running the statement — the way to take ownership of
a view yourself. Quoting it ('CURRENT_USER') instead names a principal literally called
CURRENT_USER, following the usual SQL distinction.
Parameters
<principal>— the identity to pin on every refresh trigger of the view. Every automatic refresh runs, and is billed, as this principal from then on.CURRENT_USER— the identity running the statement; the way to take ownership yourself.
Why This Needs Workspace Owner
OWNER TO <principal> requires the owner role on the workspace, deliberately stricter
than owning the view itself.
When a view is created, its triggers' owner is necessarily an identity that held every permission the definition needed — because it was the identity that ran it. This statement can break that: it can point a view's refresh at a principal with broader permissions than the caller's own, and nothing can inspect another principal's grants to prevent it. A workspace owner can already grant themselves anything in the workspace, so requiring that tier escalates nothing that was not already available.
OWNER TO CURRENT_USER is the safe case — it can only ever point a view at the person
running it, so no permission can be borrowed.
If The Owner Loses Access
Refreshes stop, visibly: the job fails and last_refresh_status records the denial. That
is intended. The alternative — quietly falling back to whoever's commit fired the refresh —
would make a view's behaviour depend on which principal happened to write last, and could run
it with permissions it was never granted. A refresh trigger with no owner recorded is
refused the same way, with last_fired_status reading owner-missing.
For views that must outlive an individual, set the owner to a service principal.
SUSPEND and RESUME
Stops and restarts automatic refresh.
ALTER MATERIALIZED VIEW analytics.daily_orders SUSPEND;
ALTER MATERIALIZED VIEW analytics.daily_orders RESUME;A suspended view stays queryable and keeps its current contents; writes to its sources simply stop queuing refreshes. Suspension is recorded — who suspended it and when — and survives commits to the view, so it stays suspended until someone resumes it.
Suspending is expressed on the view, never on the triggers underneath it. A view reading four tables has four triggers, and disabling three of them would not stop it refreshing — it would refresh from a subset of its sources and produce quietly incomplete results.
Useful for:
- holding a view still during maintenance on its sources
- stopping a view that refreshes more often, or more expensively, than it needs to
- parking a view that is failing, while the cause is investigated, without losing its definition or its refresh history
SUSPEND requires the writer role on the view — not the workspace-owner tier OWNER TO
needs. Suspending borrows nobody's permissions: anyone who may replace a view's contents may
certainly stop them being replaced automatically.
Suspend, Don't Drop The Trigger
Dropping a view's refresh trigger also stops it refreshing, and used to be the only way. It is a worse tool. A missing trigger is indistinguishable from one that was never created or that something broke, so "deliberately switched off" and "quietly broken" look identical afterwards. A suspended view says which it is, since when, and by whom — and resumes with one statement rather than a reconstruction.
Examples
-- Hand a view to the service principal that should keep it fresh
ALTER MATERIALIZED VIEW analytics.daily_orders OWNER TO 'svc-etl';
-- Take ownership of a view whose owner has left
ALTER MATERIALIZED VIEW analytics.daily_orders OWNER TO CURRENT_USER;
-- Hold a view still while its source table is rebuilt
ALTER MATERIALIZED VIEW analytics.daily_orders SUSPEND;
-- ... maintenance ...
ALTER MATERIALIZED VIEW analytics.daily_orders RESUME;
REFRESH MATERIALIZED VIEW analytics.daily_orders;Resuming does not itself trigger a refresh — the view stays as it was until its next source write, so follow with REFRESH MATERIALIZED VIEW if you need it current immediately.
Notes
- Refused if the named relation is not a materialized view.
ALTER TABLEandALTER WORKSPACEare unaffected; only statements namingALTER MATERIALIZED VIEWare handled here.OWNER TO CURRENT_USERrequires an authenticated session — there is otherwise no identity to assign the view to.- Requires a connector with a catalog. Not every backend supports materialized views.