Hello,
I am working with data warehouse based on postgresql and would like to propose a feature. The idea is to give control and ability for developer to execute queries in parallel within single transaction. Usual flow is next: START_TRANSACTION -> QUERY1 -> QUERY2 -> QUERY3 -> END_TRANSACTION. However sometimes QUERY1 and QUERY2 are independent and can be executed in parallel mode. E.g.: START_TRANSACTION -> DEFINE_QUERY1(no execution) -> DEFINE_QUERY2(no_execution) -> EXECUTE_QUERY1_AND_QUERY2(in parallel) -> QUERY3 -> END
Of course QUERY1 and QUERY2 can be dependent and then this would not work, but sometimes it is useful, especially when you have bound to e.g. CPU and query stuck.
If we go further, the postgresql engine could possible find such cases by itself and run queries in parallel mode, but that’s sounds too far.
Syntax:
BEGIN;
--create two temp tables because prepare does not support CTAS
—query1
CREATE TEMP TABLE QUERY1_RESULT ON COMMIT DROP (…);
PREPARE query1 (id int, val varchar) as INSERT INTO QUERY1_RESULT(...) SELECT …
—query2
CREATE TEMP TABLE QUERY2_RESULT ON COMMIT DROP (…);
PREPARE query2 (id int, val varchar) as INSERT INTO QUERY2_RESULT(...) SELECT …
—exec in parallel
execute parallel (query1, query2);
—query3
….
END;
