You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "Dev-iL (via GitHub)" <gi...@apache.org> on 2026/06/12 14:32:53 UTC
[I] Remove psycopg2 and migrate the sync Postgres driver to psycopg3 [airflow]
Dev-iL opened a new issue, #68453:
URL: https://github.com/apache/airflow/issues/68453
## Context
#67801 switches the **async** Postgres driver default from asyncpg to psycopg3 (`postgresql+psycopg_async://`) and makes `psycopg[binary]` a hard dependency of `apache-airflow-providers-postgres`. To keep that PR reviewable, `psycopg2-binary` was deliberately kept as a hard dependency and the **sync** engine still uses psycopg2 (`postgresql+psycopg2://`, made explicit in #68314). This issue tracks the deferred follow-up: removing psycopg2 entirely and serving both sync and async from psycopg3.
## Why deferred (and why it should happen)
- Keeping both drivers doubles the installed footprint for no long-term benefit; psycopg3 serves sync and async from a single driver.
- SQLAlchemy 2.1 changes the default dialect for `postgresql://` URLs from psycopg2 to psycopg3, so the sync migration naturally aligns with the SQLA 2.1 upgrade (early experiments with that upgrade already surfaced breakage, e.g. in celery, from the implicit driver flip — hence the explicit-URL groundwork in #68314).
## Work
1. Migrate the sync metadata engine default to psycopg3 (`postgresql+psycopg://`), coordinated with (or after) the SQLAlchemy 2.1 upgrade.
2. Remove `psycopg2-binary` from `providers/postgres` hard dependencies (breaking change — major provider bump).
3. **Auto-upgrade legacy URLs:** once psycopg2 is no longer guaranteed to be installed, `postgresql://` (and `postgresql+psycopg2://`) connection URLs must be automatically mapped to `postgresql+psycopg://` when psycopg2 is absent, so existing deployment configs keep working.
4. Validate bulk-write paths (psycopg3 showed markedly slower batch INSERT/COPY in driver benchmarks — see #67801) before any bulk path moves to it.
5. Update deployment docs and `PostgresHook` as applicable.
## Acceptance criteria for closing
- psycopg2 is no longer a hard dependency anywhere in the repo.
- Legacy `postgresql://` / `postgresql+psycopg2://` configs either keep working via auto-upgrade or fail with a clear actionable error.
- Docs describe psycopg3 as the single default Postgres driver for sync and async.
---
Drafted-by: Claude Code (Fable 5) (no human review before posting)
--
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: commits-unsubscribe@airflow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Remove psycopg2 and migrate the sync Postgres driver to psycopg3 [airflow]
Posted by "Dev-iL (via GitHub)" <gi...@apache.org>.
Dev-iL commented on issue #68453:
URL: https://github.com/apache/airflow/issues/68453#issuecomment-5054800470
This was completed, but not exactly as originally envisioned:
- In core: modified per the ticket. New default will roll out in 3.4.0.
- In providers: both drivers (v2 and v3) remain installed for the foreseeable future to avoid breaking old cores.
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Remove psycopg2 and migrate the sync Postgres driver to psycopg3 [airflow]
Posted by "Dev-iL (via GitHub)" <gi...@apache.org>.
Dev-iL closed issue #68453: Remove psycopg2 and migrate the sync Postgres driver to psycopg3
URL: https://github.com/apache/airflow/issues/68453
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Remove psycopg2 and migrate the sync Postgres driver to psycopg3 [airflow]
Posted by "Dev-iL (via GitHub)" <gi...@apache.org>.
Dev-iL commented on issue #68453:
URL: https://github.com/apache/airflow/issues/68453#issuecomment-5054798808
This was completed, but not exactly as originally envisioned:
- In core: modified per the ticket. New default will roll out in 3.4.0.
- In providers: both drivers (v2 and v3) remain installed for the foreseeable future to avoid breaking old cores.
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Remove psycopg2 and migrate the sync Postgres driver to psycopg3 [airflow]
Posted by "Dev-iL (via GitHub)" <gi...@apache.org>.
Dev-iL commented on issue #68453:
URL: https://github.com/apache/airflow/issues/68453#issuecomment-4887631062
### Manifest
~~~md
# Definition: Migrate the sync Postgres driver from psycopg2 to psycopg3 (apache/airflow#68453)
## 1. Intent & Context
- **Goal:** Close apache/airflow#68453 — make psycopg3 the single default Postgres driver for both sync and async, and remove `psycopg2-binary` as a hard dependency anywhere in the repo, mirroring the async-side precedent (#67801/#68496/#69089) exactly where it applies.
- **Mental Model:**
- Two independent Postgres driver questions exist in this codebase: the **metadata engine** (`sql_alchemy_conn`, owned by `airflow-core`, used by the scheduler/ORM) and **`PostgresHook`** (`providers/postgres`, used by task operators to reach arbitrary user Postgres databases). This issue touches both, plus several *other* provider packages that independently hardcode or transitively depend on psycopg2 for reasons unrelated to `PostgresHook` itself.
- The async default switch (#67801 core, #69089 provider) is the direct precedent for every decision here: same "no implicit schemes, fail loudly" philosophy, same hard-dep↔extra flip mechanics, same changelog-only (no newsfragment) treatment for providers, same core-vs-provider PR split.
- A repo-wide grep sweep (this session) found the blast radius is wider than the issue text: `providers/celery`, `providers/google`, and `providers/pgvector` each have unconditional psycopg2 dependencies that would break once `providers/postgres` stops guaranteeing psycopg2 is installed. All three are in scope.
- Item 4 of the issue (bulk-write validation) cannot be executed in this environment (no resources to run breeze/Postgres) — its *artifacts* (a benchmark script + a spec for extending the user's external `Dev-iL/pgbench` tool) are this session's deliverable; the actual benchmark *run*, and the final go/no-go call it feeds, happen later on a separate system.
- Full evidence trail: `~/.manifest-dev/logs/figure-out-log-20260705-192456.md`.
## 2. Initial Approach
- **Architecture:** Five independent, narrowly-scoped changes rather than one monolithic change — mirrors how the async work was split into a core PR (#68496) and a provider PR (#69089), extended here to one PR per affected package for release-manager friendliness (per explicit user decision).
- **Execution Order:**
- D1 (airflow-core) → D2 (providers/postgres); D3 (providers/celery), D4 (providers/google), D5 (providers/pgvector), and D6 (benchmark artifacts) can all proceed in parallel with each other and largely in parallel with D2.
- Rationale: D1 establishes the actual driver-detection/default logic every other deliverable either consumes or independently mirrors. D2–D5 are independent leaf packages that only need to stop hardcoding/hard-depending on psycopg2 — each is self-contained and separately verifiable. D6 has no code dependency on the others.
- **Risk Areas:**
- [R-1] The deferred benchmark (D6) could later show psycopg3 is unacceptably slower for real bulk-write paths, contradicting the D1/D2 default flip. | Detect: the user's benchmark run on their other system; a markedly-slower result reopens D1's design (see "what would overturn this" below).
- [R-2] Undiscovered psycopg2 consumers beyond the five found via this session's grep sweep (`providers/postgres`, `providers/celery`, `providers/google`, `providers/pgvector`, plus benign comment/mock-import hits in `airflow-core/src/airflow/utils/sqlalchemy.py` and `devel-common/src/docs/utils/conf_constants.py` that need no change). | Detect: re-run the repo-wide grep in INV-G-BLAST-RADIUS as a final check before considering the manifest done.
- [R-3] `uv.lock` / constraints drift after each `pyproject.toml` change (D2). | Detect: `uv lock` diff review; CI constraint-update check.
- [R-4] pgvector's fix (D5) could be mistaken for a full fix of that provider's psycopg2-vs-psycopg3 connection-type bug. | Detect: keep the tracking-issue scope explicit in the code comment and PR description so reviewers don't expect more than the import-guard here.
- **Trade-offs:**
- [T-1] Fail loudly vs. auto-remap when psycopg2 is missing for an explicit `+psycopg2` URL → **fail loudly** (explicit user decision: "no implicit schemes in the project").
- [T-2] Delete psycopg2 support from `PostgresHook` vs. keep it guarded → **keep, guarded** — preserves compatibility down to the provider's declared `apache-airflow>=2.11.0` floor (which may still run SQLAlchemy 1.4).
- [T-3] Auto-push/open PRs vs. stop at ready-for-PR → **stop** (explicit user decision) — the deferred benchmark (D6) gates actual submission, so /do halts once each package is self-reviewed and its checks are green.
- [T-4] Bundle celery/google/pgvector fixes into this manifest vs. defer to separate issues → **bundle** (explicit user decision) — they're mechanical, directly-caused consequences of the same migration; pgvector's *deeper* pre-existing bug still gets its own tracking issue rather than a full fix here.
- [T-5] One combined PR vs. one per affected package → **one per package** (explicit user decision, mirrors the async core/provider split).
## 3. Global Invariants
- [INV-G1] `change-intent` review dimension: no LOW+ findings across the full diff.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=change-intent and review the full diff across all deliverables (airflow-core, providers/postgres, providers/celery, providers/google, providers/pgvector, dev/ benchmark script). PASS only if no LOW-or-higher findings."
phase: 2
```
- [INV-G2] `code-bugs` review dimension: no LOW+ findings.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=code-bugs and review the full diff across all deliverables. PASS only if no LOW-or-higher findings."
phase: 2
```
- [INV-G3] `contracts` review dimension (applies — this changes public dependency contracts in multiple `pyproject.toml` files, a durable config-upgrade contract in `configuration.py`, and public docstrings/behavior in `PostgresHook`): no LOW+ findings.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=contracts and review the full diff, paying particular attention to providers/postgres/pyproject.toml, providers/celery, providers/google, and providers/pgvector dependency/behavior changes. PASS only if no LOW-or-higher findings."
phase: 2
```
- [INV-G4] `type-safety` review dimension (Python with type hints throughout): no LOW+ findings.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=type-safety and review the full diff. PASS only if no LOW-or-higher findings."
phase: 2
```
- [INV-G5] `operational-readiness` review dimension: no MEDIUM+ findings.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=operational-readiness and review the full diff. PASS only if no MEDIUM-or-higher findings."
phase: 2
```
- [INV-G6] `code-maintainability` review dimension: no MEDIUM+ findings.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=code-maintainability and review the full diff. PASS only if no MEDIUM-or-higher findings."
phase: 2
```
- [INV-G7] `code-simplicity` review dimension: no MEDIUM+ findings.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=code-simplicity and review the full diff. PASS only if no MEDIUM-or-higher findings."
phase: 2
```
- [INV-G8] `test-quality` review dimension: no MEDIUM+ findings.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=test-quality and review the full diff. PASS only if no MEDIUM-or-higher findings."
phase: 2
```
- [INV-G9] `code-testability` review dimension: no MEDIUM+ findings.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=code-testability and review the full diff. PASS only if no MEDIUM-or-higher findings."
phase: 2
```
- [INV-G10] `docs` review dimension: no MEDIUM+ findings.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=docs and review the full diff, including set-up-database.rst and all changelog.rst edits. PASS only if no MEDIUM-or-higher findings."
phase: 2
```
- [INV-G11] `context-file-adherence` review dimension (CLAUDE.md conformance — newsfragment rules, exception-type rules, commit message style, PR-per-package split): no MEDIUM+ findings.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=context-file-adherence and review the full diff against this repo's CLAUDE.md, in particular: no new raise AirflowException usages; newsfragment only added for airflow-core's user-facing change, never for providers/*; commit messages use imperative mood with no Conventional Commits prefixes; the tracking-issue-plus-code-comment pattern was followed for the pgvector deferred bug. PASS only if no MEDIUM-or-higher findings."
phase: 2
```
- [INV-G12] Project static checks pass across every touched file.
```yaml
verify:
prompt: "Run `prek run --from-ref main --stage pre-commit` and `prek run --from-ref main --stage manual` from the repo root. PASS only if both complete with no failures on files touched by this manifest (airflow-core/src/airflow/configuration.py, airflow-core/src/airflow/settings.py, airflow-core/docs/howto/set-up-database.rst, providers/postgres/**, providers/celery/**, providers/google/**, providers/pgvector/**, dev/**). Report any failure verbatim."
phase: 3
```
- [INV-G13] `mypy-airflow-core` hook passes for the touched airflow-core files.
```yaml
verify:
prompt: "Run `prek run mypy-airflow-core --all-files` (or targeted equivalent) and confirm it passes for airflow-core/src/airflow/configuration.py and airflow-core/src/airflow/settings.py. PASS only if there are no new mypy errors introduced by this manifest's changes."
phase: 3
```
- [INV-G14] No new unconditional/module-top-level `psycopg2` (or `pgvector.psycopg2`) imports remain anywhere in the repo outside what this manifest deliberately keeps guarded.
```yaml
verify:
prompt: "Run `grep -rln '^import psycopg2\\|^from psycopg2\\|from pgvector.psycopg2' --include='*.py' .` from the repo root, excluding .venv/, /tests/, /test_ paths. For every remaining hit, confirm the import is lazy/guarded (inside a function or a try/except, not at module top level) or is a documented, deliberate exception. PASS only if no unconditional module-level psycopg2-family import remains anywhere in source code."
phase: 3
```
- [INV-G15] Newsfragment discipline: `airflow-core` gets exactly one newsfragment for this user-facing change; no newsfragment exists anywhere under `providers/*/newsfragments/` for this work (providers/postgres, providers/celery, providers/google, providers/pgvector all use their `docs/changelog.rst` directly instead, per CLAUDE.md).
```yaml
verify:
prompt: "Check that airflow-core/newsfragments/ contains exactly one new .rst file for this change (any of .improvement/.feature/.bugfix/.misc is acceptable — judge fit against the actual change) and that no new files were added under providers/postgres/newsfragments/, providers/celery/newsfragments/, providers/google/newsfragments/, or providers/pgvector/newsfragments/ (none of these directories should even exist as targets of new files). PASS only if both conditions hold."
phase: 3
```
## 4. Process Guidance
- [PG-1] Do not push any branch to `origin` or open any PR for any of the five packages touched here — that is a manual step the user takes after the deferred benchmark (D6) comes back from their other system and confirms it's safe to ship the default flip.
- [PG-2] Do not attempt to actually run the Postgres benchmark, spin up breeze/Docker Postgres, or push changes to the external `Dev-iL/pgbench` repository from this workspace — produce the spec and the `dev/` script only; the run itself happens elsewhere.
- [PG-3] No ADRs for any decision in this work. This repo's own precedent for this exact class of decision (the async driver default switch) was recorded via issue body + changelog note + PR description only — follow the same convention.
- [PG-4] Do not bump any `apache-airflow` / SQLAlchemy minimum-version floor anywhere, and do not introduce a gradual-deprecation warning period — this mirrors the async precedent's immediate-flip treatment exactly.
- [PG-5] Organize the work into five separate, self-contained diffs/branches mirroring the async precedent's core/provider split: one for `airflow-core` (D1), and one each for `providers/postgres` (D2), `providers/celery` (D3), `providers/google` (D4), and `providers/pgvector` (D5). Do not bundle unrelated packages' changes into a single commit.
- [PG-6] For `providers/pgvector` (D5): open a GitHub tracking issue for the separate pre-existing bug (the operator calls the psycopg2-specific `pgvector.psycopg2.register_vector` regardless of whether `PostgresHook` is actually using psycopg2 or psycopg3 — likely wrong in the common psycopg3 case) *before* finalizing that deliverable, per CLAUDE.md's "Tracking issues for deferred work" process — then add the full tracking-issue URL as a code comment at the guarded-import site. Do not use vague forward-looking language in its place.
- [PG-7] This environment may not be able to run breeze or a live Postgres instance. Run whatever pure-Python/unit-level tests are feasible via `uv run --project <PROJECT> pytest ...` (CLAUDE.md's documented breeze-unavailable fallback). Anything genuinely requiring a live Postgres instance (D6's actual benchmark execution, and any Postgres-backed integration tests touching D1–D5) is validated by the user separately, outside this manifest's automated verification.
## 5. Known Assumptions
- [ASM-1] Deliverable-to-package grouping (airflow-core / postgres / celery / google / pgvector — five groups) matches the user's "split core vs providers, like the async case" instruction; exact branch names are left to the implementer. | Default: five branches named by convention (e.g. `.../psycopg3-sync-core`, `.../psycopg3-sync-postgres-provider`, etc.) | Impact if wrong: cosmetic — nothing is pushed by this manifest, so regrouping later is free.
- [ASM-2] D6's benchmark artifacts are verified via code/structural review plus a non-Postgres smoke test, not live execution against real Postgres, because this workspace lacks the resources to run breeze/Postgres. | Default: AC-6.1 accepts a script proven correct by review + a SQLite/mocked-engine smoke run. | Impact if wrong: a latent bug in the `dev/` script surfaces only when the user runs it for real later — acceptable, since that run is the actual validation gate regardless.
- [ASM-3] The airflow-core newsfragment's exact classification (`.improvement.rst` vs `.misc.rst` vs another type) is left to the implementer's judgment at authoring time. | Default: `.improvement.rst`, since this changes default behavior without adding a new feature or fixing a defect. | Impact if wrong: trivial, a maintainer or reviewer will request a reclassification.
- [ASM-4] pgvector's fix (D5) is a lazy-import + clear-error guard, not a new hard/extra dependency declaration in `providers/pgvector/pyproject.toml`. | Default: guard via `try/except ImportError` inside `_register_vector`, raising `AirflowOptionalProviderFeatureException` with an actionable message if `pgvector.psycopg2` (or psycopg2 itself) is unavailable. | Impact if wrong: if reviewers prefer an explicit dependency declaration instead, that's a small, low-risk pyproject.toml addition during review.
- [ASM-5] `providers/celery`'s result-backend URL default change is treated as user-facing and gets a changelog "Breaking changes" note (same reasoning as the core change); `providers/google`'s adapter-registration fix is treated as a pure bugfix with no behavior change for working configurations and does **not** get a breaking-change note. | Impact if wrong: a maintainer may ask for the classification to be flipped for one or the other during review — low-cost to adjust.
## 6. Deliverables
### Deliverable 1: airflow-core — sync driver default & bulk-tuning correctness
**Acceptance Criteria:**
- [AC-1.1] `_upgrade_postgres_metastore_conn` (`airflow-core/src/airflow/configuration.py`) has its `good_scheme` constant changed from `"postgresql+psycopg2"` to `"postgresql+psycopg"`. The `bad_schemes` list is unchanged (`["postgres+psycopg2", "postgres", "postgresql"]`) — no new environment-probing/remap logic is added, and an explicit `postgresql+psycopg2://` URL is never rewritten. `airflow-core/tests/unit/core/test_configuration.py::test_upgrade_postgres_metastore_conn` is updated to assert the new target scheme, plus a new case confirming an explicit `postgresql+psycopg2://` value is left untouched.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=code-bugs and review airflow-core/src/airflow/configuration.py's _upgrade_postgres_metastore_conn and its test coverage in test_configuration.py. Confirm: good_scheme is now 'postgresql+psycopg'; bad_schemes list is unchanged; an explicit postgresql+psycopg2:// input is never rewritten by this function (verify via a passing test case, not just inspection). PASS only if all three hold and no LOW-or-higher findings."
phase: 2
```
- [AC-1.2] `prepare_engine_args` (`airflow-core/src/airflow/settings.py`) decides whether to apply psycopg2-specific `executemany_mode`/`executemany_batch_page_size` tuning based on the **actual configured** `SQL_ALCHEMY_CONN` scheme (e.g. does it start with `postgresql+psycopg2`), not merely whether the `psycopg` package is importable. New unit tests cover both a `postgresql+psycopg2://`-configured case (tuning applied) and a `postgresql+psycopg://`-configured case (tuning skipped), independent of what happens to be importable in the test environment.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=code-bugs and review airflow-core/src/airflow/settings.py's prepare_engine_args and its _USE_PSYCOPG3-derived logic. Confirm the psycopg2-specific executemany tuning is gated on the actually-configured SQL_ALCHEMY_CONN scheme, not on psycopg package importability, and that this is covered by a test that would fail against the old (importability-only) logic. PASS only if confirmed and no LOW-or-higher findings."
phase: 2
```
- [AC-1.3] Behavioral exercise: running configuration loading and `prepare_engine_args()` locally (no live Postgres required) against representative `SQL_ALCHEMY_CONN` values — bare `postgresql://`, explicit `postgresql+psycopg2://`, explicit `postgresql+psycopg://` — produces the correct resulting scheme and correct `engine_args` in each case.
```yaml
verify:
prompt: "Run a short Python snippet (via `uv run --project airflow-core python -c ...` or equivalent) that loads AirflowConfigParser with each of: bare 'postgresql://...', explicit 'postgresql+psycopg2://...', explicit 'postgresql+psycopg://...' as sql_alchemy_conn, and separately calls prepare_engine_args() against each resulting SQL_ALCHEMY_CONN value. Confirm: the bare/legacy case ends up as postgresql+psycopg; the explicit +psycopg2 case is untouched and gets executemany tuning; the explicit +psycopg case is untouched and does not get executemany tuning. PASS only if all three behave as described. Report BLOCKED if this requires infrastructure unavailable in this environment (it should not, since no live Postgres connection is needed for config-parsing/dialect-detection logic)."
phase: 2
```
- [AC-1.4] `airflow-core/docs/howto/set-up-database.rst` gains a sync-side section mirroring the existing async auto-derivation section (~lines 210–220), describing psycopg3 as the default sync driver and pointing to the providers/postgres `[psycopg2]` extra for anyone who needs the old driver.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=docs and review the new sync-driver section added to airflow-core/docs/howto/set-up-database.rst. Confirm it parallels the existing async section's structure and correctly documents: psycopg3 as the new default, how the [psycopg2] extra restores the old driver, and what happens if a config still has an explicit postgresql+psycopg2:// URL without psycopg2 installed. PASS only if no MEDIUM-or-higher findings."
phase: 2
```
- [AC-1.5] A newsfragment is added under `airflow-core/newsfragments/` describing this user-facing default change.
```yaml
verify:
prompt: "Confirm exactly one new .rst file exists under airflow-core/newsfragments/ describing the sync Postgres driver default change, following the existing newsfragment format/conventions in that directory. PASS if present and well-formed."
phase: 3
```
### Deliverable 2: providers/postgres — dependency flip & hook guard
**Acceptance Criteria:**
- [AC-2.1] `providers/postgres/pyproject.toml`: `psycopg2-binary` is removed from the main `dependencies` array and added as a new `"psycopg2"` optional-dependencies extra with its existing version constraints (`>=2.9.9` / `>=2.9.10` python-version split) preserved. The `apache-airflow>=2.11.0` floor and the `"sqlalchemy"` extra pin are unchanged. `prek run update-providers-dependencies --all-files` has been run and `uv.lock` regenerated/committed.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=contracts and review the diff to providers/postgres/pyproject.toml. Confirm: psycopg2-binary moved from dependencies to a new 'psycopg2' extra with its version constraints preserved; apache-airflow floor and 'sqlalchemy' extra unchanged; uv.lock reflects the change. PASS only if all hold and no LOW-or-higher findings."
phase: 2
```
- [AC-2.2] `providers/postgres/src/airflow/providers/postgres/hooks/postgres.py`'s module-level `from psycopg2 import connect as ppg2_connect` and `from psycopg2.extras import DictCursor, NamedTupleCursor, RealDictCursor, execute_values` are made lazy/guarded (moved inside the functions/branches that need them, following the existing try/except pattern already used for the psycopg3 import a few lines below). The module imports successfully even when psycopg2 is not installed. `USE_PSYCOPG3 = is_sqla2` dual-path logic is otherwise structurally unchanged.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=code-bugs and review providers/postgres/src/airflow/providers/postgres/hooks/postgres.py. Confirm the psycopg2 imports are no longer unconditional at module top level, the module can be imported with psycopg2 absent, and USE_PSYCOPG3's is_sqla2-based logic is preserved (not redesigned). PASS only if confirmed and no LOW-or-higher findings."
phase: 2
```
- [AC-2.3] Behavioral exercise: importing `airflow.providers.postgres.hooks.postgres` succeeds in an environment where `psycopg2` is uninstalled/mocked as absent (e.g. via `sys.modules` patching or a dedicated venv), and the psycopg2-specific code path (when reached) raises a clear error rather than an import-time crash.
```yaml
verify:
prompt: "Write and run a test (or use an existing one if added) that simulates psycopg2 being absent (e.g. patch sys.modules['psycopg2'] = None or use importlib machinery) and confirms `import airflow.providers.postgres.hooks.postgres` still succeeds. PASS only if import succeeds under simulated absence and any subsequent attempt to actually use the psycopg2 path fails with a clear, actionable error rather than a bare traceback at import time."
phase: 2
```
- [AC-2.4] `providers/postgres/docs/changelog.rst` gains a "Breaking changes" note for the sync driver default change and the new `[psycopg2]` extra, in the same style and location as the existing async breaking-change note (added by #69089).
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=docs and review the new entry in providers/postgres/docs/changelog.rst. Confirm it sits in the same 'Breaking changes' section as the existing async note, describes the sync default change to psycopg3 and the psycopg2-binary-to-extra move, and gives users the exact remediation ('install apache-airflow-providers-postgres[psycopg2] to keep using psycopg2'). PASS only if no MEDIUM-or-higher findings."
phase: 2
```
### Deliverable 3: providers/celery — result-backend URL scheme fix
**Acceptance Criteria:**
- [AC-3.1] `providers/celery/src/airflow/providers/celery/executors/default_celery.py`'s hardcoded `sql_alchemy_conn.replace("postgresql://", "postgresql+psycopg2://", 1)` is updated to target `"postgresql+psycopg://"` instead, matching the new core default, with the adjacent comment updated to reflect the new rationale.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=code-bugs and review providers/celery/src/airflow/providers/celery/executors/default_celery.py's result-backend URL derivation. Confirm the hardcoded replacement target changed from postgresql+psycopg2:// to postgresql+psycopg://, the explanatory comment was updated to match, and this is covered by an updated/new test asserting the new scheme in the derived result-backend URL. PASS only if confirmed and no LOW-or-higher findings."
phase: 2
```
- [AC-3.2] `providers/celery/src/airflow/providers/celery/get_provider_info.py`'s example connection string is updated from `db+postgresql+psycopg2://...` to `db+postgresql+psycopg://...`.
```yaml
verify:
prompt: "Confirm providers/celery/src/airflow/providers/celery/get_provider_info.py's example Postgres connection string uses postgresql+psycopg instead of postgresql+psycopg2. PASS if updated."
phase: 2
```
- [AC-3.3] Existing celery executor tests covering the result-backend URL derivation are updated to assert the new default scheme.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=test-quality and confirm the celery executor's tests for result-backend URL derivation (search providers/celery/tests or providers/celery/tests_unit for default_celery-related tests) assert postgresql+psycopg, not postgresql+psycopg2, as the derived scheme. PASS only if the tests would fail against the pre-change code and no MEDIUM-or-higher findings."
phase: 2
```
- [AC-3.4] `providers/celery/docs/changelog.rst` gains a note describing this default-URL-derivation change as user-facing (per ASM-5, treated as breaking-adjacent — same reasoning as the core change), unless the implementer's review of the actual celery provider release conventions concludes otherwise (documented if so).
```yaml
verify:
prompt: "Check providers/celery/docs/changelog.rst for a new note describing the result-backend URL default change (postgresql+psycopg2 -> postgresql+psycopg). If absent, confirm the implementer left a clear rationale (e.g. in the PR description or a commit note reachable from this diff) for why it was judged non-user-facing. PASS if either a changelog note exists or a documented rationale for omitting it is present."
phase: 2
```
### Deliverable 4: providers/google — bigquery_to_postgres adapter-registration guard
**Acceptance Criteria:**
- [AC-4.1] `providers/google/src/airflow/providers/google/cloud/transfers/bigquery_to_postgres.py`'s unconditional `from psycopg2.extensions import register_adapter` / `from psycopg2.extras import Json` and the `register_adapter(...)` calls in `postgres_hook` are made conditional — only imported/executed when the resolved `PostgresHook` is actually on its psycopg2 path (`not PostgresHook.USE_PSYCOPG3`) — so the module imports cleanly without psycopg2 installed and the (currently always-run, psycopg3-no-op) registration only happens when it's meaningful.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=code-bugs and review providers/google/src/airflow/providers/google/cloud/transfers/bigquery_to_postgres.py. Confirm: the psycopg2 imports are no longer unconditional at module top level; register_adapter is only invoked when PostgresHook is actually on the psycopg2 path; the module imports successfully with psycopg2 absent. PASS only if confirmed and no LOW-or-higher findings."
phase: 2
```
- [AC-4.2] Existing `bigquery_to_postgres` tests still pass, and a new/updated test confirms the module imports successfully when psycopg2 is simulated as absent.
```yaml
verify:
prompt: "Run providers/google's bigquery_to_postgres test suite (e.g. `uv run --project providers/google pytest providers/google/tests/.../test_bigquery_to_postgres.py -xvs` or breeze equivalent) and confirm all pass, including a case that simulates psycopg2 being absent and confirms successful import. PASS only if all tests pass."
phase: 3
```
### Deliverable 5: providers/pgvector — import-guard fix + tracking issue for the deeper bug
**Acceptance Criteria:**
- [AC-5.1] `providers/pgvector/src/airflow/providers/pgvector/operators/pgvector.py`'s unconditional `from pgvector.psycopg2 import register_vector` is moved inside `_register_vector()` (or equivalent lazy point), guarded with a clear, actionable error (e.g. `AirflowOptionalProviderFeatureException`) if `pgvector.psycopg2`/psycopg2 is unavailable, so the module imports cleanly without psycopg2 installed.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=code-bugs and review providers/pgvector/src/airflow/providers/pgvector/operators/pgvector.py. Confirm the pgvector.psycopg2 import is no longer unconditional at module top level, the module imports successfully with psycopg2 absent, and the guard raises a clear actionable error (not a bare ImportError) when psycopg2/pgvector.psycopg2 is genuinely needed but missing. PASS only if confirmed and no LOW-or-higher findings."
phase: 2
```
- [AC-5.2] A GitHub tracking issue exists (opened per CLAUDE.md's "Tracking issues for deferred work" process) documenting the separate pre-existing bug — this operator calls the psycopg2-specific `register_vector` regardless of whether the underlying `PostgresHook` connection is actually psycopg2 or psycopg3 — and its full URL is added as a code comment at the guard site in `pgvector.py`.
```yaml
verify:
prompt: "Confirm a GitHub issue exists in apache/airflow describing the pgvector operator's psycopg2-vs-psycopg3 connection-type mismatch bug, and that providers/pgvector/src/airflow/providers/pgvector/operators/pgvector.py contains a code comment with that issue's full URL (not a bare #NNNNN reference) at the guarded-import site. PASS only if both exist."
phase: 3
```
### Deliverable 6: Bulk-write validation artifacts (benchmark, execution deferred)
**Acceptance Criteria:**
- [AC-6.1] A runnable script exists under `dev/` that connects via both `postgresql+psycopg2://` and `postgresql+psycopg://`, performs a bulk INSERT through SQLAlchemy's `insertmanyvalues` path at `insertmanyvalues_page_size=10000` (matching `prepare_engine_args`'s configured value), and reports comparable timing/throughput for both drivers. Verified via code review (this environment cannot run it against live Postgres) plus a smoke check that it fails clearly and immediately if no Postgres connection is reachable, rather than hanging or crashing unhelpfully.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=code-bugs and review the new dev/ benchmark script. Confirm it: exercises SQLAlchemy's real insertmanyvalues path (not a hand-rolled loop) at page_size=10000 matching airflow-core/src/airflow/settings.py's prepare_engine_args configuration; runs the same operation against both postgresql+psycopg2:// and postgresql+psycopg:// for a fair comparison; fails with a clear, immediate, actionable message if it cannot reach a Postgres instance (do not require an actual live run in this environment — that is deferred). PASS only if confirmed and no LOW-or-higher findings."
phase: 2
```
- [AC-6.2] A self-contained written spec (e.g. `dev/benchmark-postgres-sync-driver-spec.md`) precisely describes the batch INSERT/COPY comparison scenario to add to the external `Dev-iL/pgbench` repository, detailed enough to implement without further context from this session.
```yaml
verify:
prompt: "Activate the manifest-dev:review-code skill with dimension=docs and review the written benchmark spec. Confirm it is self-contained: names the exact scenario (batch INSERT and COPY, sync psycopg2 vs sync psycopg3), the methodology to mirror from the existing async psycopg3-vs-asyncpg comparison cited in apache/airflow#67801, and what output/metrics would constitute a clear go/no-go signal. PASS only if a reader unfamiliar with this session could implement the benchmark from the spec alone, and no MEDIUM-or-higher findings."
phase: 2
```
~~~
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org