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

From: bemanuel(dot)pe(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #11208: Refresh Materialized View Concurrently bug using user Postgres
Date: 2014-08-18 21:28:03
Message-ID: 20140818212803.2536.42468@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

The following bug has been logged on the website:

Bug reference: 11208
Logged by: Bruno Emanuel de Andrade Silva
Email address: bemanuel(dot)pe(at)gmail(dot)com
PostgreSQL version: 9.4beta2
Operating system: Linux
Description:

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=> \d+ mv_foo
Materialized view "public.mv_foo"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
i | integer | | plain | |
md5 | text | | extended | |
View definition:
SELECT foo_data.i,
foo_data.md5
FROM foo_data;

tjma_dw=> create unique index on mv_foo (i);
CREATE INDEX
tjma_dw=> \q
--ATÉ AQUI OK
/pgsql/pg94/bin/psql -Upostgres -p 5434 tjma_dw
psql (9.4beta2)
Type "help" for help.

tjma_dw=# \d+ mv_foo ^C
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)"
--WRONG THING
tjma_dw=#

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kevin Grittner 2014-08-18 22:25:31 Re: BUG #11208: Refresh Materialized View Concurrently bug using user Postgres
Previous Message justin.vanwinkle 2014-08-18 19:56:34 BUG #11207: empty path will segfault jsonb #>

Browse pgsql-hackers by date

  From Date Subject
Next Message Greg Stark 2014-08-18 22:19:49 Re: GIST create index very very slow
Previous Message Joshua D. Drake 2014-08-18 21:02:26 Re: Hokey wrong versions of libpq in apt.postgresql.org