Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
the issue with a local PostgreSQL install?: Local Postgres
Python version: 3.11.8
Platform: Linux
Do you use pgbouncer?: No
Did you install asyncpg with pip?: Yes
Can the issue be reproduced under both asyncio and
uvloop?: Yes
I'm using a connection pool and doing individual connection.execute however if there's any kind of error with the SQL I will get an error that asyncpg is trying to rollback the transaction.
I'm very specifically not trying to use transactions at all.
app.ctx.crate_pool = await asyncpg.create_pool(**CRATE_DB_CONFIG, loop=loop, max_size=10)
async with app.ctx.crate_pool.acquire() as connection:
await connection.execute("INSERT INTO table1 (col1, col2, col3) values ($1, $2, $3)", var1, var2, var3)
If there's a conflict on the insert I will receive the following error:
Resetting connection with an active transaction <asyncpg.connection.Connection object at 0xffffa51cfc40>
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 2377, 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 353, 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 1794, 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 1892, in __execute
result, stmt = await self._do_execute(
^^^^^^^^^^^^^^^^^^^^^^^
File "/root/.cache/pypoetry/virtualenvs/allfactors-sanic-pIZXgjO4-py3.11/lib/python3.11/site-packages/asyncpg/connection.py", line 1925, 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 433, in _get_statement
statement = await self._protocol.prepare(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "asyncpg/protocol/protocol.pyx", line 166, in prepare
asyncpg.exceptions.InternalServerError: ERROR: duplicate key value violates unique constraint "var2"
DETAIL: Key (var2)=(test1) already exists.
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/af_events/af_events.py", line 2376, in get_userconfig
async with app.ctx.crate_pool.acquire() as connection:
File "/root/.cache/pypoetry/virtualenvs/allfactors-sanic-pIZXgjO4-py3.11/lib/python3.11/site-packages/asyncpg/pool.py", line 219, in release
raise ex
File "/root/.cache/pypoetry/virtualenvs/allfactors-sanic-pIZXgjO4-py3.11/lib/python3.11/site-packages/asyncpg/pool.py", line 209, in release
await self._con.reset(timeout=budget)
File "/root/.cache/pypoetry/virtualenvs/allfactors-sanic-pIZXgjO4-py3.11/lib/python3.11/site-packages/asyncpg/connection.py", line 1500, in reset
await self.execute(reset_query, timeout=timeout)
File "/root/.cache/pypoetry/virtualenvs/allfactors-sanic-pIZXgjO4-py3.11/lib/python3.11/site-packages/asyncpg/connection.py", line 350, in execute
result = await self._protocol.query(query, timeout)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "asyncpg/protocol/protocol.pyx", line 374, in query
asyncpg.exceptions.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'}
I'm using a connection pool and doing individual connection.execute however if there's any kind of error with the SQL I will get an error that asyncpg is trying to rollback the transaction.
I'm very specifically not trying to use transactions at all.
If there's a conflict on the insert I will receive the following error: