You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "karenbraganz (via GitHub)" <gi...@apache.org> on 2026/04/21 17:58:22 UTC

[PR] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

karenbraganz opened a new pull request, #65618:
URL: https://github.com/apache/airflow/pull/65618

    <!-- 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.
   
   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
   -->
   
   ---
   
   ##### 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".
   -->
   
   - [ ] Yes (please specify the tool below)
   
   <!--
   Generated-by: [Tool Name] following [the guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions)
   -->
   
   This PR modifies the SqlExecuteQueryOperator to be deferrable. I have also created a SqlExecuteQueryTrigger and added asynchronous functions to the DbApiHook for use with the SqlExecuteQueryOperator when it is deferred.
   
   I plan on making this compatible with several database types. This PR only adds compatibility with Postgres.
   
   ---
   
   * 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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3501510560


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +114,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):
+    """
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
+
+    :param sql: the sql statement to be executed (str) or a list of sql statements to execute
+    :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
+    """
+
+    def __init__(
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+    ):
+        super().__init__()
+        self.sql = sql
+        self.conn_id = conn_id
+        self.autocommit = autocommit
+        self.parameters = parameters
+        self.fetch_results = fetch_results
+        self.split_statements = split_statements
+        self.return_last = return_last
+
+    def serialize(self) -> tuple[str, dict[str, Any]]:
+        """Serialize the SQLExecuteQueryTrigger arguments and classpath."""
+        return (
+            f"{self.__class__.__module__}.{self.__class__.__name__}",
+            {
+                "sql": self.sql,
+                "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+            },
+        )
+
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def get_hook(self) -> DbApiHook:
+        """
+        Return DbApiHook.
+
+        :return: DbApiHook for this connection
+        """
+        connection = await sync_to_async(BaseHook.get_connection)(self.conn_id)
+        hook = await sync_to_async(connection.get_hook)()
+        if not isinstance(hook, DbApiHook) or not hasattr(hook, "run_async"):
+            raise AirflowException(
+                f"You are trying to use the SqlExecuteQueryOperator in deferrable mode with {hook.__class__.__name__},"
+                f" but its provider does not support this. Please set deferrable=False"
+                f" Got {hook.__class__.__name__} with class hierarchy: {hook.__class__.mro()}"
+            )
+        return hook
+
+    async def run(self) -> AsyncIterator[TriggerEvent]:
+        try:
+            hook = await self.get_hook()
+
+            self.log.info("Extracting data from %s", self.conn_id)
+            self.log.info("Executing: \n %s", self.sql)
+
+            if self.fetch_results:
+                # Fetch the raw rows with the built-in handler and return them with the cursor
+                # descriptions; the operator applies any user handler on the worker.
+                results = await hook.run_async(

Review Comment:
   yes, this could result in a duplicate query run if the trigger is killed. I can look into whether this can be avoided.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "dabla (via GitHub)" <gi...@apache.org>.
dabla commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3507361214


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +112,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):
+    """
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
+
+    :param sql: the sql statement to be executed (str) or a list of sql statements to execute
+    :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
+    """
+
+    def __init__(
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+    ):
+        super().__init__()
+        self.sql = sql
+        self.conn_id = conn_id
+        self.autocommit = autocommit
+        self.parameters = parameters
+        self.fetch_results = fetch_results
+        self.split_statements = split_statements
+        self.return_last = return_last
+
+    def serialize(self) -> tuple[str, dict[str, Any]]:
+        """Serialize the SQLExecuteQueryTrigger arguments and classpath."""
+        return (
+            f"{self.__class__.__module__}.{self.__class__.__name__}",
+            {
+                "sql": self.sql,
+                "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+            },
+        )
+
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def get_hook(self) -> DbApiHook:
+        """
+        Return DbApiHook.
+
+        :return: DbApiHook for this connection
+        """
+        connection = await sync_to_async(BaseHook.get_connection)(self.conn_id)

Review Comment:
   You should use the `common.compat` `get_async_connection` here.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "uranusjr (via GitHub)" <gi...@apache.org>.
uranusjr commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3975785704


##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py:
##########
@@ -1171,3 +1185,220 @@ def get_db_log_messages(self, conn) -> None:
 
         :param conn: Connection object
         """
+
+    def _translate_sql(self, sql: str) -> str:
+        """
+        Translate SQL to driver-specific paramstyle.
+
+        DB-specific hooks may override this to translate from a canonical style
+        to their driver's paramstyle if you want a unified SQL authoring style.
+        """
+        return sql
+
+    def _prepare_parameters(self, parameters: Iterable | Mapping[str, Any] | None):
+        """DB hooks may override to adapt parameter style."""
+        return parameters
+
+    def _build_conn_kwargs_from_airflow_connection(self, db) -> dict:
+        """Build a DB-agnostic kwargs dict."""
+        extra = {}
+        extra_dejson = getattr(db, "extra_dejson", None)
+        if isinstance(extra_dejson, dict):
+            extra = extra_dejson
+        try:
+            port = int(db.port) if db.port else None
+        except (TypeError, ValueError):
+            port = None
+        return {
+            "host": db.host or "",
+            "port": port,
+            "username": db.login or "",
+            "password": db.password or "",
+            "database": extra.get("database") or extra.get("dbname") or "",
+            "schema": db.schema or "",
+            "conn_type": db.conn_type,
+            "extra": extra,
+            "raw_connection": db,
+        }
+
+    async def aget_conn(self) -> Any:

Review Comment:
   `arun` only runs when a subclass overrides `aget_conn`. Postgres overrides it fully and never calls this base version, so this base `aget_conn` and everything else related ship untested against any real driver.
   
   Since this is public common.sql API that's costly to change later, could we add tests for this base class, or trim it to what Postgres actually uses instead?



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #65618:
URL: https://github.com/apache/airflow/pull/65618#issuecomment-5574929854

   <!-- uv-lock-rebase-notice -->
   `uv.lock` on `main` just moved via commit [`ec71f28`](https://redirect.github.com/apache/airflow/commit/ec71f28f2dba324d7398db3712d5764d774752b0) and this PR currently conflicts.
   
   Quickest fix:
   ```bash
   git fetch upstream main && git rebase upstream/main
   rm uv.lock && uv lock
   git add uv.lock && git rebase --continue
   git push --force-with-lease
   ```
   
   _Automated nudge — ignore if you're not ready to rebase. This comment is updated in place on future `uv.lock` bumps._


-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3547973201


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +112,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):
+    """
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
+
+    :param sql: the sql statement to be executed (str) or a list of sql statements to execute
+    :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
+    """
+
+    def __init__(
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+    ):
+        super().__init__()
+        self.sql = sql
+        self.conn_id = conn_id
+        self.autocommit = autocommit
+        self.parameters = parameters
+        self.fetch_results = fetch_results
+        self.split_statements = split_statements
+        self.return_last = return_last
+
+    def serialize(self) -> tuple[str, dict[str, Any]]:
+        """Serialize the SQLExecuteQueryTrigger arguments and classpath."""
+        return (
+            f"{self.__class__.__module__}.{self.__class__.__name__}",
+            {
+                "sql": self.sql,
+                "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+            },
+        )
+
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def get_hook(self) -> DbApiHook:
+        """
+        Return DbApiHook.
+
+        :return: DbApiHook for this connection
+        """
+        connection = await sync_to_async(BaseHook.get_connection)(self.conn_id)

Review Comment:
   done



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "uranusjr (via GitHub)" <gi...@apache.org>.
uranusjr commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3975785704


##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py:
##########
@@ -1171,3 +1185,220 @@ def get_db_log_messages(self, conn) -> None:
 
         :param conn: Connection object
         """
+
+    def _translate_sql(self, sql: str) -> str:
+        """
+        Translate SQL to driver-specific paramstyle.
+
+        DB-specific hooks may override this to translate from a canonical style
+        to their driver's paramstyle if you want a unified SQL authoring style.
+        """
+        return sql
+
+    def _prepare_parameters(self, parameters: Iterable | Mapping[str, Any] | None):
+        """DB hooks may override to adapt parameter style."""
+        return parameters
+
+    def _build_conn_kwargs_from_airflow_connection(self, db) -> dict:
+        """Build a DB-agnostic kwargs dict."""
+        extra = {}
+        extra_dejson = getattr(db, "extra_dejson", None)
+        if isinstance(extra_dejson, dict):
+            extra = extra_dejson
+        try:
+            port = int(db.port) if db.port else None
+        except (TypeError, ValueError):
+            port = None
+        return {
+            "host": db.host or "",
+            "port": port,
+            "username": db.login or "",
+            "password": db.password or "",
+            "database": extra.get("database") or extra.get("dbname") or "",
+            "schema": db.schema or "",
+            "conn_type": db.conn_type,
+            "extra": extra,
+            "raw_connection": db,
+        }
+
+    async def aget_conn(self) -> Any:

Review Comment:
   `arun` only runs when a subclass overrides `aget_conn`. Postgres overrides it fully and never calls this base version, so this base `aget_conn` and everything else related ship untested against any real driver.
   
   Since this is public common.sql API that's costly to change later, could we trim it to what Postgres actually uses instead? Or properly test them?



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub) via commits" <co...@airflow.apache.org>.
karenbraganz commented on PR #65618:
URL: https://github.com/apache/airflow/pull/65618#issuecomment-6576769961

   @potiuk yes, I am working on suggested changes and will address the open threads.


-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub) via commits" <co...@airflow.apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r4464712868


##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py:
##########
@@ -1171,3 +1185,220 @@ def get_db_log_messages(self, conn) -> None:
 
         :param conn: Connection object
         """
+
+    def _translate_sql(self, sql: str) -> str:
+        """
+        Translate SQL to driver-specific paramstyle.
+
+        DB-specific hooks may override this to translate from a canonical style
+        to their driver's paramstyle if you want a unified SQL authoring style.
+        """
+        return sql
+
+    def _prepare_parameters(self, parameters: Iterable | Mapping[str, Any] | None):
+        """DB hooks may override to adapt parameter style."""
+        return parameters
+
+    def _build_conn_kwargs_from_airflow_connection(self, db) -> dict:
+        """Build a DB-agnostic kwargs dict."""
+        extra = {}
+        extra_dejson = getattr(db, "extra_dejson", None)
+        if isinstance(extra_dejson, dict):
+            extra = extra_dejson
+        try:
+            port = int(db.port) if db.port else None
+        except (TypeError, ValueError):
+            port = None
+        return {
+            "host": db.host or "",
+            "port": port,
+            "username": db.login or "",
+            "password": db.password or "",
+            "database": extra.get("database") or extra.get("dbname") or "",
+            "schema": db.schema or "",
+            "conn_type": db.conn_type,
+            "extra": extra,
+            "raw_connection": db,
+        }
+
+    async def aget_conn(self) -> Any:

Review Comment:
   Makes sense! I am looking into removing code that is not used by Postgres.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "RNHTTR (via GitHub)" <gi...@apache.org>.
RNHTTR commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3501298787


##########
providers/common/sql/src/airflow/providers/common/sql/operators/sql.py:
##########
@@ -552,31 +616,82 @@ def _process_output(
     def _should_run_output_processing(self) -> bool:
         return self.do_xcom_push
 
+    def _get_handler_import_path(self) -> str:

Review Comment:
   Does this get called anywhere?



##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +114,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):
+    """
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
+
+    :param sql: the sql statement to be executed (str) or a list of sql statements to execute
+    :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
+    """
+
+    def __init__(
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+    ):
+        super().__init__()
+        self.sql = sql
+        self.conn_id = conn_id
+        self.autocommit = autocommit
+        self.parameters = parameters
+        self.fetch_results = fetch_results
+        self.split_statements = split_statements
+        self.return_last = return_last
+
+    def serialize(self) -> tuple[str, dict[str, Any]]:
+        """Serialize the SQLExecuteQueryTrigger arguments and classpath."""
+        return (
+            f"{self.__class__.__module__}.{self.__class__.__name__}",
+            {
+                "sql": self.sql,
+                "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+            },
+        )
+
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def get_hook(self) -> DbApiHook:
+        """
+        Return DbApiHook.
+
+        :return: DbApiHook for this connection
+        """
+        connection = await sync_to_async(BaseHook.get_connection)(self.conn_id)
+        hook = await sync_to_async(connection.get_hook)()
+        if not isinstance(hook, DbApiHook) or not hasattr(hook, "run_async"):
+            raise AirflowException(
+                f"You are trying to use the SqlExecuteQueryOperator in deferrable mode with {hook.__class__.__name__},"
+                f" but its provider does not support this. Please set deferrable=False"
+                f" Got {hook.__class__.__name__} with class hierarchy: {hook.__class__.mro()}"
+            )
+        return hook
+
+    async def run(self) -> AsyncIterator[TriggerEvent]:
+        try:
+            hook = await self.get_hook()
+
+            self.log.info("Extracting data from %s", self.conn_id)
+            self.log.info("Executing: \n %s", self.sql)
+
+            if self.fetch_results:
+                # Fetch the raw rows with the built-in handler and return them with the cursor
+                # descriptions; the operator applies any user handler on the worker.
+                results = await hook.run_async(

Review Comment:
   What happens if the triggerer is killed in the middle of a run? Will this trigger be re-executed? Is there a chance that multiple instances of the same query are invoked?



##########
providers/snowflake/src/airflow/providers/snowflake/operators/snowflake.py:
##########
@@ -409,7 +409,7 @@ def __init__(
                 "session_parameters": session_parameters,
                 **hook_params,
             }
-        super().__init__(conn_id=snowflake_conn_id, **kwargs)  # pragma: no cover
+        super().__init__(conn_id=snowflake_conn_id, deferrable=deferrable, **kwargs)  # pragma: no cover

Review Comment:
   is this intentional?



##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -17,21 +17,32 @@
 # under the License.
 from __future__ import annotations
 
+import importlib
+import sys
 from typing import TYPE_CHECKING
 
+from asgiref.sync import sync_to_async
+
 from airflow.providers.common.compat.sdk import AirflowException, BaseHook
 from airflow.providers.common.compat.version_compat import AIRFLOW_V_3_2_PLUS
+from airflow.providers.common.sql.hooks.handlers import fetch_all_handler
 from airflow.providers.common.sql.hooks.sql import DbApiHook
 from airflow.triggers.base import BaseTrigger, TriggerEvent
 
 if TYPE_CHECKING:
     from collections.abc import AsyncIterator
     from typing import Any
 
+from collections.abc import (
+    Iterable,
+    Mapping,
+    Sequence,
+)
 
-class SQLExecuteQueryTrigger(BaseTrigger):
+
+class SQLGenericTransferTrigger(BaseTrigger):

Review Comment:
   This is a breaking change, right? If so, we'll need to bump the major version of this provider.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3538978134


##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi:
##########
@@ -185,6 +186,27 @@ class DbApiHook(BaseHook):
     @staticmethod
     def get_openlineage_authority_part(connection, default_port: int | None = None) -> str: ...
     def get_db_log_messages(self, conn) -> None: ...
+    async def async_get_conn(self) -> Any: ...
+    @overload
+    async def run_async(

Review Comment:
   I have made this change for `run_async` and other async functions.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on PR #65618:
URL: https://github.com/apache/airflow/pull/65618#issuecomment-5415127340

   > I see the sqlglot dependency was added, what's the reason behind it? (hence failing static check)
   
   @dabla check out my dicussion with Ryan [here](https://github.com/apache/airflow/pull/65618#discussion_r3501325615). I am adding a read-only parameter, which ensures that only read queries (not write queries) are allowed when it is set to True. This is to prevent execution of non-idempotent write queries on the triggerer, which could be problematic if the triggerer suddenly shuts down. Such a trigger will not be able to run without any issues on the new triggerer. I am using the sqlglot parser as one of the methods to verify that the query is read-only. 
   
   This check can be disabled by setting the parameter to False.
   
   I explored the option of starting the query on the worker, then deferring to the tirggerer only for polling query completion. I researched this and found that some DB types do not let you start a query on one connection, then poll it on a different connection, so this isn't possible.


-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "uranusjr (via GitHub)" <gi...@apache.org>.
uranusjr commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3975795991


##########
providers/common/sql/src/airflow/providers/common/sql/operators/sql.py:
##########
@@ -554,29 +624,78 @@ def _should_run_output_processing(self) -> bool:
 
     def execute(self, context):

Review Comment:
   With the defaults (`deferrable=True, enforce_read_only=True`) on a non-Postgres DbApiHook, the operator goes through here, defers, and only fails in the triggerer with NotImplementedError from `aget_hook`.
   
   Worth validating async/read-only hook support in `execute()` before deferring, so users get an immediate failure on the worker instead of a deferred one?



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "dabla (via GitHub)" <gi...@apache.org>.
dabla commented on PR #65618:
URL: https://github.com/apache/airflow/pull/65618#issuecomment-5214359545

   I see the sqlglot dependency was added, what's the reason behind it?


-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub) via commits" <co...@airflow.apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r4490915964


##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py:
##########
@@ -1171,3 +1185,220 @@ def get_db_log_messages(self, conn) -> None:
 
         :param conn: Connection object
         """
