Error Handling and Rolling Back to Original State: EXCEPTION, RAISE

Errors in PostgreSQL can pop up for all sorts of reasons: constraint violations (NOT NULL, UNIQUE, CHECK), syntax mistakes, duplicate values, and so on. If you don't catch and handle these errors, the whole outer transaction might get rolled back. For solid business operations, it's super important to handle them right.

PL/pgSQL gives you a powerful BEGIN ... EXCEPTION ... END block mechanism to catch and handle errors in functions and procedures. They're kinda like try-catch in Python or Java, but with some PostgreSQL transaction quirks.

Heads up:

Every BEGIN ... EXCEPTION ... END block acts like a "virtual savepoint." If an exception happens, all changes in that block are automatically rolled back. This is the only proper way to do a partial rollback in PL/pgSQL functions and procedures.

Error Handling Syntax with EXCEPTION

BEGIN
    -- main code
EXCEPTION
    WHEN ERROR_TYPE THEN
        -- handle specific error
    WHEN ANOTHER_ERROR_TYPE THEN
        -- another handler
    WHEN OTHERS THEN
        -- handle all other errors
END;

How it looks in functions/procedures

DO $$
BEGIN
    RAISE NOTICE 'An error is coming up...';
    PERFORM 1 / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Division by zero caught!';
END;
$$;

Example: Update with Error Handling and Rollback

Let's say you have an orders table:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    amount NUMERIC NOT NULL,
    status TEXT NOT NULL
);

Let's make a function that updates the order status and does nothing if there's an error:

CREATE OR REPLACE FUNCTION update_order_status(order_id INT, new_status TEXT)
RETURNS VOID AS $$
BEGIN
    BEGIN
        UPDATE orders
        SET status = new_status
        WHERE id = order_id;

        -- Simulate an error
        IF new_status = 'FAIL' THEN
            RAISE EXCEPTION 'Simulating an error!';
        END IF;

        RAISE NOTICE 'Order status % updated', order_id;

    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Error updating order %: %', order_id, SQLERRM;
            -- All changes inside this block are rolled back automatically!
            -- Rethrow the error for outer handling if needed
            RAISE;
    END;
END;
$$ LANGUAGE plpgsql;

How This Works in Procedures with Explicit Transaction Control

In procedures (CREATE PROCEDURE), you can use COMMIT, ROLLBACK, SAVEPOINT, but you can't do ROLLBACK TO SAVEPOINT. If you need to roll back just part of the operations inside a procedure, still use BEGIN ... EXCEPTION ... END:

