Cannot rollback while a subtransaction is active – Error 2D000

I have written a stored procedure that basically loops over an array of fields and performs some manipulation in the db for each iteration. What I want to achieve is, either all the iterations of loops should occur or neither one of them should occur.

So let’s say there were 5 elements in the fields array and the loop iterates up to the 3rd element before noticing that some condition is true and throwing an error, I want to rollback all the changes that occurred during the first 2 iterations. I’ve used ROLLBACK statements to achieve the same, but every time it reaches the ROLLBACK statement it throws the following error:

Cannot rollback while a subtransaction is active : 2D000

Surprisingly, it works as normal if I comment out the outobj := json_build_object('code',0); statement within the EXCEPTION WHEN OTHERS THEN block or if I remove that whole block completely.

I’ve checked the PostgreSQL documentation for error codes, but it didn’t really help. My stored procedure is as follows:

CREATE OR REPLACE PROCEDURE public.usp_add_fields(
    field_data json,
    INOUT outobj json DEFAULT NULL::json)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE 
v_user_id bigint;
farm_and_bussiness json;
_field_obj json;
_are_wells_inserted boolean;
BEGIN

-- get user id
 v_user_id = ___uf_get_user_id(json_extract_path_text(field_data,'user_email'));

IF(v_user_id IS NULL) THEN
    outobj := json_build_object('code',17);
    RETURN;
END IF;

-- Loop over entities to create farms & businesses
FOR _field_obj IN SELECT * FROM json_array_elements(json_extract_path(field_data,'fields'))
LOOP
    -- check if irrigation unit id is already linked to some other field
    IF(SELECT EXISTS(
        SELECT field_id FROM user_fields WHERE irrig_unit_id LIKE json_extract_path_text(_field_obj,'irrig_unit_id') AND deleted=FALSE
    )) THEN
        outobj := json_build_object('code',26);
        -- Rollback any changes made by previous iterations of loop
        ROLLBACK;
        RETURN;
    END IF;
    
    -- check if this field name already exists
    IF( SELECT EXISTS(
            SELECT uf.field_id FROM user_fields uf
            INNER JOIN user_farms ufa ON (ufa.farm_id=uf.user_farm_id AND ufa.deleted=FALSE)
            INNER JOIN user_businesses ub ON (ub.business_id=ufa.user_business_id AND ub.deleted=FALSE)
            INNER JOIN users u ON (ub.user_id = u.user_id AND u.deleted=FALSE)
            WHERE u.user_id = v_user_id
            AND uf.field_name LIKE json_extract_path_text(_field_obj,'field_name')
            AND uf.deleted=FALSE
        )) THEN 
        outobj := json_build_object('code', 22);
        -- Rollback any changes made by previous iterations of loop
        ROLLBACK;
        RETURN;
    END IF;

    --create/update user business and farm and return farm_id 
    CALL usp_add_user_bussiness_and_farm(
        json_build_object('user_email', json_extract_path_text(field_data,'user_email'),
                          'business_name', json_extract_path_text(_field_obj,'business_name'),
                          'farm_name', json_extract_path_text(_field_obj,'farm_name')
        ), farm_and_bussiness);

    IF(json_extract_path_text(farm_and_bussiness, 'code')::int != 1) THEN
        outobj := farm_and_bussiness;
        -- Rollback any changes made by previous iterations of loop
        ROLLBACK;
        RETURN;
    END IF;

    -- insert into users fields
    INSERT INTO user_fields (user_farm_id, irrig_unit_id, field_name, ground_water_percent, surface_water_percent)
    SELECT json_extract_path_text(farm_and_bussiness,'farm_id')::bigint,
    json_extract_path_text(_field_obj,'irrig_unit_id'),
    json_extract_path_text(_field_obj,'field_name'),
    json_extract_path_text(_field_obj,'groundWaterPercentage'):: int,
    json_extract_path_text(_field_obj,'surfaceWaterPercentage'):: int;

    -- add to user wells
    CALL usp_insert_user_wells(json_extract_path(_field_obj,'well_data'), v_user_id, _are_wells_inserted);
END LOOP;

outobj := json_build_object('code',1);
RETURN;

EXCEPTION WHEN OTHERS THEN 
    raise notice '% : %', SQLERRM, SQLSTATE;
    outobj := json_build_object('code',0);
RETURN;

END;
$BODY$;

Answer

Explanation:

Based on the clue provided by @Laurez Albe, I came up with a cleaner way to solve the above problem.

Basically, what I’ve done is, I’ve raised a custom exception whenever a condition is true. So when an exception is thrown, all the changes made by block X are rolled back gracefully. I can even perform last minute cleanup within the exception conditional blocks.

Implementation:

CREATE OR REPLACE procedure mProcedure(INOUT resp json DEFAULT NULL::JSON)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
field_data json := '{ "fields": [1,2,3,4,5] }';
_field_id int;
BEGIN

-- Start of block X
FOR _field_id IN SELECT * FROM json_array_elements(json_extract_path(field_data,'fields'))
LOOP
    INSERT INTO demo VALUES(_field_id);
    
    IF(_field_id = 3) THEN
      RAISE EXCEPTION USING ERRCODE='22013';
    END IF;
   
    IF(_field_id = 5) THEN
      RAISE EXCEPTION USING ERRCODE='22014';
    END IF;
END LOOP;

SELECT json_agg(row_to_json(d)) INTO resp FROM demo d;
RETURN;
-- end of block X


-- if an exception occurs in block X, then all the changes made within the block are rollback
-- and the control is passed on to the EXCEPTION WHEN OTHERS block.  
EXCEPTION 
WHEN sqlstate '22013' THEN
resp := json_build_object('code',26);

WHEN sqlstate '22014' THEN
resp := json_build_object('code',22);

END;
$BODY$;

Demo:

Dbfiddle