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/07/06 12:57:20 UTC
[PR] airflow-core: make psycopg (v3) the default synchronous Postgres driver [airflow]
Dev-iL opened a new pull request, #69469:
URL: https://github.com/apache/airflow/pull/69469
<!-- SPDX-License-Identifier: Apache-2.0
https://www.apache.org/licenses/LICENSE-2.0 -->
<!--
Thank you for contributing!
Please provide above a brief description of the changes made in this pull request.
Write a good git commit message following this guide: http://chris.beams.io/posts/git-commit/
Please make sure that your code changes are covered with tests.
And in case of new features or big changes remember to adjust the documentation.
For user-facing UI changes, please attach before/after screenshots (or a short
screen recording) so reviewers can assess the visual impact.
Feel free to ping (in general) for the review if you do not see reaction for a few days
(72 Hours is the minimum reaction time you can expect from volunteers) - we sometimes miss notifications.
In case of an existing issue, reference it using one of the following:
* closes: #ISSUE
* related: #ISSUE
-->
related:
- #68453
- #68496
Mirrors the async default switch: a bare `postgresql://` or legacy `postgres://` / `postgres+psycopg2://` `sql_alchemy_conn` is now rewritten to `postgresql+psycopg://` instead of `postgresql+psycopg2://`. An explicit `postgresql+psycopg2://` URL is never rewritten and keeps working as long as `apache-airflow-providers-postgres[psycopg2]` is installed.
**This PR touches core only - there will be separate PRs for providers.**
### What changed
- `_upgrade_postgres_metastore_conn` (`airflow-core/src/airflow/configuration.py`): `good_scheme` changed from `postgresql+psycopg2` to `postgresql+psycopg`. `bad_schemes` is unchanged.
- `prepare_engine_args` (`airflow-core/src/airflow/settings.py`): the psycopg2-specific `executemany_mode`/`executemany_batch_page_size` tuning now follows the **actually-configured** `SQL_ALCHEMY_CONN` scheme, not whether the `psycopg` package happens to be importable.
- New sync-driver section in `airflow-core/docs/howto/set-up-database.rst`, mirroring the existing async section, documenting the new default and how to restore psycopg2 via the provider's `[psycopg2]` extra.
- Updated remaining hardcoded `postgresql+psycopg2://` example connection strings across docs and reference deployment configs (Docker Compose quick-start, Helm chart values, CI backend config, task-sdk integration tests, a few executor/logging docs) that would otherwise break for anyone following them as-is now that the postgres provider no longer bundles psycopg2-binary by default.
- Newsfragment: `airflow-core/newsfragments/68453.improvement.rst`.
### Test plan
- `airflow-core/tests/unit/core/test_configuration.py::test_upgrade_postgres_metastore_conn` updated to assert `postgresql+psycopg`, plus a case confirming an explicit `postgresql+psycopg2://` value is left untouched.
- `airflow-core/tests/unit/core/test_sqlalchemy_config.py`: new parametrized test covering all four combinations of `{postgresql+psycopg2, postgresql+psycopg} × {psycopg3 available, not available}`, confirming the executemany tuning follows the configured scheme rather than package importability.
- Verified behaviorally against bare/explicit-psycopg2/explicit-psycopg `sql_alchemy_conn` values with no live Postgres required.
---
##### Was generative AI tooling used to co-author this PR?
- [X] Yes — Claude Code (Sonnet 5)
Generated-by: Claude Code (Sonnet 5) following [the guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions)
---
##### 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 Sonnet 5 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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] Core: Make psycopg (v3) the default synchronous Postgres driver [airflow]
Posted by "Dev-iL (via GitHub)" <gi...@apache.org>.
Dev-iL commented on PR #69469:
URL: https://github.com/apache/airflow/pull/69469#issuecomment-4977701286
@Lee-W @kacpermuda @FrankYang0529 Could you please see if the openlineage changes are acceptable?
--
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: [PR] Core: Make psycopg (v3) the default synchronous Postgres driver [airflow]
Posted by "Miretpl (via GitHub)" <gi...@apache.org>.
Miretpl commented on PR #69469:
URL: https://github.com/apache/airflow/pull/69469#issuecomment-4973976961
@Dev-iL could you take a look at the failed tests? they look related to the changes in the PR
--
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: [PR] Core: Make psycopg (v3) the default synchronous Postgres driver [airflow]
Posted by "Dev-iL (via GitHub)" <gi...@apache.org>.
Dev-iL commented on code in PR #69469:
URL: https://github.com/apache/airflow/pull/69469#discussion_r3534623598
##########
airflow-core/src/airflow/migrations/versions/0094_3_2_0_replace_deadline_inline_callback_with_fkey.py:
##########
Review Comment:
**Why editing two already-released migrations is safe and necessary:** both fixes are purely driver-rendering workarounds, not behavior changes — verified by running the exact SQLAlchemy constructs used in each migration against a live Postgres under both drivers. `literal(0)` vs. the original `literal("0")` persists the identical `integer` value `0` on both psycopg2 and psycopg3; `CAST(:x AS text)` vs. the original untyped bind param produces the identical `json_typeof(...) == "string"` value on both drivers (Postgres already defaulted the untyped psycopg2 param to `text` here, so the explicit cast just makes psycopg3 resolve it the same way psycopg2 always did). Nothing downstream — the `dag.max_consecutive_failed_dag_runs` ORM column (`Mapped[int]`) or the `__classname__`/`prefix` JSON fields consumed via `import_string()`/deadline callback deserialization — observes any difference. We also checked for precedent: neither #68314 (explicit psycopg2 scheme) nor #69089/#684
96 (async driver default) ever touched `migrations/versions/` at all, because neither ever ran the *synchronous* migration chain under psycopg3 — this PR is the first change in the repo's history to do that, so there's no established "never edit released migrations" pattern being broken here, just new territory the project hasn't hit before.
A corrective migration added at a later version was considered instead of editing 0017/0094 directly, but doesn't work: Alembic runs the chain sequentially, so any user upgrading from a pre-0017/pre-0094 revision on psycopg3 would still crash *at* 0017/0094 before ever reaching a later corrective migration — the fix has to live at the revision that actually executes the problematic SQL.
--
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: [PR] Core: Make psycopg (v3) the default synchronous Postgres driver [airflow]
Posted by "Dev-iL (via GitHub)" <gi...@apache.org>.
Dev-iL commented on code in PR #69469:
URL: https://github.com/apache/airflow/pull/69469#discussion_r3534598423
##########
airflow-core/src/airflow/migrations/versions/0017_2_9_2_fix_inconsistency_between_ORM_and_migration_files.py:
##########
@@ -110,7 +110,7 @@ def upgrade():
batch_op.create_unique_constraint(batch_op.f("connection_conn_id_uq"), ["conn_id"])
max_cons = sa.table("dag", sa.column("max_consecutive_failed_dag_runs"))
- op.execute(max_cons.update().values(max_consecutive_failed_dag_runs=literal("0")))
+ op.execute(max_cons.update().values(max_consecutive_failed_dag_runs=literal(0)))
Review Comment:
It is safe to modify the old migration because a str literal makes SQLAlchemy emit an explicit `::VARCHAR` cast, which psycopg (v3) enforces literally against this integer column (DatatypeMismatch). psycopg2 ignores the cast and lets Postgres coerce the text implicitly, so both drivers persist the same value either way.
Re other backends - compiling that exact statement against all three SQLAlchemy dialects shows only the `postgresql+psycopg` dialect ever adds a cast — `UPDATE dag SET max_consecutive_failed_dag_runs=%(max_consecutive_failed_dag_runs)s` for plain `postgresql`, `=%s` for `mysql`, `=?` for `sqlite`, none of them casting.
--
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: [PR] Core: Make psycopg (v3) the default synchronous Postgres driver [airflow]
Posted by "Miretpl (via GitHub)" <gi...@apache.org>.
Miretpl commented on code in PR #69469:
URL: https://github.com/apache/airflow/pull/69469#discussion_r3580238208
##########
chart/values.yaml:
##########
@@ -442,12 +442,12 @@ data:
# connection: base64_encoded_connection_string
#
# The 'connection' key is base64-encoded SQLAlchemy connection string, e.g.:
- # postgresql+psycopg2://airflow:password@postgres/airflow
+ # postgresql+psycopg://airflow:password@postgres/airflow
metadataSecretName: ~
# If not set, falls back to metadataSecretName. The secret must contain 'connection' key which is
# a base64-encoded connection string, e.g.:
- # postgresql+psycopg2://user:password@host/db
+ # postgresql+psycopg://user:password@host/db
Review Comment:
Helm Chart on main supports Airflow > 3.1 version (as far as I remember, it will not work on 3.1) and 1.2x line supports >=2.11. Could you adjust the comment here so that it shows which version supports what?
It would also be good to do a manual backport of this file to the `chart/v1-2x-test` branch
--
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: [PR] Core: Make psycopg (v3) the default synchronous Postgres driver [airflow]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #69469:
URL: https://github.com/apache/airflow/pull/69469#issuecomment-5049732081
### Backport failed to create: v3-3-test. View the failure log <a href='https://github.com/apache/airflow/actions/runs/29945408732'> Run details </a>
Note: As of [Merging PRs targeted for Airflow 3.X](https://github.com/apache/airflow/blob/main/dev/README_AIRFLOW3_DEV.md#merging-prs-targeted-for-airflow-3x)
the committer who merges the PR is responsible for backporting the PRs that are bug fixes (generally speaking) to the maintenance branches.
In matter of doubt please ask in [#release-management](https://apache-airflow.slack.com/archives/C03G9H97MM2) Slack channel.
<table>
<tr>
<th>Status</th>
<th>Branch</th>
<th>Result</th>
</tr>
<tr>
<td>❌</td>
<td>v3-3-test</td>
<td><a href="https://github.com/apache/airflow/commit/45a93a1e1fbadcdf46648fe7658b61ea55fff9d3"><img src='https://img.shields.io/badge/Commit-45a93a1-red' alt='Commit Link'></a></td>
</tr>
</table>
You can attempt to backport this manually by running:
```bash
cherry_picker 45a93a1 v3-3-test
```
This should apply the commit to the v3-3-test branch and leave the commit in conflict state marking
the files that need manual conflict resolution.
After you have resolved the conflicts, you can continue the backport process by running:
```bash
cherry_picker --continue
```
If you don't have cherry-picker installed, see the [installation guide](https://github.com/apache/airflow/blob/main/dev/README.md#how-to-backport-pr-with-cherry-picker-cli).
--
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: [PR] Core: Make psycopg (v3) the default synchronous Postgres driver [airflow]
Posted by "shahar1 (via GitHub)" <gi...@apache.org>.
shahar1 merged PR #69469:
URL: https://github.com/apache/airflow/pull/69469
--
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: [PR] Core: Make psycopg (v3) the default synchronous Postgres driver [airflow]
Posted by "Dev-iL (via GitHub)" <gi...@apache.org>.
Dev-iL commented on code in PR #69469:
URL: https://github.com/apache/airflow/pull/69469#discussion_r3581567251
##########
chart/values.yaml:
##########
@@ -442,12 +442,12 @@ data:
# connection: base64_encoded_connection_string
#
# The 'connection' key is base64-encoded SQLAlchemy connection string, e.g.:
- # postgresql+psycopg2://airflow:password@postgres/airflow
+ # postgresql+psycopg://airflow:password@postgres/airflow
metadataSecretName: ~
# If not set, falls back to metadataSecretName. The secret must contain 'connection' key which is
# a base64-encoded connection string, e.g.:
- # postgresql+psycopg2://user:password@host/db
+ # postgresql+psycopg://user:password@host/db
Review Comment:
Thanks! Fixed to show both drivers with the actual cutover:
```yaml
# postgresql+psycopg://airflow:password@postgres/airflow (Airflow >=3.4.0)
# postgresql+psycopg2://airflow:password@postgres/airflow (Airflow <3.4.0, or with the [psycopg2] extra)
```
(and the analogous pair for `resultBackendSecretName`).
Re backport - makes sense. Let's follow up with a separate PR once this merges.
--
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: [PR] Core: Make psycopg (v3) the default synchronous Postgres driver [airflow]
Posted by "Dev-iL (via GitHub)" <gi...@apache.org>.
Dev-iL commented on code in PR #69469:
URL: https://github.com/apache/airflow/pull/69469#discussion_r3534623598
##########
airflow-core/src/airflow/migrations/versions/0094_3_2_0_replace_deadline_inline_callback_with_fkey.py:
##########
Review Comment:
**Why editing two already-released migrations is safe and necessary:** both fixes are purely driver-rendering workarounds, not behavior changes — verified by running the exact SQLAlchemy constructs used in each migration against a live Postgres under both drivers. `literal(0)` vs. the original `literal("0")` persists the identical `integer` value `0` on both psycopg2 and psycopg3; `CAST(:x AS text)` vs. the original untyped bind param produces the identical `json_typeof(...) == "string"` value on both drivers (Postgres already defaulted the untyped psycopg2 param to `text` here, so the explicit cast just makes psycopg3 resolve it the same way psycopg2 always did). Nothing downstream — the `dag.max_consecutive_failed_dag_runs` ORM column (`Mapped[int]`) or the `__classname__`/`prefix` JSON fields consumed via `import_string()`/deadline callback deserialization — observes any difference. We also checked for precedent: neither #68314 (explicit psycopg2 scheme) nor #69089/#684
96 (async driver default) ever touched `migrations/versions/` at all, because neither ever ran the *synchronous* migration chain under psycopg3 — this PR is the first change in the repo's history to do that, so there's no established "never edit released migrations" pattern being broken here, just new territory the project hasn't hit before.
A corrective migration added at a later version was considered instead of editing 0017/0094 directly, but doesn't work: Alembic runs the chain sequentially, so any user upgrading from a pre-0017/pre-0094 revision on psycopg3 would still crash *at* 0017/0094 before ever reaching a later corrective migration — the fix has to live at the revision that actually executes the problematic SQL.
--
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: [PR] Core: Make psycopg (v3) the default synchronous Postgres driver [airflow]
Posted by "Dev-iL (via GitHub)" <gi...@apache.org>.
Dev-iL commented on code in PR #69469:
URL: https://github.com/apache/airflow/pull/69469#discussion_r3534598423
##########
airflow-core/src/airflow/migrations/versions/0017_2_9_2_fix_inconsistency_between_ORM_and_migration_files.py:
##########
@@ -110,7 +110,7 @@ def upgrade():
batch_op.create_unique_constraint(batch_op.f("connection_conn_id_uq"), ["conn_id"])
max_cons = sa.table("dag", sa.column("max_consecutive_failed_dag_runs"))
- op.execute(max_cons.update().values(max_consecutive_failed_dag_runs=literal("0")))
+ op.execute(max_cons.update().values(max_consecutive_failed_dag_runs=literal(0)))
Review Comment:
It is safe to modify the old migration because a str literal makes SQLAlchemy emit an explicit `::VARCHAR` cast, which psycopg (v3) enforces literally against this integer column (DatatypeMismatch). psycopg2 ignores the cast and lets Postgres coerce the text implicitly, so both drivers persist the same value either way.
--
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: [PR] Core: Make psycopg (v3) the default synchronous Postgres driver [airflow]
Posted by "kacpermuda (via GitHub)" <gi...@apache.org>.
kacpermuda commented on PR #69469:
URL: https://github.com/apache/airflow/pull/69469#issuecomment-4979369296
Looks ok, but maybe we can just install postgres provider for these OL compat tests instead? AFAIU, it will solve the problem? They contain the minimum number of providers already defined in `OPENLINEAGE_E2E_COMPAT_PROVIDERS` in `dev/breeze/src/airflow_breeze/commands/testing_commands.py:1441`. I don't think it will break anything, and will definitely make everything simpler.
--
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: [PR] Core: Make psycopg (v3) the default synchronous Postgres driver [airflow]
Posted by "Dev-iL (via GitHub)" <gi...@apache.org>.
Dev-iL commented on code in PR #69469:
URL: https://github.com/apache/airflow/pull/69469#discussion_r3581567251
##########
chart/values.yaml:
##########
@@ -442,12 +442,12 @@ data:
# connection: base64_encoded_connection_string
#
# The 'connection' key is base64-encoded SQLAlchemy connection string, e.g.:
- # postgresql+psycopg2://airflow:password@postgres/airflow
+ # postgresql+psycopg://airflow:password@postgres/airflow
metadataSecretName: ~
# If not set, falls back to metadataSecretName. The secret must contain 'connection' key which is
# a base64-encoded connection string, e.g.:
- # postgresql+psycopg2://user:password@host/db
+ # postgresql+psycopg://user:password@host/db
Review Comment:
Thanks! Fixed to show both drivers with the actual cutover:
```yaml
# postgresql+psycopg://airflow:password@postgres/airflow (Airflow >=3.4.0)
# postgresql+psycopg2://airflow:password@postgres/airflow (Airflow <3.4.0, or with the [psycopg2] extra)
```
(and the analogous pair for `resultBackendSecretName`).
Re backport to `chart/v1-2x-test` - makes sense. Let's follow up with a separate PR against `chart/v1-2x-test` once this merges.
--
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: [PR] Core: Make psycopg (v3) the default synchronous Postgres driver [airflow]
Posted by "Dev-iL (via GitHub)" <gi...@apache.org>.
Dev-iL commented on PR #69469:
URL: https://github.com/apache/airflow/pull/69469#issuecomment-4984001140
> Looks ok, but maybe we can just install postgres provider for these OL compat tests instead? AFAIU, it will solve the problem? They contain the minimum number of providers already defined in `OPENLINEAGE_E2E_COMPAT_PROVIDERS` in `dev/breeze/src/airflow_breeze/commands/testing_commands.py:1441`. I don't think it will break anything, and will definitely make everything simpler.
Tried this - looks like it's not a complete solution. postgres isn't actually imported by any of the OL system-test DAGs (only openlineage/standard/common.compat are), so adding it only mattered as a side channel to get psycopg (v3) physically installed. That part worked for 3.1.8/3.2.2/3.3.0 (which already pin SQLAlchemy 2.0.x in their constraints), but 3.0.6 fails since its pyproject.toml hard-caps `sqlalchemy[asyncio]>=1.4.49,<2.0` (which doesn't have the `+psycopg` dialect) - so no package we install changes that.
I reverted back to the explicit-psycopg2 compose override, which is version-agnostic and already proven on CI.
--
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