This is an automated email from the ASF dual-hosted git repository.
kaxil pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/airflow.git
The following commit(s) were added to refs/heads/main by this push:
new 9fb5deaa209 Fix driverless PostgreSQL Celery `result_backend`
requiring psycopg2 (#77166)
9fb5deaa209 is described below
commit 9fb5deaa2093f7d3e3d5d55bb70e1b2c4c9815f2
Author: Kaxil Naik <[email protected]>
AuthorDate: Mon Nov 16 18:37:41 2026 +0000
Fix driverless PostgreSQL Celery `result_backend` requiring psycopg2
(#77166)
An explicit [celery] result_backend such as db+postgresql://... was passed
to
Celery unchanged, so SQLAlchemy 2.0 resolved it to psycopg2. Without
psycopg2
installed, the scheduler and Celery workers crashed with
ModuleNotFoundError.
A result_backend derived from sql_alchemy_conn already got Airflow's default
sync driver; the explicit setting now gets the same one.
---
providers/celery/docs/changelog.rst | 7 +++
providers/celery/provider.yaml | 4 +-
.../providers/celery/executors/default_celery.py | 23 ++++++---
.../airflow/providers/celery/get_provider_info.py | 2 +-
.../unit/celery/executors/test_celery_executor.py | 58 +++++++++++++---------
.../unit/celery/executors/test_default_celery.py | 15 +++---
6 files changed, 67 insertions(+), 42 deletions(-)
diff --git a/providers/celery/docs/changelog.rst
b/providers/celery/docs/changelog.rst
index 7a24187910f..747a538ec10 100644
--- a/providers/celery/docs/changelog.rst
+++ b/providers/celery/docs/changelog.rst
@@ -27,6 +27,13 @@
Changelog
---------
+.. note::
+ An explicit ``[celery] result_backend`` with a driverless
``db+postgresql://`` URL now gets the same
+ driver as one derived from ``sql_alchemy_conn``:
``db+postgresql+psycopg://...`` when psycopg (v3) and
+ SQLAlchemy 2.0+ are installed, ``db+postgresql+psycopg2://...`` otherwise.
Before, the URL went to
+ SQLAlchemy unchanged, which picks psycopg2 on SQLAlchemy 2.0 and fails
when psycopg2 is not installed.
+ To keep psycopg2 with both drivers installed, set
``db+postgresql+psycopg2://`` explicitly.
+
4.0.0
.....
diff --git a/providers/celery/provider.yaml b/providers/celery/provider.yaml
index fc82ff3b1b2..22e526ba3ca 100644
--- a/providers/celery/provider.yaml
+++ b/providers/celery/provider.yaml
@@ -268,7 +268,9 @@ config:
https://docs.celeryq.dev/en/latest/userguide/configuration.html#task-result-backend-settings
The psycopg2 driver in the example below works on every supported
Airflow version.
Set db+postgresql+psycopg:// only on Airflow 3.2.0 and later -
earlier releases do not
- guarantee SQLAlchemy 2.0, which is where the psycopg (v3) dialect
lives
+ guarantee SQLAlchemy 2.0, which is where the psycopg (v3) dialect
lives.
+ A driverless db+postgresql:// URL, set here or derived from
sql_alchemy_conn, uses psycopg (v3)
+ when psycopg and SQLAlchemy 2.0+ are installed and psycopg2
otherwise; name the driver to choose one
version_added: ~
type: string
sensitive: true
diff --git
a/providers/celery/src/airflow/providers/celery/executors/default_celery.py
b/providers/celery/src/airflow/providers/celery/executors/default_celery.py
index 76362f32ac7..71e21fd8706 100644
--- a/providers/celery/src/airflow/providers/celery/executors/default_celery.py
+++ b/providers/celery/src/airflow/providers/celery/executors/default_celery.py
@@ -117,6 +117,19 @@ def _broker_transport_options(broker_url: str, conf:
AirflowSDKConfigParser | An
return broker_transport_options
+def _apply_default_postgres_driver(result_backend: str) -> str:
+ """
+ Give a driverless ``db+postgresql://`` result backend Airflow's default
sync Postgres driver.
+
+ SQLAlchemy before 2.1 picks psycopg2 for a driverless URL, while Airflow
defaults to psycopg (v3);
+ fall back to psycopg2 without psycopg/SQLAlchemy 2.0, matching
PostgresHook's USE_PSYCOPG3 gating.
+ """
+ if not result_backend.startswith("db+postgresql://"):
+ return result_backend
+ driver = "psycopg" if _USE_PSYCOPG3 else "psycopg2"
+ return result_backend.replace("db+postgresql://",
f"db+postgresql+{driver}://", 1)
+
+
def _get_database_engine_options(result_backend: str, options: dict[str, Any])
-> dict[str, Any]:
if not result_backend.startswith("db+"):
return options
@@ -157,14 +170,8 @@ def get_default_celery_config(team_conf:
AirflowSDKConfigParser | Any) -> dict[s
result_backend = team_conf.get_mandatory_value("celery",
"RESULT_BACKEND")
else:
log.debug("Value for celery result_backend not found. Using
sql_alchemy_conn with db+ prefix.")
- sql_alchemy_conn = team_conf.get("database", "SQL_ALCHEMY_CONN")
- # Airflow's sync metadata-database driver default is psycopg (v3);
mirror that explicitly
- # for driverless PostgreSQL URLs instead of relying on SQLAlchemy's
own default. Fall back to
- # psycopg2 when psycopg/SQLAlchemy 2.0 aren't both available, matching
PostgresHook's own
- # USE_PSYCOPG3 gating so this doesn't break environments still on the
older combination.
- target_scheme = "postgresql+psycopg://" if _USE_PSYCOPG3 else
"postgresql+psycopg2://"
- sql_alchemy_conn = sql_alchemy_conn.replace("postgresql://",
target_scheme, 1)
- result_backend = f"db+{sql_alchemy_conn}"
+ result_backend = f"db+{team_conf.get('database', 'SQL_ALCHEMY_CONN')}"
+ result_backend = _apply_default_postgres_driver(result_backend)
# Handle result backend transport options (for Redis Sentinel support)
result_backend_transport_options: dict = (
diff --git a/providers/celery/src/airflow/providers/celery/get_provider_info.py
b/providers/celery/src/airflow/providers/celery/get_provider_info.py
index cbb914e55f4..13370bf00f9 100644
--- a/providers/celery/src/airflow/providers/celery/get_provider_info.py
+++ b/providers/celery/src/airflow/providers/celery/get_provider_info.py
@@ -133,7 +133,7 @@ def get_provider_info():
"default": "redis://redis:6379/0",
},
"result_backend": {
- "description": "The Celery result_backend. When a job
finishes, it needs to update the\nmetadata of the job. Therefore it will post a
message on a message bus,\nor insert it into a database (depending of the
backend)\nThis status is used by the scheduler to update the state of the
task\nThe use of a database is highly recommended\nWhen not specified,
sql_alchemy_conn with a db+ scheme prefix will be
used\nhttps://docs.celeryq.dev/en/latest/userguide/configuration. [...]
+ "description": "The Celery result_backend. When a job
finishes, it needs to update the\nmetadata of the job. Therefore it will post a
message on a message bus,\nor insert it into a database (depending of the
backend)\nThis status is used by the scheduler to update the state of the
task\nThe use of a database is highly recommended\nWhen not specified,
sql_alchemy_conn with a db+ scheme prefix will be
used\nhttps://docs.celeryq.dev/en/latest/userguide/configuration. [...]
"version_added": None,
"type": "string",
"sensitive": True,
diff --git
a/providers/celery/tests/unit/celery/executors/test_celery_executor.py
b/providers/celery/tests/unit/celery/executors/test_celery_executor.py
index dfbb38eefb4..980cfc43825 100644
--- a/providers/celery/tests/unit/celery/executors/test_celery_executor.py
+++ b/providers/celery/tests/unit/celery/executors/test_celery_executor.py
@@ -961,30 +961,42 @@ def
test_result_backend_transport_options_with_multiple_options():
assert result_backend_opts["master_name"] == "mymaster"
-@conf_vars(
- {
- ("celery", "result_backend"): None,
- ("database", "sql_alchemy_conn"): "postgresql://user:pass@host/db",
- }
-)
-def
test_result_backend_derived_from_sql_alchemy_conn_uses_psycopg(monkeypatch):
- """A driverless sql_alchemy_conn must derive a psycopg (v3)
result_backend, not psycopg2."""
- monkeypatch.setattr(default_celery, "_USE_PSYCOPG3", True)
- config = default_celery.get_default_celery_config(conf)
- assert config["result_backend"] ==
"db+postgresql+psycopg://user:pass@host/db"
-
-
-@conf_vars(
- {
- ("celery", "result_backend"): None,
- ("database", "sql_alchemy_conn"): "postgresql://user:pass@host/db",
- }
[email protected](
+ ("result_backend", "use_psycopg3", "expected"),
+ [
+ pytest.param(None, True, "db+postgresql+psycopg://user:pass@host/db",
id="derived"),
+ pytest.param(None, False,
"db+postgresql+psycopg2://user:pass@host/db", id="derived-no-psycopg3"),
+ pytest.param(
+ "db+postgresql://user:pass@host/db",
+ True,
+ "db+postgresql+psycopg://user:pass@host/db",
+ id="explicit-driverless",
+ ),
+ pytest.param(
+ "db+postgresql://user:pass@host/db",
+ False,
+ "db+postgresql+psycopg2://user:pass@host/db",
+ id="explicit-driverless-no-psycopg3",
+ ),
+ pytest.param(
+ "db+postgresql+psycopg2://user:pass@host/db",
+ True,
+ "db+postgresql+psycopg2://user:pass@host/db",
+ id="explicit-driver-kept",
+ ),
+ ],
)
-def test_result_backend_falls_back_to_psycopg2_without_psycopg3(monkeypatch):
- """Without psycopg/SQLAlchemy 2.0 available, the derivation must fall back
to psycopg2."""
- monkeypatch.setattr(default_celery, "_USE_PSYCOPG3", False)
- config = default_celery.get_default_celery_config(conf)
- assert config["result_backend"] ==
"db+postgresql+psycopg2://user:pass@host/db"
+def test_result_backend_postgres_driver(monkeypatch, result_backend,
use_psycopg3, expected):
+ """A driverless PostgreSQL result_backend gets Airflow's default sync
driver; a named driver is kept."""
+ monkeypatch.setattr(default_celery, "_USE_PSYCOPG3", use_psycopg3)
+ with conf_vars(
+ {
+ ("celery", "result_backend"): result_backend,
+ ("database", "sql_alchemy_conn"): "postgresql://user:pass@host/db",
+ }
+ ):
+ config = default_celery.get_default_celery_config(conf)
+ assert config["result_backend"] == expected
@conf_vars({("celery_result_backend_transport_options", "sentinel_kwargs"):
"invalid_json"})
diff --git
a/providers/celery/tests/unit/celery/executors/test_default_celery.py
b/providers/celery/tests/unit/celery/executors/test_default_celery.py
index 77aab202f1c..50d1bcd485d 100644
--- a/providers/celery/tests/unit/celery/executors/test_default_celery.py
+++ b/providers/celery/tests/unit/celery/executors/test_default_celery.py
@@ -22,7 +22,6 @@ import pytest
from celery import current_app
from celery.contrib.testing.app import setup_default_app
from sqlalchemy import event
-from sqlalchemy.engine import make_url
from airflow.providers.celery.executors import default_celery
from airflow.providers.celery.executors.celery_executor_utils import
create_celery_app
@@ -65,12 +64,12 @@ def get_backend_connect_kwargs(backend):
@pytest.mark.usefixtures("restore_current_celery_app")
@pytest.mark.parametrize(
- "result_backend",
+ ("result_backend", "expected_driver"),
[
- pytest.param(None, id="derived-from-metadata"),
- pytest.param("db+postgresql+psycopg://user:pass@host/db",
id="explicit-psycopg"),
- pytest.param("db+postgresql://user:pass@host/db",
id="sqlalchemy-default-driver"),
- pytest.param("db+postgresql+psycopg2://user:pass@host/db",
id="explicit-psycopg2"),
+ pytest.param(None, "psycopg", id="derived-from-metadata"),
+ pytest.param("db+postgresql+psycopg://user:pass@host/db", "psycopg",
id="explicit-psycopg"),
+ pytest.param("db+postgresql://user:pass@host/db", "psycopg",
id="explicit-driverless"),
+ pytest.param("db+postgresql+psycopg2://user:pass@host/db", "psycopg2",
id="explicit-psycopg2"),
],
)
@pytest.mark.parametrize(
@@ -80,9 +79,7 @@ def get_backend_connect_kwargs(backend):
pytest.param({"pool_recycle": 1800, "connect_args":
{"connect_timeout": 7}}, id="custom-options"),
],
)
-def test_result_backend_disables_psycopg_preparation(result_backend,
engine_options):
- expected_url = result_backend or
"db+postgresql+psycopg://user:pass@host/db"
- expected_driver =
make_url(expected_url.removeprefix("db+")).get_driver_name()
+def test_result_backend_disables_psycopg_preparation(result_backend,
expected_driver, engine_options):
expected_connect_args = {
**({"prepare_threshold": None} if expected_driver == "psycopg" else
{}),
**engine_options.get("connect_args", {}),