Hi PAscal, On Tue, 11 Feb 2020 15:04:12 -0700 (MST) legrand legrand <legrand_legr...@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 <nag...@sraoss.co.jp>
