* I think we should avoid modifying the pathtarget, because it would be the default pathtarget, which other paths might reference. I think it’s safe to use a copied pathtarget, like the attached.
Yeah, that's right. The EPQ path is a shallow copy of the original. A copied pathtarget should be saner here.
* I think this issue occurs also when there are PlaceHolderVars in the relation’s reltarget. Here is an example:
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM local_tbl LEFT JOIN (SELECT ft1.*, COALESCE(ft1.c3 || ft2.c3, 'foobar') FROM ft1 INNER JOIN ft2 ON (ft1.c1 = ft2.c1 AND ft1.c1 < 100)) ss ON (local_tbl.c1 = ss.c1) ORDER BY local_tbl.c1 FOR UPDATE OF local_tbl; ERROR: variable not found in subplan target list
where local_tbl, ft1, and ft2 are local/foreign tables defined as in postgres_fdw.sql. To fix, I modified the patch so that we add to the pathtarget not only columns required for evaluating the local conditions but columns required for evaluating the PlaceHoderVars.
Correct. Good catch! I can reproduce this error regarding PHVs in master and verify that this change can fix it. It makes the logic of adjusting the pathtarget for the epq_path in add_paths_with_pathkeys_for_rel be more consistent with the logic in build_tlist_to_deparse, which is good.
* The test case reported in this thread produces the planner error only in v14 and later, but I think this issue exists since v9.6. So I created/added new test cases, including the above one, that would otherwise produce the error even in previous versions.