> 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 <nag...@sraoss.co.jp> wrote: > >> Hi Paul, >> >> Thank you for your suggestion. >> >> On Sun, 15 Sep 2019 11:52:22 -0600 >> Paul Draper <paulddra...@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 <nag...@sraoss.co.jp> >>
