psycopg2を使ってcreate databaseをしようとしたら”CREATE DATABASE cannot run inside a transaction block”とエラーが出た

こんにちは、CX事業本部の夏目です。

DBのテストでdatabaseの作成と削除を行おうとしたら、エラーが出た。

エラーが出たときのコード

src/01_failed.py

from psycopg2 import connect
from psycopg2._psycopg import connection, cursor

dsn = {
    "dbname": "postgres",
    "user": "postgres",
    "password": "postgres",
    "port": "5432",
    "host": "localhost",
}

try:
    with connect(**dsn) as conn:
        conn: connection

        with conn.cursor() as cur:
            cur: cursor

            cur.execute("create database test;")
        conn.commit()
    print("finish")
finally:
    conn.close()

$ poetry run python src/01_failed.py
Traceback (most recent call last):
  File "/home/yuta/workspace/repos/psycopg2-2.9-create-database/src/01_failed.py", line 19, in <module>
    cur.execute("create database test;")
psycopg2.errors.ActiveSqlTransaction: CREATE DATABASE cannot run inside a transaction block

トランザクションの内側では create databaseは使えないようです。

対処を施したコード

次のissueに対処法が書いてありました。

withの中でconnect()をを使わずに、AUTOCOMMITに設定することで問題なく動くようになるようです。

src/02_success.py

from typing import Optional

from psycopg2 import connect
from psycopg2._psycopg import connection, cursor
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

dsn = {
    "dbname": "postgres",
    "user": "postgres",
    "password": "postgres",
    "port": "5432",
    "host": "localhost",
}


conn: Optional[connection] = None
try:
    conn = connect(**dsn)
    conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)

    with conn.cursor() as cur:
        cur: cursor

        cur.execute("create database test;")
    print("finish")
finally:
    if conn:
        conn.close()

$ poetry run python src/02_success.py
finish

まとめ

AUTOCOMMITの設定をすることでトランザクションを使わないように設定することができ、トランザクションを使わない状態ならcreate databaseを使えるようです。

検証に使ったコードは次のリポジトリに置いています。