Hitoshi Harada <umi.tanuki@gmail.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