Django throws "[12728] ERROR: CREATE DATABASE cannot run inside a transaction block" when tried to create postgresql database

Question

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:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\promise\promise.py", line 487, in _resolve_from_executo
r
    executor(resolve, reject)
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\promise\promise.py", line 754, in executor
    return resolve(f(*args, **kwargs))
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\graphql\execution\middleware.py", line 75, in make_it_p
romise
    return next(*args, **kwargs)
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\contextlib.py", line 52, in inner
    return func(*args, **kwds)
  File "D:\xERP\graphqltest\users\schema.py", line 68, in mutate
    db.save()
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\base.py", line 718, in save
    force_update=force_update, update_fields=update_fields)
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\base.py", line 748, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\base.py", line 831, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\base.py", line 869, in _do_insert
    using=using, raw=raw)
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\manager.py", line 82, in manager_metho
d
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\query.py", line 1136, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\sql\compiler.py", line 1289, in execut
e_sql
    cursor.execute(sql, params)
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py", line 100, in execute
    return super().execute(sql, params)
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py", line 77, in _execute_with
_wrappers
    return executor(sql, params, many, context)
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\Envy\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.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

Please don't post your code as an image. Instead, create a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) and format your code using the appropriate markdown. — Mihai Chelaru, Oct 22 '19 at 18:24
Possible duplicate of [Create a Postgres database using python](https://stackoverflow.com/questions/34484066/create-a-postgres-database-using-python) — Mihai Chelaru, Oct 22 '19 at 18:26
I don't think that was my problem. I just tried the code without transaction_atomic, it worked perfectly. Why is it throwing error if I add transaction_atomic decorator? — Mahmud Abdur Rahman, Oct 22 '19 at 18:44
I would say to read [this answer](https://stackoverflow.com/a/22936871/9374673), although it doesn't really provide a specific answer to your question. `transaction_atomic` will start a transaction in which the statements will be executed, and Postgres does not permit the CREATE DATABASE statement to be executed during a transaction. — Mihai Chelaru, Oct 22 '19 at 19:22
Thanks a lot for the help @MihaiChelaru brother. I finally realized that, you can't create a database inside a transaction block. Because that doesn't make any sense. transaction_atomic works on a specific database, if any error occurs, it rolls back. Creating another database inside there will never work, because it just can't delete that database if any error occurs. Thanks anyway. — Mahmud Abdur Rahman, Oct 22 '19 at 19:35
You should post your solution (omitting `@transaction.atomic`) and what you just put in your latest comment as an [answer to your own question](https://stackoverflow.com/help/self-answer). You can even accept your own answer if you want to show people that your question has been resolved, as I'm sure other Django users might run into this problem in the future. — Mihai Chelaru, Oct 22 '19 at 20:34

score 0 · Accepted Answer · answered Oct 22 '19 at 20:33

0

You must set the connection to autocommit mode before running CREATE DATABASE, otherwise psycopg2 will wrap it in an explicit transaction, which leads to the observed error.

answered Oct 22 '19 at 20:33

Laurenz Albe

209,280
17
206
263

Django throws "[12728] ERROR: CREATE DATABASE cannot run inside a transaction block" when tried to create postgresql database

1 Answers1