Re: Implementing Incremental View Maintenance - Mailing list pgsql-hackers

From Paul Draper
Subject Re: Implementing Incremental View Maintenance
Date
Msg-id CAApx4VQJHtiXXoPaEsr51awFE8GZr8m+HB_q5YV7R27bT4p82w@mail.gmail.com
Whole thread Raw
In response to Implementing Incremental View Maintenance  (Yugo Nagata <nagata@sraoss.co.jp>)
Responses Re: Implementing Incremental View Maintenance
List pgsql-hackers
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.

pgsql-hackers by date:

Previous
From: Kuntal Ghosh
Date:
Subject: Re: POC: Cleaning up orphaned files using undo logs
Next
From: Tomas Vondra
Date:
Subject: Re: (Re)building index using itself or another index of the sametable