Re: Implementing Incremental View Maintenance

> Have you had any thoughts for more than two joined tables?

I am not sure what you are asking here but if you are asking if IVM
supports two or more tables involved in a join, we already support it:

DROP MATERIALIZED VIEW mv1;
DROP MATERIALIZED VIEW
DROP TABLE t1;
DROP TABLE
DROP TABLE t2;
DROP TABLE
DROP TABLE t3;
DROP TABLE
CREATE TABLE t1(i int, j int);
CREATE TABLE
CREATE TABLE t2(k int, l int);
CREATE TABLE
CREATE TABLE t3(m int, n int);
CREATE TABLE
INSERT INTO t1 VALUES(1,10),(2,11);
INSERT 0 2
INSERT INTO t2 VALUES(1,20),(2,21);
INSERT 0 2
INSERT INTO t3 VALUES(1,30),(2,31);
INSERT 0 2
CREATE INCREMENTAL MATERIALIZED VIEW mv1 AS SELECT * FROM t1 INNER JOIN t2 ON t1.i = t2.k INNER JOIN t3 ON t1.i = t3.m;
SELECT 2
SELECT * FROM mv1;
i | j | k | l | m | n
---+----+---+----+---+----
1 | 10 | 1 | 20 | 1 | 30
2 | 11 | 2 | 21 | 2 | 31
(2 rows)

UPDATE t1 SET j = 15 WHERE i = 1;
UPDATE 1
SELECT * FROM mv1;
i | j | k | l | m | n
---+----+---+----+---+----
2 | 11 | 2 | 21 | 2 | 31
1 | 15 | 1 | 20 | 1 | 30
(2 rows)

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Either there needs to be an quadratic number of joins, or intermediate join
> results need to be stored and reused.
>
> On Tue, Sep 17, 2019 at 8:50 AM Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:
>
>> Hi Paul,
>>
>> Thank you for your suggestion.
>>
>> On Sun, 15 Sep 2019 11:52:22 -0600
>> Paul Draper <paulddraper(at)gmail(dot)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(at)sraoss(dot)co(dot)jp>
>>