In Postgres, how do I write to a table in a child procedure during an error that is to be rolled back?

Viewed 38

Hopefully I'm just missing something here, but I would like to be able to do the following:

  1. call a procedure from another procedure.
  2. the parent procedure has a rollback in an exception handler.
  3. the child has an exception handler as well to conceal the inner error from the parent.
  4. the child writes to a table in the error handler.
  5. the child RAISES a new error in the error handler.
  6. the parent catches this error and issues a rollback.
  7. 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.

0 Answers
Related