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?

Eric Leschinski's user avatar

asked Oct 21, 2014 at 9:01

Nikunj K.'s user avatar

4

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's user avatar

Lord Elrond

13.1k6 gold badges38 silver badges77 bronze badges

answered Oct 21, 2014 at 9:45

Nikunj K.'s user avatar

Nikunj K.Nikunj K.

8,6734 gold badges43 silver badges53 bronze badges

8

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 Buzzi's user avatar

Javier BuzziJavier Buzzi

6,21436 silver badges50 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

Asencion's user avatar

AsencionAsencion

1991 silver badge11 bronze badges

With psql you can also run drop and and create your database in one command by using multiple --command arguments:

psql postgres -c "drop database if exists <your_db_here>;" -c "create database <your_db_here>;"

answered Feb 21 at 7:52

mpoqq's user avatar

mpoqqmpoqq

765 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

ScottyBlades's user avatar

ScottyBladesScottyBlades

11.8k5 gold badges74 silver badges81 bronze badges

0

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

  1. sudo su - postgres

  2. psql -d postgres -U postgres

  3. create database dbName;

answered Dec 14, 2022 at 8:34

ParisaN's user avatar

ParisaNParisaN

1,7822 gold badges22 silver badges53 bronze badges

выходит такая ошибка:

ERROR: ОШИБКА:  CREATE DATABASE не может выполняться внутри блока транзакции


SQL-состояние: 25001

А вот и сам код:

CREATE DATABASE "KATLA"
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'Russian_Russia.1251'
    LC_CTYPE = 'Russian_Russia.1251'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;
-- PostgreSQL database dump
--

-- Dumped from database version 14.3
-- Dumped by pg_dump version 14.2

-- Started on 2022-06-06 14:35:09

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- TOC entry 2 (class 3079 OID 16384)
-- Name: adminpack; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS adminpack WITH SCHEMA pg_catalog;


--
-- TOC entry 3239 (class 0 OID 0)
-- Dependencies: 2
-- Name: EXTENSION adminpack; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION adminpack IS 'administrative functions for PostgreSQL';


SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- TOC entry 213 (class 1259 OID 16427)
-- Name: prodagiDB; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public."prodagiDB" (
    name character varying(255) NOT NULL,
    id uuid NOT NULL,
    prodaji integer NOT NULL
);


ALTER TABLE public."prodagiDB" OWNER TO postgres;

--
-- TOC entry 210 (class 1259 OID 16395)
-- Name: t1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.t1 (
    "Петров" character varying(255) NOT NULL,
    id text NOT NULL
);


ALTER TABLE public.t1 OWNER TO postgres;

--
-- TOC entry 3240 (class 0 OID 0)
-- Dependencies: 210
-- Name: TABLE t1; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.t1 IS 'задачи пользователя';


--
-- TOC entry 211 (class 1259 OID 16398)
-- Name: test2; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.test2 (
);


ALTER TABLE public.test2 OWNER TO postgres;

--
-- TOC entry 212 (class 1259 OID 16401)
-- Name: test3; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.test3 (
);


ALTER TABLE public.test3 OWNER TO postgres;

--
-- TOC entry 3233 (class 0 OID 16427)
-- Dependencies: 213
-- Data for Name: prodagiDB; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO public."prodagiDB" (name, id, prodaji) VALUES ('Петров', '959416a1-92eb-4124-a951-a547c2df0d20', 25);
INSERT INTO public."prodagiDB" (name, id, prodaji) VALUES ('Иванов', '6c67fe46-94a4-4891-a7b4-c6041fee7bc6', 30);


--
-- TOC entry 3230 (class 0 OID 16395)
-- Dependencies: 210
-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: postgres
--



--
-- TOC entry 3231 (class 0 OID 16398)
-- Dependencies: 211
-- Data for Name: test2; Type: TABLE DATA; Schema: public; Owner: postgres
--



--
-- TOC entry 3232 (class 0 OID 16401)
-- Dependencies: 212
-- Data for Name: test3; Type: TABLE DATA; Schema: public; Owner: postgres
--



--
-- TOC entry 3090 (class 2606 OID 16431)
-- Name: prodagiDB prodagiDB_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public."prodagiDB"
    ADD CONSTRAINT "prodagiDB_pkey" PRIMARY KEY (id);