+
+    def _translate_sql(self, sql: str) -> str:
+        """
+        Translate SQL to driver-specific paramstyle.
+
+        DB-specific hooks may override this to translate from a canonical style
+        to their driver's paramstyle if you want a unified SQL authoring style.
+        """
+        return sql
+
+    def _prepare_parameters(self, parameters: Iterable | Mapping[str, Any] | None):
+        """DB hooks may override to adapt parameter style."""
+        return parameters
+
+    def _build_conn_kwargs_from_airflow_connection(self, db) -> dict:
+        """Build a DB-agnostic kwargs dict."""
+        extra = {}
+        extra_dejson = getattr(db, "extra_dejson", None)
+        if isinstance(extra_dejson, dict):
+            extra = extra_dejson
+        try:
+            port = int(db.port) if db.port else None
+        except (TypeError, ValueError):
+            port = None
+        return {
+            "host": db.host or "",
+            "port": port,
+            "username": db.login or "",
+            "password": db.password or "",
+            "database": extra.get("database") or extra.get("dbname") or "",
+            "schema": db.schema or "",
+            "conn_type": db.conn_type,
+            "extra": extra,
+            "raw_connection": db,
+        }
+
+    async def aget_conn(self) -> Any:
+        if not hasattr(self, "_conn_lock"):
+            self._conn_lock = asyncio.Lock()
+        async with self._conn_lock:
+            if not self._connection:
+                self._connection = await get_async_connection(self.get_conn_id())
+            db = self._connection
+            if self.connector is None:
+                raise RuntimeError(f"{type(self).__name__} didn't have `self.connector` set!")
+            conn_kwargs = self._build_conn_kwargs_from_airflow_connection(db)
+            return await self.connector.connect(**conn_kwargs)
+
+    async def _call(self, func: Callable, *args, **kwargs) -> Any:
+        if inspect.iscoroutinefunction(func):
+            return await func(*args, **kwargs)
+        return await sync_to_async(func)(*args, **kwargs)
+
+    @asynccontextmanager
+    async def _acreate_autocommit_connection(self, autocommit: bool = False):

Review Comment:
   Makes sense! I will remove any code that guesses at behavior/functionality from the base class. 



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "uranusjr (via GitHub)" <gi...@apache.org>.
uranusjr commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3975799785


##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py:
##########
@@ -1171,3 +1185,220 @@ def get_db_log_messages(self, conn) -> None:
 
         :param conn: Connection object
         """
+
+    def _translate_sql(self, sql: str) -> str:
+        """
+        Translate SQL to driver-specific paramstyle.
+
+        DB-specific hooks may override this to translate from a canonical style
+        to their driver's paramstyle if you want a unified SQL authoring style.
+        """
+        return sql
+
+    def _prepare_parameters(self, parameters: Iterable | Mapping[str, Any] | None):
+        """DB hooks may override to adapt parameter style."""
+        return parameters
+
+    def _build_conn_kwargs_from_airflow_connection(self, db) -> dict:
+        """Build a DB-agnostic kwargs dict."""
+        extra = {}
+        extra_dejson = getattr(db, "extra_dejson", None)
+        if isinstance(extra_dejson, dict):
+            extra = extra_dejson
+        try:
+            port = int(db.port) if db.port else None
+        except (TypeError, ValueError):
+            port = None
+        return {
+            "host": db.host or "",
+            "port": port,
+            "username": db.login or "",
+            "password": db.password or "",
+            "database": extra.get("database") or extra.get("dbname") or "",
+            "schema": db.schema or "",
+            "conn_type": db.conn_type,
+            "extra": extra,
+            "raw_connection": db,
+        }
+
+    async def aget_conn(self) -> Any:
+        if not hasattr(self, "_conn_lock"):
+            self._conn_lock = asyncio.Lock()
+        async with self._conn_lock:
+            if not self._connection:
+                self._connection = await get_async_connection(self.get_conn_id())
+            db = self._connection
+            if self.connector is None:
+                raise RuntimeError(f"{type(self).__name__} didn't have `self.connector` set!")
+            conn_kwargs = self._build_conn_kwargs_from_airflow_connection(db)
+            return await self.connector.connect(**conn_kwargs)
+
+    async def _call(self, func: Callable, *args, **kwargs) -> Any:
+        if inspect.iscoroutinefunction(func):
+            return await func(*args, **kwargs)
+        return await sync_to_async(func)(*args, **kwargs)
+
+    @asynccontextmanager
+    async def _acreate_autocommit_connection(self, autocommit: bool = False):
+        conn = await self.aget_conn()
+        try:
+            if self.supports_autocommit:
+                set_autocommit = getattr(conn, "set_autocommit", None)
+                if set_autocommit and inspect.iscoroutinefunction(set_autocommit):
+                    await set_autocommit(autocommit)
+                else:
+                    self.set_autocommit(conn, autocommit)
+            yield conn
+        finally:
+            close = getattr(conn, "aclose", None) or getattr(conn, "close", None)
+            if close:
+                await self._call(close)
+
+    @asynccontextmanager
+    async def _aget_cursor(self, conn):
+        cursor = getattr(conn, "cursor", None)
+        if cursor is None:
+            raise TypeError(
+                f"{type(conn).__name__} has no cursor() method. "
+                "Override _aget_cursor in the DB-specific hook."
+            )
+        cur_or_cm = cursor()
+        if inspect.isawaitable(cur_or_cm):
+            cur_or_cm = await cur_or_cm
+        if hasattr(cur_or_cm, "__aenter__") and hasattr(cur_or_cm, "__aexit__"):
+            async with cur_or_cm as cur:
+                yield cur
+            return
+        execute = getattr(cur_or_cm, "execute", None)
+        if execute and inspect.iscoroutinefunction(execute):
+            try:
+                yield cur_or_cm
+            finally:
+                close = getattr(cur_or_cm, "aclose", None) or getattr(cur_or_cm, "close", None)
+                if close:
+                    if inspect.iscoroutinefunction(close):
+                        await close()
+                    else:
+                        close()
+            return
+        raise RuntimeError(
+            f"Unsupported cursor type returned by {type(conn).__name__}. "
+            "Override _aget_cursor in the DB-specific hook."
+        )
+
+    def supports_async_execution(self) -> bool:
+        """Whether the DB-specific hook overrides :meth:`aget_conn` for a real async driver."""
+        return type(self).aget_conn is not DbApiHook.aget_conn
+
+    def supports_readonly_execution(self) -> bool:
+        """Whether the DB-specific hook overrides :meth:`_aenter_read_only`."""
+        return type(self)._aenter_read_only is not DbApiHook._aenter_read_only
+
+    async def _aenter_read_only(self, conn):
+        """
+        Put the opened async connection in read-only mode.
+
+        Called by :meth:`arun` before any statement runs, whenever the operator
+        requests read-only execution. The base implementation raises an exception
+        if this method is not implemented in the DB-specific hook.
+        """
+        raise NotImplementedError(
+            f"{type(self).__name__} does not implement read-only execution. Override "
+            "_aenter_read_only to support deferrable read-only queries for this database,"

Review Comment:
   ```suggestion
               "_aenter_read_only to support deferrable read-only queries for this database."
   ```
   
   Or did a part of the sentence accidentally drop off?



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3625321363


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +114,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):
+    """
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
+
+    :param sql: the sql statement to be executed (str) or a list of sql statements to execute
+    :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
+    """
+
+    def __init__(
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+    ):
+        super().__init__()
+        self.sql = sql
+        self.conn_id = conn_id
+        self.autocommit = autocommit
+        self.parameters = parameters
+        self.fetch_results = fetch_results
+        self.split_statements = split_statements
+        self.return_last = return_last
+
+    def serialize(self) -> tuple[str, dict[str, Any]]:
+        """Serialize the SQLExecuteQueryTrigger arguments and classpath."""
+        return (
+            f"{self.__class__.__module__}.{self.__class__.__name__}",
+            {
+                "sql": self.sql,
+                "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+            },
+        )
+
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def get_hook(self) -> DbApiHook:
+        """
+        Return DbApiHook.
+
+        :return: DbApiHook for this connection
+        """
+        connection = await sync_to_async(BaseHook.get_connection)(self.conn_id)
+        hook = await sync_to_async(connection.get_hook)()
+        if not isinstance(hook, DbApiHook) or not hasattr(hook, "run_async"):
+            raise AirflowException(
+                f"You are trying to use the SqlExecuteQueryOperator in deferrable mode with {hook.__class__.__name__},"
+                f" but its provider does not support this. Please set deferrable=False"
+                f" Got {hook.__class__.__name__} with class hierarchy: {hook.__class__.mro()}"
+            )
+        return hook
+
+    async def run(self) -> AsyncIterator[TriggerEvent]:
+        try:
+            hook = await self.get_hook()
+
+            self.log.info("Extracting data from %s", self.conn_id)
+            self.log.info("Executing: \n %s", self.sql)
+
+            if self.fetch_results:
+                # Fetch the raw rows with the built-in handler and return them with the cursor
+                # descriptions; the operator applies any user handler on the worker.
+                results = await hook.run_async(

Review Comment:
   I have added some logic for this. Still need to run tests and confirm that it is working as expected.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "Srabasti (via GitHub)" <gi...@apache.org>.
Srabasti commented on PR #65618:
URL: https://github.com/apache/airflow/pull/65618#issuecomment-4609442661

   Thanks for incorporating the review changes for import of conf @karenbraganz !!!
   
   Seeing that the 3 failing tests are related to docker failures and files not being found as below. 
   <img width="1544" height="561" alt="image" src="https://github.com/user-attachments/assets/99141636-362b-4975-b0a1-9ab3c38a2f69" />
   https://github.com/apache/airflow/actions/runs/26853708158/job/79193370724?pr=65618
   https://github.com/apache/airflow/actions/runs/26853708158/job/79193370736?pr=65618
   https://github.com/apache/airflow/actions/runs/26853708158/job/79193370689?pr=65618
   
   Can you try rebasing and pushing the changes to repo, so as to ensure any bug fixes for unrelated failures are resolved by another open source contributor, automatically?
   
   I observed @jscheffl pushed no files however CI ran fine, from log above last on May 29th. Looks to me, possibly some change happened after that to cause these errors to start failing.
   
   FYI - @potiuk I see same error in failed CI run below. Looks to me the same issue.
   https://github.com/apache/airflow/actions/runs/26858010209/job/79206609169?pr=67929
   
   


-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "kacpermuda (via GitHub)" <gi...@apache.org>.
kacpermuda commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3381354546


##########
providers/common/sql/src/airflow/providers/common/sql/operators/sql.py:
##########
@@ -513,6 +517,7 @@ def __init__(
         return_last: bool = True,
         show_return_value_in_logs: bool = False,
         requires_result_fetch: bool = False,
+        deferrable: bool = conf.getboolean("operators", "default_deferrable", fallback=False),

Review Comment:
   I think there is no requirement to wait until major version release, it can happen anytime based on the gravity of changes added, but I guess this operator is so widely used that breaking it may be very annoying, even with clear error message, requiring action from majority of users is not a great idea. Maybe a better solution would be to just not use the global `[operators. default_deferrable]` here and use False by default, this way we make it a clear opt-in for users. If we document the reasoning behind this, it should be clear for users and developers why the global config is not used yet. Then, when the coverage improves for async hooks, we can re-visit this operator, switch to using global config as default and then do a major release, when the coverage of async db hooks is already substantial. 



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "kacpermuda (via GitHub)" <gi...@apache.org>.
kacpermuda commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3506400963


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -17,21 +17,32 @@
 # under the License.
 from __future__ import annotations
 
+import importlib
+import sys
 from typing import TYPE_CHECKING
 
+from asgiref.sync import sync_to_async
+
 from airflow.providers.common.compat.sdk import AirflowException, BaseHook
 from airflow.providers.common.compat.version_compat import AIRFLOW_V_3_2_PLUS
+from airflow.providers.common.sql.hooks.handlers import fetch_all_handler
 from airflow.providers.common.sql.hooks.sql import DbApiHook
 from airflow.triggers.base import BaseTrigger, TriggerEvent
 
 if TYPE_CHECKING:
     from collections.abc import AsyncIterator
     from typing import Any
 
+from collections.abc import (
+    Iterable,
+    Mapping,
+    Sequence,
+)
 
-class SQLExecuteQueryTrigger(BaseTrigger):
+
+class SQLGenericTransferTrigger(BaseTrigger):

Review Comment:
   I'm not saying making a major release is pointless, just wondering if renaming a class is needed, trying to think of a way we can add this deferrable functionality as purely additive



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "dabla (via GitHub)" <gi...@apache.org>.
dabla commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3507389565


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +112,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):

Review Comment:
   I would prefer to see the GenericTransfer also use this SQLExecuteQueryTrigger, which is better suited due to it's more async native nature.  I don't like the fact that we have now a dedicated trigger for the GenericTransfer, I think it should be possible to reuse the new SQLExecuteQueryTrigger.



##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi:
##########
@@ -185,6 +186,27 @@ class DbApiHook(BaseHook):
     @staticmethod
     def get_openlineage_authority_part(connection, default_port: int | None = None) -> str: ...
     def get_db_log_messages(self, conn) -> None: ...
+    async def async_get_conn(self) -> Any: ...
+    @overload
+    async def run_async(

Review Comment:
   I would tend more to call the method `arun `instead of `run_async`, just to follow the same naming convention applied like in TaskSDK (`aget_connection`, `aget_hook`, ...)



##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +112,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):
+    """
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
+
+    :param sql: the sql statement to be executed (str) or a list of sql statements to execute
+    :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
+    """
+
+    def __init__(
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+    ):
+        super().__init__()
+        self.sql = sql
+        self.conn_id = conn_id
+        self.autocommit = autocommit
+        self.parameters = parameters
+        self.fetch_results = fetch_results
+        self.split_statements = split_statements
+        self.return_last = return_last
+
+    def serialize(self) -> tuple[str, dict[str, Any]]:
+        """Serialize the SQLExecuteQueryTrigger arguments and classpath."""
+        return (
+            f"{self.__class__.__module__}.{self.__class__.__name__}",
+            {
+                "sql": self.sql,
+                "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+            },
+        )
+
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def get_hook(self) -> DbApiHook:
+        """
+        Return DbApiHook.
+
+        :return: DbApiHook for this connection
+        """
+        connection = await sync_to_async(BaseHook.get_connection)(self.conn_id)

Review Comment:
   You should use the common compat `get_async_connection` here.



##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +112,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):
+    """
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
+
+    :param sql: the sql statement to be executed (str) or a list of sql statements to execute
+    :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
+    """
+
+    def __init__(
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+    ):
+        super().__init__()
+        self.sql = sql
+        self.conn_id = conn_id
+        self.autocommit = autocommit
+        self.parameters = parameters
+        self.fetch_results = fetch_results
+        self.split_statements = split_statements
+        self.return_last = return_last
+
+    def serialize(self) -> tuple[str, dict[str, Any]]:
+        """Serialize the SQLExecuteQueryTrigger arguments and classpath."""
+        return (
+            f"{self.__class__.__module__}.{self.__class__.__name__}",
+            {
+                "sql": self.sql,
+                "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+            },
+        )
+
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def get_hook(self) -> DbApiHook:
+        """
+        Return DbApiHook.
+
+        :return: DbApiHook for this connection
+        """
+        connection = await sync_to_async(BaseHook.get_connection)(self.conn_id)
+        hook = await sync_to_async(connection.get_hook)()

Review Comment:
   I could maybe also add a `get_async_hook` helper method in `common.compat`, so that you can use it here as well just like the `get_async_connection`, that would be much cleaner as newer Airflow will now also support the native async `BaseHook.aget_hook` method which was added in this [PR](https://github.com/apache/airflow/pull/68506).



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3227605172


##########
providers/common/sql/src/airflow/providers/common/sql/operators/sql.py:
##########
@@ -18,11 +18,13 @@
 from __future__ import annotations
 
 import ast
+import inspect
 import re
 from collections.abc import Callable, Iterable, Mapping, Sequence
 from functools import cached_property
 from typing import TYPE_CHECKING, Any, ClassVar, NoReturn, SupportsAbs
 
+from airflow.configuration import conf

Review Comment:
   I have made this change.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "kacpermuda (via GitHub)" <gi...@apache.org>.
kacpermuda commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3372192751


##########
providers/common/sql/src/airflow/providers/common/sql/operators/sql.py:
##########
@@ -513,6 +517,7 @@ def __init__(
         return_last: bool = True,
         show_return_value_in_logs: bool = False,
         requires_result_fetch: bool = False,
+        deferrable: bool = conf.getboolean("operators", "default_deferrable", fallback=False),

Review Comment:
   Small note / question. We should probably mark this somewhere in the changelog / release notes, to underline that SQlExecuteQueryOperator can now be deferrable and it uses this global config. I assume not all the hooks will be compatible on day 0, and if somebody is using config of "default_deferrable=True", it can break their workflow (correct?). Wondering if this should be a "breaking" change or we just assume it's a feature, and if somebody is using "default_deferrable=True" = they know what they're doing. Was just thinking that there were no major changes to this operator for a long time and it's a crucial one, so such change can have a big impact on the end users. 



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "RNHTTR (via GitHub)" <gi...@apache.org>.
RNHTTR commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3506428718


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +114,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):
+    """
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
+
+    :param sql: the sql statement to be executed (str) or a list of sql statements to execute
+    :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
+    """
+
+    def __init__(
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+    ):
+        super().__init__()
+        self.sql = sql
+        self.conn_id = conn_id
+        self.autocommit = autocommit
+        self.parameters = parameters
+        self.fetch_results = fetch_results
+        self.split_statements = split_statements
+        self.return_last = return_last
+
+    def serialize(self) -> tuple[str, dict[str, Any]]:
+        """Serialize the SQLExecuteQueryTrigger arguments and classpath."""
+        return (
+            f"{self.__class__.__module__}.{self.__class__.__name__}",
+            {
+                "sql": self.sql,
+                "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+            },
+        )
+
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def get_hook(self) -> DbApiHook:
+        """
+        Return DbApiHook.
+
+        :return: DbApiHook for this connection
+        """
+        connection = await sync_to_async(BaseHook.get_connection)(self.conn_id)
+        hook = await sync_to_async(connection.get_hook)()
+        if not isinstance(hook, DbApiHook) or not hasattr(hook, "run_async"):
+            raise AirflowException(
+                f"You are trying to use the SqlExecuteQueryOperator in deferrable mode with {hook.__class__.__name__},"
+                f" but its provider does not support this. Please set deferrable=False"
+                f" Got {hook.__class__.__name__} with class hierarchy: {hook.__class__.mro()}"
+            )
+        return hook
+
+    async def run(self) -> AsyncIterator[TriggerEvent]:
+        try:
+            hook = await self.get_hook()
+
+            self.log.info("Extracting data from %s", self.conn_id)
+            self.log.info("Executing: \n %s", self.sql)
+
+            if self.fetch_results:
+                # Fetch the raw rows with the built-in handler and return them with the cursor
+                # descriptions; the operator applies any user handler on the worker.
+                results = await hook.run_async(

Review Comment:
   I wonder if we can initiate on the worker and then poll on the triggerer? Not sure there's a straightforward way to do that.
   
   We could also potentially block non-read-only queries by default and include a parameter to do things like `INSERT`, `UPDATE`, `DELETE`, etc with a docstring explaining the risks?



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3547972138


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +112,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):

Review Comment:
   Makes sense, I will look into how this can be implemented. @RNHTTR @kacpermuda we might be able to avoid releasing this in a major version if I do this since the trigger name for GenericTransfer won't change.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "dabla (via GitHub)" <gi...@apache.org>.
dabla commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r4015589898


##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi:
##########
@@ -185,6 +186,27 @@ class DbApiHook(BaseHook):
     @staticmethod
     def get_openlineage_authority_part(connection, default_port: int | None = None) -> str: ...
     def get_db_log_messages(self, conn) -> None: ...
+    async def aget_conn(self) -> Any: ...
+    @overload
+    async def arun(
+        self,
+        sql: str | Iterable[str],
+        autocommit: bool = ...,
+        parameters: Iterable | Mapping[str, Any] | None = ...,
+        handler: None = ...,
+        split_statements: bool = ...,
+        return_last: bool = ...,
+    ) -> None: ...
+    @overload
+    async def arun(
+        self,
+        sql: str | Iterable[str],
+        autocommit: bool = ...,
+        parameters: Iterable | Mapping[str, Any] | None = ...,

Review Comment:
   **[blocker]** `arun()` type stub is out of sync with the implementation — missing `read_only`.
   
   In `hooks/sql.py`, both `@overload` signatures of `arun` include `read_only: bool = ...`, and the trigger calls it with that keyword (`triggers/sql.py`: `await hook.arun(..., read_only=self.read_only)`). The `.pyi` stub (the declared "public interface" per its own module docstring) only kept `sql`, `autocommit`, `parameters`, `handler`, `split_statements`, `return_last` — `read_only` was dropped from both overloads.
   
   Since this package ships `.pyi` stubs that mypy resolves in preference to the real implementation, any caller passing `read_only=` (including this PR's own trigger) will fail static type checking (`breeze run mypy path/to/code` / the `mypy-shared-*`-style CI hooks for providers). Please add `read_only: bool = ...,` to both overloads in the stub, matching `hooks/sql.py`.



##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py:
##########
@@ -1171,3 +1185,220 @@ def get_db_log_messages(self, conn) -> None:
 
         :param conn: Connection object
         """
