Re: Implementing Incremental View Maintenance

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).

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.

On Fri, Oct 23, 2020 at 3:58 AM Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp> wrote:

> Hi Adam,
>
> On Thu, 22 Oct 2020 10:07:29 -0400
> Adam Brusselback <adambrusselback(at)gmail(dot)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(at)sraoss(dot)co(dot)jp>
>