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

On Tue, 27 Oct 2020 12:14:52 -0400
Adam Brusselback <adambrusselback@gmail.com> wrote:

> That was a good bit more work to get ready than I expected. It's broken
> into two scripts, one to create the schema, the other to load data and
> containing a couple check queries to ensure things are working properly
> (checking the materialized tables against a regular view for accuracy).

Thank you very much! I am really grateful.
 
> The first test case is to give us a definitive result on what "agreed
> pricing" is in effect at a point in time based on a product hierarchy
> our customers setup, and allow pricing to be set on nodes in that
> hierarchy, as well as specific products (with an order of precedence).
> The second test case maintains some aggregated amounts / counts / boolean
> logic at an "invoice" level for all the detail lines which make up that
> invoice.
> 
> Both of these are real-world use cases which were simplified a bit to make
> them easier to understand. We have other use cases as well, but with how
> much time this took to prepare i'll keep it at this for now.
> If you need anything clarified or have any issues, just let me know.

Although I have not look into it in details yet, in my understanding, it seems
that materialized views are used to show "pricing" or "invoice" information before
the order is confirmed, that is, before the transaction is committed. Definitely,
these will be use cases where immediate view maintenance is useful.  

I am happy because I found concrete use cases of immediate IVM. However, 
unfortunately, the view definitions in your cases are complex, and the current
implementation of the patch doesn't support it. We would like to improve the
feature in future so that more complex views could benefit from IVM.

Regards,
Yugo Nagata

> On Fri, Oct 23, 2020 at 3:58 AM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
> 
> > Hi Adam,
> >
> > On Thu, 22 Oct 2020 10:07:29 -0400
> > Adam Brusselback <adambrusselback@gmail.com> wrote:
> >
> > > Hey there Yugo,
> > > I've asked a coworker to prepare a self contained example that
> > encapsulates
> > > our multiple use cases.
> >
> > Thank you very much!
> >
> > > The immediate/eager approach is exactly what we need, as within the same
> > > transaction we have statements that can cause one of those "materialized
> > > tables" to be updated, and then sometimes have the need to query that
> > > "materialized table" in a subsequent statement and need to see the
> > changes
> > > reflected.
> >
> > The proposed patch provides the exact this feature and I think this will
> > meet
> > your needs.
> >
> > > As soon as my coworker gets that example built up I'll send a followup
> > with
> > > it attached.
> >
> > Great! We are looking forward to it.
> >
> > Regards,
> > Yugo Nagata
> >
> > --
> > Yugo NAGATA <nagata@sraoss.co.jp>
> >


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