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", {}),

Reply via email to