Regression in v0.31.0: with `statement_cache_size=0`, type introspection reuses the unnamed statement name `""` and clobbers a user statement on the server-side cursor path

[quentinverlhac]

Summary

Since 0.31.0, connecting with statement_cache_size=0 and then opening a server-side cursor over a result that contains an unknown result type (e.g. an enum) fails deterministically with:

asyncpg.exceptions.ProtocolViolationError:
bind message supplies 2 parameters, but prepared statement "" requires 1

The cause is that asyncpg's own type-introspection query is executed as unnamed statement "", replacing the user statement prepared as "" because the cache is disabled. The subsequent cursor bind then targets the wrong statement.

This is a regression from PR #1245 (shipped in 0.31.0), which made prepare(name=None) produce the unnamed statement "" when the statement cache is disabled.

Before 0.31.0, prepare(name=None) produced a uniquely named statement regardless of the cache setting, so it could never collide with the unnamed introspection query.

Environment

asyncpg	0.31.0
PostgreSQL server	16.14
Python	3.14.6
Connection pooler	none (direct connection)
ORM / driver wrapper	none (pure asyncpg)

Reproduction

Full setup here:
https://gist.github.com/quentinverlhac/dbbf98401721239f77b969645b9d880d

Requires only asyncpg and a PostgreSQL instance. Point DSN at any database where the connecting role may create a type and a table.

"""Pure-asyncpg reproducer of the 0.31.0 statement_cache_size=0 cursor regression.

The failing server-side cursor sequence is:

    prepared_stmt = await conn.prepare(operation, name=None)
    cursor        = await prepared_stmt.cursor(*parameters)

Key conditions to trigger the bug:
  - statement_cache_size=0        -> prepare(name=None) produces the UNNAMED "" statement
  - result contains an enum       -> asyncpg runs its own introspection as "" and clobbers it
  - a FRESH connection            -> the enum is unknown, so introspection fires here
  - >=1 bind params (2 here)      -> gives the clean ProtocolViolationError (2 vs 1)

The trigger is asyncpg's own type introspection running *inside* conn.prepare()
on a fresh connection (the enum result type is unknown), which clobbers the ""
statement; the later cursor bind then targets it. This is a direct connection,
no connection pooler involved.
"""
import asyncio
import asyncpg

DSN = dict(user='asyncpg', password='asyncpg_pw',
           database='asyncpg_db', host='127.0.0.1', port=5433)

OPERATION = 'SELECT id, value FROM mytable WHERE id > $1 AND id < $2 ORDER BY id'
PARAMETERS = (0, 100)


async def setup():
    # Setup on a SEPARATE connection so the streaming connection below is fresh
    # and has never introspected my_enum.
    conn = await asyncpg.connect(**DSN)
    await conn.execute('DROP TABLE IF EXISTS mytable')
    await conn.execute('DROP TYPE IF EXISTS my_enum')
    await conn.execute("CREATE TYPE my_enum AS ENUM ('value1', 'value2')")
    await conn.execute(
        'CREATE TABLE mytable(id serial PRIMARY KEY, value my_enum NOT NULL)')
    await conn.execute("INSERT INTO mytable(value) VALUES('value1'), ('value2')")
    await conn.close()


async def stream_over_cursor():
    # Fresh connection with the cache disabled -> unnamed "" statements.
    conn = await asyncpg.connect(**DSN, statement_cache_size=0)

    # A server-side cursor must run inside a transaction.
    tr = conn.transaction()
    await tr.start()
    try:
        # Step 1: prepare(name=None). With cache disabled this is the "" statement.
        # Because the result has an unknown enum type, asyncpg's own type
        # introspection runs here as "" too, clobbering this user statement.
        prepared_stmt = await conn.prepare(OPERATION, name=None)
        print('prepared name =', repr(prepared_stmt.get_name()))

        # Step 2: drive the server-side cursor with the bound params. The bind now
        # lands on the wrong "" statement (the 1-param introspection query).
        cursor = await prepared_stmt.cursor(*PARAMETERS)
        rows = await cursor.fetch(50)
        print('rows =', rows)
    finally:
        await tr.rollback()
        await conn.close()


async def main():
    await setup()
    await stream_over_cursor()


asyncio.run(main())

Actual result on 0.31.0

prepared name = ''
Traceback (most recent call last):
  ...
  File ".../asyncpg/cursor.py", line 144, in _bind
    buffer = await protocol.bind(self._state, self._args, ...)
  File "asyncpg/protocol/protocol.pyx", line 294, in bind
asyncpg.exceptions.ProtocolViolationError:
bind message supplies 2 parameters, but prepared statement "" requires 1

The prepared name = '' line confirms the precondition (the user statement is the unnamed ""), and the ProtocolViolationError is the regression.

Expected result

The cursor should stream the two rows, e.g. rows = [<Record id=1 value='value1'>, <Record id=2 value='value2'>].

Result on 0.30.0

prepared name = '__asyncpg_stmt_1__'
rows = [<Record id=1 value='value1'>, <Record id=2 value='value2'>]

Confirms the regression.

Server-side wire sequence (from log_statement='all')

The streaming connection, inside its transaction:

BEGIN;
execute <unnamed>:                               -- user SELECT parsed as ""
execute <unnamed>: WITH RECURSIVE typeinfo_tree  -- asyncpg enum introspection, ALSO "" (clobbers the SELECT)
execute <unnamed>:                               -- bind user SELECT (2 params) against "" (now the 1-param introspection)
ERROR:  bind message supplies 2 parameters, but prepared statement "" requires 1

Both the user statement and asyncpg's internal introspection use the empty statement name "", and the introspection runs between prepare and bind.

Root cause

Since PR #1245, conn.prepare(operation, name=None) resolves name=None against self._stmt_cache_enabled, which is False when statement_cache_size == 0, so the statement is created unnamed (""), which is intentional.

However, asyncpg's internal type-introspection query is also unnamed (""). When the result contains an unknown type, introspection runs while the user's "" statement is live and overwrites it.

Later, the cursor bind then targets the introspection statement instead of the initial user statement.

Notes

• The plain execute/fetch path is protected by the
  mark_unprepared() reprepare logic, so it does not suffer from the same issue than the PreparedStatement.cursor() bind path.

• This was originally surfaced to me through SQLAlchemy's stream_scalars with asyncpg dialect and connect_args={"statement_cache_size": 0}).
  The repro above removes SQLAlchemy entirely and fails identically, confirming the behavior is at the asyncpg level.

Related PRs

• Make prepare() not use named statements by default when cache is disabled #1245
• Avoid performing type introspection on known types #1243

Related issues

• copy_records_to_table uses named prepared statement even when statement_cache_size is 0 #1219
• is there any way asyncpg could be configured to not do a giant query for PG custom types when you prepare #1078