Should Airflow provide a compatibility option for Psycopg 3 client-side parameter binding? #76455

Background

With the migration from Psycopg 2 to Psycopg 3, PostgreSQL connections may use server-side parameter binding by default.

While server-side binding works well for common DML statements such as SELECT, INSERT, UPDATE, and DELETE, it introduces compatibility issues when executing parameterized statements in certain DDL and utility contexts.

For example, consider the following SQL executed through SQLExecuteQueryOperator:

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 statement may fail with an error similar to:

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.

Psycopg documents this limitation and provides alternatives, including ClientCursor, which performs client-side parameter binding:

https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#server-side-binding

Problem

This behavior can affect existing Airflow DAGs that rely on parameterized SQL scripts, particularly:

• CREATE TABLE AS SELECT (CTAS).
• Temporary table creation and population.
• Parameterized DDL and PostgreSQL utility statements.
• SQL scripts containing multiple statements.

This is particularly relevant to users who use SQLExecuteQueryOperator to manage SQL-based ETL pipelines.

There is also an important distinction between the SQLAlchemy and direct DB-API connection paths in PostgresHook.

Starting with PostgreSQL provider 7.1.0, sqlalchemy_scheme allows users to select a SQLAlchemy dialect such as postgresql+psycopg2 for SQLAlchemy engines created by the hook ([#72000](#72000)).

However, this setting does not control the driver used by the direct DB-API connection returned by PostgresHook.get_conn(), which is normally used by SQLExecuteQueryOperator.

According to the PostgreSQL provider changelog, there is currently no equivalent connection or configuration option to explicitly select Psycopg 2 for this direct connection path.

Consequently, configuring sqlalchemy_scheme or installing Psycopg 2 does not necessarily resolve compatibility issues in the execution path used by SQLExecuteQueryOperator.

Expected Behavior

It would be useful to provide an optional client-side parameter binding mode when using Psycopg 3.

Psycopg 3 provides ClientCursor, which supports client-side parameter binding and may help address these compatibility issues.

One possible approach would be to expose an optional configuration through PostgresHook.

Since SQLExecuteQueryOperator already supports hook_params, users could enable this behavior through the existing generic mechanism:

SQLExecuteQueryOperator(
    task_id="create_temp_table",
    conn_id="postgres_default",
    sql="create_temp_table.sql",
    parameters={
        "start_time": "...",
        "end_time": "...",
    },
    hook_params={
        "client_side_binding": True,
    },
)

This would allow PostgresHook to control the cursor implementation without introducing PostgreSQL-specific parameters into the generic SQL operator.

A PostgreSQL connection extra could also be considered for users who want to apply this behavior consistently across tasks using the same connection.

The default behavior should remain unchanged to avoid unexpected changes for existing users.

Note: client_side_binding is a proposed configuration option and does not currently exist.

Implementation Considerations

There are several aspects worth considering when evaluating this approach:

1. Existing cursor configuration

   The PostgreSQL provider already supports cursor-related connection extras, including row factory configuration under Psycopg 3. Any new configuration should remain compatible with existing cursor settings.

2. Transaction behavior

   Switching cursor implementations should not introduce unexpected changes to transaction handling or commit behavior.

3. Parameter compatibility

   ClientCursor has documented behavioral differences and limitations compared with the default Psycopg 3 cursor, including prepared statement and binary parameter/result support.

4. Multiple statements

   SQL scripts containing multiple statements and parameters should be considered. Splitting statements may address restrictions on executing multiple parameterized commands in one call, but it does not resolve parameters inside CTAS statements themselves.

5. Streaming queries

   Streaming queries require special consideration because Psycopg uses named server-side cursors for this execution mode. The behavior should be evaluated separately from ordinary cursor execution.

6. SQLAlchemy execution path

   SQLAlchemy provides its own configuration for using ClientCursor:

   https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#using-a-different-cursor-class

   However, configuring the SQLAlchemy engine alone does not change the direct DB-API execution path normally used by SQLExecuteQueryOperator.

Alternatives and Limitations

There are statement-specific alternatives for some PostgreSQL utility statements.

For example, Psycopg documents set_config() and pg_notify() as alternatives to parameterized SET and NOTIFY statements.

Dynamic SQL identifiers also require safe identifier composition rather than ordinary value parameters.

However, these alternatives do not provide a general solution for existing parameterized SQL scripts, particularly CTAS statements.

Questions

1. Is this a known compatibility concern for users migrating from Psycopg 2 to Psycopg 3?
2. Would supporting ClientCursor through an optional PostgresHook configuration be a reasonable solution?
3. Should this configuration be exposed through hook_params, PostgreSQL connection extras, or both?
4. Are there other approaches that the PostgreSQL provider should consider?

References

• [Psycopg 3: Server-side binding](https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#server-side-binding)
• [Psycopg 3: Cursor types](https://www.psycopg.org/psycopg3/docs/advanced/cursors.html)
• [Airflow PR #72000](Allow keeping PostgresHook SQLAlchemy engines on psycopg2 #72000)
• [PostgreSQL provider changelog](https://github.com/apache/airflow/blob/1ddcaff9e322cbd3534c4f23e2cf5c4c84f920ce/providers/postgres/docs/changelog.rst)
• [SQLAlchemy: Using a different cursor class](https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#using-a-different-cursor-class)
• [Related discussion #76275](Discussion: Should Airflow provide a compatibility option for Psycopg 3 client-side parameter binding? #76275 (comment))