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!
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