GitHub user Dev-iL added a comment to the discussion: Discussion: Should Airflow provide a compatibility option for Psycopg 3 client-side parameter binding?
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](https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#server-side-binding), 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 is important here. The provider’s [7.0.0 changelog](https://github.com/apache/airflow/blob/1ddcaff9e322cbd3534c4f23e2cf5c4c84f920ce/providers/postgres/docs/changelog.rst) describes `PostgresHook` selecting psycopg3 when it is available with SQLAlchemy 2, and says there is currently no connection or configuration option to keep hook connections on psycopg2. Installing psycopg2 or selecting it for the metadata database does not by itself change that hook selection. For affected deployments, 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](https://www.psycopg.org/psycopg3/docs/advanced/cursors.html). 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](https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#using-a-different-cursor-class), 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`](https://docs.sqlalchemy.org/en/21/core/connections.html#engine-stream-results), also available in [2.0](https://docs.sqlalchemy.org/en/20/core/connections.html#engine-stream-results), 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 GitHub link: https://github.com/apache/airflow/discussions/76275#discussioncomment-19368014 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected]
