transaction_rollback_scope parameter#
To set the transaction rollback scope inside the database, use the
transaction_rollback_scope parameter. The
transaction_rollback_scope parameter has two possible values:
transaction— Standard Postgres behavior, where each error aborts the whole transaction.statement— An error while executing one statement affects only that statement and not the status of the transaction as a whole.
Setting the parameter#
You can set the parameter as a user-level property, a connection option, or the mode for specific functions or procedures.
Set the parameter as a user-level property#
ALTER USER somebody SET transaction_rollback_scope TO statement;
Set the parameter as a connection option#
PGOPTIONS="-c transaction_rollback_scope=statement" psql <other options>
Set the mode for specific functions or procedures#
If using PL/pgSQL, you can set the mode for specific functions or procedures:
ALTER FUNCTION myfunc SET transaction_rollback_scope TO statement;
How subtransactions are handled#
If you select the statement value, then a subtransaction is opened
just before each SQL command. If the command is successful, the
subtransaction is committed. If the command causes an error, the
subtransaction is rolled back, and the parent transaction can continue
normally. The effect is that an error during execution of one statement
affects only that statement and not the status of the transaction as a
whole.
Committing a subtransaction assigns the resources it holds only to its parent transaction, which might be the top-level transaction. Or it might be some other subtransaction if there are user-defined savepoints involved. So this is not an “autonomous transaction.” Rolling back a subtransaction releases all the resources it holds, such as any locks it acquired.