--
-- TOC entry 3088 (class 2606 OID 16418)
-- Name: t1 t1_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.t1
    ADD CONSTRAINT t1_pkey PRIMARY KEY (id);


-- Completed on 2022-06-06 14:35:09

--
-- PostgreSQL database dump complete
--

I’m running django 2.1.4 on python 3.6 with latest Postgresql.
Problem is, when I’m trying to create database from django code, it returning «current transaction is aborted, commands ignored until end of transaction block». Here is my code ->

@transaction.atomic
    def mutate(self, info, username, password, email, name, phone, company, country):
        user = get_user_model()(
            first_name=name,
            username=username,
            email=email,
            is_active=False
        )
        user.set_password(password)
        user.save()

        get_country = Country.objects.filter(id=country).first()
        company = Company(name=company, phone=phone, user=user, country=get_country)
        company.save()

        code = secrets.token_hex(16)
        activation = ActivationCode(user=user, code=code)
        activation.save()

        try:
            with connection.cursor() as cursor:
                cursor.execute("CREATE DATABASE company_" + str(company.id))
        except:
            GraphQLError("Something went wrong! Please contact the support center!")
        finally:
            cursor.close()
            db = CompanyDatabase(db_name="company_" + str(company.id), company=company)
            db.save()

        return CreateUser(user=user, company=company)

But it creates the database from django shell ->
shell image

Here’s the postgres log error ->

[12728] ERROR: CREATE DATABASE cannot run inside a transaction block

Here’s the terminal errors generated ->

Traceback (most recent call last):
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagespromisepromise.py", line 487, in _resolve_from_executo
r
    executor(resolve, reject)
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagespromisepromise.py", line 754, in executor
    return resolve(f(*args, **kwargs))
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagesgraphqlexecutionmiddleware.py", line 75, in make_it_p
romise
    return next(*args, **kwargs)
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libcontextlib.py", line 52, in inner
    return func(*args, **kwds)
  File "D:xERPgraphqltestusersschema.py", line 68, in mutate
    db.save()
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagesdjangodbmodelsbase.py", line 718, in save
    force_update=force_update, update_fields=update_fields)
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagesdjangodbmodelsbase.py", line 748, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagesdjangodbmodelsbase.py", line 831, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagesdjangodbmodelsbase.py", line 869, in _do_insert
    using=using, raw=raw)
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagesdjangodbmodelsmanager.py", line 82, in manager_metho
d
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagesdjangodbmodelsquery.py", line 1136, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagesdjangodbmodelssqlcompiler.py", line 1289, in execut
e_sql
    cursor.execute(sql, params)
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagesdjangodbbackendsutils.py", line 100, in execute
    return super().execute(sql, params)
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagesdjangodbbackendsutils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagesdjangodbbackendsutils.py", line 77, in _execute_with
_wrappers
    return executor(sql, params, many, context)
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagesdjangodbbackendsutils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagesdjangodbutils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:UsersEnvyAppDataLocalProgramsPythonPython36libsite-packagesdjangodbbackendsutils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
graphql.error.located_error.GraphQLLocatedError: current transaction is aborted, commands ignored until end of transaction block

Hi guys I’m stuck on a problem for my first database on Postgres and I cannot find a solution, I tried to see if it was a matter of setting autocommit on but apparently from postgres 9.x it’s an inconclusive operation. The documentation suggests that it might be an error on permissions or a full disk.
This is the code

CREATE DATABASE datacamp_courses 
WITH 
OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'English_United States.1252'
LC_CTYPE = 'English_United States.1252'
TABLESPACE = pg_default
CONNECTION LIMIT = -1;


CREATE TABLE datacamp_courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR (50) UNIQUE NOT NULL,
    course_instructor VARCHAR (100) NOT NULL,
    topic VARCHAR (2) NOT NULL
    );

This is the error :

ERROR:  CREATE DATABASE cannot run inside a transaction block
SQL state: 25001

Any help would be greatly appreciated

a_horse_with_no_name's user avatar

asked Mar 4, 2020 at 14:35

Luca Conin's user avatar

2

Message is self explanatory: you cannot create a database in a transaction.

You can try to run your statements in psql (CLI) with runs in AUTOCOMMIT by default: it will work but note that the table will be created in current database (by default postgres) which is maybe not what you want. If you want to create a database and a table in this new database you can try:

  create database mydb;
  connect mydb
  create table test(c int);

answered Mar 4, 2020 at 14:58

