Determine if a transaction already exists

[KaplanAlex]

Is there a way to tell when I'm already in a transaction created by the connection context manager while autocommit is set to True?

I have a method which starts a transaction using the connection context manager. This method is called by another method which starts a transaction. I want to update my inner method to only start a transaction if it doesn't exist already. To do so the method needs to know if it's in a transaction. All fields I can find appear to be the same before and during the context manager block

import psycopg2
from psycopg2.extensions import connection

def print_conn(conn: connection):
    print(
        f"status: {conn.status}, isolation_level: {conn.isolation_level}, txn status {conn.get_transaction_status()}, isexecuting {conn.isexecuting()}, autocommit {conn.autocommit}"
    )


_conn = None
def get_conn_cached():
    global _conn
    if _conn is not None:
        return _conn
    _conn = psycopg2.connect(host="localhost", port=5432, user="postgres", password="postgres", dbname="postgres")
    _conn.autocommit = True
    print_conn(_conn)
    return _conn

def outer_method():
    with get_conn_cached() as conn:
        print_conn(conn)
        # Stuff requiring txn
        inner_method()


def inner_method():
    with get_conn_cached() as conn:
        print_conn(conn)
        # Other stuff that can be in an existing txn or a new txn
        print("in the inner txn")

outer_method()

Output

status: 1, isolation_level: None, txn status 0, isexecuting False, autocommit False
status: 1, isolation_level: None, txn status 0, isexecuting False, autocommit False
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
...

ProgrammingError: the connection cannot be re-entered recursively

[dvarrazzo]

You can use connection.info.transaction_status.

[dvarrazzo]

Ah, you are using get_transaction_status() which returns the same info.

What happens is that the transaction is started but not at the beginning of the block; rather at the first statement, like what happens for a non-autocommit transaction:

# No transaction despite the block
with conn:
    print(conn.info.transaction_status)
0

# Transaction after a statement
with conn:
    cur = conn.cursor()
    cur.execute("select 1")
    print(conn.info.transaction_status)
2

I suggest you to move to psycopg 3 which has a more friendly conn.transaction() block and the behaviour you expect:

import psycopg

conn = psycopg.connect("", autocommit=True)

print(conn.info.transaction_status)
TransactionStatus.IDLE

with conn.transaction():
    print(conn.info.transaction_status)
TransactionStatus.INTRANS

print(conn.info.transaction_status)
TransactionStatus.IDLE

[KaplanAlex]

Got it thank you!