ALTER MATERIALIZED VIEW

Use ALTER MATERIALIZED VIEW to:

• Rename a materialized view.
• Change owner of a materialized view.
• Change retain history configuration for the materialized view.

• Replace a materialized view. Public preview.

Syntax

ALTER MATERIALIZED VIEW <name> RENAME TO <new_name>;

ALTER MATERIALIZED VIEW <name> OWNER TO <new_owner_role>;

ALTER MATERIALIZED VIEW <name> SET (RETAIN HISTORY [=] FOR <retention_period>);

ALTER MATERIALIZED VIEW <name> RESET (RETAIN HISTORY);

ALTER MATERIALIZED VIEW <name> APPLY REPLACEMENT <replacement_view>;

Details

Replacing a materialized view

You can use CREATE REPLACEMENT MATERIALIZED VIEW with ALTER MATERIALIZED VIEW ... APPLY REPLACEMENT to replace materialized views in-place without recreating dependent objects or incurring downtime.

When replacing a materialized view, the operation:

• Replaces the materialized view’s definition with that of the replacement view and drops the replacement view at the same time.

  NOTE: If the replacement view is ahead of the target view, both the command and the replacement view wait for the target view to catch up. During this wait, the replacement view buffers any changes it receives from its inputs, which, depending on the amount to buffer, could cause the cluster to run out of memory.

• Emits a diff representing the changes between the old and new output.

  NOTE: All downstream objects must process this diff, which may cause temporary CPU and memory spikes depending on the size of the changes.

See Recommended checks before replacing a view.

Recommended checks before replacing a view

Before applying the replacement view:

• Verify that the replacement view is hydrated to avoid downtime:

  SELECT
     mv.name,
     h.hydrated
  FROM mz_catalog.mz_materialized_views AS mv
  JOIN mz_internal.mz_hydration_statuses AS h ON (mv.id = h.object_id)
  WHERE mv.name = '<replacement_view>';
  

• Verify that the target materialized view are is not behind the replacement materialized view by checking their write_frontier.

  SELECT o.name, f.write_frontier
  FROM mz_objects o, mz_cluster_replica_frontiers f
  WHERE o.name in ('<view>', '<view_replacement>')
  AND f.object_id = o.id;
  

  If the target view is behind, it is recommended to drop the replacement view instead of having it run for an extended period, buffering changes.

Privileges

The privileges required to execute this statement are:

• Ownership of the materialized view.
• In addition, to change owners:
  • Role membership in new_owner.
  • CREATE privileges on the containing schema if the materialized view is namespaced by a schema.

• In addition, to apply a replacement:
  • Ownership of the replacement materialized view.

Examples

Replace a materialized view

A replacement materialized view can only be applied to the target materialized view specified in the FOR clause of the CREATE REPLACEMENT MATERIALIZED VIEW statement.

Prerequisite

The following example creates a replacement materialized view winning_bids_replacement for the winning_bids materialized view. The replacement view specifies a different filter mz_now() > a.end_time than the existing view mz_now() >= a.end_time.

CREATE REPLACEMENT MATERIALIZED VIEW winning_bids_replacement
FOR winning_bids AS
SELECT DISTINCT ON (a.id) b.*, a.item, a.seller
FROM auctions AS a
JOIN bids AS b
  ON a.id = b.auction_id
WHERE b.bid_time < a.end_time
  AND mz_now() > a.end_time
ORDER BY a.id,
  b.amount DESC,
  b.bid_time,
  b.buyer;

The replacement view hydrates in the background.

Apply the replacement

When replacing a materialized view, the operation:

• Replaces the materialized view’s definition with that of the replacement view and drops the replacement view at the same time.

  NOTE: If the replacement view is ahead of the target view, both the command and the replacement view wait for the target view to catch up. During this wait, the replacement view buffers any changes it receives from its inputs, which, depending on the amount to buffer, could cause the cluster to run out of memory.

• Emits a diff representing the changes between the old and new output.

  NOTE: All downstream objects must process this diff, which may cause temporary CPU and memory spikes depending on the size of the changes.

Assume that the winning_bids_replacement is hydrated to avoid downtime and that the target winning_bids is not behind the replacement view (see Recommended checks before replacing a view for details).

The following example replaces the winning_bids materialized view with winning_bids_replacement:

ALTER MATERIALIZED VIEW winning_bids
APPLY REPLACEMENT winning_bids_replacement;

For a step-by-step tutorial on replacing a materialized view, see Replace materialized views guide

Related pages

• CREATE MATERIALIZED VIEW
• SHOW MATERIALIZED VIEWS
• SHOW CREATE MATERIALIZED VIEW
• DROP MATERIALIZED VIEW