Exception Handling in Stored Procedures

You will implement robust error handling blocks within procedures to manage transactions and state.

In this lesson, you will learn how to implement robust EXCEPTION blocks within PL/pgSQL procedures to gracefully manage errors without crashing your entire transaction.

The Mechanics of Exception Blocks

When a stored procedure encounters a runtime error—such as a division by zero, a constraint violation, or a missing table—PostgreSQL normally aborts the entire transaction. However, using an EXCEPTION block allows you to "catch" these errors, log them, and potentially recover, keeping the surrounding transaction state stable.

Inside a procedure, you wrap the risky code within a BEGIN ... EXCEPTION ... END block. If an error occurs, control immediately jumps to the EXCEPTION section, where you can inspect the error context or perform cleanup.

Implementing a Safeguard

Consider a scenario where you are updating account balances. If a constraint fails (like a check for negative balances), you want to record the attempt rather than failing the whole process:

CREATE PROCEDURE update_balance(account_id INT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance + amount WHERE id = account_id;
    
    EXCEPTION WHEN OTHERS THEN
        -- Log the error state internally
        INSERT INTO error_logs (msg, log_time) 
        VALUES (SQLERRM, CURRENT_TIMESTAMP);
        
        -- Optionally notify the caller or suppress the error
        RAISE NOTICE 'Caught an error: %', SQLERRM;
END;
$$;

Transactional Implications

It is critical to remember that once an error occurs inside a block, the state of that subtransaction is rolled back to the point immediately before the BEGIN block started. You cannot "fix" the data and continue inside the same block; the EXCEPTION block is designed to allow you to perform recovery tasks, like logging or setting a default state, after the specific operation fails. Because PostgreSQL handles subtransactions internally, the outer transaction remains valid unless the error is re-raised.

By using SQLERRM (the error message text) and SQLSTATE (the specific error code), you can differentiate between expected application errors and critical system failures, allowing your procedures to be more resilient in production environments.

Key Takeaway: Use EXCEPTION blocks to catch runtime errors within subtransactions, allowing you to log failures and maintain control flow without forcing a complete transaction rollback.