2

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?

2
  • 1
    "do I need to catch exceptions" -- that depends on how you invoke your procedure.
    – mustaccio
    Feb 29 at 12:48
  • I am using supabase and I will either be invoking them using their api from client side or periodically using pg_cron.
    – Lugoom485
    Feb 29 at 13:05

1 Answer 1

3

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:

1
  • 1
    Since this in now a Hot Network Question, it should be noted that this does not always apply outside of Postgres. For example, SQL Server's approach to deciding if an error should rollback a transaction is hilariously unpredictable.
    – J. Mini
    Feb 29 at 20:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.