Re: refresh materialized view concurrently

Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> wrote:

> Oops!

Indeed.  Thanks for the careful testing.

> drop materialized view if exists mv;
> drop table if exists foo;
> create table foo(a, b) as values(1, 10);
> create materialized view mv as select * from foo;
> create unique index on mv(a);
> insert into foo select * from foo;
> refresh materialized view mv;
> refresh materialized view concurrently mv;
>
> test=# refresh materialized view mv;
> ERROR:  could not create unique index "mv_a_idx"
> DETAIL:  Key (a)=(1) is duplicated.
> test=# refresh materialized view concurrently mv;
> REFRESH MATERIALIZED VIEW

Fixed by scanning the temp table for duplicates before generating
the diff:

test=# refresh materialized view concurrently mv;
ERROR:  new data for "mv" contains duplicate rows without any NULL columns
DETAIL:  Row: (1,10)

> [ matview with all columns covered by unique indexes fails ]

Fixed.

> Other than these, I've found index is opened with NoLock, relying
> on ExclusiveLock of parent matview, and ALTER INDEX SET
> TABLESPACE or something similar can run concurrently, but it is
> presumably safe.  DROP INDEX, REINDEX would be blocked by the
> ExclusiveLock.

Since others were also worried that an index definition could be
modified while another process is holding an ExclusiveLock on its
table, I changed this.

New version attached.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company