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
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
13.1k6 gold badges38 silver badges77 bronze badges
answered Oct 21, 2014 at 9:45
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 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
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
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
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.
-
sudo su - postgres
-
psql -d postgres -U postgres
-
create database dbName;
answered Dec 14, 2022 at 8:34
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
asked Mar 4, 2020 at 14:35
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
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
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
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.6SQLAlchemy
1.3.22SQLAlchemy-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
Привет, ребята, я застрял в проблеме с моей первой базой данных на 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
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
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
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?
torkashvand and tmikulin reacted with confused emoji
perminovs, ihor-pyvovarnyk, alecbz, aszubarev, 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.6SQLAlchemy
1.3.22SQLAlchemy-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
If someone face it trying to create test db with psycopg
driver then try psycopg2
just for creating db.