CREATE OR REPLACE PROCEDURE pay_order(order_id INT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    -- The whole procedure can use COMMIT/ROLLBACK, but to roll back just one step:
    BEGIN
        UPDATE accounts
        SET balance = balance - amount
        WHERE id = (SELECT account_id FROM orders WHERE id = order_id);

        -- error
        IF amount < 0 THEN
            RAISE EXCEPTION 'Amount cannot be negative!';
        END IF;

        UPDATE orders SET status = 'PAID' WHERE id = order_id;

    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Error processing payment for order %: %', order_id, SQLERRM;
            -- changes in this block are rolled back automatically
    END;

    COMMIT; -- You can only explicitly finish a transaction in procedures!
END;
$$;

Error Logging

It's important not just to catch errors, but to save them for later analysis.

CREATE TABLE error_log (
    id SERIAL PRIMARY KEY,
    order_id INT,
    error_message TEXT,
    error_time TIMESTAMP DEFAULT now()
);

In a function or procedure:

EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO error_log (order_id, error_message)
        VALUES (order_id, SQLERRM);
        RAISE NOTICE 'Error logged: %', SQLERRM;
        RAISE;

Important Limitations and Gotchas in PostgreSQL 17

Inside functions (CREATE FUNCTION ... ) you can't use transaction control commands (BEGIN, COMMIT, ROLLBACK, SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT)! All functions run completely inside the outer transaction.

Inside procedures (CREATE PROCEDURE ... ) you can explicitly write SAVEPOINT, RELEASE SAVEPOINT, COMMIT, ROLLBACK. BUT: ROLLBACK TO SAVEPOINT — FORBIDDEN! (You'll get a syntax error if you try to use ROLLBACK TO SAVEPOINT in a PL/pgSQL procedure).

Rolling back "part of the code" inside functions and procedures is done with BEGIN ... EXCEPTION ... END blocks. If an error happens, everything inside the block is rolled back automatically, and execution can keep going.

Procedures (CREATE PROCEDURE) can't be called inside a function or via SELECT — only with a separate CALL ... command.

How Do You Actually Do "Partial Rollback" in PL/pgSQL?

The only way that really works is to use error handling with BEGIN ... EXCEPTION ... END blocks. This block automatically creates a savepoint, and if there's an error, it rolls back changes inside the block without touching the rest of the procedure/function.

Example with EXCEPTION (Recommended Way):

CREATE OR REPLACE PROCEDURE demo_savepoint()
LANGUAGE plpgsql
AS $$
BEGIN
    -- Some code
    BEGIN
        -- An error here won't roll back the whole procedure,
        -- just this block!
        INSERT INTO demo VALUES ('bad data'); -- might cause an error
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Error handled, changes inside the block are cancelled';
    END;
    -- Execution keeps going here!
END;
$$;

Example: Loading a Data Batch with Protection from Rolling Back the Whole Process

CREATE OR REPLACE PROCEDURE load_big_batch()
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT * FROM import_table LOOP
        BEGIN
            INSERT INTO target_table (col1, col2)
            VALUES (rec.col1, rec.col2);
        EXCEPTION WHEN OTHERS THEN
            INSERT INTO import_errors (err_msg)
            VALUES ('Error in row: ' || rec.col1 || ': ' || SQLERRM);
            -- changes inside this block are cancelled!
        END;
    END LOOP;
    COMMIT;  -- only allowed if the procedure is run outside an explicit outer transaction!
END;
$$;

-- Calling the procedure
CALL load_big_batch();

Note: if you call this procedure from a client that already started a transaction (like Python with autocommit=False), running COMMIT or SAVEPOINT inside the procedure will throw an error.

Tips for Working with Nested Savepoints and EXCEPTION

1. Don't use ROLLBACK TO SAVEPOINT in PL/pgSQL! It'll throw a syntax error.
2. For partial rollbacks, always use nested BEGIN ... EXCEPTION ... END blocks.
3. Remember, COMMIT and ROLLBACK inside procedures restart the transaction — only use them when the procedure is running in autocommit mode!
4. Log errors in a separate table so you don't lose info about bad rows.
5. If your business operation needs to be strictly atomic (all-or-nothing) — make it a function, with no COMMIT/ROLLBACK inside; if you need step-by-step handling — do it as a procedure.

Example: Batch Import with Partial Handling

CREATE OR REPLACE PROCEDURE import_batch()
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT * FROM staging_table LOOP
        BEGIN
            INSERT INTO data_table (data)
            VALUES (rec.data);
        EXCEPTION
            WHEN unique_violation THEN
                INSERT INTO import_log (msg)
                VALUES ('Duplicate: ' || rec.data);
            WHEN OTHERS THEN
                INSERT INTO import_log (msg)
                VALUES ('Error: ' || rec.data || ' — ' || SQLERRM);
        END;
    END LOOP;
END;
$$;

The Main Thing to Remember for PostgreSQL 17:

In PL/pgSQL procedures you can use SAVEPOINT, RELEASE SAVEPOINT, COMMIT, ROLLBACK, but ROLLBACK TO SAVEPOINT — nope.

"Partial rollback" inside functions and procedures is only done with nested BEGIN ... EXCEPTION ... END blocks.

It's better to manage transactions from the outside (with connection parameters and autocommit), and inside procedures — use the error handling tricks described above.