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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
