Re: [GENERAL] pg_restore misuse or bug?

Поиск
Список
Период
Сортировка
От Jordan Gigov
Тема Re: [GENERAL] pg_restore misuse or bug?
Дата
Msg-id CA+nBocBUFr1-xBvaj3rYVrDqDSgvzqNVjdYZdEghVBLDoKayCg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [GENERAL] pg_restore misuse or bug?  (Jordan Gigov <coladict@gmail.com>)
Список pgsql-general
Assuming you have a user called "test", this will create a database that suffers from this problem.

create database mvtest;
\c mvtest
create table main_table (id serial not null, something varchar(20), primary key (id));
create table child_table (id serial not null, parent_id int not null, somedate date not null, someval int not null, primary key(id), foreign key(parent_id) references main_table(id));

insert into main_table(something) values('X-Men'),('Batman');
insert into child_table(parent_id, somedate, someval) values(2,'1989-06-23',10),(2,'1992-06-19',4),(1,'2000-07-14',13),(1,'2014-05-23',16);

CREATE MATERIALIZED VIEW movie_things AS
SELECT mt.*, jsonb_object(array_agg(ct.somedate)::text[], array_agg(ct.someval)::text[]) AS release_prizes FROM main_table mt
LEFT JOIN child_table ct ON (mt.id = ct.parent_id) GROUP BY mt.id;
CREATE UNIQUE INDEX IF NOT EXISTS movie_things_id_idx ON movie_things USING btree (id);
ALTER MATERIALIZED VIEW movie_things CLUSTER ON movie_things_id_idx, OWNER TO test;

On 21 July 2017 at 17:25, Jordan Gigov <coladict@gmail.com> wrote:
This is on version 9.5, 9.6 and 10beta2. I could probably make a
test-case over the weekend if I'm at home.

On 21 July 2017 at 17:03, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jordan Gigov <coladict@gmail.com> writes:
>> When running pg_restore as the superuser it gives the following error
>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>> pg_restore: [archiver (db)] Error from TOC entry 3253; 0 320628
>> MATERIALIZED VIEW DATA combined_query_data web_user
>> pg_restore: [archiver (db)] could not execute query: ERROR:
>> permission denied for relation first_table_in_from_list
>>     Command was: REFRESH MATERIALIZED VIEW combined_query_data;
>
> What PG version is this?  Can you provide a self-contained test case?
>
>> I see no reason why the superuser would get a "permission denied"
>> error.
>
> Matview queries are run as the owner of the matview, so this isn't
> as surprising as all that.  But if the matview works in your normal
> usage, then pg_dump must be doing something wrong, perhaps emitting
> grants in the wrong order.
>
>                         regards, tom lane

В списке pgsql-general по дате отправления:

Предыдущее
От: Tim Clarke
Дата:
Сообщение: Re: [GENERAL] monitoring PostgreSQL
Следующее
От: Jeff Janes
Дата:
Сообщение: Re: [GENERAL] Monitoring of a hot standby with a largely idle master