| From: | Ziming Zhang <toren(dot)zhang(at)outlook(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | [PATCH] Fix whole-row type errors in postgres_fdw UPDATE joins |
| Date: | 2026-10-23 04:15:25 |
| Message-ID: | TY4P301MB1730B0D25C9D3556D926F54AEF642@TY4P301MB1730.JPNP301.PROD.OUTLOOK.COM |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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.
[2] https://www.postgresql.org/message-id/flat/7817fb9ebd6661cdf9b67dec6e129a78(at)postgrespro(dot)ru
Regards,
Ziming Zhang
| Attachment | Content-Type | Size |
|---|---|---|
| 0001-Fix-whole-row-type-errors-in-postgres_fdw-UPDATE-joins.patch | application/octet-stream | 9.8 KB |
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | Prashant Chinnam | 2026-10-23 03:52:35 | Re: [DEPARSE] AT TIME ZONE and similar as a function in FROM do not read back |