I am running a do loop that has four queries that can be run independently of each other inside two doubly nested FOR LOOPs. I have marked the functions PARALLEL SAFE, but they still won't execute in parallel. Are the EXCEPTION blocks causing any problems? I have these settings in postgresql.conf:
#max_worker_processes = 8 # (change requires restart)
max_parallel_maintenance_workers = 4 # taken from max_parallel_workers
max_parallel_workers_per_gather = 4 # taken from max_parallel_workers
#parallel_leader_participation = on
#max_parallel_workers = 8 # maximum number of max_worker_processes that
Here's the code. I'm starting to believe the four create materialized view statements have to be executed sequentially, but I don't know for sure why.
do $code$
declare
f text;
qs text;
oid int;
tbl text;
key text;
pair cursor(key text) for select tablename from pg_tables where tablename like key order by tablename;
begin
FOR y in 2010..2022 LOOP
select substring('''%' || y || '%''',2,6) into key;
FOR f in pair(key)
LOOP
select substring(f::text, 2, 6) into tbl;
raise info 'f: %, tbl: %', f, tbl;
qs = format('create materialized view %I_2010_candlestick1m tablespace forex_view as select * from candlestick(''%I_2010'', 1, ''minute'', ''-infinity'', ''infinity'') with data;', tbl, tbl );
begin
raise info 'executing: %', qs;
execute qs;
exception when others then
raise notice '% %', SQLERRM, SQLSTATE;
end;
qs = format('create materialized view %I_2010_candlestick5m tablespace forex_view as select * from candlestick(''%I_2010'', 5, ''minute'', ''-infinity'', ''infinity'') with data;', tbl, tbl );
begin
raise info 'executing: %', qs;
execute qs;
exception when others then
raise notice '% %', SQLERRM, SQLSTATE;
end;
qs = format('create materialized view %I_2010_candlestick15m tablespace forex_view as select * from candlestick(''%I_2010'', 15, ''minute'', ''-infinity'', ''infinity'') with data;', tbl, tbl );
begin
raise info 'executing: %', qs;
execute qs;
exception when others then
raise notice '% %', SQLERRM, SQLSTATE;
end;
qs = format('create materialized view %I_2010_candlestick1day tablespace forex_view as select * from candlestick(''%I_2010'', 1, ''day'', ''-infinity'', ''infinity'') with data;', tbl, tbl );
begin
raise info 'executing: %', qs;
execute qs;
exception when others then
raise notice '% %', SQLERRM, SQLSTATE;
end;
COMMIT;
END LOOP;
END LOOP;
end $code$
language 'plpgsql';