Re: Implementing Incremental View Maintenance

Hi,

Attached is the latest patch (v12) to add support for Incremental Materialized View Maintenance (IVM).
It is possible to apply to current latest master branch.

Differences from the previous patch (v11) include:
* support executing REFRESH MATERIALIZED VIEW command with IVM.
* support unscannable state by WITH NO DATA option.
* add a check for LIMIT/OFFSET at creating an IMMV

If REFRESH is executed for IMMV (incremental maintainable materialized view), its contents is re-calculated as same as usual materialized views (full REFRESH). Although IMMV is basically keeping up-to-date data, rounding errors can be accumulated in aggregated value in some cases, for example, if the view contains sum/avg on float type columns. Running REFRESH command on IMMV will resolve this. Also, WITH NO DATA option allows to make IMMV unscannable. At that time, IVM triggers are dropped from IMMV because these become unneeded and useless.

Also, we added new deptype option 'm' in pg_depend view for checking a trigger is for IVM. Please tell me, if add new deptype option is unacceptable. It is also possible to perform the check by referencing pg_depend and pg_trigger, pg_proc view instead of adding a new deptype.
We update IVM restrictions. LIMIT/OFFSET clause is not supported with iVM because it is not suitable for incremental changes to the materialized view.
This issue is reported by nuko-san.
https://www.postgresql.org/message-id/CAF3Gu1ZK-s9GQh=70n8+21rBL8+fKW4tV3Ce-xuFXMsNFPO+zQ@mail.gmail.com

Best Regards,
Takuma Hoshiai

On Mon, 27 Jan 2020 09:19:05 +0900
Takuma Hoshiai <hoshiai(at)sraoss(dot)co(dot)jp> wrote:

> On Mon, 20 Jan 2020 16:57:58 +0900
> Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp> wrote:
>
> > On Fri, 17 Jan 2020 14:10:32 -0700 (MST)
> > legrand legrand <legrand_legrand(at)hotmail(dot)com> wrote:
> >
> > > Hello,
> > >
> > > It seems that patch v11 doesn't apply any more.
> > > Problem with "scanRTEForColumn" maybe because of change:
> >
> > Thank you for your reporting! We will fix this in the next update.
>
> Although I have been working conflict fix and merge latest master, it
> takes a little longer, because it has large impact than we thought.
>
> Please wait a little more.
>
> Regards
> Takuma Hoshiai
>
>
> > Regards,
> > Yugo Nagata
> >
> > >
> > > https://git.postgresql.org/pg/commitdiff/b541e9accb28c90656388a3f827ca3a68dd2a308
> > >
> > > Regards
> > > PAscal
> > >
> > >
> > >
> > > --
> > > Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
> > >
> > >
> >
> >
> > --
> > Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
> >
> >
> >
>
>
> --
> Takuma Hoshiai <hoshiai(at)sraoss(dot)co(dot)jp>
>
>
>
>

--
Takuma Hoshiai <hoshiai(at)sraoss(dot)co(dot)jp>