Re: Implementing Incremental View Maintenance

Hello Takahashi-san,

On Wed, 22 Sep 2021 18:53:43 +0900
Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp> wrote:

> Hello Takahashi-san,
>
> On Thu, 5 Aug 2021 08:53:47 +0000
> "r(dot)takahashi_2(at)fujitsu(dot)com" <r(dot)takahashi_2(at)fujitsu(dot)com> wrote:
>
> > Hi Nagata-san,
> >
> >
> > Thank you for your reply.
> >
> > > I'll investigate this more, but we may have to prohibit views on partitioned
> > > table and partitions.
> >
> > I think this restriction is strict.
> > This feature is useful when the base table is large and partitioning is also useful in such case.
>
> One reason of this issue is the lack of triggers on partitioned tables or partitions that
> are not specified in the view definition.
>
> However, even if we create triggers recursively on the parents or children, we would still
> need more consideration. This is because we will have to convert the format of tuple of
> modified table to the format of the table specified in the view for cases that the parent
> and some children have different format.
>
> I think supporting partitioned tables can be left for the next release.
>
> >
> > I have several additional comments on the patch.
> >
> >
> > (1)
> > The following features are added to transition table.
> > - Prolong lifespan of transition table
> > - If table has row security policies, set them to the transition table
> > - Calculate pre-state of the table
> >
> > Are these features only for IVM?
> > If there are other useful case, they should be separated from IVM patch and
> > should be independent patch for transition table.
>
> Maybe. However, we don't have good idea about use cases other than IVM of
> them for now...
>
> >
> > (2)
> > DEPENDENCY_IMMV (m) is added to deptype of pg_depend.
> > What is the difference compared with existing deptype such as DEPENDENCY_INTERNAL (i)?
>
> DEPENDENCY_IMMV was added to clear that a certain trigger is related to IMMV.
> We dropped the IVM trigger and its dependencies from IMMV when REFRESH ... WITH NO DATA
> is executed. Without the new deptype, we may accidentally delete a dependency created
> with an intention other than the IVM trigger.
>
> > (3)
> > Converting from normal materialized view to IVM or from IVM to normal materialized view is not implemented yet.
> > Is it difficult?
> >
> > I think create/drop triggers and __ivm_ columns can achieve this feature.
>
> I think it is harder than you expected. When an IMMV is switched to a normal
> materialized view, we needs to drop hidden columns (__ivm_count__ etc.), and in
> the opposite case, we need to create them again. The former (IMMV->IVM) might be
> easer, but for the latter (IVM->IMMV) I wonder we would need to re-create IMMV.

I am sorry but I found a mistake in the above description.
"IMMV->IVM" and "IVM->IMMV" were wrong. I've should use "IMMV->MV" and "MV->IMMV"
where MV means normal materialized view.w.

Regards,
Yugo Nagata

--
Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>