Thread: 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$;
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$;
<soumik.bhattacharjee@kpn.com> writes: > I have a procedure that may encounter an exception. I want to have an exception handling block where exception is to catchis any FK violations. > Could you please suggest where it's getting missed as part of a transaction. I think you're confused about the scope of the exception block. The syntax is BEGIN some code here that might throw an exception EXCEPTION some WHEN clauses here to catch exceptions from the covered code END You wrote: > begin > RAISE SQLSTATE 'MYERR'; > EXCEPTION > WHEN ... So this exception block can *only* trap errors arising from that one RAISE command, not anything earlier in the procedure. Seems unlikely that's what you wanted. regards, tom lane