postgresqlplpgsql

PostgreSQL: Does an EXCEPTION block automatically release its subtransaction after finishing?


In PostgreSQL, I know that an EXCEPTION block starts a subtransaction within the current transaction. However, I'm not sure when this subtransaction is released. For example:

CREATE PROCEDURE example_proc()
LANGUAGE PLPGSQL
AS $$
BEGIN
    -- Start a subtransaction
    BEGIN
        -- Do some operations...
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Caught error';
    END;
    
    -- Do some operations...
    COMMIT;
END;
$$;

After the EXCEPTION block finishes (either normally or due to an exception), is the subtransaction automatically released, or does it remain open until the main transaction ends?

Thanks!


Solution

  • Postgres supports subtransactions at the SQL level via savepoints, so that is probably the simplest way to illustrate what it's doing here.

    When you run this exception handler:

    BEGIN
      PERFORM foo();
    EXCEPTION WHEN others THEN
      PERFORM handle_error();
    END
    

    ...what actually happens is something like this:

    BEGIN
      SAVEPOINT a;
      PERFORM foo();
      RELEASE SAVEPOINT a;
    EXCEPTION WHEN others THEN
      ROLLBACK TO SAVEPOINT a;
      RELEASE SAVEPOINT a;
      PERFORM handle_error();
    END