pifor's user avatar

piforpifor

7,1342 gold badges7 silver badges15 bronze badges

0

If you use the db browser in Intellij idea, select the auto commit field from the database settings you are connecting to.

answered Jun 23, 2021 at 15:08

Vedat's user avatar

VedatVedat

111 bronze badge

выходит такая ошибка:

ERROR: ОШИБКА:  CREATE DATABASE не может выполняться внутри блока транзакции


SQL-состояние: 25001

А вот и сам код:

CREATE DATABASE "KATLA"
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'Russian_Russia.1251'
    LC_CTYPE = 'Russian_Russia.1251'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;
-- PostgreSQL database dump
--

-- Dumped from database version 14.3
-- Dumped by pg_dump version 14.2

-- Started on 2022-06-06 14:35:09

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- TOC entry 2 (class 3079 OID 16384)
-- Name: adminpack; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS adminpack WITH SCHEMA pg_catalog;


--
-- TOC entry 3239 (class 0 OID 0)
-- Dependencies: 2
-- Name: EXTENSION adminpack; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION adminpack IS 'administrative functions for PostgreSQL';


SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- TOC entry 213 (class 1259 OID 16427)
-- Name: prodagiDB; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public."prodagiDB" (
    name character varying(255) NOT NULL,
    id uuid NOT NULL,
    prodaji integer NOT NULL
);


ALTER TABLE public."prodagiDB" OWNER TO postgres;

--
-- TOC entry 210 (class 1259 OID 16395)
-- Name: t1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.t1 (
    "Петров" character varying(255) NOT NULL,
    id text NOT NULL
);


ALTER TABLE public.t1 OWNER TO postgres;

--
-- TOC entry 3240 (class 0 OID 0)
-- Dependencies: 210
-- Name: TABLE t1; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.t1 IS 'задачи пользователя';


--
-- TOC entry 211 (class 1259 OID 16398)
-- Name: test2; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.test2 (
);


ALTER TABLE public.test2 OWNER TO postgres;

--
-- TOC entry 212 (class 1259 OID 16401)
-- Name: test3; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.test3 (
);


ALTER TABLE public.test3 OWNER TO postgres;

--
-- TOC entry 3233 (class 0 OID 16427)
-- Dependencies: 213
-- Data for Name: prodagiDB; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO public."prodagiDB" (name, id, prodaji) VALUES ('Петров', '959416a1-92eb-4124-a951-a547c2df0d20', 25);
INSERT INTO public."prodagiDB" (name, id, prodaji) VALUES ('Иванов', '6c67fe46-94a4-4891-a7b4-c6041fee7bc6', 30);


--
-- TOC entry 3230 (class 0 OID 16395)
-- Dependencies: 210
-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: postgres
--



--
-- TOC entry 3231 (class 0 OID 16398)
-- Dependencies: 211
-- Data for Name: test2; Type: TABLE DATA; Schema: public; Owner: postgres
--



--
-- TOC entry 3232 (class 0 OID 16401)
-- Dependencies: 212
-- Data for Name: test3; Type: TABLE DATA; Schema: public; Owner: postgres
--



--
-- TOC entry 3090 (class 2606 OID 16431)
-- Name: prodagiDB prodagiDB_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public."prodagiDB"
    ADD CONSTRAINT "prodagiDB_pkey" PRIMARY KEY (id);


--
-- TOC entry 3088 (class 2606 OID 16418)
-- Name: t1 t1_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.t1
    ADD CONSTRAINT t1_pkey PRIMARY KEY (id);


-- Completed on 2022-06-06 14:35:09

--
-- PostgreSQL database dump complete
--

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

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

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

@alvassin

@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?

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

@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

@ghaggart

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

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

Привет, ребята, я застрял в проблеме с моей первой базой данных на Postgres, и я не могу найти решение, я попытался увидеть, было ли это вопросом настройки автофиксации, но, очевидно, из postgres 9.x это неубедительная операция. В документации предполагается, что это может быть ошибка разрешений или полный диск. Это код

CREATE DATABASE datacamp_courses 
WITH 
OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'English_United States.1252'
LC_CTYPE = 'English_United States.1252'
TABLESPACE = pg_default
CONNECTION LIMIT = -1;


CREATE TABLE datacamp_courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR (50) UNIQUE NOT NULL,
    course_instructor VARCHAR (100) NOT NULL,
    topic VARCHAR (2) NOT NULL
    );

