Date: Tue, 11 Feb 2020 15:04:12 -0700 (MST)
From: legrand legrand <legrand_legrand@hotmail.com>
To: pgsql-hackers@postgresql.org
Message-ID: <1581458652080-0.post@n3.nabble.com>
In-Reply-To: <20200204105802.7bf70c62993d63c69b3f860f@sraoss.co.jp>
References: <20191129181954.208da0d63c1ff9e556008e80@sraoss.co.jp>
 <20191201025514.GY2355@paquier.xyz>
 <20191202.100118.1160224336488931090.t-ishii@sraoss.co.jp>
 <20191202110538.a6ff2cd84f488f42eab621eb@sraoss.co.jp>
 <20191220140232.57b43c4f6296f793d42946b2@sraoss.co.jp>
 <20191226110302.6e85967388aea26292dde1b5@sraoss.co.jp>
 <1579295432714-0.post@n3.nabble.com>
 <20200120165758.d30f22cd597975466c7f54ae@sraoss.co.jp>
 <20200127091905.dbcbc384867dd81d0acfb168@sraoss.co.jp>
 <20200204105802.7bf70c62993d63c69b3f860f@sraoss.co.jp>
Subject: Re: Implementing Incremental View Maintenance
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

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