Psycopg3 : set_isolation_level

Question

I'm trying to connect with psycopg: v3.1.12 to PostgreSQL and set the Isolation Level to 1.

I've tried adding the set_isolation_level(1) found in the official Docs in both the connection and the transaction set up but it doesn't work for me.

Any help would be appreciated.

My connection :

self.pg_connection = psycopg.connect(
        (
            "dbname="
            + os.environ["PG_DB"]
            + " user="
            + os.environ["PG_USER"]
            + " password="
            + os.environ["PG_PASS"]
            + " host="
            + os.environ["PG_HOST"]
            + " port="
            + os.environ["PG_PORT"]
        ),
        row_factory=dict_row,
        autocommit=False,
)

My Transaction :

with self.pg_connection.transaction() as tx:
    tx.set_isolation_level(1)

The above returns the exception :

Exception: 'Transaction' object has no attribute 'set_isolation_level'

Answer 1

1. Use Isolation-level constants from psycopg2.extensions instead of arbitrary integers.

2. Set it on the connection with Connection.set_isolation_level()

   conn.set_isolation_level(ISOLATION_LEVEL_READ_UNCOMMITTED)
   

3. Or set it on the session with set_session()

   conn.set_session(isolation_level=ISOLATION_LEVEL_READ_UNCOMMITTED)
   

Also, why are you constructing the DSN string like that if you already have the host, db, pass, etc. separately? Use keyword args when connecting:

conn = psycopg2.connect(
    dbname=os.environ["PG_DB"],
    user=os.environ["PG_USER"],
    password=os.environ["PG_PASS"],
    host=os.environ["PG_HOST"],
    port=os.environ["PG_PORT"],
    row_factory=dict_row,
    autocommit=False,
)

Or store the entire DSN as a single environment var and use that directly:

conn = psycopg2.connect(
    dsn=os.environ["PG_DSN"],
    row_factory=dict_row,
    autocommit=False,
)

Answer 2

I might have found a way to add the isolation_level on the connection, not on the transaction.

self.pg_connection = psycopg.connect(
        (
            "dbname="
            + os.environ["PG_DB"]
            + " user="
            + os.environ["PG_USER"]
            + " password="
            + os.environ["PG_PASS"]
            + " host="
            + os.environ["PG_HOST"]
            + " port="
            + os.environ["PG_PORT"]
        ),
        row_factory=dict_row,
        autocommit=False,
)

self.pg_connection.isolation_level=1