I am getting exception:
2D000 cannot commit while a subtransaction is active
when I use the EXCEPTION handling inside a TRANSACTION.
-- PROCEDURE: public.transaction_test1()
-- DROP PROCEDURE IF EXISTS public.transaction_test1();
CREATE OR REPLACE PROCEDURE public.transaction_test1(
)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
v_sqlerrm text;
v_sqlstate text;
BEGIN
FOR i IN 0..9 LOOP
BEGIN
--INSERT INTO test1 (a) VALUES (i);
INSERT INTO public.my_table (id, name) VALUES (i, 'Rajiv');
IF i % 2 = 0 THEN
-- RAISE EXCEPTION USING ERRCODE='22013';
COMMIT;
ELSE
--RAISE EXCEPTION USING ERRCODE='22014';
ROLLBACK;
END IF;
EXCEPTION WHEN others THEN
v_sqlerrm := sqlerrm;
v_sqlstate := sqlstate;
RAISE NOTICE 'exception: % % ', v_sqlstate , v_sqlerrm ;
truncate table public.my_table;
commit;
END;
END LOOP;
--EXCEPTION WHEN others THEN
-- v_sqlerrm := sqlerrm;
-- v_sqlstate := sqlstate;
-- RAISE NOTICE 'exception: % % ', v_sqlstate , v_sqlerrm ;
--EXCEPTION
--WHEN sqlstate '22013' THEN
--COMMIT;
--WHEN sqlstate '22014' THEN
--ROLLBACK;
--WHEN others THEN
-- v_sqlerrm := sqlerrm;
-- v_sqlstate := sqlstate;
-- RAISE NOTICE 'exception: % % ', v_sqlstate , v_sqlerrm ;
END;
$BODY$;
ALTER PROCEDURE public.transaction_test1()
OWNER TO postgres;
I have tried to load the data into the tables when the counter is even and rollback for the counter is odd.It is throwing an error.