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$;
CodePudding user response：
If you have an EXCEPTION clause in a PL/pgSQL block, that whole block will be executed in a subtransaction that is rolled back when an exception happens. So you cannot use COMMIT or ROLLBACK in such a block.
If you really need that ROLLBACK, rewrite your code like this:
DECLARE
should_rollback boolean := FALSE;
BEGIN
FOR ... LOOP
BEGIN -- inner block for exception handling
/* do stuff */
IF (/* condition that should cause a rollback */) THEN
should_rollback := TRUE;
EXIT; -- from LOOP
END IF;
EXCEPTION
WHEN OTHERS THEN
/* handle the error */
END;
END LOOP;
IF should_rollback THEN
ROLLBACK;
/* do whatever else is needed */
END IF;
END;
Now the rollback does not happen in a block with an exception handler, and it should work the way you want.
CodePudding user response：
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$;