Error ошибка create database не может выполняться внутри блока транзакции

I am working on AWS server + PostgreSQL. When I execute a query for creating the database I get an error:

CREATE DATABASE cannot run inside a transaction block

I am working on Linux Ubuntu 12.04 LTS.

How can I resolve this issue?

asked Oct 21, 2014 at 9:01

I have used turn on autocommit in PostgreSQL and it’s working for me.

Here is the query to turn on the autocommit

SET AUTOCOMMIT = ON

Note that this only works for PostgreSQL 9.4 and below

Lord Elrond

12.3k6 gold badges35 silver badges71 bronze badges

answered Oct 21, 2014 at 9:45

Nikunj K.Nikunj K.

8,4774 gold badges43 silver badges52 bronze badges

Note, for postgres 9.5+ you have to use:

psql -c 'set AUTOCOMMIT on'

But I’m going to guess, that what you really wanted to do is destroy the database and recreate it in a single command. Here you go:

printf 'set AUTOCOMMIT onndrop database <your_db_here>; create database <your_db_here>; ' |  psql postgres

answered Nov 20, 2020 at 22:27

Javier BuzziJavier Buzzi

5,93535 silver badges49 bronze badges

In Postgres SQL 14.1, pgAdmin query tool, I see this same error when running the create database query with other queries. Running the create database query by itself completes successfully.

answered Dec 10, 2021 at 18:24

AsencionAsencion

1691 silver badge10 bronze badges

This can also happen if you have commented lines within a create table block

problem code:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    first_name VARCHAR(30),
    last_name VARCHAR(30) NOT NULL,
    date_of_birth DATE
-- managers INT FOREIGN KEY,
-- employees INT FOREIGN KEY,
-- companies INT FOREIGN KEY
);

Solution:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    first_name VARCHAR(30),
    last_name VARCHAR(30) NOT NULL,
    date_of_birth DATE
);

-- managers INT FOREIGN KEY,
-- employees INT FOREIGN KEY,
-- companies INT FOREIGN KEY

answered May 14, 2021 at 18:58

ScottyBladesScottyBlades

11.1k5 gold badges70 silver badges76 bronze badges

I did through Query Tools and got your error. To resolve this problem, I opened PSQL Tool and then created my database in.

sudo su - postgres
psql -d postgres -U postgres
create database dbName;

answered Dec 14, 2022 at 8:34

ParisaNParisaN

1,5592 gold badges20 silver badges52 bronze badges

Источник

I am triing to run a script in pgAdmin 4, but I get this error:

CREATE DATABASE cannot run inside a transaction block

And this is the script:

CREATE USER ky_auth WITH PASSWORD 'ky_auth';
COMMENT ON ROLE ky_auth IS 'KnowYourself Auth Database User';
CREATE DATABASE ky_auth WITH OWNER = ky_auth;
COMMENT ON DATABASE ky_auth IS 'KnowYourself Auth Database';

CREATE USER ky_pers WITH PASSWORD 'ky_pers';
COMMENT ON ROLE ky_pers IS 'KnowYourself Personal Database User';
CREATE DATABASE ky_pers WITH OWNER = ky_pers;
COMMENT ON DATABASE ky_pers IS 'KnowYourself Personal Database';

CREATE USER ky_oper WITH PASSWORD 'ky_oper';
COMMENT ON ROLE ky_oper IS 'KnowYourself Operational Database User';
CREATE DATABASE ky_oper WITH OWNER = ky_oper;
COMMENT ON DATABASE ky_oper IS 'KnowYourself Operational Database';

CREATE USER knowyourself_tests WITH PASSWORD 'ky_tests' CREATEDB;
COMMENT ON ROLE knowyourself_tests IS 'KnowYourself Integration Tests Database User';

So what I have to change?

Thank you

and if put this above the script:

SET AUTOCOMMIT = ON

Then I get this error:

 unrecognized configuration parameter "autocommit"

if I do this:

CREATE USER ky_auth WITH PASSWORD 'ky_auth';
COMMENT ON ROLE ky_auth IS 'KnowYourself Auth Database User';

then it succeeds. but then If I do this:


CREATE DATABASE ky_auth WITH OWNER = ky_auth;
COMMENT ON DATABASE ky_auth IS 'KnowYourself Auth Database';

I get this error:

ERROR:  CREATE DATABASE cannot run inside a transaction block

Источник

I am triing to run a script in pgAdmin 4, but I get this error:

CREATE DATABASE cannot run inside a transaction block

And this is the script:

CREATE USER ky_auth WITH PASSWORD 'ky_auth';
COMMENT ON ROLE ky_auth IS 'KnowYourself Auth Database User';
CREATE DATABASE ky_auth WITH OWNER = ky_auth;
COMMENT ON DATABASE ky_auth IS 'KnowYourself Auth Database';

CREATE USER ky_pers WITH PASSWORD 'ky_pers';
COMMENT ON ROLE ky_pers IS 'KnowYourself Personal Database User';
CREATE DATABASE ky_pers WITH OWNER = ky_pers;
COMMENT ON DATABASE ky_pers IS 'KnowYourself Personal Database';

CREATE USER ky_oper WITH PASSWORD 'ky_oper';
COMMENT ON ROLE ky_oper IS 'KnowYourself Operational Database User';
CREATE DATABASE ky_oper WITH OWNER = ky_oper;
COMMENT ON DATABASE ky_oper IS 'KnowYourself Operational Database';

CREATE USER knowyourself_tests WITH PASSWORD 'ky_tests' CREATEDB;
COMMENT ON ROLE knowyourself_tests IS 'KnowYourself Integration Tests Database User';

So what I have to change?

Thank you

and if put this above the script:

SET AUTOCOMMIT = ON

Then I get this error:

 unrecognized configuration parameter "autocommit"

if I do this:

CREATE USER ky_auth WITH PASSWORD 'ky_auth';
COMMENT ON ROLE ky_auth IS 'KnowYourself Auth Database User';

then it succeeds. but then If I do this:


CREATE DATABASE ky_auth WITH OWNER = ky_auth;
COMMENT ON DATABASE ky_auth IS 'KnowYourself Auth Database';

I get this error:

ERROR:  CREATE DATABASE cannot run inside a transaction block

Источник

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and
privacy statement. We’ll occasionally send you account related emails.

Already on GitHub?
Sign in
to your account

Open

alvassin opened this issue

Apr 11, 2020

· 10 comments

Comments

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?

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')
...

alvassin

pushed a commit
to alvassin/sqlalchemy-utils
that referenced
this issue

Apr 11, 2020

alvassin

added a commit
to alvassin/sqlalchemy-utils
that referenced
this issue

Apr 11, 2020

Hi guys,

I have the same problem.

How can I get this correction?

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?

alecbz, ZubAnt, Denhai, shunt16, m-ajay, ShoulddaBeenaWhaleBiologist, joshshifman, chbndrhnns, chrisguillory, gerazenobi, and 7 more reacted with thumbs up emoji
torkashvand and tmikulin reacted with confused emoji
perminovs, ihor-pyvovarnyk, alecbz, ZubAnt, joshshifman, and tmikulin reacted with eyes emoji

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)

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

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.

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.

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.

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).

glumia

pushed a commit
to glumia/sqlalchemy-utils
that referenced
this issue

Jan 22, 2021

glumia

pushed a commit
to glumia/sqlalchemy-utils
that referenced
this issue

Jan 23, 2021

glumia

pushed a commit
to glumia/sqlalchemy-utils
that referenced
this issue

Jan 23, 2021

Источник

Error ошибка create database не может выполняться внутри блока транзакции

Comments

А вот еще интересные материалы: