I am using Postgres. I am trying to execute two functions in PARALLEL, and then union their results
- here is my code so far –
begin;
--------
WITH area_ids AS (
SELECT DISTINCT "areaID"::character varying
FROM campaign_scope cs
WHERE '2024-03-02' BETWEEN cs."campaignStartDate" AND cs."catchupEndDate"
),
area_id_counts AS (
SELECT COUNT(*) AS total_count
FROM area_ids
),
selected_area_ids AS (
SELECT "areaID"::character varying,
ROW_NUMBER() OVER (ORDER BY "areaID"::character varying) AS row_num
FROM area_ids
)
----------
,
results_1 as
(
SELECT inter.* FROM
(select * from public.interactions_looped_ucmo_aic(array(SELECT selected_area_ids."areaID"::character varying
FROM selected_area_ids
JOIN area_id_counts ON true
WHERE selected_area_ids.row_num <= area_id_counts.total_count / 2
), '2024-03-02','2024-03-02 06:00:00','2024-03-02 18:00:00')
order by area_id,parent_imei,child_imei,parent_ping_time) as inter)
,
results_2 as
(
SELECT inter.* FROM
(select * from public.interactions_looped_ucmo_aic(array(SELECT selected_area_ids."areaID"::character varying
FROM selected_area_ids
JOIN area_id_counts ON true
WHERE selected_area_ids.row_num > area_id_counts.total_count / 2
), '2024-03-02','2024-03-02 06:00:00','2024-03-02 18:00:00')
order by area_id,parent_imei,child_imei,parent_ping_time) as inter
)
SELECT * FROM result_1
UNION ALL
SELECT * FROM result_2;
commit;
this throws the following error:
SQL Error [25P02]: ERROR: current transaction is aborted, commands ignored until end of transaction block
I want to run the functions in parallel to save time, although I am not if my current code even achieves this. I read that begin and commit should be used in order to tell postgres that this is a single transaction – so I am hoping this would be sufficient.