Materialized Views: Failed Refresh Invalidates a Materialized View¶

Materialized views are updated automatically on a regular basis by a background process.

Currently, if the refresh of a materialized view fails, the data for the materialized view is not updated, and the background process continues to attempt to refresh the materialized view periodically.

If the failure is due to a problem that will continue to occur (e.g. a division by zero error that is caused by the materialized view definition or the data), the background process will continually fail to refresh the materialized view, and the data for the materialized view will not be updated.

In the current release, specific types of errors during the refresh process invalidate the materialized view. In addition:

• When you query the materialized view, the output will include the reason for the invalidation.

• The output of the SHOW MATERIALIZED VIEWS command will include the reason why the materialized view was invalidated.

Using the information from the output of these commands, address the problem with the materialized view, and execute the ALTER MATERIALIZED VIEW … RESUME command to resume the materialized view.

In summary, the process of refreshing and querying the materialized view changed as described below:

Previously:

The background process fails to refresh the materialized view.

Although the data in the materialized view is out of date, the output from querying the materialized view does not indicate that the data is stale.

When you execute the SHOW MATERIALIZED VIEWS command, the refreshed_on column indicates that the data is out of date, but the output does not include a reason for this.

Currently:

The background process invalidates the materialized view.

Querying the materialized view results in an error that indicates why the refresh process failed to update the materialized view. For example:

SELECT * FROM my_mv;

Copy

002037 (42601): SQL compilation error:
  Failure during expansion of view 'MY_MV':
    SQL compilation error: Materialized View MY_MV is invalid.
    Invalidation reason: Division by zero

When you execute the SHOW MATERIALIZED VIEWS command, the invalid column indicates that the materialized view is invalid, and the invalid_reason column contains the reason for the invalidation. For example:

SHOW MATERIALIZED VIEWS;

Copy

...  +---------+------------------+ ...
...  | invalid | invalid_reason   | ...
...  +---------+------------------+ ...
...  | true    | Division by zero | ...
...  +---------+------------------+ ...

For example, suppose that you execute the following statements to create a materialized view:

CREATE OR REPLACE TABLE my_base_table (a INT, b INT, c VARCHAR(16));

INSERT INTO my_base_table VALUES (1, 1, 'valid data');

CREATE OR REPLACE MATERIALIZED VIEW my_mv AS SELECT a / b AS div FROM my_base_table;

Suppose that you insert data into the table that will cause the refresh of the materialized view to fail. For example, suppose that you execute the following statement:

INSERT INTO my_base_table VALUES (1, 0, 'invalid data');

When the materialized view is refreshed next, the refresh will fail with a “division by zero” error. Because the refresh fails, the materialized view will be invalidated.

To view the reason for the invalidation, query the materialized view or execute the SHOW MATERIALIZED VIEWS command:

SELECT * FROM my_mv;

002037 (42601): SQL compilation error:
  Failure during expansion of view 'MY_MV':
    SQL compilation error: Materialized View MY_MV is invalid.
    Invalidation reason: Division by zero

SHOW MATERIALIZED VIEWS;

...  +---------+------------------+ ...
...  | invalid | invalid_reason   | ...
...  +---------+------------------+ ...
...  | true    | Division by zero | ...
...  +---------+------------------+ ...

Address the problem that caused the invalidation, and execute the ALTER MATERIALIZED VIEW … RESUME command to resume the materialized view:

ALTER MATERIALIZED VIEW my_mv RESUME;

Ref: 1178