13129 opened a new issue, #76455: URL: https://github.com/apache/airflow/issues/76455
### Under which category would you file this issue? Airflow Core ### Apache Airflow version 3.3.2 ### What happened and how to reproduce it? https://github.com/apache/airflow/discussions/76275#discussion-11205219 ## Background With the migration from Psycopg 2 to Psycopg 3, PostgreSQL connections may use server-side parameter binding by default. This is generally beneficial and works well for common DML statements such as `SELECT`, `INSERT`, `UPDATE`, and `DELETE`. However, it may introduce compatibility issues for some existing SQL scripts, particularly parameterized DDL statements. For example, consider a SQL script executed through `SQLExecuteQueryOperator`: ```sql CREATE TEMP TABLE tmp_result AS SELECT * FROM source_table WHERE create_time >= %(start_time)s::timestamp AND create_time < %(end_time)s::timestamp; ``` In some Psycopg 3 execution paths, this can fail with an error similar to: ```text psycopg.errors.UndefinedParameter: there is no parameter $1 ``` The same SQL may have worked with Psycopg 2, where parameters were adapted on the client side. ## Why this matters Airflow is widely used for data engineering workflows, where SQL scripts often contain: - `CREATE TABLE AS SELECT` (CTAS) - Temporary table creation and population - Multiple SQL statements executed in sequence - Parameterized DDL and utility statements For users migrating existing DAGs to newer Airflow and PostgreSQL provider versions, this behavior may require rewriting SQL scripts even when the SQL itself is valid for PostgreSQL. This is particularly relevant to users who use `SQLExecuteQueryOperator` to manage SQL-based ETL pipelines. ## Questions for the community I'd like to hear the community's thoughts on a few questions: 1. Have other users encountered compatibility issues with parameterized DDL statements after moving to Psycopg 3? 2. Is this an expected limitation of Psycopg 3's server-side binding, or should Airflow provide an abstraction to handle these cases? 3. Would it make sense for `PostgresHook` to expose an optional client-side binding mode using Psycopg 3's `ClientCursor`? 4. Should this configuration be available through `PostgresHook`, connection extras, or `SQLExecuteQueryOperator`? ## Possible approach One possible solution would be to allow users to opt into client-side parameter binding while keeping server-side binding as the default. Psycopg 3 already provides `ClientCursor` for client-side binding, so Airflow might be able to expose this capability without changing the default behavior. For example, a hypothetical configuration could look like: ```python PostgresHook( postgres_conn_id="postgres_default", client_side_binding=True, ) ``` This is only an illustrative API proposal, not an existing Airflow parameter. ## What I'd like to understand Before proposing an implementation or opening a bug report, I'd like to understand whether this is a broader compatibility concern for Airflow users and whether the community considers an optional client-side binding mode a useful addition. Any feedback or alternative approaches would be appreciated. ### What you think should happen instead? _No response_ ### Operating System _No response_ ### Deployment None ### Apache Airflow Provider(s) _No response_ ### Versions of Apache Airflow Providers _No response_ ### Official Helm Chart version Not Applicable ### Kubernetes Version _No response_ ### Helm Chart configuration _No response_ ### Docker Image customizations _No response_ ### Anything else? _No response_ ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
