PostgreSQL存储过程实现原子性事务时遭遇报错：Cannot rollback while a subtransaction is active - Error 2D000

首先，你遇到的Cannot rollback while a subtransaction is active : 2D000错误，根源在于PL/pgSQL的EXCEPTION块会隐式创建一个子事务上下文。当你的存储过程包含EXCEPTION块时，整个BEGIN...EXCEPTION代码段会运行在一个子事务中，此时你在块内手动执行ROLLBACK会尝试回滚整个父事务，而子事务仍处于活跃状态，从而触发这个错误。

而当你注释掉EXCEPTION块中的赋值语句或移除整个EXCEPTION块时，子事务不再存在，ROLLBACK就能正常回滚整个事务了。

接下来，针对你的需求（所有循环操作原子性，要么全部成功要么全部回滚），提供两种可行的解决方案：

方案一：移除EXCEPTION块，手动控制事务回滚 #

这种方案直接去掉EXCEPTION块，在所有错误分支中手动执行ROLLBACK并设置返回值，这样就不会触发子事务冲突：

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;
END;
$BODY$;

这种方式的优点是逻辑直接，符合你的原始需求，所有错误分支都会回滚整个事务，确保原子性。

方案二：保留EXCEPTION块，通过异常触发回滚 #

如果你需要保留EXCEPTION块来处理未预期的错误，可以修改逻辑，不再手动调用ROLLBACK，而是通过抛出异常让EXCEPTION块统一处理回滚：

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
		RAISE EXCEPTION '17' USING ERRCODE = 'P0001';
	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
			RAISE EXCEPTION '26' USING ERRCODE = 'P0001';
		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
			RAISE EXCEPTION '22' USING ERRCODE = 'P0001';
		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;
			RAISE EXCEPTION 'Failed to create farm/business' USING ERRCODE = 'P0001';
		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;
	-- 处理自定义异常的错误码
	IF SQLERRM ~ '^\d+$' THEN
		outobj := json_build_object('code', SQLERRM::int);
	ELSE
		outobj := json_build_object('code',0);
	END IF;
	-- 回滚整个事务
	ROLLBACK;
	RETURN;
END;
$BODY$;

这种方式的思路是：

1. 在所有预期错误分支中抛出携带错误码的自定义异常
2. 在EXCEPTION块中捕获所有异常，根据异常信息设置返回的错误码
3. 最后统一执行ROLLBACK回滚整个事务

这里使用了PostgreSQL的用户自定义错误码P0001，你也可以根据需要选择其他合适的错误码。

关键原理说明 #

PostgreSQL的PL/pgSQL中，BEGIN...EXCEPTION块会自动创建一个子事务：当块内发生错误时，PL/pgSQL会回滚子事务到块开始的状态，然后执行EXCEPTION块中的逻辑。如果你在块内手动调用ROLLBACK，就会尝试回滚整个父事务，但此时子事务仍处于活跃状态，从而触发2D000错误。

所以，要么避免使用EXCEPTION块（方案一），要么通过异常统一处理回滚（方案二），就能解决这个问题，同时保证事务的原子性。

内容的提问来源于stack exchange，提问作者Salvino D'sa