Re: Consider Parallelism While Planning For REFRESH MATERIALIZED VIEW

On Mon, Mar 15, 2021 at 8:25 PM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> > > The problem with a case like REFRESH MATERIALIZED VIEW is that there's
> > > nothing to prevent something that gets run in the course of the query
> > > from trying to access the view (and the heavyweight lock won't prevent
> > > that, due to group locking). That's probably a stupid thing to do,
> > > but it can't be allowed to break the world. The other cases are safe
> > > from that particular problem because the table doesn't exist yet.
>
> Please correct me if my understanding of the above comment (from the
> commit e9baa5e9) is wrong - even if the leader opens the matview
> relation in exclusive mode, because of group locking(in case we allow
> parallel workers to feed in the data to the new heap that gets created
> for RMV, see ExecRefreshMatView->make_new_heap), can other sessions
> still access the matview relation with older data?
>
> I performed below testing to prove myself wrong for the above understanding:
> session 1:
> 1) added few rows to the table t1 on which the mv1 is defined;
> 2) refresh materialized view mv1;
>
> session 2:
> 1) select count(*) from mv1; ---> this query is blocked until
> session 1's step (2) is completed and gives the latest result even if
> the underlying data-generating query runs select part in parallel.
>
> Is there anything I'm missing here?

I think he was talking about things like functions that try to access
the mv from inside the same query, in a worker. I haven't figured out
exactly which hazards he meant. I thought about wrong-relfilenode
hazards and combocid hazards, but considering the way this thing
always inserts into a fresh table before performing merge or swap
steps later, I don't yet see why this is different from any other
insert-from-select-with-gather.

With the script below you can reach this error in the leader:

postgres=# refresh materialized view CONCURRENTLY mv;
ERROR: cannot start commands during a parallel operation
CONTEXT: SQL function "crazy"

But that's reachable on master too, even if you change crazy() to do
"select 42 from pg_class limit 1" instead of reading mv, when
performing a CREATE M V without NO DATA. :-( Without parallel
leader participation it runs to completion.

===8<===

set parallel_leader_participation = off;
set parallel_setup_cost = 0;
set min_parallel_table_scan_size = 0;
set parallel_tuple_cost = 0;

drop table if exists t cascade;

create table t (i int);
insert into t select generate_series(1, 100000);
create materialized view mv as select 42::int i;
create or replace function crazy() returns int as $$ select i from mv
limit 1; $$ language sql parallel safe;
drop materialized view mv;
create materialized view mv as select i + crazy() i from t with no data;
create unique index on mv(i);

refresh materialized view mv;
refresh materialized view concurrently mv;

begin;
refresh materialized view mv;
refresh materialized view mv;
commit;

begin;
refresh materialized view concurrently mv;
refresh materialized view concurrently mv;
commit;

===8<===

PS, off-topic observation made while trying to think of ways to break
your patch: I noticed that REFRESH CONCURRENTLY spends a lot of time
in refresh_by_match_merge()'s big FULL JOIN. That is separate from
the view query that you're parallelising here, and is used to perform
the merge between a temporary table and the target MV table. I hacked
the code a bit so that it wasn't scanning a temporary table
(unparallelisable), and tried out the Parallel Hash Full Join patch
which I intend to commit soon. This allowed REFRESH CONCURRENTLY to
complete much faster. Huzzah! Unfortunately that query also does
ORDER BY tid; I guess we could remove that to skip a Sort and use
Gather instead of the more expensive Gather Merge, and hopefully soon
a pushed-down Insert.