0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RAJIV
  • 1

0 Answers0