I wrote a stored procedure that calls other procedures inside multiple transaction blocks (BEGIN…END) with their own error handlers. I expected that if one block encounters an exception, it would go to the corresponding error handling section and then continue with the next block. However, the stored procedure is exiting entirely instead of continuing. What could be wrong?

Here's the error I'm receiving:

SQL Error [XX000]: ERROR: Cannot insert a NULL value into column process
  Detail: 
  -----------------------------------------------
  error:  Cannot insert a NULL value into column process
  code:      8007
  context:   query execution
  query:     54021995[child_sequence:1]
  location:  column:10
  process:   query0_3881_54022009 [pid=7602]
  -----------------------------------------------

The SQL query for the procedure is:

CREATE TABLE dummy_table (
  id BIGINT,
  process VARCHAR(100) NOT NULL
);

CREATE OR REPLACE PROCEDURE sp_dummy_etl()
LANGUAGE plpgsql
AS $$
DECLARE
  v_load_id BIGINT := 1;
  v_row_count INT;
BEGIN
  RAISE INFO 'Dummy ETL process started. Load ID: %', v_load_id;
  
  -- Step 1: Attempt to insert a row that violates the NOT NULL constraint.
  BEGIN
    RAISE INFO 'Step 1: Inserting record into dummy_table';
    INSERT INTO dummy_table (id, process)
    VALUES (v_load_id, NULL);  -- This will trigger error (NOT NULL violation).
    RAISE INFO 'Step 1 completed successfully';
  EXCEPTION WHEN OTHERS THEN
    RAISE INFO 'Step 1 error caught: %', SQLERRM;
    -- This call is intended to log the error.
    CALL dummy_error_log(v_load_id, SQLSTATE, SQLERRM);
  END;
  
  RAISE INFO 'Continuing to Step 2';
  
  -- Step 2: Dummy successful operation.
  BEGIN
    RAISE INFO 'Step 2: Calling dummy_step2';
    CALL dummy_step2(v_load_id, v_row_count);
    RAISE INFO 'Step 2 completed successfully. Row count: %', v_row_count;
  EXCEPTION WHEN OTHERS THEN
    RAISE INFO 'Step 2 error caught: %', SQLERRM;
    CALL dummy_error_log(v_load_id, SQLSTATE, SQLERRM);
  END;
  
  RAISE INFO 'Dummy ETL process finished';
  
END;
$$;

When calling the sp_dummy_etl(), it executes the first block, encounters the exception, handling it by writing in the error_log, and after that, it completely exit the procedure sp_dummy_etl instead of continuing to the next transaction block (Step 2).

I wrote a stored procedure that calls other procedures inside multiple transaction blocks (BEGIN…END) with their own error handlers. I expected that if one block encounters an exception, it would go to the corresponding error handling section and then continue with the next block. However, the stored procedure is exiting entirely instead of continuing. What could be wrong?

Here's the error I'm receiving:

SQL Error [XX000]: ERROR: Cannot insert a NULL value into column process
  Detail: 
  -----------------------------------------------
  error:  Cannot insert a NULL value into column process
  code:      8007
  context:   query execution
  query:     54021995[child_sequence:1]
  location:  column:10
  process:   query0_3881_54022009 [pid=7602]
  -----------------------------------------------

The SQL query for the procedure is:

CREATE TABLE dummy_table (
  id BIGINT,
  process VARCHAR(100) NOT NULL
);

CREATE OR REPLACE PROCEDURE sp_dummy_etl()
LANGUAGE plpgsql
AS $$
DECLARE
  v_load_id BIGINT := 1;
  v_row_count INT;
BEGIN
  RAISE INFO 'Dummy ETL process started. Load ID: %', v_load_id;
  
  -- Step 1: Attempt to insert a row that violates the NOT NULL constraint.
  BEGIN
    RAISE INFO 'Step 1: Inserting record into dummy_table';
    INSERT INTO dummy_table (id, process)
    VALUES (v_load_id, NULL);  -- This will trigger error (NOT NULL violation).
    RAISE INFO 'Step 1 completed successfully';
  EXCEPTION WHEN OTHERS THEN
    RAISE INFO 'Step 1 error caught: %', SQLERRM;
    -- This call is intended to log the error.
    CALL dummy_error_log(v_load_id, SQLSTATE, SQLERRM);
  END;
  
  RAISE INFO 'Continuing to Step 2';
  
  -- Step 2: Dummy successful operation.
  BEGIN
    RAISE INFO 'Step 2: Calling dummy_step2';
    CALL dummy_step2(v_load_id, v_row_count);
    RAISE INFO 'Step 2 completed successfully. Row count: %', v_row_count;
  EXCEPTION WHEN OTHERS THEN
    RAISE INFO 'Step 2 error caught: %', SQLERRM;
    CALL dummy_error_log(v_load_id, SQLSTATE, SQLERRM);
  END;
  
  RAISE INFO 'Dummy ETL process finished';
  
END;
$$;

When calling the sp_dummy_etl(), it executes the first block, encounters the exception, handling it by writing in the error_log, and after that, it completely exit the procedure sp_dummy_etl instead of continuing to the next transaction block (Step 2).

• sql
• amazon-web-services
• stored-procedures
• amazon-redshift
• amazon-redshift-serverless

2 Answers 2

The documentation is always a good place to start:

“When entering the exception-handling block, the current transaction is rolled back and a new transaction is created to run the statements in the block. If the statements in the block run without error, the transaction is committed and the exception is re-thrown. Lastly, the stored procedure exits.”

NONATOMIC stored procedures let you catch errors and continue