pyscopg2 WITHOUT transaction

Question

Sometimes I have a need to execute a query from psycopg2 that is not in a transaction block.

For example:

cursor.execute('create index concurrently on my_table (some_column)')

Doesn't work:

InternalError: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

I don't see any easy way to do this with psycopg2. What am I missing?

I can probably call os.system('psql -c "create index concurrently"') or something similar to get it to run from my python code, however it would be much nicer to be able to do it inside python and not rely on psql to actually be in the container.

Yes, I have to use the concurrently option for this particular use case.

---

Another time I've explored this and not found an obvious answer is when I have a set of sql commands that I'd like to call with a single execute(), where the first one briefly locks a resource. When I do this, that resource will remain locked for the entire duration of the execute() rather than for just when the first statement in the sql string was running because they all run together in one big happy transaction.

In that case I could break the query up into a series of execute() statements - each became its own transaction, which was ok.

It seems like there should be a way, but I seem to be missing it. Hopefully this is an easy answer for someone.

---

EDIT: Add code sample:

#!/usr/bin/env python3.10

import psycopg2 as pg2

# -- set the standard psql environment variables to specify which database this should connect to.

# We have to set these to 'None' explicitly to get psycopg2 to use the env variables
connDetails = {'database': None, 'host': None, 'port': None, 'user': None, 'password': None}

with (pg2.connect(**connDetails) as conn, conn.cursor() as curs):

    conn.set_session(autocommit=True)

    curs.execute("""
create index concurrently if not exists my_new_index on my_table (my_column);
""")

Throws:

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

Answer 1

Per psycopg2 documentation:

It is possible to set the connection in autocommit mode: this way all the commands executed will be immediately committed and no rollback is possible. A few commands (e.g. CREATE DATABASE, VACUUM, CALL on stored procedures using transaction control…) require to be run outside any transaction: in order to be able to run these commands from Psycopg, the connection must be in autocommit mode: you can use the autocommit property.

Hence on the connection: conn.set_session(autocommit=True)

Further resources from psycopg2 documentation:

transactions-control

connection.autocommit

Answer 2

To summarize and clarify, the working answer is:

#!/usr/bin/env python3.10

import psycopg2 as pg2

# -- set the standard psql environment variables to specify 
# -- which database this should connect to.

# We have to set these to 'None' explicitly to get psycopg2 to use the env variables
conn_args = {'database': None, 'host': None, 'port': None, 'user': None, 'password': None}

conn = pg2.connect(**conn_args)
conn.set_session(autocommit=True)
with conn.cursor() as curs:
    curs.execute("""
       create index concurrently if not exists 
       my_new_index on my_table(my_column);
    """)
conn.close()