aaron-y-chen opened a new pull request, #74841: URL: https://github.com/apache/airflow/pull/74841
Closes: #74732 ## Why Celery creates separate result-backend engines, so #74581 does not protect them. With psycopg3 and PgBouncer transaction pooling without statement tracking, automatic preparation causes statement errors that Celery retries can hide. ## How Default `connect_args.prepare_threshold` to `None` only when SQLAlchemy resolves the backend driver to `psycopg`. Preserve explicit overrides and other engine options, leaving psycopg2 and non-database backends unchanged. ## What - Cover URL derivation, driver defaults, overrides and unknown dialects; document the default and how to override it. - Verified 42 regression checks across SQLAlchemy 2.0/2.1, the existing engine-options test, and 300 end-to-end tasks with zero backend warnings or database errors. - RST parsing and metadata generation checks passed. The full Celery docs build is pending image preparation. <details> <summary>Detailed verification matrix and evidence</summary> Runtime checks used local Breeze, Python 3.12.14, PostgreSQL 16.15 and PgBouncer 1.23.1. Celery workloads used eight prefork workers. Tracking below means PgBouncer's `max_prepared_statements`. **Environments** | Environment | Airflow core | SQLAlchemy | psycopg | Celery | | --- | --- | --- | --- | --- | | V1 | 3.3.2 | 2.0.52 | 3.3.5 | 5.6.3 | | V2 | 3.4.0b1 | 2.1.4 | 3.3.6 | 5.6.3; also 5.6.2 for reproduction | | V3 | main snapshot (`df5cf9ec`) | 2.0.54 | 3.3.6 | 5.6.3 | The original V1 provider was 3.24.0. Fixed runs installed the same locally built provider candidate, versioned 3.25.0, in all three environments without upgrading core or SQLAlchemy. Loaded backend-factory source hashes matched the patched source. **Reproduction and controls before the patch** Each run submitted 100 tasks: 90 expected successes and 10 expected failures. The table counts backend retry warnings; PostgreSQL independently recorded matching errors. Transaction-pool runs used tracking `0`. | Environment / backend route | Direct PostgreSQL, one run | Automatic preparation, three runs | `prepare_threshold=None`, three runs | | --- | --- | --- | --- | | V1, derived from metadata URL / shared pool | 0 | 28 / 31 / 26 | 0 / 0 / 0 | | V2, explicit psycopg3 / separate backend database | 0 | 28 / 31 / 29 | 0 / 0 / 0 | | V2, derived from metadata URL / shared pool | 0 | 27 / 28 / 27 | 0 / 0 / 0 | | V2, driverless `db+postgresql://` | 0 | 24 / 32 / 28 | 0 / 0 / 0 | | V3, explicit psycopg3 / separate backend database | 0 | 32 / 30 / 28 | 0 / 0 / 0 | | V2 with Celery 5.6.2, separate backend database | 0 | 28 / 25 / 27 | 0 / 0 / 0 | Across 42 runs and 4,200 tasks, final results and scheduler reads matched expectations. Automatic preparation through transaction pooling nevertheless produced **509 warning/error pairs**: `42P05` × 387, `26000` × 116, `08P01` × 5 and `22021` × 1. Celery 5.6.3 invalidated engines after 429 errors; 5.6.2 reported 80 errors without invalidation. Client INSERT parameters were also observed binding to a server-side UPDATE statement. No incorrect Celery result payload was observed; successful retries do not establish statement correctness. Twelve additional pre-fix Airflow smoke runs compared direct, default-preparation and preparation-disabled paths. Default-preparation groups recorded 120 backend warnings/database errors; controls recorded zero. API, metadata and Celery terminal-state checks still passed. A representative direct run also covered 20 expected failures and 20 retry tasks, with all 160 Celery attempts accounted for. **Additional compatibility checks and diagnostics before the patch** | Scenario | Observed evidence | | --- | --- | | Explicit psycopg2, transaction pooling, tracking `0`; V1 and V2 | 200 tasks total; zero backend warnings or database errors; no psycopg3 connection parameter. | | Psycopg3, session pooling, tracking `0`; V2 | 100 tasks; zero errors; all eight worker connections retained server-session affinity. Session pool size was 20. | | Psycopg3, transaction pooling, tracking `100`; V1/V2 separate backend and V2 metadata-derived backend | 300 tasks total; zero errors; 330 named executions per run, confirming preparation remained active. Binary libpq `180006` supported prepared-statement Close. | | Full Airflow smoke with tracking `100`, V2 shared metadata pool | 100 successful tasks/results; zero warnings/errors; 340 Celery named executions and ten server PIDs shared by core SQL and Celery writes. | | Standalone psycopg cache-eviction/rollback diagnostic, tracking `100` | 320 correct primary results; 288 protocol Close messages, 32 `DEALLOCATE ALL` and 640 named executions. Only the 32 intentionally triggered `22012` errors occurred. | | Standalone eight-client positive probe, transaction pooling, tracking `0` | Automatic preparation produced 12 SQL errors and 369 incorrect scalar results; direct and preparation-disabled controls produced neither. These were generic probe results, not observed Celery payload corruption. | **Regression coverage after the patch** Fourteen cases passed in each environment: **42 passed**, with no failures, errors or skips. Tests inspect the real Celery backend's SQLAlchemy engine and intercept DBAPI connection kwargs before opening a network connection. | Configuration | Verified behavior | | --- | --- | | Metadata-derived URL or explicit `+psycopg` | Actual connection kwargs include `prepare_threshold=None`. | | Driverless PostgreSQL URL | SQLAlchemy 2.0 resolves psycopg2 and receives no preparation parameter; 2.1 resolves psycopg3 and receives `None`. | | Explicit psycopg2 or SQLite | No psycopg3 preparation parameter is added. | | Explicit threshold `None`, `0` or `5` | The configured value wins; `pool_recycle=1800` and `connect_timeout=7` remain intact. | | Redis result backend | Configuration succeeds and engine options remain unchanged. | | Unknown dialect, `db+bad_schema:///` | Configuration preserves engine options. Removing the exception guard in an isolated process makes the same test fail with `NoSuchModuleError`. | The existing engine-options test also passed against an isolated PostgreSQL metadata database after explicitly selecting its SQLite result backend. **End-to-end verification with the patched provider** These runs supplied empty engine options, used transaction pooling with tracking `0`, and relied on the new provider default. Each observed eight prefork QueuePool engines and eight actual worker connections. | Environment / route | Actual worker driver / preparation setting | Successful tasks / Celery results | Backend warnings / database errors | Named executions | | --- | --- | --- | --- | --- | | V1, metadata-derived backend / shared pool | psycopg3 / `None` | 100 / 100 | 0 / 0 | 0 | | V1, explicit driverless backend URL | psycopg2 / no preparation parameter | 100 / 100 | 0 / 0 | 0 | | V2, explicit driverless backend URL | psycopg3 / `None` | 100 / 100 | 0 / 0 | 0 | All three runs passed Airflow/Celery state comparisons and worker-drain checks, with zero engine invalidations. The shared-pool run observed ten PostgreSQL server PIDs serving both core SQL and Celery writes. Helm rendering separately confirmed that an unset result backend routes through the metadata pool; no Kubernetes deployment was used. Evidence was cross-checked using worker connection traces, PostgreSQL JSON logs and JUnit reports; raw artifacts are retained locally. Fixed end-to-end runs used Celery 5.6.3. Older libpq workarounds, production deployments and performance were not evaluated. New RST sections parsed without warnings and the provider-generation hook passed; the full Sphinx/spelling build remains pending. </details> --- ##### Was generative AI tooling used to co-author this PR? <!-- If generative AI tooling has been used in the process of authoring this PR, please change below checkbox to `[X]` followed by the name of the tool, uncomment the "Generated-by". --> - [x] Yes (please specify the tool below) Generated-by: [Claude Opus 5.5 + GPT Sol 6] following [the guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions) --- * Read the **[Pull Request Guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#pull-request-guidelines)** for more information. Note: commit author/co-author name and email in commits become permanently public when merged. * For fundamental code changes, an Airflow Improvement Proposal ([AIP](https://cwiki.apache.org/confluence/display/AIRFLOW/Airflow+Improvement+Proposals)) is needed. * When adding dependency, check compliance with the [ASF 3rd Party License Policy](https://www.apache.org/legal/resolved.html#category-x). * For significant user-facing changes create newsfragment: `{pr_number}.significant.rst`, in [airflow-core/newsfragments](https://github.com/apache/airflow/tree/main/airflow-core/newsfragments). You can add this file in a follow-up commit after the PR is created so you know the PR number. -- 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]
