vatsrahul1001 opened a new pull request, #74581: URL: https://github.com/apache/airflow/pull/74581
Since 3.4 a bare `postgresql://` metadata URL is rewritten to `postgresql+psycopg://`, so psycopg3 is now the default driver for both the sync and async engines. psycopg3 prepares a statement server-side once it has been executed `prepare_threshold` times, which defaults to 5. Under a transaction-mode pooler the next transaction can land on a different server connection, so unless the pooler tracks prepared statements itself, the scheduler starts failing once it crosses that threshold: ``` sqlalchemy.exc.ProgrammingError: (psycopg.errors.InvalidSqlStatementName) prepared statement "_pg3_69" does not exist [SQL: SELECT task_instance.pool, task_instance.state, sum(task_instance.pool_slots) ...] ``` This is not an exotic setup. The Helm chart sets `pool_mode = transaction` (`chart/templates/_helpers.yaml`) and pins PgBouncer 1.23.1 (`chart/values.yaml`), and PgBouncer only enables prepared statements by default from 1.24. So the deployment we recommend is one of the affected ones. This sets `prepare_threshold = None` for psycopg3 URLs in `_get_connect_args`. Anything in `[database] sql_alchemy_connect_args` is merged over the top and wins, so operators whose pooler does track prepared statements — or who run without one — can set it back and keep the optimisation. related: #68496, #69469 ### Reproducing Postgres plus PgBouncer in transaction mode with `max_prepared_statements = 0`, eight concurrent clients running one parameterised query each: ``` psycopg3 defaults 3/8 workers failed, DuplicatePreparedStatement prepare_threshold=None 0/8 ``` It only shows up under concurrency, which is why a single-threaded check passes. The same harness against PgBouncer 1.26 passes without this change, since `max_prepared_statements` defaults to 200 there. ### Why this wasn't caught earlier The driver switch landed in two parts. #68496 moved the async default and was validated against a transaction-mode PgBouncer. #69469 moved the sync default and describes itself as mirroring that change; pooling isn't mentioned. Neither covered a pooler with prepared statements turned off, and the async conclusion doesn't carry over — the async engine reports `prepare_threshold=5` too, so the fix applies to both. ### Also updated `config.yml` described both connect-args options as defaulting to an empty dict, and told operators the async psycopg3 driver needed no extra args. `set-up-database.rst` made the same claim and now documents the default and how to opt out of it. --- ##### Was generative AI tooling used to co-author this PR? - [X] Yes — Claude Code (Opus 5) Generated-by: Claude Code (Opus 5) following [the guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions) -- 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]
