Support for disabling transactions

[robd003]

• 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.

[elprans]

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

[robd003]

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.

[Badg]

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.

[elprans]

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.