sohailshk opened a new pull request, #76962:
URL: https://github.com/apache/airflow/pull/76962
`PostgresDialect.get_primary_keys` and `get_column_names` query
`information_schema` with `table_schema = %s`. For a table name without a
schema prefix (`orders` instead of `public.orders`) the schema is `None`, so
the query compares against `NULL` and finds nothing. As a result
`PostgresHook.insert_rows("orders", rows, target_fields=[...], replace=True)`
fails with `PostgreSQL ON CONFLICT upsert syntax requires an unique index`
although the table has a primary key, and `get_target_fields` returns an empty
list. For `get_column_names` this is a regression from #54437, which replaced
the SQLAlchemy inspector with these queries.
The queries now use `coalesce(%s, current_schema())`: an unqualified name is
looked up in `current_schema()`, the first existing schema on the connection's
`search_path` (`public` by default). A table that lives in a later schema of
the `search_path` is still not found, which is no worse than before. Qualified
names behave as before. The docstring of `get_primary_keys` said "public by
default"; it now says "the current schema by default".
Side effect: `get_target_fields` for an unqualified table now returns the
real columns (identity and generated columns excluded), as it already does for
qualified names. This affects the opt-in `[core]
dbapihook_resolve_target_fields` and `SQLInsertRowsOperator.columns` for
unqualified tables.
Checked against a PostgreSQL 18 server:
- main: `get_primary_keys("orders")` returns `None`,
`get_column_names("orders")` returns `[]`, and the upsert above raises the
`unique index` error.
- with this change: `['id']`, `['id', 'name', 'hits']`, and the upsert
succeeds. `coalesce(%s, current_schema())` with a `None` and with a string
parameter also returns the primary key through psycopg 3.
- Unit test `test_unqualified_table_resolves_current_schema` (both methods)
and the real-database test
`TestPostgresHook::test_insert_rows_replace_unqualified_table` (creates a table
with a primary key in the default schema, upserts the same key twice, expects
one row with the updated value) fail on main and pass here. The real-database
test ran against the scratch server through a local redirect of the `postgres`
host; `uv run --project providers/postgres pytest
providers/postgres/tests/unit/postgres` then shows 193 passed, 22 skipped and 2
failures that are unrelated (SQLAlchemy/psycopg2 connections to the breeze host
`postgres`, also failing on main).
I did not run mypy or the other backend tests (Docker is not available to
me).
---
##### Was generative AI tooling used to co-author this PR?
- [X] Yes (please specify the tool below)
The implementation and tests were written with AI assistance.
Generated-by: AI coding assistant following [the
guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions)
--
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]
