Re: refresh materialized view concurrently

On Thu, Jun 27, 2013 at 12:19 AM, Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>wrote:

>
>
>
> On Wed, Jun 26, 2013 at 1:38 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>
>>
>
New version attached.
>>
>>
>> Will take another look.
>
>
>
Oops!

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

Here's one more.

create table foo(a, b, c) as values(1, 2, 3);
create materialized view mv as select * from foo;
create unique index on mv (a);
create unique index on mv (b);
create unique index on mv (c);
insert into foo values(2, 3, 4);
insert into foo values(3, 4, 5);
refresh materialized view concurrently mv;

test=# refresh materialized view concurrently mv;
ERROR: syntax error at or near "FROM"
LINE 1: UPDATE public.mv x SET FROM pg_temp_2.pg_temp_16615_2 d WHE...
^
QUERY: UPDATE public.mv x SET FROM pg_temp_2.pg_temp_16615_2 d WHERE
d.tid IS NOT NULL AND x.ctid = d.tid

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.

--
Hitoshi Harada