pg_cron fails to execute transaction block

Question

I have 5 jobs which contain a transaction block (similar code just modifying different tables) which get executed through pg_cron background workers and are passed as a naked string to scheduler. The blocks consist of BEGIN;(some queries)END; and all of them succeed when ran as a query, but they fail when ran through pg_cron... The jobs are ran in parallel and they fail 80% of the time with FATAL: EndTransactionBlock: unexpected state BEGIN and I had very few times where one of them succeeded.

I only managed to reproduce this using background workers in Azure/Robin k8s cluster and stolon as a database, can't really reproduce it locally.

Here is the modified script: `

BEGIN;
        LOCK TABLE public.dummy IN ACCESS EXCLUSIVE MODE;
        ALTER TABLE public.dummy DISABLE TRIGGER ALL;
        CREATE INDEX IF NOT EXISTS idx_connecteddummy_id ON public.connecteddummy(id);
        CREATE INDEX IF NOT EXISTS idx_connecteddummy_originalid ON public.connecteddummy(originalid);
        CREATE TEMP TABLE connecteddummy_ids ON COMMIT DROP AS (
            SELECT d.Id 
            FROM public.dummy d 
            LEFT JOIN public.connecteddummy cd 
            ON d.Id = cd.Id 
            LEFT JOIN public.connecteddummy cdd 
            ON d.Id = cdd.OriginalId 
            WHERE cd.Id IS null 
            AND cdd.OriginalId IS null 
            ORDER BY d.Id 
            LIMIT 50000
        );
        CREATE INDEX IF NOT EXISTS idx_ids_id ON connecteddummy_ids(Id);
        DELETE FROM public.dummy d 
        USING connecteddummy_ids v 
        WHERE d.Id = v.Id;
        DROP INDEX IF EXISTS idx_connecteddummy_Id;
        DROP INDEX IF EXISTS idx_connecteddummy_OriginalId;
        ALTER TABLE public.dummy ENABLE TRIGGER ALL;
    END;

`

Script is used for removing unreferenced entities in Dummy table (unreferenced entities meaning that id and original id do not exist in connected dummy table)