Date: Tue, 17 Sep 2019 19:02:40 +0900
From: Yugo Nagata <nagata@sraoss.co.jp>
To: Paul Draper <paulddraper@gmail.com>
Cc: pgsql-hackers@postgresql.org, kgrittn@gmail.com, thomas.munro@gmail.com
Subject: Re: Implementing Incremental View Maintenance
Message-Id: <20190917190240.f331029ccca7ba8af6bf08e8@sraoss.co.jp>
In-Reply-To: 
 <CAApx4VQJHtiXXoPaEsr51awFE8GZr8m+HB_q5YV7R27bT4p82w@mail.gmail.com>
References: 
 <CAApx4VQJHtiXXoPaEsr51awFE8GZr8m+HB_q5YV7R27bT4p82w@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hi Paul,

Thank you for your suggestion.

On Sun, 15 Sep 2019 11:52:22 -0600
Paul Draper <paulddraper@gmail.com> wrote:

> As I understand it, the current patch performs immediate IVM using AFTER
> STATEMENT trigger transition tables.
> 
> However, multiple tables can be modified *before* AFTER STATEMENT triggers
> are fired.
> 
> CREATE TABLE example1 (a int);
> CREATE TABLE example2 (a int);
> 
> CREATE INCREMENTAL MATERIALIZED VIEW mv AS
> SELECT example1.a, example2.a
> FROM example1 JOIN example2 ON a;
> 
> WITH
>   insert1 AS (INSERT INTO example1 VALUES (1)),
>   insert2 AS (INSERT INTO example2 VALUES (1))
> SELECT NULL;
> 
> Changes to example1 are visible in an AFTER STATEMENT trigger on example2,
> and vice versa. Would this not result in the (1, 1) tuple being
> "double-counted"?
> 
> IVM needs to either:
> 
> (1) Evaluate deltas "serially' (e.g. EACH ROW triggers)
> 
> (2) Have simultaneous access to multiple deltas:
> delta_mv = example1 x delta_example2 + example2 x delta_example1 -
> delta_example1 x delta_example2
> 
> This latter method is the "logged" approach that has been discussed for
> deferred evaluation.
> 
> tl;dr It seems that AFTER STATEMENT triggers required a deferred-like
> implementation anyway.

You are right,  the latest patch doesn't support the situation where
multiple tables are modified in a query. I noticed this when working
on self-join, which also virtually need to handle multiple table
modification.

I am now working on this issue and the next patch will enable to handle
this situation. I plan to submit the patch during this month. Roughly
speaking, in the new implementation, AFTER STATEMENT triggers are used to
collect  information of modified table and its changes (= transition tables), 
and then the only last trigger updates the view. This will avoid the
double-counting. I think this implementation also would be a base of
deferred approach implementation in future where "logs" are used instead
of transition tables.

Regards,
Yugo Nagata

-- 
Yugo Nagata <nagata@sraoss.co.jp>