From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
---|---|
To: | Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> |
Cc: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: refresh materialized view concurrently |
Date: | 2013-07-06 16:20:13 |
Message-ID: | 1373127613.54040.YahooMailNeo@web162905.mail.bf1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
refresh-concurrently-v3.patch | text/x-diff | 39.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2013-07-06 16:42:58 | Re: GIN improvements part 1: additional information |
Previous Message | Michael Alan Dorman | 2013-07-06 16:07:37 | A mailing-list based bug tracker |