Dev-iL opened a new issue, #67801:
URL: https://github.com/apache/airflow/issues/67801
### Under which category would you file this issue?
Airflow Core
### Apache Airflow version
main
### What happened and how to reproduce it?
Airflow's async metadata engine uses `asyncpg` for Postgres. `asyncpg`
relies on server-side prepared statements, which break when the database is
fronted by **PgBouncer in transaction (or statement) pooling mode** — a pooled
backend connection can receive a statement prepared on a different physical
backend, producing `prepared statement "__asyncpg_…__" does not exist` errors.
Today the async engine is created with empty `connect_args` by default
(`sql_alchemy_connect_args_async` defaults to `{}`), so it is **not**
PgBouncer-safe out of the box. As more routes adopt the async engine (see the
migration epic #67799 ), and especially now that the high-QPS heartbeat
endpoint uses it (#67800), this latent exposure becomes much more likely to be
hit in production.
PR #67800 documents — but does not fix — this. It adds deployment guidance
to:
- the `sql_alchemy_connect_args_async` config reference, and
- the PgBouncer section of the database setup guide,
telling operators to tune `sql_alchemy_connect_args_async` (e.g.
`prepared_statement_cache_size` and/or `prepared_statement_name_func`) for
transaction-mode PgBouncer.
#### Why a safe default was deferred
There is no single drop-in default that is both safe and free:
- **`prepared_statement_cache_size=0`** survives transaction-mode PgBouncer
but re-prepares every statement — a real performance regression for the common
case (direct connections or session-mode PgBouncer). That contradicts the
zero-regression goal of the route conversions.
- **`prepared_statement_name_func=lambda: f"__asyncpg_{uuid4()}__"`** fixes
the *name-collision* class of errors at ~zero cost but does **not** fix
transaction-mode pooling, where the statement was prepared on a different
backend than the one executing it.
- SQLAlchemy's documented PgBouncer recipe additionally expects
`poolclass=NullPool`, which conflicts with Airflow's configured async pool.
Because the correct configuration is genuinely deployment-dependent (it
hinges on PgBouncer pool mode) and the change touches engine creation for
*every* async query and every backend, it belongs in its own focused change
rather than bundled into a single-route conversion.
### What you think should happen instead?
1. Decide the policy: auto-detect/opt-in PgBouncer-safe behavior vs. a
conservative default vs. documentation-only (status quo).
2. If shipping defaults, apply them **conditionally** to the
`postgresql+asyncpg` engine only (must not be injected into the `aiosqlite` /
`aiomysql` engines, where these kwargs are invalid).
3. Consider exposing a single high-level config knob (e.g. a
`database.pgbouncer_transaction_mode`-style flag) that selects a known-good
`connect_args` bundle, instead of asking operators to hand-assemble asyncpg
kwargs.
4. Re-validate against a real transaction-mode PgBouncer in front of
Postgres.
5. Once a default lands, update/trim the deployment docs added in #67800
accordingly.
#### Acceptance criteria for closing
- Async routes work against Postgres behind transaction-mode PgBouncer with
the default (or a documented single-flag) configuration, **without** a
per-query perf regression for non-PgBouncer / session-mode deployments.
- Configuration is applied only to asyncpg engines; SQLite/MySQL async
engines are unaffected.
- Deployment docs reflect the final behavior.
### Operating System
_No response_
### Deployment
None
### Apache Airflow Provider(s)
_No response_
### Versions of Apache Airflow Providers
_No response_
### Official Helm Chart version
Not Applicable
### Kubernetes Version
_No response_
### Helm Chart configuration
_No response_
### Docker Image customizations
_No response_
### Anything else?
_No response_
### Are you willing to submit PR?
- [x] Yes I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of
Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
