sqlpostgresqlplpgsqlsupabase

How to design a transaction that calls nested functions and only commits if all called nested functions commit, e.g. abort if one subfunction fails?


How to design a transaction that calls nested functions and only commits if all called nested functions commit, e.g. abort if one subfunction fails?

For example, this transaction:

CREATE OR REPLACE FUNCTION unfollow(
    follower_id uuid,
    following_id uuid
)
    RETURNS void
    LANGUAGE plpgsql
    SECURITY INVOKER
AS
$$
BEGIN
        PERFORM delete_relationship(
    follower_id,
    following_id
        );
        PERFORM decrement_counter(
    following_id
            );

END;
$$;

should only commit if both update functions are successful. This works when I add to every database operation in the called functions a statement that checks if the previous CRUD operation was successful and raises an exception if unsucessful. Like:

CREATE OR REPLACE FUNCTION delete_relationship(
    follower_id uuid,
    following_id uuid
)
    RETURNS void
    LANGUAGE plpgsql
    SECURITY INVOKER
AS
$$
BEGIN
    DELETE
    FROM
        following_profiles
    WHERE
          follower = follower_id
      AND following = following_id;

    IF NOT FOUND THEN
        ROLLBACK;
        -- Raise an exception to roll back the transaction
        RAISE EXCEPTION 'Error in unfollow_transaction: %', SQLERRM;
    END IF;
END;
$$;

If I do not raise that exception, the transaction commits even one of the sub function fails.

Is there a smarter way to do this? Is it possible to make functions raise an exception by default if a CRUD operation fails without mentioning it every time, e.g. is there a way to avoid writing all the exception statements?


Solution

  • Failing to select update or delete rows that match a where clause is not exceptional.

    However you don't need to rollback anything. Any exception - whether from the dB itself or your code does that. That is basically the whole point of transactions. Everything works or nothing does.


    Additon in response to the comment below.

    It's not exceptional to affect zero rows because... there's nothing unusual about that.

    UPDATE enquiries SET status='expired'
    WHERE status='pending' AND query_date < CURRENT_DATE - 365
    

    That's the sort of query that you might run every night from a scheduled task. Why exactly should it throw an exception if there aren't any old pending enquiries? That's a perfectly normal situation.