Re: Implementing Incremental View Maintenance

Hi PAscal,

On Tue, 11 Feb 2020 15:04:12 -0700 (MST)
legrand legrand <legrand_legrand@hotmail.com> wrote:
> 
> 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 ?

We use "REFRESH ... WITH NO DATA" because there is already the syntax
to make materialized views non-scannable. We are just following in this.

https://www.postgresql.org/docs/12/sql-refreshmaterializedview.html

> 
> 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
> ?

Thank you for your suggestion! I agree that the feature to switch between
normal materialized view and incrementally maintainable view is useful.
We will add this to our ToDo list. Regarding its syntax, 
I would not like to add new keyword like NONINCREMENTAL, so how about 
the following

 ALTER MATERIALIZED VIEW ... SET {WITH | WITHOUT} INCREMENTAL REFRESH

although this is just a idea and we will need discussion on it.


Regards,
Yugo Nagata

-- 
Yugo NAGATA <nagata@sraoss.co.jp>