Re: Implementing Incremental View Maintenance

Hi PAscal,

On Tue, 11 Feb 2020 15:04:12 -0700 (MST)
legrand legrand <legrand_legrand(at)hotmail(dot)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(at)sraoss(dot)co(dot)jp>