create_database: CREATE DATABASE cannot run inside a transaction block

[alvassin]

Recently after update from sqlalchemy==1.3.13 (working fine) to 1.3.14 (and upper) i discovered that sqlalchemy_utils module started to raise CREATE DATABASE cannot run inside a transaction block error when creating database. psycopg2-binary version was same for both tests, 2.8.5.

Reason is the following code in sqlalchemy_utils.functions.database, please see example to reproduce issue below, it works with 1.3.13 and does not work with 1.3.14:

from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
from sqlalchemy import create_engine

engine = create_engine('postgresql://user:hackme@localhost/postgres')
engine.raw_connection().set_isolation_level(
    ISOLATION_LEVEL_AUTOCOMMIT
)

result_proxy = engine.execute(
    "CREATE DATABASE testdb ENCODING 'utf8' TEMPLATE template1"
)

It works if i acquire connection explicitly:

from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
from sqlalchemy import create_engine

engine = create_engine('postgresql://user:hackme@localhost/postgres')
with engine.connect() as conn:
    conn.connection.set_isolation_level(
        ISOLATION_LEVEL_AUTOCOMMIT
    )
    result_proxy = conn.execute(
        "CREATE DATABASE testdb ENCODING 'utf8' TEMPLATE template1"
    )

I suppose it is related to sqlalchemy/sqlalchemy#5182.

I suppose that acquiring connection and specifying explicitly connection level is better. What do you think?

[alvassin]

engine argument isolation_level='AUTOCOMMIT' also works:

from sqlalchemy import create_engine

engine = create_engine('postgresql://user:hackme@localhost/postgres', 
                       isolation_level="AUTOCOMMIT")
engine.execute("CREATE DATABASE testdb ENCODING 'utf8' TEMPLATE template1")

So, perhaps we could engine for any postgresql driver with isolaton_level option or extend condition for postgresql drivers?

...
elif url.drivername.startswith('postgresql'):
    engine = sa.create_engine(url, isolation_level='AUTOCOMMIT')
...

[avancinirodrigo]

Hi guys,

I have the same problem.

How can I get this correction?

[vladalexeev]

It's strange, but I have the same problem but with dependency of the running environment.
I have some pytest tests for my application, which create a temporary database.
If I simply run tests, everything works fine.
But if I try to debug the same tests in PyCharm, I see the same problem "CREATE DATABASE cannot run inside a transaction block".

If I use sqlalchemy==1.3.13 then debug if PyCharm works correctly.

Is there any solution?

[perminovs]

I have the same problem with debuging from PyCharm. Executing "CREATE DATABASE..." in context manager with engine.connect() as conn: helps but please let me know, if you found better solution)

[alecbz]

Another PyCharm user, same thing. I have no idea what about running tests in debug mode causes this to crop up...

[fish-face]

I still have this issue if I update to current SQLAlchemy rel_1_3 branch, which has the fix to the linked issue. If I revert to 1.3.13 then the problem goes away.

[tmikulin]

any progress on this?

[gordondavies]

Still getting this error myself. Works fine from command line and inside 'run', but breaks when using 'debug'. I've tried setting it to AUTOCOMMIT and it still fails. Reverted to SQLAlchemy==1.3.13 for the time being.

[joaonc]

Exact same thing as @ghaggart is describing.. have all the latest of the moment and still happening:

• PyCharm 2020.3.1
• psycopg2 2.8.6
• SQLAlchemy 1.3.22
• SQLAlchemy-Utils 0.36.8

Suspition # 1:
If I place a breakpoint here and expand engine.pool and then continue, it works fine, so I'm wondering if it's something to do with connection pool manager and also wondering if it's not PyCharm causing this..

Suspition # 2:
This code here is appears to not be setting isolation_level. Here's a little demo:

if engine.driver == 'psycopg2':
    from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
    i1 = engine.raw_connection().isolation_level
    engine.raw_connection().set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
    i2 = engine.raw_connection().isolation_level
    engine.raw_connection().set_isolation_level(1)
    i3 = engine.raw_connection().isolation_level
    breakpoint()

results in:

i1 = None  # Expected
i2 = None  # Error. Should be 0
i3 = 1  # Expected

If we set isolation_level here with engine = sa.create_engine(url, isolation_level='AUTOCOMMIT'), then it works.

[glumia]

Same for me, I experience this issue only when trying to debug tests with Pycharm.

As other said my problem disappears if the engine is created with the isolation_level'='AUTOCOMMIT' parameter:

diff --git a/sqlalchemy_utils/functions/database.py b/sqlalchemy_utils/functions/database.py
index 9613e12..62531ff 100644
--- a/sqlalchemy_utils/functions/database.py
+++ b/sqlalchemy_utils/functions/database.py
@@ -531,7 +531,7 @@ def create_database(url, encoding='utf8', template=None):
 
     if url.drivername == 'mssql+pyodbc':
         engine = sa.create_engine(url, connect_args={'autocommit': True})
-    elif url.drivername == 'postgresql+pg8000':
+    elif url.drivername in {'postgresql+pg8000', 'postgresql+psycopg2'}:
         engine = sa.create_engine(url, isolation_level='AUTOCOMMIT')
     else:
         engine = sa.create_engine(url)

Apart from understanding why this weird bug happens (its has probably to do with pydevd), couldn't we in the meanwhile apply this patch? Sqlalchemy's documentation states that this option can be used for the psycopg2 dialect (link).

[vvalentinvolkov]

If someone face it trying to create test db with psycopg driver then try psycopg2 just for creating db.