[PATCH] Fix whole-row type errors in postgres_fdw UPDATE joins

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.

[1] https://www.postgresql.org/message-id/flat/3518c50a-ab18-482f-b916-a37263622501(at)deepbluecap(dot)com

[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

Browse pgsql-hackers by date

  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