Re: Implementing Incremental View Maintenance

From: Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
To: "r(dot)takahashi_2(at)fujitsu(dot)com" <r(dot)takahashi_2(at)fujitsu(dot)com>
Cc: '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-11-25 07:37:10
Message-ID: 20211125163710.2f32ae3d4be5d5f9ade020b6@sraoss.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Wed, 24 Nov 2021 04:31:25 +0000
"r(dot)takahashi_2(at)fujitsu(dot)com" <r(dot)takahashi_2(at)fujitsu(dot)com> wrote:

> > > 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.
>
> I checked the same procedure on v24 patch.
> But following error occurs instead of the original error.
>
> ERROR: relation "ivm_t_index" already exists

Thank you for pointing out it!

Hmmm, an index is created when IMMV is defined, so CREAE INDEX called
after this would fail... Maybe, we should not create any index automatically
if IMMV is created WITH NO DATA.

I'll fix it after some investigation.

Regards,
Yugo Nagata

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Greg Nancarrow 2021-11-25 07:37:48 Re: [Proposal] Fully WAL logged CREATE DATABASE - No Checkpoints
Previous Message Michael Paquier 2021-11-25 07:04:23 Re: Deduplicate code updating ControleFile's DBState.