Catching exceptions and rolling back transactions

Question

I am just trying to see if I understand this correctly:

By default, any error occurring in a PL/pgSQL function aborts execution of the function and the surrounding transaction

I have a procedure which does multiple insert operations on different tables, however at the moment I did not make them into a singular transaction. Does the above documentation mean that if an error were to occur all operations performed inside the procedure would be rolled back or do I need to catch exceptions and perform rollback/commit manually?

Answer 1

The whole procedure runs inside a single transaction automatically. Except, if you execute COMMIT, which commits everything so far and starts a new transaction.

So, no, you do not have to catch exceptions unless you want to. If an exception occurs (and is not caught), the whole transaction is rolled back automatically.

There are some limitations in which constellations a nested COMMIT is allowed. Read the manual about Transaction Management.

Be sure to know the difference between functions and procedures in Postgres:

• In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?
• When to use stored procedure / user-defined function?