Re: Implementing Incremental View Maintenance

Takuma Hoshiai wrote
> Hi,
>
> Attached is the latest patch (v12) to add support for Incremental
> Materialized View Maintenance (IVM).
> It is possible to apply to current latest master branch.
>
> Differences from the previous patch (v11) include:
> * support executing REFRESH MATERIALIZED VIEW command with IVM.
> * support unscannable state by WITH NO DATA option.
> * add a check for LIMIT/OFFSET at creating an IMMV
>
> If REFRESH is executed for IMMV (incremental maintainable materialized
> view), its contents is re-calculated as same as usual materialized views
> (full REFRESH). Although IMMV is basically keeping up-to-date data,
> rounding errors can be accumulated in aggregated value in some cases, for
> example, if the view contains sum/avg on float type columns. Running
> REFRESH command on IMMV will resolve this. Also, WITH NO DATA option
> allows to make IMMV unscannable. At that time, IVM triggers are dropped
> from IMMV because these become unneeded and useless.
>
> [...]

Hello,

regarding syntax REFRESH MATERIALIZED VIEW x WITH NO DATA

I understand that triggers are removed from the source tables, transforming
the INCREMENTAL MATERIALIZED VIEW into a(n unscannable) MATERIALIZED VIEW.

postgres=# refresh materialized view imv with no data;
REFRESH MATERIALIZED VIEW
postgres=# select * from imv;
ERROR: materialized view "imv" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.

This operation seems to me more of an ALTER command than a REFRESH ONE.

Wouldn't the syntax
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
SET WITH NO DATA
or
SET WITHOUT DATA
be better ?

Continuing into this direction, did you ever think about an other feature
like:
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
SET { NOINCREMENTAL }
or even
SET { NOINCREMENTAL | INCREMENTAL | INCREMENTAL CONCURRENTLY }

that would permit to switch between those modes and would keep frozen data
available in the materialized view during heavy operations on source tables
?

Regards
PAscal

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html