psycopg2.errors.ActiveSqlTransaction: CREATE TABLESPACE cannot run inside a transaction block

user_16289790 0
Viewed 3669

I am quite new to Python, we have an app container & a DB container. App container collects values like DB_HOST,PORT etc from CLI & try to create Tablespace on containerized Postgres DB running on same docker host.

While execution the query we are getting below error.

psycopg2.errors.ActiveSqlTransaction: CREATE TABLESPACE cannot run inside a transaction block

Python libraries used:

  • psycopg2
  • psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

App Dockefile Sample

FROM python:3.7-alpine

RUN apk update && \
    apk add --no-cache openssh sshpass && \
    apk add --no-cache --virtual .build-deps gcc musl-dev && \
    apk add linux-headers && \
    apk add python3-dev && \
    apk add postgresql-dev && \
    apk add postgresql-client && \
    apk add bash && \
    apk add libffi-dev make

RUN pip install --upgrade pip
RUN pip install PyYAML==5.3.1 \
                docker==4.2.1 \
                cryptography==2.8.0 \
                docker-pycreds==0.4.0 \
                docker-compose==1.26.0 \
                dockerpty==0.4.1 \
                netaddr==0.7.19 \
                netifaces==0.10.9 \
                pycryptodome \
                psycopg2-binary \
                mock
                
ARG AP_DIR=/var/abc/sm

RUN mkdir -p $AP_DIR/log

ADD src/main/python $AP_DIR/python
ADD src/main/resources_hardcopy $AP_DIR/resources
ADD src/main/resources/certs $AP_DIR/resources/certs
ADD build.properties $AP_DIR

VOLUME /opt/abc/sm
VOLUME /opt/abc/apconn

RUN addgroup --system cloud && \
    adduser --system --disabled-password --ingroup cloud stackhelp
USER stackhelp
WORKDIR $AP_DIR/python

CMD ["../launch.sh"]

The same python code has been working till 16 June 2021. Is there any recent changes in psycopg2 , psycopg2.extensions can cause this ?

Following things tried, but did not work.

  1. Updated python:3.7-alpine to python:alpine3.12
  2. Postgres Container Updated to Postgres13
postgres-12 psycopg2 python
2 Answers

@Learner is right.

Version 2.9.x always starts a transaction when you connect to a database using a context manager like this:

with psycopg2.connect(...) as connection:
    # This starts a transaction as of v2.9
    ...

Here's a quote from the release notes:

with connection starts a transaction on autocommit transactions too (ticket #941).

This means that commands like CREATE DATABASE or DROP DATABASE can no longer be issued when connecting in this way.

Although the docs don't seem to offer an official solution to this problem, it has been suggested that the "new" way to do this is to use the "old" method of connecting to the database:

try:
    connection = psycopg2.connect(...)
    with connection.cursor() as cursor:
        cursor.execute("CREATE DATABASE foo")
finally:
    if connection:
        connection.close()
user_20718070
user_107599410
Related