Transaction management

psycopg3 has a behaviour that may result surprising compared to psql: by default, any database operation will start a new transaction. As a consequence, changes made by any cursor of the connection will not be visible until Connection.commit() is called, and will be discarded by Connection.rollback(). The following operation on the same connection will start a new transaction.

If a database operation fails, the server will refuse further commands, until a rollback() is called.

Hint

If a database operation fails with an error message such as InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block, it means that a previous operation failed and the database session is in a state of error. You need to call rollback() if you want to keep on using the same connection.

Autocommit transactions

The manual commit requirement can be suspended using autocommit, either as connection attribute or as connect() parameter. This may be required to run operations that cannot be executed inside a transaction, such as CREATE DATABASE, VACUUM, CALL on stored procedures using transaction control.

Warning

By default even a simple SELECT will start a transaction: in long-running programs, if no further action is taken, the session will remain idle in transaction, an undesirable condition for several reasons (locks are held by the session, tables bloat…). For long lived scripts, either make sure to terminate a transaction as soon as possible or use an autocommit connection.

Transaction blocks

A more transparent way to make sure that transactions are finalised at the right time is to use with Connection.transaction() to create a transaction block. When the block is entered a transaction is started; when leaving the block the transaction is committed, or it is rolled back if an exception is raised inside the block.

For instance, an hypothetical but extremely secure bank may have the following code to avoid that no accident between the following two lines leaves the accounts unbalanced:

with conn.transaction():
    move_money(conn, account1, -100)
    move_money(conn, account2, +100)

# The transaction is now committed

But because the bank is, like, extremely secure, they also verify that no account goes negative:

def move_money(conn, account, amount):
    new_balance = add_to_balance(conn, account, amount)
    if new_balance < 0:
        raise ValueError("account balance cannot go negative")

In case this function raises an exception, be it the ValueError in the example or any other exception expected or not, the transaction will be rolled back, and the exception will propagate out of the with block, further down the call stack.

Transaction blocks can also be nested (internal transaction blocks are implemented using SAVEPOINT): an exception raised inside an inner block has a chance of being handled and not completely fail outer operations. The following is an example where a series of operations interact with the database: operations are allowed to fail, plus we also want to store the number of operations successfully processed.

with conn.transaction() as tx1:
    num_ok = 0
    for operation in operations:
        try:
            with conn.transaction() as tx2:
                unreliable_operation(conn, operation)
        except Exception:
            logger.exception(f"{operation} failed")
        else:
            num_ok += 1

    save_number_of_successes(conn, num_ok)

If unreliable_operation() causes an error, including an operation causing a database error, all its changes will be reverted. The exception bubbles up outside the block: in the example it is intercepted by the try so that the loop can complete. The outermost block is unaffected (unless other errors happen there).

You can also write code to explicitly roll back any currently active transaction block, by raising the Rollback exception. The exception “jumps” to the end of a transaction block, rolling back its transaction but allowing the program execution to continue from there. By default the exception rolls back the innermost transaction block, but any current block can be specified as the target. In the following example, an hypothetical CancelCommand may stop the processing and cancel any operation previously performed, but not entirely committed yet.

from psycopg3 import Rollback

with conn.transaction() as outer_tx:
    for command in commands():
        with conn.transaction() as inner_tx:
            if isinstance(command, CancelCommand):
                raise Rollback(outer_tx)
            process_command(command)

# If `Rollback` is raised, it would propagate only up to this block,
# and the program would continue from here with no exception.