Re: Implementing Incremental View Maintenance

On Wed, 22 Sep 2021 19:17:12 +0900
Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp> wrote:

> Hi hackers,
>
> I attached the updated patch including fixes reported by
> Zhihong Yu and Ryohei Takahashi.

Cfbot seems to fail to open the tar file, so I attached
patch files instead of tar ball.

Regards,
Yugo Nagata

> On Wed, 22 Sep 2021 19:12:27 +0900
> Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp> wrote:
>
> > Hello Takahashi-san,
> >
> > On Mon, 6 Sep 2021 10:06:37 +0000
> > "r(dot)takahashi_2(at)fujitsu(dot)com" <r(dot)takahashi_2(at)fujitsu(dot)com> wrote:
> >
> > > Hi Nagata-san,
> > >
> > >
> > > I'm still reading the patch.
> > > I have additional comments.
> >
> > Thank you for your comments!
> >
> > >
> > > (1)
> > > In v23-0001-Add-a-syntax-to-create-Incrementally-Maintainabl.patch, ivm member is added to IntoClause struct.
> > > I think it is necessary to modify _copyIntoClause() and _equalIntoClause() functions.
> >
> > Ok. I'll fix _copyIntoClause() and _equalIntoClause() as well as _readIntoClause() and _outIntoClause().
> >
> > > (2)
> > > By executing pg_dump with v23-0005-Add-Incremental-View-Maintenance-support-to-pg_d.patch,
> > > the constraint which is automatically created during "CREATE INCREMENTAL MATERIALIZED VIEW" is also dumped.
> > > This cause error during recovery as follows.
> > >
> > > ivm=# create table t (c1 int, c2 int);
> > > CREATE TABLE
> > > ivm=# create incremental materialized view ivm_t as select distinct c1 from t;
> > > NOTICE: created index "ivm_t_index" on materialized view "ivm_t"
> > > SELECT 0
> > >
> > > Then I executed pg_dump.
> > >
> > > In the dump, the following SQLs appear.
> > >
> > > CREATE INCREMENTAL MATERIALIZED VIEW public.ivm_t AS
> > > SELECT DISTINCT t.c1
> > > FROM public.t
> > > WITH NO DATA;
> > >
> > > ALTER TABLE ONLY public.ivm_t
> > > ADD CONSTRAINT ivm_t_index UNIQUE (c1);
> > >
> > > If I execute psql with the result of pg_dump, following error occurs.
> > >
> > > ERROR: ALTER action ADD CONSTRAINT cannot be performed on relation "ivm_t"
> > > DETAIL: This operation is not supported for materialized views.
> >
> > Good catch! It was my mistake creating unique constraints on IMMV in spite of
> > we cannot defined them via SQL. I'll fix it to use unique indexes instead of
> > constraints.
> >
> > Regards,
> > Yugo Nagata
> >
> > --
> > Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
> >
> >
>
>
> --
> Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>

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