transaction_rollback_scope parameter v17

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.