In AWS Redshift, are there errors that can cause a stored procedure to stop, even when having a error handling block?

The behavior you're experiencing with your stored procedure in Amazon Redshift is due to the limited support for exception handling in Redshift compared to standard PostgreSQL. Redshift doesn't support subtransactions, which affects how exception handling works within stored procedures.

In your case, even though you have separate BEGIN...END blocks with their own error handlers, the entire stored procedure is still considered as one transaction. When an error occurs in one of these blocks, it's caught by the EXCEPTION block, but it still affects the overall transaction state.

The error you're receiving ("Cannot insert a NULL value into column process") is a constraint violation. This type of error causes the entire transaction to be aborted, even if it's caught in an EXCEPTION block. After the error is caught and logged, the procedure exits because the transaction has been invalidated.

To address this issue and make your stored procedure continue execution after an error, you have a few options:

1. Use a CONTINUE handler instead of an EXIT handler. The CONTINUE handler allows the procedure to continue execution after an error. However, be aware that this is specific to NONATOMIC procedures in Redshift.

2. Move operations that don't need to be in the same atomic transaction outside of the transaction. This can help isolate errors and prevent them from affecting the entire procedure.

3. Consider using separate stored procedures for each step, and then call these procedures from a main procedure. This way, if one step fails, it won't affect the execution of subsequent steps.

4. If possible, validate data before attempting to insert it. This can help prevent constraint violation errors in the first place.

Remember, when working with stored procedures in Redshift, it's important to be aware of these limitations in exception handling and plan your error handling strategy accordingly.
Sources
Trapping errors - Amazon Redshift
Stored procedure limitations - Amazon Redshift

Add exception control block in your stored procedure:

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;
  
  -- Wrap the entire procedure body in an exception block
  BEGIN
    -- Step 1
    BEGIN
      RAISE INFO 'Step 1: Inserting record into dummy_table';
      INSERT INTO dummy_table (id, process)
      VALUES (v_load_id, NULL);
      RAISE INFO 'Step 1 completed successfully';
    EXCEPTION WHEN OTHERS THEN
      RAISE INFO 'Step 1 error caught: %', SQLERRM;
      CALL dummy_error_log(v_load_id, SQLSTATE, SQLERRM);
    END;
    
    RAISE INFO 'Continuing to Step 2';
    
    -- Step 2
    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;
  EXCEPTION WHEN OTHERS THEN
    RAISE INFO 'Main procedure error caught: %', SQLERRM;
  END;
  
  RAISE INFO 'Dummy ETL process finished';
END;
$$;

Alternatively use the transactional control approach:

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
  BEGIN
    SAVEPOINT step1;
    
    RAISE INFO 'Step 1: Inserting record into dummy_table';
    INSERT INTO dummy_table (id, process)
    VALUES (v_load_id, NULL);
    RAISE INFO 'Step 1 completed successfully';
    
    RELEASE SAVEPOINT step1;
  EXCEPTION WHEN OTHERS THEN
    ROLLBACK TO step1;
    RAISE INFO 'Step 1 error caught: %', SQLERRM;
    CALL dummy_error_log(v_load_id, SQLSTATE, SQLERRM);
  END;
  
  RAISE INFO 'Continuing to Step 2';
  
  -- Step 2
  BEGIN
    SAVEPOINT step2;
    
    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;
    
    RELEASE SAVEPOINT step2;
  EXCEPTION WHEN OTHERS THEN
    ROLLBACK TO step2;
    RAISE INFO 'Step 2 error caught: %', SQLERRM;
    CALL dummy_error_log(v_load_id, SQLSTATE, SQLERRM);
  END;
  
  RAISE INFO 'Dummy ETL process finished';
END;
$$;

Check out NONATOMIC stored procedures they won’t raise caught errors