Rollback outer function if nested function throws a (trapped!) exception

Question

Consider the following example function in plpgsql:

create or replace function fn_delete(p_id int)
  returns SETOF result_transaction as
$$
declare 
   related_rows_affected int :=0;
begin
   --some previous code that alters some tables--
   .
   .
   .
   select count(id1) into related_rows_affected from td_other,
   lateral fn_delete_secondary(id1)
   where id = p_id;
end
$$ language 'plpgsql';

Consider fn_delete_secondary just deletes some records. I want to rollback any previous changes in fn_delete if fn_delete_secondary throws an exception that prevents it from deleting the appropriated rows. How can I achieve the aforementioned result?

I thought that throwing an exception inside the fn_delete_secondary would rollback fn_delete too, but isn't the case... Note that fn_delete_secondary handles exceptions in the form

EXCEPTION
WHEN OTHERS THEN

If I didn't handle the exceptions inside fn_delete_secondary, would that work? I would prefer to leave it as is right now because I also use this function directly.

Other question is: How can I get the results of fn_delete_secondary when is used inside in a lateral statement?

Erwin Brandstetter · Accepted Answer · 2022-06-05 01:46:00Z

I thought that throwing an exception inside the fn_delete_secondary() would rollback fn_delete() too, ...

You thought correctly, but the exception in the second function is trapped instead of thrown. So, yes, if you don't handle it, everything is rolled back.

Else you need to return some information from fn_delete_secondary() indicating a trapped exception. Then fn_delete() can identify that and raise an exception itself.

This way you can keep error handling in fn_delete_secondary() - if you must. Code blocks with an EXCEPTION clause are considerably more expensive, so that's expensive complication.

To answer your question, assuming fn_delete_secondary() like this:

CREATE OR REPLACE FUNCTION fn_delete_secondary(_id int, OUT _deleted_rows int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   DELETE FROM tbl2 t WHERE t.id = _id;
   
   GET DIAGNOSTICS _deleted_rows = ROW_COUNT;   

EXCEPTION WHEN OTHERS THEN
   _deleted_rows := -1;

END
$func$;

It returns the number of deleted rows - or -1 if there was a handled error.
Then your main function could work like this:

CREATE OR REPLACE FUNCTION fn_delete(p_id int)
  RETURNS SETOF result_transaction
  LANGUAGE plpgsql AS
$func$
DECLARE
   related_rows_affected int := 0;
   _error_reported bool;
BEGIN
   -- put this first!
   SELECT sum(f._deleted_rows) , bool_or(f._deleted_rows < 0)
   INTO   related_rows_affected, _error_reported
   FROM   td_other, fn_delete_secondary(id1) f
   WHERE  id = p_id;

   IF _error_reported THEN
      RAISE EXCEPTION 'fn_delete_secondary() reported an error'; 
   END IF;
   
   -- some code that alters some tables, etc.
END
$func$;

Move the call that might fail to the top. That's cheaper. Everything will be rolled back either way.

Great explanation and examples considering good practices, thanks a lot!

Collectives™ on Stack Overflow

Rollback outer function if nested function throws a (trapped!) exception

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related