Define grace/staleness period for materialized views

[findepi]

Per @martint comment #15104 (comment)

I'm thinking of syntax like this:

CREATE MATERIALIZED VIEW <name>
    (GRACE PERIOD <interval>)?
    (WHEN STALE (FAIL | REFRESH))?
    AS (<query>)
    (WITH NO DATA)?

• A materialized view can be in either usable or unusable state.
• GRACE PERIOD indicates the maximum allowed time since creation or last refresh before the MV is considered unusable. If omitted, the MV will always be considered usable (i.e., infinite grace period)
• WHEN STALE (FAIL | REFRESH) indicates what to do once the MV is considered unusable. If FAIL, querying the MV will result in a failure indicating the MV is stale. If REFRESH, the query will transparently produce the correct results as if the MV was up to date (either by refreshing the MV before the query runs or by running the underlying query directly as if it were a regular view.
• WITH NO DATA causes the MV to be created but not populated in the beginning. One open question is whether the MV is considered unusable until the first refresh occurs (explicitly via REFRESH MATERIALIZED VIEW or implicitly if WHEN STALE REFRESH is specified), or whether it's considered within the grace period. In the latter case, the WHEN STALE clause would only make sense when a grace period is specified explicitly (since otherwise, it's infinite), so we could constrain it syntactically just for that scenario.

We don't need to implement all those features up front, but I'd like to have a plausible plan for what the syntax would look like when we get to it.

[findepi]

• GRACE PERIOD indicates the maximum allowed time since creation or last refresh before the MV is considered unusable. If omitted, the MV will always be considered usable (i.e., infinite grace period)

I like this syntax.

Let's acknowledge that we need a backward incompatible change to introduce it:
Today we have no GRACE PERIOD, and this currently implies zero grace period.
What we want is to default to infinite grace period.

I think this is the change we should start from.

WHEN STALE (FAIL | REFRESH) indicates what to do once the MV is considered unusable. If FAIL, querying the MV will result in a failure indicating the MV is stale. If REFRESH, the query will transparently produce the correct results as if the MV was up to date (either by refreshing the MV before the query runs or by running the underlying query directly as if it were a regular view.

I don't like REFRESH meaning "potentially run query directly", as this can have dramatic impact on resource planning. I think user needs to remain in control.
Let's not use REFRESH here until we have real automatic refreshes, let's use some other word for what we support today. INLINE?

We don't need to implement all those features up front, but I'd like to have a plausible plan for what the syntax would look like when we get to it.

💯 Absolutely.

[alexjo2144]

I'd agree INLINE is better for the behavior we have today where it falls back to a regular view. At least with this value being an enum we could add a REFRESH option later if we want an option to refresh the MV during the query.

[findepi]

Updated #15104 to include WHEN STALE clause too.

[findepi]

Updated #15104 to include WHEN STALE clause too.

WHEN STALE got moved out to #15417

[findepi]

Docs for GRACE PERIOD: #15810