+
+    def _translate_sql(self, sql: str) -> str:
+        """
+        Translate SQL to driver-specific paramstyle.
+
+        DB-specific hooks may override this to translate from a canonical style
+        to their driver's paramstyle if you want a unified SQL authoring style.
+        """
+        return sql
+
+    def _prepare_parameters(self, parameters: Iterable | Mapping[str, Any] | None):
+        """DB hooks may override to adapt parameter style."""
+        return parameters
+
+    def _build_conn_kwargs_from_airflow_connection(self, db) -> dict:
+        """Build a DB-agnostic kwargs dict."""
+        extra = {}
+        extra_dejson = getattr(db, "extra_dejson", None)
+        if isinstance(extra_dejson, dict):
+            extra = extra_dejson
+        try:
+            port = int(db.port) if db.port else None
+        except (TypeError, ValueError):
+            port = None
+        return {
+            "host": db.host or "",
+            "port": port,
+            "username": db.login or "",
+            "password": db.password or "",
+            "database": extra.get("database") or extra.get("dbname") or "",
+            "schema": db.schema or "",
+            "conn_type": db.conn_type,
+            "extra": extra,
+            "raw_connection": db,
+        }
+
+    async def aget_conn(self) -> Any:
+        if not hasattr(self, "_conn_lock"):
+            self._conn_lock = asyncio.Lock()
+        async with self._conn_lock:
+            if not self._connection:
+                self._connection = await get_async_connection(self.get_conn_id())
+            db = self._connection
+            if self.connector is None:
+                raise RuntimeError(f"{type(self).__name__} didn't have `self.connector` set!")
+            conn_kwargs = self._build_conn_kwargs_from_airflow_connection(db)
+            return await self.connector.connect(**conn_kwargs)
+
+    async def _call(self, func: Callable, *args, **kwargs) -> Any:
+        if inspect.iscoroutinefunction(func):
+            return await func(*args, **kwargs)
+        return await sync_to_async(func)(*args, **kwargs)
+
+    @asynccontextmanager
+    async def _acreate_autocommit_connection(self, autocommit: bool = False):
+        conn = await self.aget_conn()
+        try:
+            if self.supports_autocommit:
+                set_autocommit = getattr(conn, "set_autocommit", None)
+                if set_autocommit and inspect.iscoroutinefunction(set_autocommit):
+                    await set_autocommit(autocommit)
+                else:
+                    self.set_autocommit(conn, autocommit)
+            yield conn
+        finally:
+            close = getattr(conn, "aclose", None) or getattr(conn, "close", None)
+            if close:
+                await self._call(close)
+
+    @asynccontextmanager
+    async def _aget_cursor(self, conn):
+        cursor = getattr(conn, "cursor", None)
+        if cursor is None:
+            raise TypeError(
+                f"{type(conn).__name__} has no cursor() method. "
+                "Override _aget_cursor in the DB-specific hook."
+            )
+        cur_or_cm = cursor()
+        if inspect.isawaitable(cur_or_cm):
+            cur_or_cm = await cur_or_cm
+        if hasattr(cur_or_cm, "__aenter__") and hasattr(cur_or_cm, "__aexit__"):
+            async with cur_or_cm as cur:
+                yield cur
+            return
+        execute = getattr(cur_or_cm, "execute", None)
+        if execute and inspect.iscoroutinefunction(execute):
+            try:
+                yield cur_or_cm
+            finally:
+                close = getattr(cur_or_cm, "aclose", None) or getattr(cur_or_cm, "close", None)
+                if close:
+                    if inspect.iscoroutinefunction(close):
+                        await close()
+                    else:
+                        close()
+            return
+        raise RuntimeError(
+            f"Unsupported cursor type returned by {type(conn).__name__}. "
+            "Override _aget_cursor in the DB-specific hook."
+        )
+
+    def supports_async_execution(self) -> bool:

Review Comment:
   **[warning]** Capability detection should be an explicit override, not implicit reflection on method identity.
   
   ```python
   def supports_async_execution(self) -> bool:
       """Whether the DB-specific hook overrides :meth:`aget_conn` for a real async driver."""
       return type(self).aget_conn is not DbApiHook.aget_conn
   
   def supports_readonly_execution(self) -> bool:
       """Whether the DB-specific hook overrides :meth:`_aenter_read_only`."""
       return type(self)._aenter_read_only is not DbApiHook._aenter_read_only
   ```
   
   Comparing `type(self).aget_conn is not DbApiHook.aget_conn` infers capability from *whether a method was overridden*, rather than a hook stating its capability directly. This is fragile: it silently breaks for any indirection between the subclass and `DbApiHook` — a mixin providing `aget_conn`, a decorator wrapping the method, `functools.wraps`-based instrumentation, or simply an intermediate abstract base between `DbApiHook` and the concrete hook — any of these makes the identity check `True` even when the override doesn't actually provide a working async driver, or `False` when it does. It also makes the contract implicit: a hook author has to know this reflection trick exists rather than just declaring "I support async."
   
   `DbApiHook` already has the right pattern a few lines above (`hooks/sql.py:170-173`) for exactly this kind of capability negotiation — plain class attributes, defaulting to `False`, that a DB-specific hook overrides:
   ```python
   # Override if this db supports autocommit.
   supports_autocommit = False
   # Override if this db supports executemany.
   supports_executemany = False
   ```
   `supports_async_execution`/`supports_readonly_execution` should follow the same convention instead of introducing a different, reflection-based mechanism for the same kind of question:
   ```python
   # DbApiHook
   # Override if this db supports a real async driver (aget_conn/_aget_cursor).
   supports_async_execution = False
   # Override if this db supports a read-only transaction (_aenter_read_only).
   supports_readonly_execution = False
   
   # PostgresHook
   supports_async_execution = True
   supports_readonly_execution = True
   ```
   This is simpler, is robust regardless of how the subclass structures its overrides (mixins, decorators, indirection), and keeps all of `DbApiHook`'s capability flags consistent with one pattern instead of two.



##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -57,49 +84,160 @@ def serialize(self) -> tuple[str, dict[str, Any]]:
             {
                 "sql": self.sql,
                 "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+                "read_only": self.read_only,
                 "hook_params": self.hook_params,
             },
         )
 
-    def get_hook(self) -> DbApiHook:
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def aget_hook(self) -> DbApiHook:
         """
         Return DbApiHook.
 
         :return: DbApiHook for this connection
         """
-        connection = BaseHook.get_connection(self.conn_id)
-        hook = connection.get_hook(hook_params=self.hook_params)
+        hook = await get_async_hook(self.conn_id, hook_params=self.hook_params)
         if not isinstance(hook, DbApiHook):
