Postgresql – Materialized View PostgreSQL

There are three ways,

• Use foreign data in a table that holds the inputs.
• Make sell_date a function of the current time. Such as AND sell_date > current_time - '5 days'.
• If nothing depends on it, drop and reload a new ad-hoc MATERIALIZED VIEW by the same name in a transaction.

And this is exactly what happens when you create materialized views on a prebuilt table that is already stale.

Recreating the mview the same way will not solve this, why would it? You still use the same incomplete prebuilt table.

Recreating mview logs has nothing to do with it. If you recreate mview logs now, that does not include data that was modified in the past.

Yes, the solution is complete refresh, and that does not necessarily truncate the table.

REFRESH Procedures

DBMS_MVIEW.REFRESH has a parameter called atomic_refresh which defaults to true.

A complete refresh with atomic_refresh set to true refreshes all mviews given as parameter in a single transaction. It is achieved by DELETE-ing the rows from the mview.

A complete refresh with atomic_refresh set to false refreshes all mviews given as parameter in seperate transactions. It is achieved by TRUNCATE-ing the mviews (which is much quicker than DELETE).

So specify atomic_refresh => true, and the mview will not be truncated.