Это ошибка:

ERROR:  CREATE DATABASE cannot run inside a transaction block
SQL state: 25001

Любая помощь была бы высоко оценена

2 ответа

Лучший ответ

Сообщение говорит само за себя: вы не можете создать базу данных в транзакции.

Вы можете попробовать запустить свои операторы в psql (CLI) с запусками в AUTOCOMMIT по умолчанию: это будет работать, но обратите внимание, что таблица будет создана в текущей базе данных (по умолчанию postgres), что, возможно, не то, что вы хотите. Если вы хотите создать базу данных и таблицу в этой новой базе данных, вы можете попробовать:

  create database mydb;
  connect mydb
  create table test(c int);


1

pifor
4 Мар 2020 в 17:58

Если вы используете браузер db в Intellij idea, выберите поле автоматической фиксации в настройках базы данных, к которой вы подключаетесь.


0

Vedat
23 Июн 2021 в 18:08

Я пытаюсь создать базу данных в PostgreSQL с помощью pgadmin4. В базах данных я создал базу данных с именем movie_data.

Я выбрал CREATE script из контекстного меню базы данных, в котором отображается следующее:

--Database: movie_data

--DROP DATABASE movie_data;

CREATE DATABASE movie_data
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'English_Switzerland.1252'
    LC_CTYPE = 'English_Switzerland.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

Я поправил это

SET AUTOCOMMIT = ON;
-- create the directors table 

CREATE TABLE directors (
    director_id SERIAL PRIMARY KEY, --primary key, serial is integer which automatically increments as owner adds data 
    first_name VARCHAR(30), --VARCHAR is varying character, 30 is a maximum number of characters 
    last_name VARCHAR(30) NOT NULL, -- last name requires a value because its not null
    date_of_birth DATE,
    nationality VARCHAR(20) --no need to put a coma for last entry 
);

Запустив все вместе прямо оттуда, я всегда получаю следующую ошибку:

ERROR:  CREATE DATABASE cannot run inside a transaction block
SQL state: 25001

Я смотрел похожие вопросы, но не нашел ответа, так как это очень тривиальный случай. Может кто-нибудь объяснить, что не так и почему. Большое спасибо за Вашу помощь. Обратите внимание: я новичок в PostgreSQL.

I want to create a concurrently index in postgresql inside a trigger like following code

    CREATE OR REPLACE FUNCTION hour_production_index_partition(partition_name text) RETURNS VOID AS
$BODY$
BEGIN
    -- Ensure we have all the necessary indices in this partition;
    EXECUTE 'CREATE INDEX CONCURRENTLY IF NOT EXISTS ' || partition_name || '_domain_write_date_idx ON ' || partition_name || ' (fk_domain, write_date)';
    END;
$BODY$
LANGUAGE plpgsql;

The problem is that when I execute that statement, postgresql complains sending an error like

WARN SqlExceptionHelper — SQL Error: 0, SQLState: 25001 ERROR
SqlExceptionHelper — ERROR: CREATE INDEX CONCURRENTLY cannot run
inside a transaction block Where: SQL statement «CREATE INDEX
CONCURRENTLY IF NOT EXISTS
hour_production_1_2018_07_01_domain_write_date_idx ON
hour_production_1_2018_07_01 (fk_domain, write_date)»

I need to create this index in this way because hour_production table is dinamically break into small parts.

asked Jul 27, 2018 at 23:17

Joag's user avatar

1

CREATE INDEX CONCURRENTLY cannot run inside a transaction, and all functions are transactions, (but ordinary create index can).

Perhaps something like PG_AGENT can be leveraged to create the index upon command from your trigger.

https://www.pgadmin.org/docs/pgadmin4/3.x/pgagent.html

Alternatively you could do a regular create index when you create the table, an empty table will not be locked for long during a create index.

answered Jul 28, 2018 at 1:35

Jasen's user avatar

JasenJasen

3,0981 gold badge12 silver badges15 bronze badges

2

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

· 11 comments

Comments

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

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

@alvassin

@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?

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

@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

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

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

@vvalentinvolkov

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

  • Error какие бывают ошибки
  • Error как удалить ошибку
  • Error while unpacking program code lp5 please report to author ошибка
  • Error while executing the query ошибка
  • Error while evaluating uicontrol callback ошибка как решить