Hopefully I'm just missing something here, but I would like to be able to do the following:
- call a procedure from another procedure.
- the parent procedure has a rollback in an exception handler.
- the child has an exception handler as well to conceal the inner error from the parent.
- the child writes to a table in the error handler.
- the child RAISES a new error in the error handler.
- the parent catches this error and issues a rollback.
- the child procedure's write to a table is preserved.
Possible?
I wrote the below test showing my issue. Please execute the segments separate. Thank you so much ahead of time.
create schema dbo;
drop table dbo.test_transaction_table;
drop table dbo.test_transaction_errortable;
create table dbo.test_transaction_table(a int);
create table dbo.test_transaction_errortable(a int);
/**********************************************************/
create or replace PROCEDURE dbo.test_transaction()
LANGUAGE plpgsql
AS $$
DECLARE v_returnvalue int;
BEGIN
insert into dbo.test_transaction_table(a) values(1);
call dbo.test_transaction_inner(v_returnvalue);
insert into dbo.test_transaction_table(a) values(3);
commit;
EXCEPTION WHEN OTHERS THEN
rollback;
END; $$;
create or replace PROCEDURE dbo.test_transaction_inner(INOUT v_returnvalue INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
insert into dbo.test_transaction_table(a) values(2);
v_returnvalue = 1 / 0;
v_returnvalue = 3;
EXCEPTION WHEN OTHERS THEN
--I WANT THIS BELOW INSERT TO ALWAYS HAPPEN
--I WANT TO RAISE AN ERROR SO THE PARENT PROCEDURE CAN STILL DO A ROLLBACK IF NECESSARY
insert into dbo.test_transaction_errortable(a) values(1);
COMMIT;
RAISE 'There was one or more errors.';
END; $$;
/**********************************************************/
delete from dbo.test_transaction_table;
delete from dbo.test_transaction_errortable;
call dbo.test_transaction();
select * from dbo.test_transaction_table;
select * from dbo.test_transaction_errortable;
/***********************************************************/
--I expect the dbo.test_transaction_errortable to have 1 row.
--I expect the dbo.test_transaction_table to have 0 rows.