-            raise AirflowException(
-                f"You are trying to use `common-sql` with {hook.__class__.__name__},"
-                " but its provider does not support it. Please upgrade the provider to a version that"
-                " supports `common-sql`. The hook class should be a subclass of"
-                f" `{hook.__class__.__module__}.{hook.__class__.__name__}`."
-                f" Got {hook.__class__.__name__} hook with class hierarchy: {hook.__class__.mro()}"
+            raise TypeError(
+                f"You are trying to use the SqlExecuteQueryOperator in deferrable mode with {hook.__class__.__name__},"
+                " but its provider does not support this. Please set deferrable=False"
+                f" Got {hook.__class__.__name__} with class hierarchy: {hook.__class__.mro()}"
+            )
+        if self.read_only and not (hook.supports_async_execution() and hook.supports_readonly_execution()):
+            raise NotImplementedError(
+                f"{hook.__class__.__name__} does not support read-only execution, so it cannot run a"
+                " deferred query safely (a triggerer restart could re-run it). Set"
+                " enforce_read_only=False to run without the read-only guard if the query is"
+                " idempotent, or deferrable=False to run it on the worker."
             )
         return hook
 
-    async def _get_records(self) -> Any:
-        from asgiref.sync import sync_to_async
-
-        hook = self.get_hook()
+    async def _run_query(self, hook: DbApiHook, fetch_results: bool):
+        """
+        Run the query against ``hook``, using its native async driver if available.
 
+        Hooks without a real async driver (:meth:`DbApiHook.supports_async_execution`) fall back to
+        running the synchronous :meth:`DbApiHook.run` in a worker thread, matching the compatibility the
+        `GenericTransfer` operator has always relied on for arbitrary DB-specific hooks.
+        """
+        if hook.supports_async_execution():
+            if fetch_results:
+                return await hook.arun(
+                    sql=self.sql,
+                    autocommit=self.autocommit,
+                    parameters=self.parameters,
+                    handler=fetch_all_handler,
+                    split_statements=self.split_statements,
+                    return_last=self.return_last,
+                    read_only=self.read_only,
+                )
+            return await hook.arun(
+                sql=self.sql,
+                autocommit=self.autocommit,
+                parameters=self.parameters,
+                handler=None,
+                split_statements=self.split_statements,
+                return_last=self.return_last,
+                read_only=self.read_only,
+            )
         if AIRFLOW_V_3_2_PLUS:
             # This is only supported from Airflow 3.2 or higher due to added async support in CommsDecoder
-            return await sync_to_async(hook.get_records)(self.sql)
-        return hook.get_records(self.sql)
+            # `sync_to_async` erases `run`'s overloads, so it is cast to a plain callable first.
+            arun_in_thread = sync_to_async(cast("Callable[..., Any]", hook.run))
+            if fetch_results:
+                return await arun_in_thread(
+                    sql=self.sql,
+                    autocommit=self.autocommit,
+                    parameters=self.parameters,
+                    handler=fetch_all_handler,
+                    split_statements=self.split_statements,
+                    return_last=self.return_last,
+                )
+            return await arun_in_thread(
+                sql=self.sql,
+                autocommit=self.autocommit,
+                parameters=self.parameters,
+                handler=None,
+                split_statements=self.split_statements,
+                return_last=self.return_last,
+            )
+        if fetch_results:
+            return hook.run(
+                sql=self.sql,
+                autocommit=self.autocommit,
+                parameters=self.parameters,
+                handler=fetch_all_handler,
+                split_statements=self.split_statements,
+                return_last=self.return_last,
+            )
+        return hook.run(
+            sql=self.sql,
+            autocommit=self.autocommit,
+            parameters=self.parameters,
+            handler=None,
+            split_statements=self.split_statements,
+            return_last=self.return_last,
+        )
 
     async def run(self) -> AsyncIterator[TriggerEvent]:
         try:
+            hook = await self.aget_hook()
+
             self.log.info("Extracting data from %s", self.conn_id)
             self.log.info("Executing: \n %s", self.sql)
-            self.log.info("Reading records from %s", self.conn_id)
 
-            results = await self._get_records()
+            if self.fetch_results:
+                # Fetch the raw rows with the built-in handler and return them with the cursor
+                # descriptions; the operator applies any user handler on the worker.
+                results = await self._run_query(hook, fetch_results=True)
+
+                self.log.info("Executing query from %s done!", self.conn_id)
+                self.log.debug("results: %s", results)
+                yield TriggerEvent(
+                    {
+                        "status": "success",
+                        "results": results,
+                        "descriptions": self._jsonsafe_descriptions(hook.descriptions),
+                    }
+                )
+
+            else:
+                await self._run_query(hook, fetch_results=False)
+
+                self.log.info("Executing query from %s done!", self.conn_id)

Review Comment:
   **[blocker]** Unbounded result set is written to the metadata database, with no way to bound or detect it.
   
   The rationale for replaying results via `_ReplayCursor` instead of running the user `handler` directly in the triggerer is sound in principle: the triggerer must not execute arbitrary user code (it has no DAG bundle access and the Security Model keeps user code off that process), so the built-in `fetch_all_handler` runs in the triggerer and the user's `handler` is deferred to the worker in `execute_complete`.
   
   However, the mechanism for getting the rows there is not an in-memory hop — `yield TriggerEvent({"results": ..., "descriptions": ...})` is consumed by the scheduler and persisted into `TaskInstance.next_kwargs`, a JSON column on the metadata database (`airflow-core/src/airflow/models/taskinstance.py:672,1864`), until the worker picks the task instance back up and calls `execute_complete`. Every row of every deferred query is serialized to JSON and written as a task-instance row in Airflow's own database, then read back out — unlike the non-deferrable path, which streams rows straight from the DB-API cursor without ever touching Airflow's metadata store.
   
   The docstring's guidance ("very large results are best fetched with deferrable=False") is not enforced anywhere, and there is no mechanism in this PR for a user or the operator to determine in advance whether a given query's result set is "large" — the row/byte count is only known after the query has already run and been serialized into `next_kwargs`. A single deferred task with an unexpectedly large result set writes that entire payload into the shared metadata database that the scheduler, webserver, and every other task instance also depend on, which is a shared-resource risk (bloated table/row size, backup size, replication lag, storage pressure), not merely a per-task inefficiency. This is a design gap, not a documentation gap: relying on docstring guidance alone is not a safeguard against a task instance writing an arbitrarily large payload into the metadata database.
   
   A general fix would parallel how workers get a customizable XCom backend to keep large payloads out of the metadata database — the trigger path currently has no equivalent, so `TriggerEvent` payloads always go through the metadata database regardless of size. That is a broader architectural change and out of scope for this PR, but this PR introduces the first deferred code path in `common-sql` that can put arbitrarily large query result sets through that channel, and currently has no limit, no size check, and no fallback for the case where the result set turns out to be large.



##########
providers/common/sql/src/airflow/providers/common/sql/operators/generic_transfer.py:
##########
@@ -206,7 +210,7 @@ def execute_complete(
         event: dict[Any, Any] | None = None,
     ) -> Any:
         if event:
-            if event.get("status") == "failure":
+            if event.get("status") == "error":

Review Comment:
   **[warning]** Avoidable breaking change to `SQLExecuteQueryTrigger`, and no changelog/version-bump entry accompanies it.
   
   `SQLExecuteQueryTrigger.__init__` dropped `**kwargs` and now requires `autocommit`, `split_statements`, and `return_last` as required args with no defaults, and the `TriggerEvent` status key changed from `"failure"` to `"error"` (reflected here in `generic_transfer.py`'s `execute_complete`). Neither change is required by the deferrable-execution feature itself:
   
   - **Constructor:** the old trigger's implicit behavior was equivalent to `autocommit=False, split_statements=False, return_last=True` (it just called `hook.get_records(self.sql)`). Giving the three new params those same defaults would let old serialized kwargs dicts (from tasks already deferred before an upgrade) still reconstruct successfully via `cls(**kwargs)`, while new callers can still pass them explicitly. As written, any `GenericTransferOperator` task already deferred before the upgrade will fail to reconstruct its trigger after upgrade with a missing-argument `TypeError`.
   - **Status rename:** `"error"` is the dominant convention across other providers' triggers (~409 occurrences vs. ~5 for `"failure"`), but the rename is not required by this feature, and it breaks any code (including a subclassed/custom operator) that checks `event["status"] == "failure"`.
   
   Review thread 8/6 raised making this "purely additive", and the response was to merge `GenericTransfer`'s trigger into `SQLExecuteQueryTrigger` so the class name wouldn't change — but the constructor signature and status key still changed underneath the same class name, so the break was moved rather than removed. Defaulting the three new params and keeping (or aliasing) `"failure"` would remove the breaking change entirely at no functional cost.
   
   If the breaking change is kept regardless, there is still no corresponding update to `providers/common/sql/docs/changelog.rst` (providers don't use newsfragments — the changelog file is the right place per `providers/AGENTS.md`) or a provider major-version bump reflected in this diff.



##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py:
##########
@@ -1171,3 +1185,220 @@ def get_db_log_messages(self, conn) -> None:
 
         :param conn: Connection object
         """
+
+    def _translate_sql(self, sql: str) -> str:
+        """
+        Translate SQL to driver-specific paramstyle.
+
+        DB-specific hooks may override this to translate from a canonical style
+        to their driver's paramstyle if you want a unified SQL authoring style.
+        """
+        return sql
+
+    def _prepare_parameters(self, parameters: Iterable | Mapping[str, Any] | None):
+        """DB hooks may override to adapt parameter style."""
+        return parameters
+
+    def _build_conn_kwargs_from_airflow_connection(self, db) -> dict:
+        """Build a DB-agnostic kwargs dict."""
+        extra = {}
+        extra_dejson = getattr(db, "extra_dejson", None)
+        if isinstance(extra_dejson, dict):
+            extra = extra_dejson
+        try:
+            port = int(db.port) if db.port else None
+        except (TypeError, ValueError):
+            port = None
+        return {
+            "host": db.host or "",
+            "port": port,
+            "username": db.login or "",
+            "password": db.password or "",
+            "database": extra.get("database") or extra.get("dbname") or "",
+            "schema": db.schema or "",
+            "conn_type": db.conn_type,
+            "extra": extra,
+            "raw_connection": db,
+        }
+
+    async def aget_conn(self) -> Any:
+        if not hasattr(self, "_conn_lock"):
+            self._conn_lock = asyncio.Lock()
+        async with self._conn_lock:
+            if not self._connection:
+                self._connection = await get_async_connection(self.get_conn_id())
+            db = self._connection
+            if self.connector is None:
+                raise RuntimeError(f"{type(self).__name__} didn't have `self.connector` set!")
+            conn_kwargs = self._build_conn_kwargs_from_airflow_connection(db)
+            return await self.connector.connect(**conn_kwargs)
+
+    async def _call(self, func: Callable, *args, **kwargs) -> Any:
+        if inspect.iscoroutinefunction(func):
+            return await func(*args, **kwargs)
+        return await sync_to_async(func)(*args, **kwargs)
+
+    @asynccontextmanager
+    async def _acreate_autocommit_connection(self, autocommit: bool = False):

Review Comment:
   **[warning]** Connection close and autocommit handling guess at driver shape via duck-typing instead of an explicit per-hook contract.
   
   ```python
   @asynccontextmanager
   async def _acreate_autocommit_connection(self, autocommit: bool = False):
       conn = await self.aget_conn()
       try:
           if self.supports_autocommit:
               set_autocommit = getattr(conn, "set_autocommit", None)
               if set_autocommit and inspect.iscoroutinefunction(set_autocommit):
                   await set_autocommit(autocommit)
               else:
                   self.set_autocommit(conn, autocommit)
           yield conn
       finally:
           close = getattr(conn, "aclose", None) or getattr(conn, "close", None)
           if close:
               await self._call(close)
   ```
   
   There is no standard async equivalent of PEP 249 that all async DB drivers share, so this code cannot assume a single connection-object shape. The current implementation compensates with `getattr`/`inspect.iscoroutinefunction` probing at the base-class level to guess whether close is named `close()` or `aclose()`, and whether autocommit is a coroutine method on the connection or must go through the hook's own synchronous `set_autocommit()`. This guessing is itself unreliable: for the one driver this PR ships, `psycopg.AsyncConnection` exposes `close()` as a coroutine but has no `aclose()` at all, so the `aclose`-first check silently falls through to the correct branch only because of the `or` fallback — a driver that has neither name, or that has a synchronous `close()` alongside an unrelated async `aclose()`, would be mishandled.
   
   The read-only enforcement path in this same PR (`_aenter_read_only`) uses the opposite, more robust pattern: the base class raises `NotImplementedError` by default, and `PostgresHook` overrides it with driver-specific knowledge. `_acreate_autocommit_connection` should follow that same contract instead of guessing generically:
   ```python
   # DbApiHook (base)
   async def _aclose_conn(self, conn) -> None:
       raise NotImplementedError(f"{type(self).__name__} does not implement _aclose_conn.")
   
   async def _aset_autocommit(self, conn, autocommit: bool) -> None:
       raise NotImplementedError(f"{type(self).__name__} does not implement _aset_autocommit.")
   
   @asynccontextmanager
   async def _acreate_autocommit_connection(self, autocommit: bool = False):
       conn = await self.aget_conn()
       try:
           if self.supports_autocommit:
               await self._aset_autocommit(conn, autocommit)
           yield conn
       finally:
           await self._aclose_conn(conn)
   
   # PostgresHook
   async def _aclose_conn(self, conn) -> None:
       await conn.close()
   
   async def _aset_autocommit(self, conn, autocommit: bool) -> None:
       await conn.set_autocommit(autocommit)
   ```
   This states the actual driver contract explicitly per hook, the same fix as comment [3]'s capability flags — one consistent override pattern instead of base-class code inferring behavior it cannot know generically.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "uranusjr (via GitHub)" <gi...@apache.org>.
uranusjr commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3975773925


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -57,49 +84,160 @@ def serialize(self) -> tuple[str, dict[str, Any]]:
             {
                 "sql": self.sql,
                 "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+                "read_only": self.read_only,
                 "hook_params": self.hook_params,
             },
         )
 
-    def get_hook(self) -> DbApiHook:
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def aget_hook(self) -> DbApiHook:
         """
         Return DbApiHook.
 
         :return: DbApiHook for this connection
         """
-        connection = BaseHook.get_connection(self.conn_id)
-        hook = connection.get_hook(hook_params=self.hook_params)
+        hook = await get_async_hook(self.conn_id, hook_params=self.hook_params)
         if not isinstance(hook, DbApiHook):
-            raise AirflowException(
-                f"You are trying to use `common-sql` with {hook.__class__.__name__},"
-                " but its provider does not support it. Please upgrade the provider to a version that"
-                " supports `common-sql`. The hook class should be a subclass of"
-                f" `{hook.__class__.__module__}.{hook.__class__.__name__}`."
-                f" Got {hook.__class__.__name__} hook with class hierarchy: {hook.__class__.mro()}"
+            raise TypeError(
+                f"You are trying to use the SqlExecuteQueryOperator in deferrable mode with {hook.__class__.__name__},"
+                " but its provider does not support this. Please set deferrable=False"
+                f" Got {hook.__class__.__name__} with class hierarchy: {hook.__class__.mro()}"
+            )
+        if self.read_only and not (hook.supports_async_execution() and hook.supports_readonly_execution()):
+            raise NotImplementedError(
+                f"{hook.__class__.__name__} does not support read-only execution, so it cannot run a"
+                " deferred query safely (a triggerer restart could re-run it). Set"
+                " enforce_read_only=False to run without the read-only guard if the query is"
+                " idempotent, or deferrable=False to run it on the worker."
             )
         return hook
 
-    async def _get_records(self) -> Any:
-        from asgiref.sync import sync_to_async
-
-        hook = self.get_hook()
+    async def _run_query(self, hook: DbApiHook, fetch_results: bool):
+        """
+        Run the query against ``hook``, using its native async driver if available.
 
+        Hooks without a real async driver (:meth:`DbApiHook.supports_async_execution`) fall back to
+        running the synchronous :meth:`DbApiHook.run` in a worker thread, matching the compatibility the
+        `GenericTransfer` operator has always relied on for arbitrary DB-specific hooks.
+        """
+        if hook.supports_async_execution():
+            if fetch_results:
+                return await hook.arun(
+                    sql=self.sql,
+                    autocommit=self.autocommit,
+                    parameters=self.parameters,
+                    handler=fetch_all_handler,
+                    split_statements=self.split_statements,
+                    return_last=self.return_last,
+                    read_only=self.read_only,
+                )
+            return await hook.arun(
+                sql=self.sql,
+                autocommit=self.autocommit,
+                parameters=self.parameters,
+                handler=None,
+                split_statements=self.split_statements,
+                return_last=self.return_last,
+                read_only=self.read_only,
+            )
         if AIRFLOW_V_3_2_PLUS:
             # This is only supported from Airflow 3.2 or higher due to added async support in CommsDecoder
-            return await sync_to_async(hook.get_records)(self.sql)
-        return hook.get_records(self.sql)
+            # `sync_to_async` erases `run`'s overloads, so it is cast to a plain callable first.
+            arun_in_thread = sync_to_async(cast("Callable[..., Any]", hook.run))
+            if fetch_results:
+                return await arun_in_thread(
+                    sql=self.sql,
+                    autocommit=self.autocommit,
+                    parameters=self.parameters,
+                    handler=fetch_all_handler,
+                    split_statements=self.split_statements,
+                    return_last=self.return_last,
+                )
+            return await arun_in_thread(
+                sql=self.sql,
+                autocommit=self.autocommit,
+                parameters=self.parameters,
+                handler=None,
+                split_statements=self.split_statements,
+                return_last=self.return_last,
+            )
+        if fetch_results:
+            return hook.run(
+                sql=self.sql,
+                autocommit=self.autocommit,
+                parameters=self.parameters,
+                handler=fetch_all_handler,
+                split_statements=self.split_statements,
+                return_last=self.return_last,
+            )
+        return hook.run(
+            sql=self.sql,
+            autocommit=self.autocommit,
+            parameters=self.parameters,
+            handler=None,
+            split_statements=self.split_statements,
+            return_last=self.return_last,
+        )
 
     async def run(self) -> AsyncIterator[TriggerEvent]:
         try:
+            hook = await self.aget_hook()
+
             self.log.info("Extracting data from %s", self.conn_id)
             self.log.info("Executing: \n %s", self.sql)
-            self.log.info("Reading records from %s", self.conn_id)
 
-            results = await self._get_records()
+            if self.fetch_results:
+                # Fetch the raw rows with the built-in handler and return them with the cursor
+                # descriptions; the operator applies any user handler on the worker.
+                results = await self._run_query(hook, fetch_results=True)
+
+                self.log.info("Executing query from %s done!", self.conn_id)
+                self.log.debug("results: %s", results)
+                yield TriggerEvent(
+                    {
+                        "status": "success",
+                        "results": results,

Review Comment:
   `descriptions` get normalized via `_jsonsafe_descriptions`, but the `results` rows are returned raw. A Postgres row holding platform-dependant types (e.g. timestamptz) has to survive TriggerEvent serialization and the trip back to the worker.
   
   Could you add a test that selects numeric/timestamptz/bytea/uuid and asserts the round-tripped values match `deferrable=False`? If serde doesn't cover a type we'd silently corrupt or fail results in the exact path this PR adds. (I honestly do not know what we did around here before this change.)



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "dabla (via GitHub)" <gi...@apache.org>.
dabla commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3578928173


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +112,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):
+    """
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
+
+    :param sql: the sql statement to be executed (str) or a list of sql statements to execute
+    :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
+    """
+
+    def __init__(
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+    ):
+        super().__init__()
+        self.sql = sql
+        self.conn_id = conn_id
+        self.autocommit = autocommit
+        self.parameters = parameters
+        self.fetch_results = fetch_results
+        self.split_statements = split_statements
+        self.return_last = return_last
+
+    def serialize(self) -> tuple[str, dict[str, Any]]:
+        """Serialize the SQLExecuteQueryTrigger arguments and classpath."""
+        return (
+            f"{self.__class__.__module__}.{self.__class__.__name__}",
+            {
+                "sql": self.sql,
+                "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+            },
+        )
+
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def get_hook(self) -> DbApiHook:
+        """
+        Return DbApiHook.
+
+        :return: DbApiHook for this connection
+        """
+        connection = await sync_to_async(BaseHook.get_connection)(self.conn_id)
+        hook = await sync_to_async(connection.get_hook)()

Review Comment:
   `"apache-airflow-providers-common-compat>=1.14.1",  # use next version`



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3945896224


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -17,21 +17,32 @@
 # under the License.
 from __future__ import annotations
 
+import importlib
+import sys
 from typing import TYPE_CHECKING
 
+from asgiref.sync import sync_to_async
+
 from airflow.providers.common.compat.sdk import AirflowException, BaseHook
 from airflow.providers.common.compat.version_compat import AIRFLOW_V_3_2_PLUS
+from airflow.providers.common.sql.hooks.handlers import fetch_all_handler
 from airflow.providers.common.sql.hooks.sql import DbApiHook
 from airflow.triggers.base import BaseTrigger, TriggerEvent
 
 if TYPE_CHECKING:
     from collections.abc import AsyncIterator
     from typing import Any
 
+from collections.abc import (
+    Iterable,
+    Mapping,
+    Sequence,
+)
 
-class SQLExecuteQueryTrigger(BaseTrigger):
+
+class SQLGenericTransferTrigger(BaseTrigger):

Review Comment:
   I ended up combining both triggers. Now, both SqlExecuteQueryOperator and GenericTransferOperator use the SqlExecuteQueryTrigger.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "kacpermuda (via GitHub)" <gi...@apache.org>.
kacpermuda commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3504004543


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -17,21 +17,32 @@
 # under the License.
 from __future__ import annotations
 
+import importlib
+import sys
 from typing import TYPE_CHECKING
 
+from asgiref.sync import sync_to_async
+
 from airflow.providers.common.compat.sdk import AirflowException, BaseHook
 from airflow.providers.common.compat.version_compat import AIRFLOW_V_3_2_PLUS
+from airflow.providers.common.sql.hooks.handlers import fetch_all_handler
 from airflow.providers.common.sql.hooks.sql import DbApiHook
 from airflow.triggers.base import BaseTrigger, TriggerEvent
 
 if TYPE_CHECKING:
     from collections.abc import AsyncIterator
     from typing import Any
 
+from collections.abc import (
+    Iterable,
+    Mapping,
+    Sequence,
+)
 
-class SQLExecuteQueryTrigger(BaseTrigger):
+
+class SQLGenericTransferTrigger(BaseTrigger):

Review Comment:
   Are there any other breaking changes in this PR? I think we discussed that for the default deferrable value we'll have a workaround to avoid breaking changes for now, so doing it just so that we can rename a class seems a bit ... pointless ? Not sure.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3506532396


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -17,21 +17,32 @@
 # under the License.
 from __future__ import annotations
 
+import importlib
+import sys
 from typing import TYPE_CHECKING
 
+from asgiref.sync import sync_to_async
+
 from airflow.providers.common.compat.sdk import AirflowException, BaseHook
 from airflow.providers.common.compat.version_compat import AIRFLOW_V_3_2_PLUS
+from airflow.providers.common.sql.hooks.handlers import fetch_all_handler
 from airflow.providers.common.sql.hooks.sql import DbApiHook
 from airflow.triggers.base import BaseTrigger, TriggerEvent
 
 if TYPE_CHECKING:
     from collections.abc import AsyncIterator
     from typing import Any
 
+from collections.abc import (
+    Iterable,
+    Mapping,
+    Sequence,
+)
 
-class SQLExecuteQueryTrigger(BaseTrigger):
+
+class SQLGenericTransferTrigger(BaseTrigger):

Review Comment:
   @kacpermuda previously, the GenericTransfer operator used a trigger called the SqlExecuteQueryTrigger. Now that I am making the SqlExecuteQueryOperator deferrable, I thought it would be confusing to have a SqlExecuteQueryTrigger that is actually used by a different operator. I renamed the trigger used by the GenericTransfer operator to SqlGenericTransferTrigger and used the SqlExecuteQueryTrigger name for the trigger used by the SqlExecuteQueryOperator.
   
   This name change is not required, but is good for consistency between the trigger and operator.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3506400178


##########
providers/common/sql/src/airflow/providers/common/sql/operators/sql.py:
##########
@@ -513,6 +517,7 @@ def __init__(
         return_last: bool = True,
         show_return_value_in_logs: bool = False,
         requires_result_fetch: bool = False,
+        deferrable: bool = conf.getboolean("operators", "default_deferrable", fallback=False),

Review Comment:
   I have modified the CI/CD prek hook, so the test is not failing anymore.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "kacpermuda (via GitHub)" <gi...@apache.org>.
kacpermuda commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3506608777


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -17,21 +17,32 @@
 # under the License.
 from __future__ import annotations
 
+import importlib
+import sys
 from typing import TYPE_CHECKING
 
+from asgiref.sync import sync_to_async
+
 from airflow.providers.common.compat.sdk import AirflowException, BaseHook
 from airflow.providers.common.compat.version_compat import AIRFLOW_V_3_2_PLUS
+from airflow.providers.common.sql.hooks.handlers import fetch_all_handler
 from airflow.providers.common.sql.hooks.sql import DbApiHook
 from airflow.triggers.base import BaseTrigger, TriggerEvent
 
 if TYPE_CHECKING:
     from collections.abc import AsyncIterator
     from typing import Any
 
+from collections.abc import (
+    Iterable,
+    Mapping,
+    Sequence,
+)
 
-class SQLExecuteQueryTrigger(BaseTrigger):
+
+class SQLGenericTransferTrigger(BaseTrigger):

Review Comment:
   Sure sure, that makes sense, just wanted to make sure we're doing all the changes consciously since they lead to major release 😄 



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "potiuk (via GitHub) via commits" <co...@airflow.apache.org>.
potiuk commented on PR #65618:
URL: https://github.com/apache/airflow/pull/65618#issuecomment-6563118264

   Thanks for the latest updates, @karenbraganz. The trigger defaults and the `"failure"` status take care of the backward-compatibility concern. There are still 9 open review threads, and the two marked as blockers are still present at `305f69568f`:
   
   - `hooks/sql.pyi`: both `arun` overloads still lack `read_only: bool = ...`, which the trigger passes, so type checking fails against the stub.
   - `triggers/sql.py`: the full result set still goes through the `TriggerEvent` into `next_kwargs` in the metadata database, with no size limit and no fallback. A bound such as a row or byte limit that fails clearly, with a pointer to `deferrable=False`, would close that gap.
   
   The other threads are still open too: the explicit per-hook close and autocommit methods, `supports_*` as class attributes, the guard for `bytes` results you mentioned, checking hook support before deferring, and the base `aget_conn` coverage. Could you push those changes, or reply on the threads you'd rather not change, and then re-request review?
   
   ---
   Drafted-by: Claude Code (Claude Opus 5); reviewed by @potiuk 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

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on PR #65618:
URL: https://github.com/apache/airflow/pull/65618#issuecomment-4846329838

   Sorry, I accidentally tangled up my branch and pushed the wrong commits, which is why all these reviews were requested. I have reverted the mistake.


-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3580568882


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +114,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):
+    """
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
+
+    :param sql: the sql statement to be executed (str) or a list of sql statements to execute
+    :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
+    """
+
+    def __init__(
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+    ):
+        super().__init__()
+        self.sql = sql
+        self.conn_id = conn_id
+        self.autocommit = autocommit
+        self.parameters = parameters
+        self.fetch_results = fetch_results
+        self.split_statements = split_statements
+        self.return_last = return_last
+
+    def serialize(self) -> tuple[str, dict[str, Any]]:
+        """Serialize the SQLExecuteQueryTrigger arguments and classpath."""
+        return (
+            f"{self.__class__.__module__}.{self.__class__.__name__}",
+            {
+                "sql": self.sql,
+                "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+            },
+        )
+
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def get_hook(self) -> DbApiHook:
+        """
+        Return DbApiHook.
+
+        :return: DbApiHook for this connection
+        """
+        connection = await sync_to_async(BaseHook.get_connection)(self.conn_id)
+        hook = await sync_to_async(connection.get_hook)()
+        if not isinstance(hook, DbApiHook) or not hasattr(hook, "run_async"):
+            raise AirflowException(
+                f"You are trying to use the SqlExecuteQueryOperator in deferrable mode with {hook.__class__.__name__},"
+                f" but its provider does not support this. Please set deferrable=False"
+                f" Got {hook.__class__.__name__} with class hierarchy: {hook.__class__.mro()}"
+            )
+        return hook
+
+    async def run(self) -> AsyncIterator[TriggerEvent]:
+        try:
+            hook = await self.get_hook()
+
+            self.log.info("Extracting data from %s", self.conn_id)
+            self.log.info("Executing: \n %s", self.sql)
+
+            if self.fetch_results:
+                # Fetch the raw rows with the built-in handler and return them with the cursor
+                # descriptions; the operator applies any user handler on the worker.
+                results = await hook.run_async(

Review Comment:
   I looked into this. There isn't a straightforward way to implement this for Postgres. It requires the client side connection to remain alive in order to continue running the query. So we cannot open a connection from the worker to start the query, then close that, and re-open a new connection on the triggerer. Your second suggestion seems doable though:
   
   > We could also potentially block non-read-only queries by default and include a parameter to do things like INSERT, UPDATE, DELETE, etc with a docstring explaining the risks?
   
   I can look into implementing it like that.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3547946601


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +112,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):
+    """
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
+
+    :param sql: the sql statement to be executed (str) or a list of sql statements to execute
+    :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
+    """
+
+    def __init__(
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+    ):
+        super().__init__()
+        self.sql = sql
+        self.conn_id = conn_id
+        self.autocommit = autocommit
+        self.parameters = parameters
+        self.fetch_results = fetch_results
+        self.split_statements = split_statements
+        self.return_last = return_last
+
+    def serialize(self) -> tuple[str, dict[str, Any]]:
+        """Serialize the SQLExecuteQueryTrigger arguments and classpath."""
+        return (
+            f"{self.__class__.__module__}.{self.__class__.__name__}",
+            {
+                "sql": self.sql,
+                "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+            },
+        )
+
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def get_hook(self) -> DbApiHook:
+        """
+        Return DbApiHook.
+
+        :return: DbApiHook for this connection
+        """
+        connection = await sync_to_async(BaseHook.get_connection)(self.conn_id)
+        hook = await sync_to_async(connection.get_hook)()

Review Comment:
   I have made changes to use the functions you suggested. You mentioned that I would have to update the dependency. Does this mean that I would update this line in `providers/common/sql/pyproject.toml`?
   ```
   "apache-airflow-providers-common-compat>=1.14.1",
   ```
   
   Would I have to update this to whatever the next version released would be (1.16.0 I assume) so that the function you added is available when people use the SEQO in deferrable mode?



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "dabla (via GitHub)" <gi...@apache.org>.
dabla commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3564322698


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +112,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):
+    """
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
+
+    :param sql: the sql statement to be executed (str) or a list of sql statements to execute
+    :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
+    """
+
+    def __init__(
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+    ):
+        super().__init__()
+        self.sql = sql
+        self.conn_id = conn_id
+        self.autocommit = autocommit
+        self.parameters = parameters
+        self.fetch_results = fetch_results
+        self.split_statements = split_statements
+        self.return_last = return_last
+
+    def serialize(self) -> tuple[str, dict[str, Any]]:
+        """Serialize the SQLExecuteQueryTrigger arguments and classpath."""
+        return (
+            f"{self.__class__.__module__}.{self.__class__.__name__}",
+            {
+                "sql": self.sql,
+                "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+            },
+        )
+
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def get_hook(self) -> DbApiHook:
+        """
+        Return DbApiHook.
+
+        :return: DbApiHook for this connection
+        """
+        connection = await sync_to_async(BaseHook.get_connection)(self.conn_id)
+        hook = await sync_to_async(connection.get_hook)()

Review Comment:
   I think you need to put a certain comment that specifies that version should be updated to the next version the release manager will apply.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "dabla (via GitHub)" <gi...@apache.org>.
dabla commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3564325396


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +112,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):

Review Comment:
   This to me stays a major change independently of this.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "uranusjr (via GitHub)" <gi...@apache.org>.
uranusjr commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3975813046


##########
providers/common/sql/src/airflow/providers/common/sql/operators/sql.py:
##########
@@ -554,29 +624,78 @@ def _should_run_output_processing(self) -> bool:
 
     def execute(self, context):
         self.log.info("Executing: %s", self.sql)
-        hook = self.get_db_hook()
-        if self.split_statements is not None:
-            extra_kwargs = {"split_statements": self.split_statements}
+        if self.deferrable:
+            if self.enforce_read_only:
+                is_write, reason = scan_for_writes(self.sql)
+                if is_write:
+                    raise ValueError(
+                        f"enforce_read_only=True but the SQL appears to contain a write: {reason}. "
+                        "Set enforce_read_only=False if the query is idempotent, or deferrable=False "
+                        "to run it on the worker."
+                    )
+            self.defer(
+                trigger=SQLExecuteQueryTrigger(
+                    sql=self.sql,
+                    conn_id=self.conn_id,
+                    autocommit=self.autocommit,
+                    parameters=self.parameters,
+                    fetch_results=self._should_run_output_processing() or self.requires_result_fetch,
+                    split_statements=self.split_statements,
+                    return_last=self.return_last,
+                    read_only=self.enforce_read_only,
+                ),
+                method_name="execute_complete",
+            )
         else:
-            extra_kwargs = {}
-        output = hook.run(
-            sql=self.sql,
-            autocommit=self.autocommit,
-            parameters=self.parameters,
-            handler=self.handler
-            if self._should_run_output_processing() or self.requires_result_fetch
-            else None,
-            return_last=self.return_last,
-            **extra_kwargs,
-        )
-        if not self._should_run_output_processing():
+            hook = self.get_db_hook()
+            if self.split_statements is not None:
+                extra_kwargs = {"split_statements": self.split_statements}
+            else:
+                extra_kwargs = {}
+            output = hook.run(
+                sql=self.sql,
+                autocommit=self.autocommit,
+                parameters=self.parameters,
+                handler=self.handler
+                if self._should_run_output_processing() or self.requires_result_fetch
+                else None,
+                return_last=self.return_last,
+                **extra_kwargs,
+            )
+            if not self._should_run_output_processing():
+                return None
+            if return_single_query_results(self.sql, self.return_last, self.split_statements):
+                # For simplicity, we pass always list as input to _process_output, regardless if
+                # single query results are going to be returned, and we return the first element
+                # of the list in this case from the (always) list returned by _process_output
+                return self._process_output([output], hook.descriptions)[-1]
+            result = self._process_output(output, hook.descriptions)
+            self.log.info("result: %s", result)
+            return result
+
+    def execute_complete(self, context: Context, event: dict[str, Any] | None = None) -> Any:
+        if event is None:
+            raise RuntimeError("Unknown error in SQLExecuteQueryTrigger")
+        if event.get("status") == "error":
+            raise RuntimeError(event.get("message", "Unknown error in SQLExecuteQueryTrigger"))
+        self.log.info("SQL query executed successfully.")
+        results = event.get("results")
+        if not self._should_run_output_processing() or results is None:
             return None
+        descriptions: list[Sequence[Sequence] | None] = event.get("descriptions") or []
         if return_single_query_results(self.sql, self.return_last, self.split_statements):
-            # For simplicity, we pass always list as input to _process_output, regardless if
-            # single query results are going to be returned, and we return the first element
-            # of the list in this case from the (always) list returned by _process_output
-            return self._process_output([output], hook.descriptions)[-1]
-        result = self._process_output(output, hook.descriptions)
+            # The trigger returns the rows of a single statement; apply the handler on the worker, where
+            # it is defined, over a replayed cursor rather than in the triggerer's event loop.
+            rows = [results] if isinstance(results, str) else results

Review Comment:
   When would a fetched result be a bare str here?



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "dabla (via GitHub) via commits" <co...@airflow.apache.org>.
dabla commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r4510809585


##########
providers/common/sql/src/airflow/providers/common/sql/operators/sql.py:
##########
@@ -575,29 +645,78 @@ def _should_run_output_processing(self) -> bool:
 
     def execute(self, context):
         self.log.info("Executing: %s", self.sql)
-        hook = self.get_db_hook()
-        if self.split_statements is not None:
-            extra_kwargs = {"split_statements": self.split_statements}
+        if self.deferrable:
+            if self.enforce_read_only:
+                is_write, reason = scan_for_writes(self.sql)
+                if is_write:
+                    raise ValueError(
+                        f"enforce_read_only=True but the SQL appears to contain a write: {reason}. "
+                        "Set enforce_read_only=False if the query is idempotent, or deferrable=False "
+                        "to run it on the worker."
+                    )
+            self.defer(
+                trigger=SQLExecuteQueryTrigger(
+                    sql=self.sql,
+                    conn_id=self.conn_id,

Review Comment:
   **[blocker]** In deferrable mode, `database` and `hook_params` never reach the trigger, so the query runs against the connection's default database.
   
   The non-deferrable path gets its hook from `get_db_hook()` → `BaseSQLOperator._hook`, which applies `self.hook_params` (plus the connection-type defaults from `get_hook`) and then overrides `hook.database`/`hook.schema` with `self.database`. The trigger is built from `conn_id` alone and calls `get_async_hook(self.conn_id, hook_params=self.hook_params)` with its own `hook_params`, which is `None` here. As a result:
   
   ```python
   SQLExecuteQueryOperator(task_id="t", conn_id="pg", database="reporting", sql="SELECT ...", deferrable=True)
   ```
   
   runs against the connection's default database, not `reporting`. The task still succeeds and returns rows from the wrong database. Any `hook_params` (e.g. `options`, `enable_log_db_messages`) are dropped the same way.
   
   Suggest passing `hook_params=self.hook_params` to the trigger and carrying `database` as well (a `database` kwarg on the trigger that `aget_hook` applies the way `_hook` does), plus one test per parameter that checks the hook the trigger builds.



##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -17,37 +17,65 @@
 # under the License.
 from __future__ import annotations
 
-from typing import TYPE_CHECKING
+from typing import TYPE_CHECKING, cast
 
-from airflow.providers.common.compat.sdk import AirflowException, BaseHook
+from asgiref.sync import sync_to_async
+
+from airflow.providers.common.compat.hook import get_async_hook
 from airflow.providers.common.compat.version_compat import AIRFLOW_V_3_2_PLUS
+from airflow.providers.common.sql.hooks.handlers import fetch_all_handler
 from airflow.providers.common.sql.hooks.sql import DbApiHook
 from airflow.triggers.base import BaseTrigger, TriggerEvent
 
 if TYPE_CHECKING:
-    from collections.abc import AsyncIterator
+    from collections.abc import AsyncIterator, Callable
     from typing import Any
 
+from collections.abc import (
+    Iterable,
+    Mapping,
+    Sequence,
+)
+
 
 class SQLExecuteQueryTrigger(BaseTrigger):
     """
-    A trigger that executes SQL code in async mode.
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
 
     :param sql: the sql statement to be executed (str) or a list of sql statements to execute
     :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
     :param hook_params: hook parameters
     """
 
     def __init__(
         self,
-        sql: str | list[str],
+        *,
+        sql: str | Iterable[str],
         conn_id: str,
+        autocommit: bool = False,
+        split_statements: bool = False,
+        return_last: bool = True,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,

Review Comment:
   **[blocker]** `fetch_results=False` by default silently ends `GenericTransfer` runs that were deferred before the upgrade.
   
   Thanks for adding the defaults (fb4f274862). However, the old trigger always fetched rows (`hook.get_records(self.sql)`), so the old behaviour corresponds to `fetch_results=True`. A `GenericTransfer` trigger serialized by the previous provider version stores only `sql`, `conn_id` and `hook_params`. After the upgrade it is rebuilt with `fetch_results=False`, yields `{"status": "success"}` without `results`, and `GenericTransfer.execute_complete` reads `rows = event.get("results")` as `None`. It then logs "No more rows to fetch … ending transfer" and the task succeeds with the remaining pages never copied.
   
   Defaulting `fetch_results` to `True` restores the old behaviour for rebuilt triggers. `SQLExecuteQueryOperator` always passes the value explicitly, so it is not affected. A test that builds the trigger from the old three-key kwargs dict and checks that rows come back would pin this down.



##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi:
##########
@@ -179,6 +180,27 @@ class DbApiHook(BaseHook):
     @staticmethod
     def get_openlineage_authority_part(connection, default_port: int | None = None) -> str: ...
     def get_db_log_messages(self, conn) -> None: ...
+    async def aget_conn(self) -> Any: ...

Review Comment:
   **[warning]** The stub also lacks the capability checks the trigger calls.
   
   Besides `read_only` on the `arun` overloads, `triggers/sql.py` calls `hook.supports_async_execution()` and `hook.supports_readonly_execution()`. The `.pyi` declares neither, so mypy, which reads the stub instead of `sql.py`, reports `attr-defined` on those calls. Whatever form the capability check ends up taking (methods, or class attributes next to `supports_autocommit`), it needs an entry here too.



##########
providers/postgres/src/airflow/providers/postgres/hooks/postgres.py:
##########
@@ -316,16 +316,43 @@ def get_conn(self) -> CompatConnection:
             if arg_name not in self.ignored_extra_options:
                 conn_args[arg_name] = arg_val
 
-        raw_cursor = conn.extra_dejson.get("cursor")
+        return conn_args, conn
 
+    def get_conn(self) -> CompatConnection:
+        """Establish a connection to a postgres database."""
+        conn_args, conn = self._build_conn_args()
+        raw_cursor = conn.extra_dejson.get("cursor")
         if raw_cursor:
             key, value = self._get_cursor_config(raw_cursor)
             conn_args[key] = value
-
         self.conn = self._create_connection(conn_args)
-
         return self.conn
 
+    async def aget_conn(self) -> Any:
+        """Establish an async connection to a postgres database."""
+        if not USE_PSYCOPG3:
+            raise NotImplementedError("Async connections for PostgresHook require psycopg3.")
+        from psycopg import AsyncConnection
+
+        conn_args, conn = self._build_conn_args()

Review Comment:
   **[warning]** `aget_conn` makes a sync connection lookup on the triggerer's event loop.
   
   `_build_conn_args()` starts with `deepcopy(self.connection)`. The hook comes from `get_async_hook` → `BaseHook.aget_hook`, which fetches the connection asynchronously but builds the hook with only the conn id (`hook_class(**{conn_id_attr: self.conn_id}, **hook_params)` in `Connection.get_hook`). So `self.connection` is still unset and `DbApiHook.connection` calls the sync `get_connection()` from inside `async def run()`.
   
   On `main` this works only through the greenback portal in `TriggerCommsDecoder.send` (`airflow-core/src/airflow/jobs/triggerer_job_runner.py`), and it raises `RuntimeError` when `AIRFLOW_DISABLE_GREENBACK_PORTAL` is set. That portal arrived in 3.3.0 with #66412 ("Fix triggerer crash when multiple triggers call sync SDK methods concurrently"). The postgres provider still supports `apache-airflow>=2.11.0`, so on 3.0–3.2 several of these triggers running at once hit the crash #66412 fixed. The IAM branch of `_build_conn_args` (`get_iam_token`, sync AWS calls) also blocks the loop.
   
   Suggest fetching the connection asynchronously before building the args, e.g. `self.connection = await get_async_connection(self.get_conn_id())` from `common.compat`, the same helper the trigger was moved to.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "Srabasti (via GitHub)" <gi...@apache.org>.
Srabasti commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3179338964


##########
providers/common/sql/src/airflow/providers/common/sql/operators/sql.py:
##########
@@ -18,11 +18,13 @@
 from __future__ import annotations
 
 import ast
+import inspect
 import re
 from collections.abc import Callable, Iterable, Mapping, Sequence
 from functools import cached_property
 from typing import TYPE_CHECKING, Any, ClassVar, NoReturn, SupportsAbs
 
+from airflow.configuration import conf

Review Comment:
   As per https://github.com/apache/airflow/issues/60000, instead of ```from airflow.configuration import conf``` that is deprecated, below is suggested to be used.
   from airflow.providers.common.compat.sdk import conf
   



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "dabla (via GitHub)" <gi...@apache.org>.
dabla commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3530054573


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +112,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):
+    """
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
+
+    :param sql: the sql statement to be executed (str) or a list of sql statements to execute
+    :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
+    """
+
+    def __init__(
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+    ):
+        super().__init__()
+        self.sql = sql
+        self.conn_id = conn_id
+        self.autocommit = autocommit
+        self.parameters = parameters
+        self.fetch_results = fetch_results
+        self.split_statements = split_statements
+        self.return_last = return_last
+
+    def serialize(self) -> tuple[str, dict[str, Any]]:
+        """Serialize the SQLExecuteQueryTrigger arguments and classpath."""
+        return (
+            f"{self.__class__.__module__}.{self.__class__.__name__}",
+            {
+                "sql": self.sql,
+                "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+            },
+        )
+
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def get_hook(self) -> DbApiHook:
+        """
+        Return DbApiHook.
+
+        :return: DbApiHook for this connection
+        """
+        connection = await sync_to_async(BaseHook.get_connection)(self.conn_id)
+        hook = await sync_to_async(connection.get_hook)()

Review Comment:
   Update the dependency to `common.compat` in the `sql provider` and use the new `get_async_hook `method instead of `sync_to_async(connection.get_hook)`.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "dabla (via GitHub)" <gi...@apache.org>.
dabla commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3507320902


##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py:
##########
@@ -1136,3 +1149,194 @@ def get_db_log_messages(self, conn) -> None:
 
         :param conn: Connection object
         """
+
+    def _translate_sql(self, sql: str) -> str:
+        """
+        Translate SQL to driver-specific paramstyle.
+
+        DB-specific hooks may override this to translate from a canonical style
+        to their driver's paramstyle if you want a unified SQL authoring style.
+        """
+        return sql
+
+    def _prepare_parameters(self, parameters: Iterable | Mapping[str, Any] | None):
+        """DB hooks may override to adapt parameter style."""
+        return parameters
+
+    def _build_conn_kwargs_from_airflow_connection(self, db) -> dict:
+        """Build a DB-agnostic kwargs dict."""
+        extra = {}
+        extra_dejson = getattr(db, "extra_dejson", None)
+        if isinstance(extra_dejson, dict):
+            extra = extra_dejson
+        try:
+            port = int(db.port) if db.port else None
+        except (TypeError, ValueError):
+            port = None
+        return {
+            "host": db.host or "",
+            "port": port,
+            "username": db.login or "",
+            "password": db.password or "",
+            "database": extra.get("database") or extra.get("dbname") or "",
+            "schema": db.schema or "",
+            "conn_type": db.conn_type,
+            "extra": extra,
+            "raw_connection": db,
+        }
+
+    async def async_get_conn(self) -> Any:
+        if not hasattr(self, "_conn_lock"):
+            self._conn_lock = asyncio.Lock()
+        async with self._conn_lock:
+            if not self._connection:
+                self._connection = await sync_to_async(self.get_connection)(self.get_conn_id())
+            db = self._connection
+            if self.connector is None:
+                raise RuntimeError(f"{type(self).__name__} didn't have `self.connector` set!")
+            conn_kwargs = self._build_conn_kwargs_from_airflow_connection(db)
+            return await self.connector.connect(**conn_kwargs)
+
+    async def _call(self, func: Callable, *args, **kwargs) -> Any:
+        if inspect.iscoroutinefunction(func):
+            return await func(*args, **kwargs)
+        return await sync_to_async(func)(*args, **kwargs)
+
+    @asynccontextmanager
+    async def _async_create_autocommit_connection(self, autocommit: bool = False):
+        conn = await self.async_get_conn()
+        try:
+            if self.supports_autocommit:
+                set_autocommit = getattr(conn, "set_autocommit", None)
+                if set_autocommit and inspect.iscoroutinefunction(set_autocommit):
+                    await set_autocommit(autocommit)
+                else:
+                    self.set_autocommit(conn, autocommit)
+            yield conn
+        finally:
+            close = getattr(conn, "aclose", None) or getattr(conn, "close", None)

Review Comment:
   Should the second 



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "uranusjr (via GitHub)" <gi...@apache.org>.
uranusjr commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3975757272


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.pyi:
##########
@@ -29,16 +29,25 @@
 #
 """Definition of the public interface for airflow.providers.common.sql.triggers.sql."""
 
-from collections.abc import AsyncIterator
+from collections.abc import AsyncIterator, Iterable, Mapping
 from typing import Any
 
 from airflow.providers.common.sql.hooks.sql import DbApiHook as DbApiHook
 from airflow.triggers.base import BaseTrigger as BaseTrigger, TriggerEvent as TriggerEvent
 
 class SQLExecuteQueryTrigger(BaseTrigger):
     def __init__(
-        self, sql: str | list[str], conn_id: str, hook_params: dict | None = None, **kwargs
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+        read_only: bool = False,
+        hook_params: dict | None = None,

Review Comment:
   This differs from the actual implementation. If you want these to always be keyword-only (i.e. argument ordering does not matter), make these keyword-only with `*`.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "uranusjr (via GitHub)" <gi...@apache.org>.
uranusjr commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3975801552


##########
providers/common/sql/src/airflow/providers/common/sql/operators/read_only_guard.py:
##########
@@ -0,0 +1,83 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+from __future__ import annotations
+
+import logging
+
+log = logging.getLogger(__name__)
+
+try:
+    import sqlglot  # this is an optional dependency
+    from sqlglot import exp
+    from sqlglot.errors import ParseError, TokenError
+
+    _SQLGLOT_AVAILABLE = True
+except ImportError:  # pragma: no cover
+    _SQLGLOT_AVAILABLE = False
+
+# Postgres only matches the current scope of the deferrable SQLExecuteQueryOperator
+_DIALECT = "postgres"
+
+_WRITE_TYPES: tuple[type[exp.Expr], ...] = ()
+if _SQLGLOT_AVAILABLE:
+    _WRITE_TYPES = (exp.Insert, exp.Update, exp.Delete, exp.Merge, exp.Create, exp.Alter, exp.Drop)
+    for name in ("TruncateTable", "Rename"):
+        cls = getattr(exp, name, None)
+        if cls is not None:
+            _WRITE_TYPES += (cls,)
+
+
+def _statement_write_node(stmt: exp.Expr):
+    """Return the first node that proves this statement writes, or None."""
+    if isinstance(stmt, _WRITE_TYPES):
+        return stmt
+    return next(stmt.find_all(*_WRITE_TYPES), None)
+
+
+def scan_for_writes(sql: str | list[str]) -> tuple[bool, str]:
+    """Pre-defer check for write statements``."""

Review Comment:
   ```suggestion
       """Pre-defer check for write statements."""
   ```



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "uranusjr (via GitHub)" <gi...@apache.org>.
uranusjr commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3975785704


##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py:
##########
@@ -1171,3 +1185,220 @@ def get_db_log_messages(self, conn) -> None:
 
         :param conn: Connection object
         """
+
+    def _translate_sql(self, sql: str) -> str:
+        """
+        Translate SQL to driver-specific paramstyle.
+
+        DB-specific hooks may override this to translate from a canonical style
+        to their driver's paramstyle if you want a unified SQL authoring style.
+        """
+        return sql
+
+    def _prepare_parameters(self, parameters: Iterable | Mapping[str, Any] | None):
+        """DB hooks may override to adapt parameter style."""
+        return parameters
+
+    def _build_conn_kwargs_from_airflow_connection(self, db) -> dict:
+        """Build a DB-agnostic kwargs dict."""
+        extra = {}
+        extra_dejson = getattr(db, "extra_dejson", None)
+        if isinstance(extra_dejson, dict):
+            extra = extra_dejson
+        try:
+            port = int(db.port) if db.port else None
+        except (TypeError, ValueError):
+            port = None
+        return {
+            "host": db.host or "",
+            "port": port,
+            "username": db.login or "",
+            "password": db.password or "",
+            "database": extra.get("database") or extra.get("dbname") or "",
+            "schema": db.schema or "",
+            "conn_type": db.conn_type,
+            "extra": extra,
+            "raw_connection": db,
+        }
+
+    async def aget_conn(self) -> Any:

Review Comment:
   `arun` only runs when a subclass overrides `aget_conn`. Postgres overrides it fully and never calls this base version, so this base `aget_conn` and everything else related ship untested against any real driver.
   
   Since this is public common.sql API that's costly to change later, could we either trim it to what Postgres actually uses, or clearly mark this generic layer provisional?



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3501506274


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -17,21 +17,32 @@
 # under the License.
 from __future__ import annotations
 
+import importlib
+import sys
 from typing import TYPE_CHECKING
 
+from asgiref.sync import sync_to_async
+
 from airflow.providers.common.compat.sdk import AirflowException, BaseHook
 from airflow.providers.common.compat.version_compat import AIRFLOW_V_3_2_PLUS
+from airflow.providers.common.sql.hooks.handlers import fetch_all_handler
 from airflow.providers.common.sql.hooks.sql import DbApiHook
 from airflow.triggers.base import BaseTrigger, TriggerEvent
 
 if TYPE_CHECKING:
     from collections.abc import AsyncIterator
     from typing import Any
 
+from collections.abc import (
+    Iterable,
+    Mapping,
+    Sequence,
+)
 
-class SQLExecuteQueryTrigger(BaseTrigger):
+
+class SQLGenericTransferTrigger(BaseTrigger):

Review Comment:
   yes, this would qualify as a breaking change, so we should bump the major version FYI @kacpermuda 
   
   I do not anticipate a lot of disruption since only the trigger name is changed- not the operator name. This would only affect people who use this trigger in a custom operator.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3381079416


##########
providers/common/sql/src/airflow/providers/common/sql/operators/sql.py:
##########
@@ -513,6 +517,7 @@ def __init__(
         return_last: bool = True,
         show_return_value_in_logs: bool = False,
         requires_result_fetch: bool = False,
+        deferrable: bool = conf.getboolean("operators", "default_deferrable", fallback=False),

Review Comment:
   That's a good point. Currently, this is only compatible with PostgresHook. Would we have to wait significantly longer for a major version release if this were classified as a breaking change? What would be the timeline?
   
   I could also add a check for the hook and explicitly raise an error that would show up in the task logs so that users at least know what is wrong immediately if their task fails. 



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3499508709


##########
providers/common/sql/src/airflow/providers/common/sql/operators/sql.py:
##########
@@ -513,6 +517,7 @@ def __init__(
         return_last: bool = True,
         show_return_value_in_logs: bool = False,
         requires_result_fetch: bool = False,
+        deferrable: bool = conf.getboolean("operators", "default_deferrable", fallback=False),

Review Comment:
   @kacpermuda this is failing the "Check and fix default value of default_deferrable" test. It is requiring this change:
   ```
   -        deferrable: bool = False,
   +        deferrable: bool = conf.getboolean("operators", "default_deferrable", fallback=False),
   ```
   Is it alright if I modify the test to make an exception for this operator?



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3501608695


##########
providers/snowflake/src/airflow/providers/snowflake/operators/snowflake.py:
##########
@@ -409,7 +409,7 @@ def __init__(
                 "session_parameters": session_parameters,
                 **hook_params,
             }
-        super().__init__(conn_id=snowflake_conn_id, **kwargs)  # pragma: no cover
+        super().__init__(conn_id=snowflake_conn_id, deferrable=deferrable, **kwargs)  # pragma: no cover

Review Comment:
   Yes, this is intentional. This was done to fix a failing test.
   
   I have hardcoded `deferrable=False` in SqlExecuteQueryOperator (unless the user sets `deferrable=True` in the task) because deferrable mode is only compatible with Postgres at the moment. I did not want users with default_deferrable=True to unexpectedly have task failures. 
   
   If `deferrable=deferrable` is not passed to the super.__init__() method of the SqlExecuteQuery parent class, the `deferrable=False` value in SqlExecuteQueryOperator will override the deferrable value set for SnowflakeSqlApiOperator even if the user has set `deferrable=True` in the task. 
   
   This class was flagged to me due to a failing test, so I still need to look into whether other subclasses are affected silently.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3501451822


##########
providers/common/sql/src/airflow/providers/common/sql/operators/sql.py:
##########
@@ -552,31 +616,82 @@ def _process_output(
     def _should_run_output_processing(self) -> bool:
         return self.do_xcom_push
 
+    def _get_handler_import_path(self) -> str:

Review Comment:
   Nope. I had added this because I was initially planning to import the handler on the trigger. I have decided against doing that so there is some code including this that needs to be deleted.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "RNHTTR (via GitHub)" <gi...@apache.org>.
RNHTTR commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3506374199


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -17,21 +17,32 @@
 # under the License.
 from __future__ import annotations
 
+import importlib
+import sys
 from typing import TYPE_CHECKING
 
+from asgiref.sync import sync_to_async
+
 from airflow.providers.common.compat.sdk import AirflowException, BaseHook
 from airflow.providers.common.compat.version_compat import AIRFLOW_V_3_2_PLUS
+from airflow.providers.common.sql.hooks.handlers import fetch_all_handler
 from airflow.providers.common.sql.hooks.sql import DbApiHook
 from airflow.triggers.base import BaseTrigger, TriggerEvent
 
 if TYPE_CHECKING:
     from collections.abc import AsyncIterator
     from typing import Any
 
+from collections.abc import (
+    Iterable,
+    Mapping,
+    Sequence,
+)
 
-class SQLExecuteQueryTrigger(BaseTrigger):
+
+class SQLGenericTransferTrigger(BaseTrigger):

Review Comment:
   I don't think it's pointless -- If someone built their own deferrable operator with `SQLExecuteQueryTrigger`, it will break.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3945897245


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +112,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):

Review Comment:
   This has been done.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "uranusjr (via GitHub)" <gi...@apache.org>.
uranusjr commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3975804374


##########
providers/postgres/src/airflow/providers/postgres/hooks/postgres.py:
##########
@@ -296,16 +296,47 @@ def get_conn(self) -> CompatConnection:
             if arg_name not in self.ignored_extra_options:
                 conn_args[arg_name] = arg_val
 
-        raw_cursor = conn.extra_dejson.get("cursor")
+        return conn_args, conn
 
+    def get_conn(self) -> CompatConnection:
+        """Establish a connection to a postgres database."""
+        conn_args, conn = self._build_conn_args()
+        raw_cursor = conn.extra_dejson.get("cursor")
         if raw_cursor:
             key, value = self._get_cursor_config(raw_cursor)
             conn_args[key] = value
-
         self.conn = self._create_connection(conn_args)
-
         return self.conn
 
+    async def aget_conn(self) -> Any:
+        """Establish an async connection to a postgres database."""
+        if not USE_PSYCOPG3:
+            raise NotImplementedError("Async connections for PostgresHook require psycopg3.")
+        from psycopg import AsyncConnection
+
+        conn_args, conn = self._build_conn_args()
+
+        raw_cursor = conn.extra_dejson.get("cursor")
+        if raw_cursor:
+            conn_args["row_factory"] = self._get_cursor(raw_cursor)
+
+        # Use Any type for the connection args to avoid type conflicts

Review Comment:
   ```suggestion
   ```



##########
providers/postgres/src/airflow/providers/postgres/hooks/postgres.py:
##########
@@ -296,16 +296,47 @@ def get_conn(self) -> CompatConnection:
             if arg_name not in self.ignored_extra_options:
                 conn_args[arg_name] = arg_val
 
-        raw_cursor = conn.extra_dejson.get("cursor")
+        return conn_args, conn
 
+    def get_conn(self) -> CompatConnection:
+        """Establish a connection to a postgres database."""
+        conn_args, conn = self._build_conn_args()
+        raw_cursor = conn.extra_dejson.get("cursor")
         if raw_cursor:
             key, value = self._get_cursor_config(raw_cursor)
             conn_args[key] = value
-
         self.conn = self._create_connection(conn_args)
-
         return self.conn
 
+    async def aget_conn(self) -> Any:
+        """Establish an async connection to a postgres database."""
+        if not USE_PSYCOPG3:
+            raise NotImplementedError("Async connections for PostgresHook require psycopg3.")
+        from psycopg import AsyncConnection
+
+        conn_args, conn = self._build_conn_args()
+
+        raw_cursor = conn.extra_dejson.get("cursor")
+        if raw_cursor:
+            conn_args["row_factory"] = self._get_cursor(raw_cursor)
+
+        # Use Any type for the connection args to avoid type conflicts
+        connection = await AsyncConnection.connect(**cast("Any", conn_args))
+
+        # Register JSON handlers for both json and jsonb types
+        # This ensures JSON data is properly decoded from bytes to Python objects
+        register_default_adapters(connection)
+
+        # Add the notice handler AFTER the connection is established

Review Comment:
   ```suggestion
   ```



##########
providers/postgres/src/airflow/providers/postgres/hooks/postgres.py:
##########
@@ -296,16 +296,47 @@ def get_conn(self) -> CompatConnection:
             if arg_name not in self.ignored_extra_options:
                 conn_args[arg_name] = arg_val
 
-        raw_cursor = conn.extra_dejson.get("cursor")
+        return conn_args, conn
 
+    def get_conn(self) -> CompatConnection:
+        """Establish a connection to a postgres database."""
+        conn_args, conn = self._build_conn_args()
+        raw_cursor = conn.extra_dejson.get("cursor")
         if raw_cursor:
             key, value = self._get_cursor_config(raw_cursor)
             conn_args[key] = value
-
         self.conn = self._create_connection(conn_args)
-
         return self.conn
 
+    async def aget_conn(self) -> Any:
+        """Establish an async connection to a postgres database."""
+        if not USE_PSYCOPG3:
+            raise NotImplementedError("Async connections for PostgresHook require psycopg3.")
+        from psycopg import AsyncConnection
+
+        conn_args, conn = self._build_conn_args()
+
+        raw_cursor = conn.extra_dejson.get("cursor")
+        if raw_cursor:
+            conn_args["row_factory"] = self._get_cursor(raw_cursor)
+
+        # Use Any type for the connection args to avoid type conflicts
+        connection = await AsyncConnection.connect(**cast("Any", conn_args))
+
+        # Register JSON handlers for both json and jsonb types
+        # This ensures JSON data is properly decoded from bytes to Python objects

Review Comment:
   ```suggestion
   ```



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "uranusjr (via GitHub)" <gi...@apache.org>.
uranusjr commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3975763341


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -17,37 +17,64 @@
 # under the License.
 from __future__ import annotations
 
-from typing import TYPE_CHECKING
+from typing import TYPE_CHECKING, cast
 
-from airflow.providers.common.compat.sdk import AirflowException, BaseHook
+from asgiref.sync import sync_to_async
+
+from airflow.providers.common.compat.hook import get_async_hook
 from airflow.providers.common.compat.version_compat import AIRFLOW_V_3_2_PLUS
+from airflow.providers.common.sql.hooks.handlers import fetch_all_handler
 from airflow.providers.common.sql.hooks.sql import DbApiHook
 from airflow.triggers.base import BaseTrigger, TriggerEvent
 
 if TYPE_CHECKING:
-    from collections.abc import AsyncIterator
+    from collections.abc import AsyncIterator, Callable
     from typing import Any
 
+from collections.abc import (
+    Iterable,
+    Mapping,
+    Sequence,
+)
+
 
 class SQLExecuteQueryTrigger(BaseTrigger):
     """
-    A trigger that executes SQL code in async mode.
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
 
     :param sql: the sql statement to be executed (str) or a list of sql statements to execute
     :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
     :param hook_params: hook parameters
     """
 
     def __init__(
         self,
-        sql: str | list[str],
+        sql: str | Iterable[str],
         conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,

Review Comment:
   Are these supposed to be required? This would break compatibility, and also I think they should have sensible defaults.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "uranusjr (via GitHub) via commits" <co...@airflow.apache.org>.
uranusjr commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r4510110629


##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi:
##########
@@ -179,6 +180,27 @@ class DbApiHook(BaseHook):
     @staticmethod
     def get_openlineage_authority_part(connection, default_port: int | None = None) -> str: ...
     def get_db_log_messages(self, conn) -> None: ...
+    async def aget_conn(self) -> Any: ...
+    @overload
+    async def arun(
+        self,
+        sql: str | Iterable[str],
+        autocommit: bool = ...,
+        parameters: Iterable | Mapping[str, Any] | None = ...,
+        handler: None = ...,
+        split_statements: bool = ...,
+        return_last: bool = ...,
+    ) -> None: ...

Review Comment:
   Missing `read_only` (same for other overloads)



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3506404237


##########
providers/common/sql/src/airflow/providers/common/sql/operators/sql.py:
##########
@@ -552,31 +616,82 @@ def _process_output(
     def _should_run_output_processing(self) -> bool:
         return self.do_xcom_push
 
+    def _get_handler_import_path(self) -> str:

Review Comment:
   I have deleted all the unused code.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "dabla (via GitHub)" <gi...@apache.org>.
dabla commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3507320902


##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py:
##########
@@ -1136,3 +1149,194 @@ def get_db_log_messages(self, conn) -> None:
 
         :param conn: Connection object
         """
+
+    def _translate_sql(self, sql: str) -> str:
+        """
+        Translate SQL to driver-specific paramstyle.
+
+        DB-specific hooks may override this to translate from a canonical style
+        to their driver's paramstyle if you want a unified SQL authoring style.
+        """
+        return sql
+
+    def _prepare_parameters(self, parameters: Iterable | Mapping[str, Any] | None):
+        """DB hooks may override to adapt parameter style."""
+        return parameters
+
+    def _build_conn_kwargs_from_airflow_connection(self, db) -> dict:
+        """Build a DB-agnostic kwargs dict."""
+        extra = {}
+        extra_dejson = getattr(db, "extra_dejson", None)
+        if isinstance(extra_dejson, dict):
+            extra = extra_dejson
+        try:
+            port = int(db.port) if db.port else None
+        except (TypeError, ValueError):
+            port = None
+        return {
+            "host": db.host or "",
+            "port": port,
+            "username": db.login or "",
+            "password": db.password or "",
+            "database": extra.get("database") or extra.get("dbname") or "",
+            "schema": db.schema or "",
+            "conn_type": db.conn_type,
+            "extra": extra,
+            "raw_connection": db,
+        }
+
+    async def async_get_conn(self) -> Any:
+        if not hasattr(self, "_conn_lock"):
+            self._conn_lock = asyncio.Lock()
+        async with self._conn_lock:
+            if not self._connection:
+                self._connection = await sync_to_async(self.get_connection)(self.get_conn_id())
+            db = self._connection
+            if self.connector is None:
+                raise RuntimeError(f"{type(self).__name__} didn't have `self.connector` set!")
+            conn_kwargs = self._build_conn_kwargs_from_airflow_connection(db)
+            return await self.connector.connect(**conn_kwargs)
+
+    async def _call(self, func: Callable, *args, **kwargs) -> Any:
+        if inspect.iscoroutinefunction(func):
+            return await func(*args, **kwargs)
+        return await sync_to_async(func)(*args, **kwargs)
+
+    @asynccontextmanager
+    async def _async_create_autocommit_connection(self, autocommit: bool = False):
+        conn = await self.async_get_conn()
+        try:
+            if self.supports_autocommit:
+                set_autocommit = getattr(conn, "set_autocommit", None)
+                if set_autocommit and inspect.iscoroutinefunction(set_autocommit):
+                    await set_autocommit(autocommit)
+                else:
+                    self.set_autocommit(conn, autocommit)
+            yield conn
+        finally:
+            close = getattr(conn, "aclose", None) or getattr(conn, "close", None)

Review Comment:
   Should the second 



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "kacpermuda (via GitHub)" <gi...@apache.org>.
kacpermuda commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3506489447


##########
providers/common/sql/src/airflow/providers/common/sql/operators/sql.py:
##########
@@ -513,6 +517,7 @@ def __init__(
         return_last: bool = True,
         show_return_value_in_logs: bool = False,
         requires_result_fetch: bool = False,
+        deferrable: bool = conf.getboolean("operators", "default_deferrable", fallback=False),

Review Comment:
   Yes, sorry, missed that, I think this might be a good place for adding an exception. in test However, since we might do a major release anyway, maybe this is not needed? Not sure, but I still think not using the conf here may be the right choice.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "dabla (via GitHub)" <gi...@apache.org>.
dabla commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3507630845


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -103,3 +112,142 @@ async def run(self) -> AsyncIterator[TriggerEvent]:
         except Exception as e:
             self.log.exception("An error occurred: %s", e)
             yield TriggerEvent({"status": "failure", "message": str(e)})
+
+
+class SQLExecuteQueryTrigger(BaseTrigger):
+    """
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
+
+    :param sql: the sql statement to be executed (str) or a list of sql statements to execute
+    :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
+    """
+
+    def __init__(
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+    ):
+        super().__init__()
+        self.sql = sql
+        self.conn_id = conn_id
+        self.autocommit = autocommit
+        self.parameters = parameters
+        self.fetch_results = fetch_results
+        self.split_statements = split_statements
+        self.return_last = return_last
+
+    def serialize(self) -> tuple[str, dict[str, Any]]:
+        """Serialize the SQLExecuteQueryTrigger arguments and classpath."""
+        return (
+            f"{self.__class__.__module__}.{self.__class__.__name__}",
+            {
+                "sql": self.sql,
+                "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+            },
+        )
+
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def get_hook(self) -> DbApiHook:
+        """
+        Return DbApiHook.
+
+        :return: DbApiHook for this connection
+        """
+        connection = await sync_to_async(BaseHook.get_connection)(self.conn_id)
+        hook = await sync_to_async(connection.get_hook)()

Review Comment:
   I created a [PR](https://github.com/apache/airflow/pull/69208) which adds this `get_async_hook` method in `common.compat`.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "uranusjr (via GitHub)" <gi...@apache.org>.
uranusjr commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3975785704


##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py:
##########
@@ -1171,3 +1185,220 @@ def get_db_log_messages(self, conn) -> None:
 
         :param conn: Connection object
         """
+
+    def _translate_sql(self, sql: str) -> str:
+        """
+        Translate SQL to driver-specific paramstyle.
+
+        DB-specific hooks may override this to translate from a canonical style
+        to their driver's paramstyle if you want a unified SQL authoring style.
+        """
+        return sql
+
+    def _prepare_parameters(self, parameters: Iterable | Mapping[str, Any] | None):
+        """DB hooks may override to adapt parameter style."""
+        return parameters
+
+    def _build_conn_kwargs_from_airflow_connection(self, db) -> dict:
+        """Build a DB-agnostic kwargs dict."""
+        extra = {}
+        extra_dejson = getattr(db, "extra_dejson", None)
+        if isinstance(extra_dejson, dict):
+            extra = extra_dejson
+        try:
+            port = int(db.port) if db.port else None
+        except (TypeError, ValueError):
+            port = None
+        return {
+            "host": db.host or "",
+            "port": port,
+            "username": db.login or "",
+            "password": db.password or "",
+            "database": extra.get("database") or extra.get("dbname") or "",
+            "schema": db.schema or "",
+            "conn_type": db.conn_type,
+            "extra": extra,
+            "raw_connection": db,
+        }
+
+    async def aget_conn(self) -> Any:

Review Comment:
   `arun` only runs when a subclass overrides `aget_conn`. Postgres overrides it fully and never calls this base version, so this base `aget_conn` and everything else related ship untested against any real driver.
   
   Since this is public common.sql API that's costly to change later, could we trim it to what Postgres actually uses instead?



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r4223820273


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -17,37 +17,64 @@
 # under the License.
 from __future__ import annotations
 
-from typing import TYPE_CHECKING
+from typing import TYPE_CHECKING, cast
 
-from airflow.providers.common.compat.sdk import AirflowException, BaseHook
+from asgiref.sync import sync_to_async
+
+from airflow.providers.common.compat.hook import get_async_hook
 from airflow.providers.common.compat.version_compat import AIRFLOW_V_3_2_PLUS
+from airflow.providers.common.sql.hooks.handlers import fetch_all_handler
 from airflow.providers.common.sql.hooks.sql import DbApiHook
 from airflow.triggers.base import BaseTrigger, TriggerEvent
 
 if TYPE_CHECKING:
-    from collections.abc import AsyncIterator
+    from collections.abc import AsyncIterator, Callable
     from typing import Any
 
+from collections.abc import (
+    Iterable,
+    Mapping,
+    Sequence,
+)
+
 
 class SQLExecuteQueryTrigger(BaseTrigger):
     """
-    A trigger that executes SQL code in async mode.
+    A SQL trigger that executes SQL code in async mode.
+
+    The query runs in the triggerer, but no user code does: when ``fetch_results`` is set the rows are
+    fetched with the built-in :func:`fetch_all_handler` and returned, together with the cursor
+    descriptions, in the ``TriggerEvent``. Any user-provided ``handler`` is applied on the worker in
+    ``SQLExecuteQueryOperator.execute_complete`` -- keeping user code out of the triggerer's event loop
+    and out of its (bundle-less) import path.
 
     :param sql: the sql statement to be executed (str) or a list of sql statements to execute
     :param conn_id: the connection ID used to connect to the database
+    :param fetch_results: whether the query results should be fetched and returned to the worker
     :param hook_params: hook parameters
     """
 
     def __init__(
         self,
-        sql: str | list[str],
+        sql: str | Iterable[str],
         conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,

Review Comment:
   Defaults have been added.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r4223817887


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.pyi:
##########
@@ -29,16 +29,25 @@
 #
 """Definition of the public interface for airflow.providers.common.sql.triggers.sql."""
 
-from collections.abc import AsyncIterator
+from collections.abc import AsyncIterator, Iterable, Mapping
 from typing import Any
 
 from airflow.providers.common.sql.hooks.sql import DbApiHook as DbApiHook
 from airflow.triggers.base import BaseTrigger as BaseTrigger, TriggerEvent as TriggerEvent
 
 class SQLExecuteQueryTrigger(BaseTrigger):
     def __init__(
-        self, sql: str | list[str], conn_id: str, hook_params: dict | None = None, **kwargs
+        self,
+        sql: str | Iterable[str],
+        conn_id: str,
+        autocommit: bool,
+        split_statements: bool,
+        return_last: bool,
+        parameters: Iterable[Any] | Mapping[str, Any] | None = None,
+        fetch_results: bool = False,
+        read_only: bool = False,
+        hook_params: dict | None = None,

Review Comment:
   I have made them kwargs only.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r4354014405


##########
providers/common/sql/src/airflow/providers/common/sql/triggers/sql.py:
##########
@@ -57,49 +84,160 @@ def serialize(self) -> tuple[str, dict[str, Any]]:
             {
                 "sql": self.sql,
                 "conn_id": self.conn_id,
+                "autocommit": self.autocommit,
+                "parameters": self.parameters,
+                "fetch_results": self.fetch_results,
+                "split_statements": self.split_statements,
+                "return_last": self.return_last,
+                "read_only": self.read_only,
                 "hook_params": self.hook_params,
             },
         )
 
-    def get_hook(self) -> DbApiHook:
+    @staticmethod
+    def _jsonsafe_descriptions(
+        descriptions: list[Sequence[Sequence] | None],
+    ) -> list[list[list[Any]] | None]:
+        """
+        Normalise cursor descriptions into a JSON-serializable form for the ``TriggerEvent``.
+
+        ``cursor.description`` is a sequence of column 7-tuples whose ``type_code`` can be a
+        driver-specific object that is not JSON-serializable; such values are stringified while
+        JSON-native fields (column names, sizes, precision, ...) are preserved.
+        """
+        safe: list[list[list[Any]] | None] = []
+        for description in descriptions:
+            if description is None:
+                safe.append(None)
+                continue
+            safe.append(
+                [
+                    [
+                        field if isinstance(field, (str, int, float, bool, type(None))) else str(field)
+                        for field in column
+                    ]
+                    for column in description
+                ]
+            )
+        return safe
+
+    async def aget_hook(self) -> DbApiHook:
         """
         Return DbApiHook.
 
         :return: DbApiHook for this connection
         """
-        connection = BaseHook.get_connection(self.conn_id)
-        hook = connection.get_hook(hook_params=self.hook_params)
+        hook = await get_async_hook(self.conn_id, hook_params=self.hook_params)
         if not isinstance(hook, DbApiHook):
-            raise AirflowException(
-                f"You are trying to use `common-sql` with {hook.__class__.__name__},"
-                " but its provider does not support it. Please upgrade the provider to a version that"
-                " supports `common-sql`. The hook class should be a subclass of"
-                f" `{hook.__class__.__module__}.{hook.__class__.__name__}`."
-                f" Got {hook.__class__.__name__} hook with class hierarchy: {hook.__class__.mro()}"
+            raise TypeError(
+                f"You are trying to use the SqlExecuteQueryOperator in deferrable mode with {hook.__class__.__name__},"
+                " but its provider does not support this. Please set deferrable=False"
+                f" Got {hook.__class__.__name__} with class hierarchy: {hook.__class__.mro()}"
+            )
+        if self.read_only and not (hook.supports_async_execution() and hook.supports_readonly_execution()):
+            raise NotImplementedError(
+                f"{hook.__class__.__name__} does not support read-only execution, so it cannot run a"
+                " deferred query safely (a triggerer restart could re-run it). Set"
+                " enforce_read_only=False to run without the read-only guard if the query is"
+                " idempotent, or deferrable=False to run it on the worker."
             )
         return hook
 
-    async def _get_records(self) -> Any:
-        from asgiref.sync import sync_to_async
-
-        hook = self.get_hook()
+    async def _run_query(self, hook: DbApiHook, fetch_results: bool):
+        """
+        Run the query against ``hook``, using its native async driver if available.
 
+        Hooks without a real async driver (:meth:`DbApiHook.supports_async_execution`) fall back to
+        running the synchronous :meth:`DbApiHook.run` in a worker thread, matching the compatibility the
+        `GenericTransfer` operator has always relied on for arbitrary DB-specific hooks.
+        """
+        if hook.supports_async_execution():
+            if fetch_results:
+                return await hook.arun(
+                    sql=self.sql,
+                    autocommit=self.autocommit,
+                    parameters=self.parameters,
+                    handler=fetch_all_handler,
+                    split_statements=self.split_statements,
+                    return_last=self.return_last,
+                    read_only=self.read_only,
+                )
+            return await hook.arun(
+                sql=self.sql,
+                autocommit=self.autocommit,
+                parameters=self.parameters,
+                handler=None,
+                split_statements=self.split_statements,
+                return_last=self.return_last,
+                read_only=self.read_only,
+            )
         if AIRFLOW_V_3_2_PLUS:
             # This is only supported from Airflow 3.2 or higher due to added async support in CommsDecoder
-            return await sync_to_async(hook.get_records)(self.sql)
-        return hook.get_records(self.sql)
+            # `sync_to_async` erases `run`'s overloads, so it is cast to a plain callable first.
+            arun_in_thread = sync_to_async(cast("Callable[..., Any]", hook.run))
+            if fetch_results:
+                return await arun_in_thread(
+                    sql=self.sql,
+                    autocommit=self.autocommit,
+                    parameters=self.parameters,
+                    handler=fetch_all_handler,
+                    split_statements=self.split_statements,
+                    return_last=self.return_last,
+                )
+            return await arun_in_thread(
+                sql=self.sql,
+                autocommit=self.autocommit,
+                parameters=self.parameters,
+                handler=None,
+                split_statements=self.split_statements,
+                return_last=self.return_last,
+            )
+        if fetch_results:
+            return hook.run(
+                sql=self.sql,
+                autocommit=self.autocommit,
+                parameters=self.parameters,
+                handler=fetch_all_handler,
+                split_statements=self.split_statements,
+                return_last=self.return_last,
+            )
+        return hook.run(
+            sql=self.sql,
+            autocommit=self.autocommit,
+            parameters=self.parameters,
+            handler=None,
+            split_statements=self.split_statements,
+            return_last=self.return_last,
+        )
 
     async def run(self) -> AsyncIterator[TriggerEvent]:
         try:
+            hook = await self.aget_hook()
+
             self.log.info("Extracting data from %s", self.conn_id)
             self.log.info("Executing: \n %s", self.sql)
-            self.log.info("Reading records from %s", self.conn_id)
 
-            results = await self._get_records()
+            if self.fetch_results:
+                # Fetch the raw rows with the built-in handler and return them with the cursor
+                # descriptions; the operator applies any user handler on the worker.
+                results = await self._run_query(hook, fetch_results=True)
+
+                self.log.info("Executing query from %s done!", self.conn_id)
+                self.log.debug("results: %s", results)
+                yield TriggerEvent(
+                    {
+                        "status": "success",
+                        "results": results,

Review Comment:
   I ran some tests and found that bytes serialization fails because serde doesn't cover it. I will raise an error if this data type is returned.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3991747024


##########
providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py:
##########
@@ -1171,3 +1185,220 @@ def get_db_log_messages(self, conn) -> None:
 
         :param conn: Connection object
         """
+
+    def _translate_sql(self, sql: str) -> str:
+        """
+        Translate SQL to driver-specific paramstyle.
+
+        DB-specific hooks may override this to translate from a canonical style
+        to their driver's paramstyle if you want a unified SQL authoring style.
+        """
+        return sql
+
+    def _prepare_parameters(self, parameters: Iterable | Mapping[str, Any] | None):
+        """DB hooks may override to adapt parameter style."""
+        return parameters
+
+    def _build_conn_kwargs_from_airflow_connection(self, db) -> dict:
+        """Build a DB-agnostic kwargs dict."""
+        extra = {}
+        extra_dejson = getattr(db, "extra_dejson", None)
+        if isinstance(extra_dejson, dict):
+            extra = extra_dejson
+        try:
+            port = int(db.port) if db.port else None
+        except (TypeError, ValueError):
+            port = None
+        return {
+            "host": db.host or "",
+            "port": port,
+            "username": db.login or "",
+            "password": db.password or "",
+            "database": extra.get("database") or extra.get("dbname") or "",
+            "schema": db.schema or "",
+            "conn_type": db.conn_type,
+            "extra": extra,
+            "raw_connection": db,
+        }
+
+    async def aget_conn(self) -> Any:
+        if not hasattr(self, "_conn_lock"):
+            self._conn_lock = asyncio.Lock()
+        async with self._conn_lock:
+            if not self._connection:
+                self._connection = await get_async_connection(self.get_conn_id())
+            db = self._connection
+            if self.connector is None:
+                raise RuntimeError(f"{type(self).__name__} didn't have `self.connector` set!")
+            conn_kwargs = self._build_conn_kwargs_from_airflow_connection(db)
+            return await self.connector.connect(**conn_kwargs)
+
+    async def _call(self, func: Callable, *args, **kwargs) -> Any:
+        if inspect.iscoroutinefunction(func):
+            return await func(*args, **kwargs)
+        return await sync_to_async(func)(*args, **kwargs)
+
+    @asynccontextmanager
+    async def _acreate_autocommit_connection(self, autocommit: bool = False):
+        conn = await self.aget_conn()
+        try:
+            if self.supports_autocommit:
+                set_autocommit = getattr(conn, "set_autocommit", None)
+                if set_autocommit and inspect.iscoroutinefunction(set_autocommit):
+                    await set_autocommit(autocommit)
+                else:
+                    self.set_autocommit(conn, autocommit)
+            yield conn
+        finally:
+            close = getattr(conn, "aclose", None) or getattr(conn, "close", None)
+            if close:
+                await self._call(close)
+
+    @asynccontextmanager
+    async def _aget_cursor(self, conn):
+        cursor = getattr(conn, "cursor", None)
+        if cursor is None:
+            raise TypeError(
+                f"{type(conn).__name__} has no cursor() method. "
+                "Override _aget_cursor in the DB-specific hook."
+            )
+        cur_or_cm = cursor()
+        if inspect.isawaitable(cur_or_cm):
+            cur_or_cm = await cur_or_cm
+        if hasattr(cur_or_cm, "__aenter__") and hasattr(cur_or_cm, "__aexit__"):
+            async with cur_or_cm as cur:
+                yield cur
+            return
+        execute = getattr(cur_or_cm, "execute", None)
+        if execute and inspect.iscoroutinefunction(execute):
+            try:
+                yield cur_or_cm
+            finally:
+                close = getattr(cur_or_cm, "aclose", None) or getattr(cur_or_cm, "close", None)
+                if close:
+                    if inspect.iscoroutinefunction(close):
+                        await close()
+                    else:
+                        close()
+            return
+        raise RuntimeError(
+            f"Unsupported cursor type returned by {type(conn).__name__}. "
+            "Override _aget_cursor in the DB-specific hook."
+        )
+
+    def supports_async_execution(self) -> bool:
+        """Whether the DB-specific hook overrides :meth:`aget_conn` for a real async driver."""
+        return type(self).aget_conn is not DbApiHook.aget_conn
+
+    def supports_readonly_execution(self) -> bool:
+        """Whether the DB-specific hook overrides :meth:`_aenter_read_only`."""
+        return type(self)._aenter_read_only is not DbApiHook._aenter_read_only
+
+    async def _aenter_read_only(self, conn):
+        """
+        Put the opened async connection in read-only mode.
+
+        Called by :meth:`arun` before any statement runs, whenever the operator
+        requests read-only execution. The base implementation raises an exception
+        if this method is not implemented in the DB-specific hook.
+        """
+        raise NotImplementedError(
+            f"{type(self).__name__} does not implement read-only execution. Override "
+            "_aenter_read_only to support deferrable read-only queries for this database,"

Review Comment:
   It is a typo.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub)" <gi...@apache.org>.
karenbraganz commented on code in PR #65618:
URL: https://github.com/apache/airflow/pull/65618#discussion_r3383859685


##########
providers/common/sql/src/airflow/providers/common/sql/operators/sql.py:
##########
@@ -513,6 +517,7 @@ def __init__(
         return_last: bool = True,
         show_return_value_in_logs: bool = False,
         requires_result_fetch: bool = False,
+        deferrable: bool = conf.getboolean("operators", "default_deferrable", fallback=False),

Review Comment:
   Sounds good to me! I will make the required changes.



-- 
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] Create deferrable SqlExecuteQueryOperator compatible with Postgres [airflow]

Posted by "karenbraganz (via GitHub) via commits" <co...@airflow.apache.org>.
karenbraganz commented on PR #65618:
URL: https://github.com/apache/airflow/pull/65618#issuecomment-6597343564

   Rebased on main to resolve conflicts


-- 
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