Support for disabling transactions

asyncpg version: 0.28.0
PostgreSQL version: CrateDB 5.4.2 https://github.com/crate/crate
Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: Using CrateDB locally
Python version: 3.11.4
Platform: Linux arm64
Do you use pgbouncer?: No
Did you install asyncpg with pip?: Yes
If you built asyncpg locally, which version of Cython did you use?: 0.29.36
Can the issue be reproduced under both asyncio and uvloop?: Yes

It would be really useful to be able to disable transaction support for Postgres-like database systems that don't support transactions.

CrateDB is an OLAP database with Postgres Wire Protocol support, however it does not have support for transactions. (BEGIN is a no-op, but ROLLBACK is an undefined term and causes issues)

Currently using asyncpg like this:

asyncpg_pool = await asyncpg.create_pool(**CRATE_DB_CONFIG, loop=loop, max_size=10)

async with asyncpg_pool.acquire() as connection:
    await connection.execute("""insert into some_table (event_time, field1, field2) values ($1, $2, $3);""", datetime.now(), user_id, session_id)

I'm deliberately avoiding any connection.transaction() calls yet asyncpg still appears to wrap everything in a transaction block.

Asyncpg does not use SQL-level transaction blocks internally (only connection.transaction() does). What is the actual issue you're seeing?

Here's the exception and traceback I'm getting:

