public inbox for [email protected]  

help / color / mirror / Atom feed

From: Yugo NAGATA <[email protected]>
To: legrand legrand <[email protected]>
Cc: [email protected]
Subject: Re: Implementing Incremental View Maintenance
Date: Thu, 13 Feb 2020 15:05:40 +0900
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

Hi PAscal,

On Tue, 11 Feb 2020 15:04:12 -0700 (MST)
legrand legrand <[email protected]> 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 <[email protected]>

view thread (215+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Implementing Incremental View Maintenance
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox