Cannot (should not?) set isolation level on connection?

[shanecarey17]

I find in the documentation here https://aiopg.readthedocs.io/en/stable/core.html?highlight=isolation_level#aiopg.Connection.isolation_level

The only value allowed in asynchronous mode value is psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED (READ COMMITTED).

However, I am not able to find in the psycopg2 documentation where this is stated. http://initd.org/psycopg/docs/advanced.html#async-support

With asynchronous connections it is also not possible to use set_client_encoding(), executemany(), large objects, named cursors.

Additionally the docs state Similarly set_session() can’t be used but it is still possible to invoke the SET command with the proper default_transaction_... parameter.

aiopg allows set_isolation_level to be called on the connection object with any value and does not check for 'READ_COMMITTED' explicitly, but this "warning" still exists in the source and documentation.

Why is this? Can the documentation link to the relevant section of the psycopg2 docs?

[vir-mir]

We have not had time to correct the documentation. can you help us by make PR?

But you can use transactions example:

import asyncio

import aiopg
from aiopg.transaction import Transaction, IsolationLevel

dsn = 'dbname=aiopg user=aiopg password=passwd host=127.0.0.1'

async def transaction(cur, isolation_level,
                      readonly=False, deferrable=False):
    async with Transaction(cur, isolation_level,
                           readonly, deferrable) as transaction:
        await cur.execute('insert into tbl values (1)')

        async with transaction.point():
            await cur.execute('insert into tbl values (3)')

        await cur.execute('insert into tbl values (4)')

async def main():
    async with aiopg.create_pool(dsn) as pool:
        async with pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute('CREATE TABLE tbl (id int)')
                await transaction(cur, IsolationLevel.repeatable_read)
                await transaction(cur, IsolationLevel.read_committed)
                await transaction(cur, IsolationLevel.serializable)

                await cur.execute('select * from tbl')

loop = asyncio.get_event_loop()
loop.run_until_complete(main())

[asvetlov]

IIRC the issue is: psycopg2 provides connection.set_isolation_level() setter for changing the connection state. The setter doesn't work in async mode. When I tried it last time 4 years ago an exception was thrown. Maybe the fresh psycopg2 allows it now, I didn't check.

I believe changing isolation level by sending SQL command works fine, e.g. conn.execute('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE') or 'BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ

The same for set_session() call.

[dvarrazzo]

You can totally implement a set_session() in aiopg which doesn't use psycopg. What psycopg does is only to use the right parameters after BEGIN according to the chosen session parameter: you already do some of that having different IsolationCompiler objects.

The default choice of READ COMMITTED as default isolation level is a broken one: see #699.