RE: Commit and Exception Block

Hi Experts,

I have a procedure that may encounter an exception. I want to have an exception handling block where exception is to catch is any FK violations.

Could you please suggest where it's getting missed as part of a transaction.

* PG Version : PostgreSQL 12.4

- Initial Error - 2D000 cannot commit while a subtransaction is active

* Moved the exception block within another Begin/End block.
* But now --> It does not go to the exception handling block itself.

Thanks in advance...

CREATE OR REPLACE PROCEDURE ddd.dddremove(
p_number_of_rows integer,
INOUT complete text)
LANGUAGE 'plpgsql'

AS $BODY$ DECLARE
tmprow ddd.order%rowtype;
p_counter INTEGER := 0;
p_final_count INTEGER := 0;
cnt_result INTEGER :=0;
begin
FOR tmprow IN
select idx from ddd.order where so_created_at< now() - interval '1460 days'
LOOP
RAISE notice 'order Id %',tmprow.idx;
delete from ddd.order_settings where sos_order_id=100;
delete from ddd.order where idx=tmprow.idx;
-- GET DIAGNOSTICS cnt_result = ROW_COUNT;
-- IF cnt_result = 0 THEN
-- RAISE NOTICE 'Removed % rows with amount = 0', cnt_result;
-- complete :='FOREIGN_KEY_VIOLATION';
-- return;
-- END IF;
p_counter := p_counter + 1;

IF (p_counter !=0) then
RAISE notice 'p_counter %',p_counter;
COMMIT;
END IF;
EXIT WHEN p_counter > p_number_of_rows;

END LOOP;
begin
RAISE SQLSTATE 'MYERR';
EXCEPTION
WHEN FOREIGN_KEY_VIOLATION then
complete :='FOREIGN_KEY_VIOLATION';
RETURN ;
WHEN SQLSTATE 'MYERR' then
complete :='Procedure Successful';
RETURN ;
WHEN no_data_found then
complete :='FOREIGN_KEY_VIOLATION';
RETURN ;
end;
SELECT COUNT(*)
INTO p_final_count
FROM ddd.order where so_created_at< now() - interval '1460 days';

RAISE NOTICE 'Sog Order Count After deleting % rows in the order table is %', p_counter,p_final_count;
complete :='completed';
return;
end $BODY$;