Re: Implementing Incremental View Maintenance

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.

Regards,
Yugo Nagata

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