GitHub user 13129 created a discussion: Discussion: Should Airflow provide a
compatibility option for Psycopg 3 client-side parameter binding?
## 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.
GitHub link: https://github.com/apache/airflow/discussions/76275
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to: [email protected]
