Hi, I found that an UPDATE referencing the target's whole row in its SET expression can fail when postgres_fdw pushes down a join.
For example, with ft1 and ft2 on the same foreign server: ``` UPDATE ft1 t SET v = length(t::text) FROM ft2 s WHERE t.id = s.id; ``` fails with: ``` ERROR: attribute 1 of type record has wrong type DETAIL: Table has type ft1, but query expects record. ``` The scan needs two whole-row Vars for `t`: one with the named composite type of `ft1`, used by the SET expression, and another with RECORDOID, used for row identity. `search_indexed_tlist_for_var()` currently matches Vars by `varno` and `varattno` only. As a result, the two whole-row Vars can both be mapped to the same output column while retaining their different `vartype`s. If that output column has type `ft1`, the row-identity Var still expects RECORDOID, and the executor reports the type mismatch when evaluating it. This is related to the whole-row type mismatch discussed in [1] and fixed by f4e7756ef9a. That fix made whole-row Var construction consistent before and after view expansion or function inlining. In this case, however, the row-identity Var intentionally has RECORDOID so that identity columns can be shared among inheritance children, as discussed in [2]. The attached patch makes `search_indexed_tlist_for_var()` also compare `vartype`, similarly to `tlist_member_match_var()`. It also changes `add_vars_to_targetlist()` to preserve both whole-row Vars. Its current attribute-number-only deduplication would otherwise omit one of the types from the base relation's target list, leaving the local EPQ plan unable to supply both outputs. The regression test covers both `row_to_json(t)` and `t::text` in UPDATE expressions with a pushed-down join. I reproduced the problem on PostgreSQL 17.11 and a local 20devel build, and tested the patch on 20devel. The postgres_fdw regression tests and EPQ isolation tests pass with the patch. Thoughts and comments are welcome. [1] https://www.postgresql.org/message-id/flat/[email protected] [2] https://www.postgresql.org/message-id/flat/[email protected] Regards, Ziming Zhang
0001-Fix-whole-row-type-errors-in-postgres_fdw-UPDATE-joins.patch
Description: 0001-Fix-whole-row-type-errors-in-postgres_fdw-UPDATE-joins.patch
