postgresql parallel query in plpgsql for loop

Viewed 56

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';
0 Answers
Related