New in v20.2: Stored query results in materialized view are not automatically updated to reflect the latest state of the table(s) they query. The REFRESH statement updates the stored query results of a materialized view.
CockroachDB does not support materialized views that are refreshed on transaction commit.
Required privileges
The user must be the owner of the materialized view.
Syntax
REFRESH MATERIALIZED VIEW [CONCURRENTLY] view_name [WITH [NO] DATA]
Parameters
| Parameter | Description |
|---|---|
CONCURRENTLY |
(Default behavior) This keyword is a no-op, added for PostgreSQL compatibility. All materialized views are refreshed concurrently with other jobs. |
view_name |
The name of the materialized view to refresh. |
WITH NO DATA |
Drop the query results of the materialized view from storage. |
WITH DATA |
(Default behavior) Refresh the stored query results. |
Example
The following example uses the sample bank database, populated with some workload values.
Suppose that you create a materialized view on the bank table:
> CREATE MATERIALIZED VIEW overdrawn_accounts
AS SELECT id, balance
FROM bank
WHERE balance < 0;
> SELECT * FROM overdrawn_accounts;
id | balance
------+----------
1 | -17643
3 | -5928
13 | -3700
...
(402 rows)
Now suppose that you update the balance values of the bank table:
> UPDATE bank SET balance = 0 WHERE balance < 0;
UPDATE 402
The changes can be seen in the table with a simple SELECT statement against the table:
> SELECT id, balance
FROM bank
WHERE balance < 0;
id | balance
-----+----------
(0 rows)
Recall that materialized views do not automatically update their stored results. Selecting from overdrawn_accounts returns stored results, which are outdated:
> SELECT * FROM overdrawn_accounts;
id | balance
------+----------
1 | -17643
3 | -5928
13 | -3700
...
(402 rows)
To update the materialized view's results, use a REFRESH statement:
> REFRESH MATERIALIZED VIEW overdrawn_accounts;
> SELECT * FROM overdrawn_accounts;
id | balance
-----+----------
(0 rows)
See also
Was this helpful?
