Should I avoid handling exception in stored procedures?

By default, any error occurring in a PL/pgSQL function aborts execution of the function and the surrounding transaction. You can trap errors and recover from them by using a BEGIN block with an EXCEPTION clause.

Redrock Postgres uses subtransactions to implement the exception-handling of the stored procedure. Then, do we need to avoid handling exception in a loop? Do we need to avoid call other stored procedures that contain exception-handling code in a loop? Will too many subtransactions affect system performance?

Redrock Postgres implemented subtransactions based on the undo record position, these subtransactions are only temporary marks in a transaction, they do not need to assign their own transaction IDs, and have little impact on system performance. You can confidently handle any exceptions in stored procedures.

In the following example, we create a stored procedure in which multiple records in the t_table are updated. At the same time, we can run the benchmark test on the database using pgbench.

CREATE TABLE t_table (id integer, name text);

INSERT INTO t_table (id, name)
  SELECT i, 'dummy' FROM generate_series(1, 1000) AS s(i);

CREATE OR REPLACE FUNCTION func_exception_in_loop(num integer)
  RETURNS void AS $$
DECLARE
  i integer;
  t text;
BEGIN
  FOR i IN 1..num LOOP
    BEGIN
      UPDATE t_table SET name = 'test' WHERE id = i;
      SELECT pg_sleep(0.1) into t;
    EXCEPTION WHEN OTHERS THEN
      RAISE WARNING 'update row error';
    END;
  END LOOP;
END $$ LANGUAGE plpgsql;

SELECT func_exception_in_loop(1000);

In the above example, we will find that even if there are too many subtransactions during the running of the database, the overall system performance is not affected too much.