Skip to content

Instantly share code, notes, and snippets.

@quentinverlhac
Created July 14, 2026 15:40
Show Gist options
  • Select an option

  • Save quentinverlhac/dbbf98401721239f77b969645b9d880d to your computer and use it in GitHub Desktop.

Select an option

Save quentinverlhac/dbbf98401721239f77b969645b9d880d to your computer and use it in GitHub Desktop.
asyncpg 0.31.0 statement_cache_size=0 cursor regression repro

asyncpg 0.31.0 cursor / statement_cache_size=0 regression reproducer

Minimal, self-contained reproducer for the asyncpg 0.31.0 bug where a server-side cursor over a result containing an unknown type (an enum) fails with ProtocolViolationError when statement_cache_size=0.

Files

  • repro.py — the reproducer (pure asyncpg).
  • Dockerfile — postgres:16-alpine with the repro's credentials baked in.
  • docker-compose.yaml — runs the database, isolated from other local infra (port 5433, container asyncpg-postgres, volume asyncpg_pgdata).

Prerequisites

  • Docker (with docker compose).
  • Python 3.x.

Setup

Start the database:

docker compose up -d

Wait until it reports healthy:

docker compose ps

Create a virtualenv and install the affected asyncpg version:

python3 -m venv .venv
.venv/bin/pip install 'asyncpg==0.31.0'

Run

.venv/bin/python repro.py

Expected output (bug present)

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

The prepared name = '' line confirms the unnamed-statement precondition, and the ProtocolViolationError is the regression. If the bug were fixed, the script would instead stream two rows and print them.

Connection details

The database listens on 127.0.0.1:5433 with:

user asyncpg
password asyncpg_pw
database asyncpg_db

These are set in docker-compose.yaml / Dockerfile and mirrored in repro.py.

Teardown

docker compose down          # stop and remove the container
docker compose down -v       # also delete the asyncpg_pgdata volume
# Isolated local PostgreSQL for the test-asyncpg project.
# Deliberately separate from ~/dev/dashboard/infra/local:
# port 5433 (not 5432), volume asyncpg_pgdata (not local_database_data),
# container asyncpg-postgres (not local-postgres-1),
# creds asyncpg/asyncpg_pw/asyncpg_db (not pg/1234).
name: test-asyncpg
services:
postgres:
build: .
image: asyncpg-postgres:local
container_name: asyncpg-postgres
environment:
POSTGRES_USER: asyncpg
POSTGRES_PASSWORD: asyncpg_pw
POSTGRES_DB: asyncpg_db
PGDATA: /var/lib/postgresql/data/pgdata
ports:
- "5433:5432"
volumes:
- asyncpg_pgdata:/var/lib/postgresql/data
healthcheck:
test: ["CMD-SHELL", "pg_isready -U $${POSTGRES_USER} -d $${POSTGRES_DB}"]
interval: 5s
timeout: 5s
retries: 10
restart: unless-stopped
volumes:
# Reuses the volume created by the earlier `docker run`; kept out of the
# compose project namespace so the data name stays exactly asyncpg_pgdata.
asyncpg_pgdata:
name: asyncpg_pgdata
FROM postgres:16-alpine
# Isolated local PostgreSQL for the test-asyncpg project.
# Deliberately kept separate from ~/dev/dashboard/infra/local infra:
# - distinct default credentials (see docker run / compose)
# - distinct host port (5433, not 5432)
# - distinct named volume (asyncpg_pgdata, not local_database_data)
# - distinct container name (asyncpg-postgres, not local-postgres-1)
# Baked-in defaults; override at runtime with -e if desired.
ENV POSTGRES_USER=asyncpg \
POSTGRES_PASSWORD=asyncpg_pw \
POSTGRES_DB=asyncpg_db \
PGDATA=/var/lib/postgresql/data/pgdata
EXPOSE 5432
HEALTHCHECK --interval=5s --timeout=5s --retries=10 \
CMD pg_isready -U "$POSTGRES_USER" -d "$POSTGRES_DB" || exit 1
"""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())
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment