Re: Implementing Incremental View Maintenance

From: Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
To: Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
Cc: "r(dot)takahashi_2(at)fujitsu(dot)com" <r(dot)takahashi_2(at)fujitsu(dot)com>, 'Zhihong Yu' <zyu(at)yugabyte(dot)com>, vignesh C <vignesh21(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp>, Thomas Munro <thomas(dot)munro(at)gmail(dot)com>
Subject: Re: Implementing Incremental View Maintenance
Date: 2021-09-30 06:37:55
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.

Yugo Nagata

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2021-09-30 06:47:44 pg_stat_replication_slots docs
Previous Message Greg Nancarrow 2021-09-30 06:21:25 Re: Logical replication keepalive flood