Re: Implementing Incremental View Maintenance

It's not mentioned below but some bugs including seg fault when
--enable-casser is enabled was also fixed in this patch.

BTW, I found a bug with min/max support in this patch and I believe
Yugo is working on it. Details:
https://github.com/sraoss/pgsql-ivm/issues/20

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

From: Yugo Nagata <nagata@sraoss.co.jp>
Subject: Re: Implementing Incremental View Maintenance
Date: Wed, 31 Jul 2019 18:08:51 +0900
Message-ID: <20190731180851.73856441d8abb494bf5e68e7@sraoss.co.jp>

> Hi,
> 
> Attached is the latest patch for supporting min and max aggregate functions.
> 
> When new tuples are inserted into base tables, if new values are small
> (for min) or large (for max), matview just have to be updated with these
> new values. Otherwise, old values just remains.
>     
> However, in the case of deletion, this is more complicated. If deleted
> values exists in matview as current min or max, we have to recomputate
> new min or max values from base tables for affected groups, and matview
> should be updated with these recomputated values. 
> 
> Also, regression tests for min/max are also added.
> 
> In addition, incremental update algorithm of avg aggregate values is a bit
> improved. If an avg result in materialized views is updated incrementally
> y using the old avg value, numerical errors in avg values are accumulated
> and the values get wrong eventually. To prevent this, both of sum and count
> values are contained in views as hidden columns and use them to calculate
> new avg value instead of using old avg values.
> 
> Regards,
> 
> On Fri, 26 Jul 2019 11:35:53 +0900
> Yugo Nagata <nagata@sraoss.co.jp> wrote:
> 
>> Hi,
>> 
>> I've updated the wiki page of Incremental View Maintenance.
>> 
>> https://wiki.postgresql.org/wiki/Incremental_View_Maintenance
>> 
>> On Thu, 11 Jul 2019 13:28:04 +0900
>> Yugo Nagata <nagata@sraoss.co.jp> wrote:
>> 
>> > Hi Thomas,
>> > 
>> > Thank you for your review and discussion on this patch!
>> > 
>> > > > 2019年7月8日(月) 15:32 Thomas Munro <thomas.munro@gmail.com>:
>> > > > 
>> > > > > On Fri, Jun 28, 2019 at 10:56 PM Yugo Nagata <nagata@sraoss.co.jp> wrote:
>> > > > > > Attached is a WIP patch of IVM which supports some aggregate functions.
>> > > > >
>> > > > > Hi Nagata-san and Hoshiai-san,
>> > > > >
>> > > > > Thank you for working on this.  I enjoyed your talk at PGCon.  I've
>> > > > > added Kevin Grittner just in case he missed this thread; he has talked
>> > > > > often about implementing the counting algorithm, and he wrote the
>> > > > > "trigger transition tables" feature to support exactly this.  While
>> > > > > integrating trigger transition tables with the new partition features,
>> > > > > we had to make a number of decisions about how that should work, and
>> > > > > we tried to come up with answers that would work for IMV, and I hope
>> > > > > we made the right choices!
>> > 
>> > Transition tables is a great feature. I am now using this in my implementation
>> > of IVM, but the first time I used this feature was when I implemented a PoC
>> > for extending view updatability of PostgreSQL[1]. At that time, I didn't know
>> > that this feature is made originally aiming to support IVM. 
>> > 
>> > [1] https://www.pgcon.org/2017/schedule/events/1074.en.html
>> > 
>> > I think transition tables is a good choice to implement a statement level
>> > immediate view maintenance where materialized views are refreshed in a statement
>> > level after trigger. However, when implementing a transaction level immediate
>> > view maintenance where views are refreshed per transaction, or deferred view
>> > maintenance, we can't update views in a after trigger, and we will need an
>> > infrastructure to manage change logs of base tables. Transition tables can be
>> > used to collect these logs, but using logical decoding of WAL is another candidate.
>> > In any way, if these logs can be collected in a tuplestore, we might able to
>> > convert this to "ephemeral named relation (ENR)" and use this to calculate diff
>> > sets for views.
>> > 
>> > > > >
>> > > > > I am quite interested to learn how IVM interacts with SERIALIZABLE.
>> > > > >
>> > > > 
>> > > >  Nagata-san has been studying this. Nagata-san, any comment?
>> > 
>> > In SERIALIZABLE or REPEATABLE READ level, table changes occurred in other 
>> > ransactions are not visible, so views can not be maintained correctly in AFTER
>> > triggers. If a view is defined on two tables and each table is modified in
>> > different concurrent transactions respectively, the result of view maintenance
>> > done in trigger functions can be incorrect due to the race condition. This is the
>> > reason why such transactions are aborted immediately in that case in my current
>> > implementation.
>> > 
>> > One idea to resolve this is performing view maintenance in two phases. Firstly, 
>> > views are updated using only table changes visible in this transaction. Then, 
>> > just after this transaction is committed, views have to be updated additionally 
>> > using changes happened in other transactions to keep consistency. This is a just 
>> > idea, but  to implement this idea, I think, we will need keep to keep and 
>> > maintain change logs.
>> > 
>> > > > > A couple of superficial review comments:
>> > 
>> > 
>> >  
>> > > > > +            const char *aggname = get_func_name(aggref->aggfnoid);
>> > > > > ...
>> > > > > +            else if (!strcmp(aggname, "sum"))
>> > > > >
>> > > > > I guess you need a more robust way to detect the supported aggregates
>> > > > > than their name, or I guess some way for aggregates themselves to
>> > > > > specify that they support this and somehow supply the extra logic.
>> > > > > Perhaps I just waid what Greg Stark already said, except not as well.
>> > 
>> > Yes. Using name is not robust because users can make same name aggregates like 
>> > sum(text) (although I am not sure this makes sense). We can use oids instead 
>> > of names, but it would be nice to extend pg_aggregate and add new attributes 
>> > for informing that this supports IVM and for providing functions for IVM logic.
>> > 
>> > > > > As for the question of how
>> > > > > to reserve a namespace for system columns that won't clash with user
>> > > > > columns, according to our manual the SQL standard doesn't allow $ in
>> > > > > identifier names, and according to my copy SQL92 "intermediate SQL"
>> > > > > doesn't allow identifiers that end in an underscore.  I don't know
>> > > > > what the best answer is but we should probably decide on a something
>> > > > > based the standard.
>> > 
>> > Ok, so we should use "__ivm_count__" since this ends in "_" at least.
>> > 
>> > Another idea is that users specify the name of this special column when 
>> > defining materialized views with IVM support. This way can avoid the conflict 
>> > because users will specify a name which does not appear in the target list.
>> > 
>> > As for aggregates supports, it may be also possible to make it a restriction 
>> > that count(expr) must be in the target list explicitly when sum(expr) or 
>> > avg(expr) is included, instead of making hidden column like __ivm_count_sum__,
>> > like Oracle does.
>> > 
>> > > > >
>> > > > > As for how to make internal columns invisible to SELECT *, previously
>> > > > > there have been discussions about doing that using a new flag in
>> > > > > pg_attribute:
>> > > > >
>> > > > >
>> > > > >
https://www.postgresql.org/message-id/flat/CAEepm%3D3ZHh%3Dp0nEEnVbs1Dig_UShPzHUcMNAqvDQUgYgcDo-pA%40mail.gmail.com
>> > 
>> > I agree implementing this feature in PostgreSQL since there are at least a few
>> > use cases, IVM and temporal database.
>> > 
>> > > > >
>> > > > > +                            "WITH t AS ("
>> > > > > +                            "  SELECT diff.__ivm_count__,
>> > > > > (diff.__ivm_count__ = mv.__ivm_count__) AS for_dlt, mv.ctid"
>> > > > > +                            ", %s"
>> > > > > +                            "  FROM %s AS mv, %s AS diff WHERE (%s) =
>> > > > > (%s)"
>> > > > > +                            "), updt AS ("
>> > > > > +                            "  UPDATE %s AS mv SET __ivm_count__ =
>> > > > > mv.__ivm_count__ - t.__ivm_count__"
>> > > > > +                            ", %s "
>> > > > > +                            "  FROM t WHERE mv.ctid = t.ctid AND NOT
>> > > > > for_dlt"
>> > > > > +                            ") DELETE FROM %s AS mv USING t WHERE
>> > > > > mv.ctid = t.ctid AND for_dlt;",
>> > > > >
>> > > > > I fully understand that this is POC code, but I am curious about one
>> > > > > thing.  These queries that are executed by apply_delta() would need to
>> > > > > be converted to C, or at least used reusable plans, right?  Hmm,
>> > > > > creating and dropping temporary tables every time is a clue that the
>> > > > > ultimate form of this should be tuplestores and C code, I think,
>> > > > > right?
>> > 
>> > I used SPI just because REFRESH CONCURRENTLY uses this, but, as you said,
>> > it is inefficient to create/drop temp tables and perform parse/plan every times.
>> > It seems to be enough to perform this once when creating materialized views or 
>> > at the first maintenance time.
>> > 
>> > 
>> > Best regards,
>> > Yugo Nagata
>> > 
>> > 
>> > -- 
>> > Yugo Nagata <nagata@sraoss.co.jp>
>> > 
>> > 
>> 
>> 
>> -- 
>> Yugo Nagata <nagata@sraoss.co.jp>
>> 
>> 
> 
> 
> -- 
> Yugo Nagata <nagata@sraoss.co.jp>