On Wed, 24 Nov 2021 04:31:25 +0000
"r.takahashi_2@fujitsu.com" <r.takahashi_2@fujitsu.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@sraoss.co.jp>