Discussion: Should Airflow provide a compatibility option for Psycopg 3 client-side parameter binding?

[13129]

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:

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:

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:

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.

[Dev-iL]

This looks like an expected Psycopg 3 migration issue and, given the hook’s current driver selection, an opt-in compatibility mode seems useful to consider.

---

PPG3 normally sends parameters separately from the SQL. PostgreSQL does not accept those parameters in several utility and DDL contexts; Psycopg documents the limitation and alternatives, including ClientCursor, which adapts and merges values on the client. It would be useful to verify the exact CTAS example against the affected PostgreSQL and provider versions and include it in an integration test.

The migration path depends on which PostgresHook API is used. Starting in provider 7.1.0, sqlalchemy_scheme can select postgresql+psycopg2 for SQLAlchemy engines created by the hook (#72000). However, that setting does not select the driver for the direct connection returned by get_conn(), which is the path normally used by SQLExecuteQueryOperator. For that path, the provider’s changelog says there is currently no connection or configuration option to force PPG2. Installing PPG2 or selecting it for the metadata database does not change that selection. The documented fallback is to test before upgrading or pin the provider below 7.0.0, subject to the deployment’s other compatibility requirements.

A hook-level option, defaulting to the current behavior, seems like a reasonable place to start. SQLExecuteQueryOperator already passes hook_params to its hook, so an implemented hook option could be used there without adding a PostgreSQL-specific argument to the generic operator. A connection extra may also be valuable for a policy shared by all users of a connection; if both are offered, their precedence should be explicit. The proposed option does not exist today.

Implementation and tests should account for the existing cursor extra, which configures row factories under psycopg3, as well as transaction behavior, parameter types and multiple statements. ClientCursor has documented differences, including no support for prepared statements or binary parameters/results. It should be treated as an explicit compatibility choice, not an exact guarantee of psycopg2 behavior. See Psycopg’s cursor types.

There are also statement-specific alternatives. For example, Psycopg documents set_config() and pg_notify() in place of parameterized SET and NOTIFY. Splitting multiple statements can address the restriction on sending several parameterized commands in one execution, but it does not fix a parameter inside CTAS itself. Airflow’s split execution passes the same parameters to each statement, so scripts should be tested as a whole. Dynamic identifiers require safe identifier composition; they cannot be supplied as value parameters.

SQLAlchemy has its own documented ClientCursor engine configuration, but the operator’s usual execution path uses the hook’s DB-API connection directly, so that engine configuration alone does not change this operator path.

Finally, SQLAlchemy’s stream_results, also available in 2.0, concerns incremental result fetching through a server-side cursor. It does not switch parameter binding to the client and is not a CTAS workaround. In fact, with psycopg, a streamed query uses a named server cursor by default even if the ordinary cursor factory is ClientCursor.

---

Drafted-by: Codex (GPT-6); reviewed by @Dev-iL before posting

[13129]

Thank you for the detailed explanation and technical insights!

This clarifies the differences between server-side and client-side parameter binding in Psycopg 3, as well as the potential compatibility issues introduced by the migration.

I appreciate your time and helpful suggestions. This is very useful for understanding the issue and possible solutions.

Thanks again for your help!