prestodb/presto · error · PrestoException

MATERIALIZED_VIEW_STALE

MATERIALIZED_VIEW_STALE

Error message

Materialized view '%s' is stale

What it means

When rewriting a query to use a materialized view, Presto checks base-table timestamps to decide whether the view is stale. If the view is stale and the view's staleness behavior is FAIL, planning throws MATERIALIZED_VIEW_STALE, forcing the user to refresh or change the policy.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/planner/iterative/rule/materializedview/MaterializedViewRewrite.java:277

            Duration stalenessWindow)
    {
        if (status.isFullyMaterialized()) {
            return false;
        }

        // If within staleness window, just return stale data - don't do stitching
        if (isWithinStalenessWindow(status, stalenessWindow)) {
            return false;
        }

        return staleReadBehavior == MaterializedViewStaleReadBehavior.USE_STITCHING;
    }

    private boolean shouldUseDataTableWhenStale(MaterializedViewStaleReadBehavior behavior, QualifiedObjectName viewName)
    {
        switch (behavior) {
            case FAIL:
                throw new PrestoException(
                        MATERIALIZED_VIEW_STALE,
                        String.format("Materialized view '%s' is stale", viewName));
            case USE_VIEW_QUERY:
                return false;
            case USE_STITCHING:
                return true;
            default:
                throw new IllegalStateException("Unexpected stale read behavior: " + behavior);
        }
    }

    private boolean canUseDataTableWithSecurityChecks(
            MaterializedViewScanNode node,
            MetadataResolver metadataResolver,
            Session session,
            MaterializedViewDefinition definition,
            Context context)
    {

View on GitHub (pinned to 55bb57d202)

Solutions

1. Refresh the materialized view: CALL system.sync_materialized_view('catalog.schema.view') or run its refresh query
2. Change the refresh type / staleness policy (e.g. allow STALE or use stitching) in the view definition
3. Adjust the ETL schedule so the view refresh runs after base-table updates

Example fix

// before
SELECT * FROM mv_daily_revenue; -- stale, FAIL policy
// after
CALL system.sync_materialized_view('hive.sales.mv_daily_revenue');
SELECT * FROM mv_daily_revenue;

// compare base table modified time to view freshness before querying
SELECT max(modification_time) FROM system.metadata.table_comments ...;

null

try {
    return query("SELECT * FROM mv");
} catch (PrestoException e) {
    if (e.getErrorCode() == MATERIALIZED_VIEW_STALE.toErrorCode()) {
        call("CALL system.sync_materialized_view('catalog.schema.mv')");
        return retryQuery("SELECT * FROM mv");
    }
    throw e;
}

When it happens

Trigger: Querying a materialized view whose base tables have been modified after the last refresh, with the materialized-view staleness policy set to FAIL for that view.

Common situations: Base tables updated by streaming/ETL jobs without a subsequent view refresh; staleness policy configured as FAIL in the view definition; scheduling gap in the refresh job.

Related errors

• MV_MISSING_TOO_MUCH_DATA
• INVALID_TABLE_PROPERTY
• INVALID_VIEW
• Materialized view already exists
• Materialized view not found

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/3ec7cb1466851436. Report an issue: GitHub.