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>