Resetting connection with an active transaction <asyncpg.connection.Connection object at 0xffff83cf3680>
InternalServerError("line 1:1: mismatched input 'ROLLBACK' expecting {'SELECT', 'DEALLOCATE', 'FETCH', 'END', 'WITH', 'CREATE', 'ALTER', 'KILL', 'CLOSE', 'BEGIN', 'START', 'COMMIT', 'ANALYZE', 'DISCARD', 'EXPLAIN', 'SHOW', 'OPTIMIZE', 'REFRESH', 'RESTORE', 'DROP', 'INSERT', 'VALUES', 'DELETE', 'UPDATE', 'SET', 'RESET', 'COPY', 'GRANT', 'DENY', 'REVOKE', 'DECLARE'}")
Traceback (most recent call last):
  File "/af_events/af_events.py", line 1191, in get_userconfig
    ret_status = await connection.execute(sql, *vals_arr)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.cache/pypoetry/virtualenvs/allfactors-sanic-pIZXgjO4-py3.11/lib/python3.11/site-packages/asyncpg/connection.py", line 319, in execute
    _, status, _ = await self._execute(
                   ^^^^^^^^^^^^^^^^^^^^
  File "/root/.cache/pypoetry/virtualenvs/allfactors-sanic-pIZXgjO4-py3.11/lib/python3.11/site-packages/asyncpg/connection.py", line 1659, in _execute
    result, _ = await self.__execute(
                ^^^^^^^^^^^^^^^^^^^^^
  File "/root/.cache/pypoetry/virtualenvs/allfactors-sanic-pIZXgjO4-py3.11/lib/python3.11/site-packages/asyncpg/connection.py", line 1684, in __execute
    return await self._do_execute(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.cache/pypoetry/virtualenvs/allfactors-sanic-pIZXgjO4-py3.11/lib/python3.11/site-packages/asyncpg/connection.py", line 1711, in _do_execute
    stmt = await self._get_statement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.cache/pypoetry/virtualenvs/allfactors-sanic-pIZXgjO4-py3.11/lib/python3.11/site-packages/asyncpg/connection.py", line 412, in _get_statement
    types, intro_stmt = await self._introspect_types(
                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.cache/pypoetry/virtualenvs/allfactors-sanic-pIZXgjO4-py3.11/lib/python3.11/site-packages/asyncpg/connection.py", line 458, in _introspect_types
    return await self.__execute(
           ^^^^^^^^^^^^^^^^^^^^^
  File "/root/.cache/pypoetry/virtualenvs/allfactors-sanic-pIZXgjO4-py3.11/lib/python3.11/site-packages/asyncpg/connection.py", line 1684, in __execute
    return await self._do_execute(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.cache/pypoetry/virtualenvs/allfactors-sanic-pIZXgjO4-py3.11/lib/python3.11/site-packages/asyncpg/connection.py", line 1711, in _do_execute
    stmt = await self._get_statement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.cache/pypoetry/virtualenvs/allfactors-sanic-pIZXgjO4-py3.11/lib/python3.11/site-packages/asyncpg/connection.py", line 397, in _get_statement
    statement = await self._protocol.prepare(
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "asyncpg/protocol/protocol.pyx", line 168, in prepare

The part that has me confused is this is from a simple connection.execute() with just a single INSERT without using any prepared statements. (I've tried to keep everything as simple as possible, although it looks like somehow a prepared statement is being made behind the scenes due to caching?)

The connection string is using all of the standard options and defining only host, user, password, port, and database.

I'm not 100% sure, but I believe I'm seeing the same behavior. I'm trying to do CREATE DATABASE, which cannot be within a transaction block, and postgres is telling me I'm in one.

python 3.12 on debian (via docker)

conn = await asyncpg.connect(
        statement_cache_size=0,
        host=POSTGRES_HOST,
        user=POSTGRES_USER,
        password=POSTGRES_PASS)
try:
    await conn.execute(
        f'''
        CREATE DATABASE {template_dbname} WITH
        ENCODING = UTF8
        OWNER = tt_admin;

        REVOKE CONNECT ON DATABASE {template_dbname} FROM PUBLIC;
        GRANT CONNECT ON DATABASE {template_dbname} TO tt_user;
        GRANT CONNECT ON DATABASE {template_dbname} TO taev_controlplane;
        ''')

finally:
    await conn.close()

(for context, I use templated databases as test fixtures during integration tests, so the database name is trusted here).

tests/conftest.py:115: in _setup_template_testdb
    await _create_template_db(template_dbname)
/taev_venv/tt-website-g-0Zgg5e-py3.12/lib/python3.12/site-packages/trio_asyncio/_adapter.py:54: in __call__
    return await self.loop.run_aio_coroutine(f)
/taev_venv/tt-website-g-0Zgg5e-py3.12/lib/python3.12/site-packages/trio_asyncio/_base.py:225: in run_aio_coroutine
    return await run_aio_future(fut)
/taev_venv/tt-website-g-0Zgg5e-py3.12/lib/python3.12/site-packages/trio_asyncio/_util.py:43: in run_aio_future
    res = await trio.lowlevel.wait_task_rescheduled(abort_cb)
/taev_venv/tt-website-g-0Zgg5e-py3.12/lib/python3.12/site-packages/trio/_core/_traps.py:178: in wait_task_rescheduled
    return (await _async_yield(WaitTaskRescheduled(abort_func))).unwrap()
/taev_venv/tt-website-g-0Zgg5e-py3.12/lib/python3.12/site-packages/outcome/_impl.py:213: in unwrap
    raise captured_error
/taev_venv/tt-website-g-0Zgg5e-py3.12/lib/python3.12/site-packages/trio_asyncio/_adapter.py:17: in _call_defer
    return await proc(*args, **kwargs)
tests/conftest.py:183: in _create_template_db
    await conn.execute(
/taev_venv/tt-website-g-0Zgg5e-py3.12/lib/python3.12/site-packages/asyncpg/connection.py:350: in execute
    result = await self._protocol.query(query, timeout)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

>   ???
E   asyncpg.exceptions.ActiveSQLTransactionError: CREATE DATABASE cannot run inside a transaction block

asyncpg/protocol/protocol.pyx:374: ActiveSQLTransactionError

If I split the database creation into a separate conn.execute and remove the semicolon, the statement succeeds.

Multi-statement queries are wrapped into an implicit transaction block by Postgres. There is no way to disable this behavior, you should split the statements on the client side instead.

MagicStack / asyncpg

Support for disabling transactions #1074