Re: BUG #11208: Refresh Materialized View Concurrently bug using user Postgres

Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> "bemanuel(dot)pe(at)gmail(dot)com" <bemanuel(dot)pe(at)gmail(dot)com> wrote:
>
>> tjma_dw=> set role user_dw;
>>
>> tjma_dw=> CREATE TABLE foo_data AS SELECT i, md5(random()::text) FROM
>> generate_series(1, 10) i;
>> SELECT 10
>> tjma_dw=> CREATE MATERIALIZED VIEW mv_foo AS SELECT * FROM foo_data;
>> SELECT 10
>> tjma_dw=> ALTER MATERIALIZED VIEW mv_foo OWNER TO user_dw;
>> ALTER MATERIALIZED VIEW
>> tjma_dw=> REFRESH MATERIALIZED VIEW mv_foo;
>> REFRESH MATERIALIZED VIEW
>> tjma_dw=> ALTER TABLE foo_data OWNER TO user_dw;
>> ALTER TABLE
>> tjma_dw=> REFRESH MATERIALIZED VIEW mv_foo;
>> REFRESH MATERIALIZED VIEW
>> tjma_dw=> create unique index on mv_foo (i);
>> CREATE INDEX
>
>> /pgsql/pg94/bin/psql -Upostgres -p 5434 tjma_dw
>
>> tjma_dw=# refresh materialized view CONCURRENTLY mv_foo;
>> ERROR:  permission denied for relation pg_temp_432971_2
>> CONTEXT:  SQL statement "DELETE FROM public.mv_foo mv WHERE ctid
>> OPERATOR(pg_catalog.=) ANY (SELECT diff.tid FROM
> pg_temp_10.pg_temp_432971_2
>> diff WHERE diff.tid IS NOT NULL AND diff.newdata IS NULL)"
>
> Yeah, that's a bug

Attached is my proposed fix.  I will push it in a day or two if there
are no objections.

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