I wonder whether such a whole-row-var would arise from the nullable side of a join? I guess not. Not that I'm saying we shouldn't account for that case at all since any and every whole-row-var in the targetlist currently gets that treatment, even those that are known non-nullable. Couldn't we have prevented the latter somehow? IOW, only generate the CASE WHEN when a Var being deparsed is known nullable as the comment there says:
deparse.c:
1639 /* 1640 * In case the whole-row reference is under an outer join then it has 1641 * to go NULL whenver the rest of the row goes NULL. Deparsing a join 1642 * query would always involve multiple relations, thus qualify_col 1643 * would be true. 1644 */ 1645 if (qualify_col) 1646 { 1647 appendStringInfoString(buf, "CASE WHEN"); 1648 ADD_REL_QUALIFIER(buf, varno); 1649 appendStringInfo(buf, "* IS NOT NULL THEN "); 1650 }
I think we could address this in another way once we support deparsing subqueries; rewrite the remote query into something that wouldn't need the CASE WHEN conversion. For example, we currently have:
postgres=# explain verbose select ft2 from ft1 left join ft2 on ft1.a = ft2.a; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan (cost=100.00..110.04 rows=1 width=32) Output: ft2.* Relations: (public.ft1) LEFT JOIN (public.ft2) Remote SQL: SELECT CASE WHEN r2.* IS NOT NULL THEN ROW(r2.a, r2.b) END FROM (public.t1 r1 LEFT JOIN public.t2 r2 ON (((r1.a = r2.a)))) (4 rows)
However, if we support deparsing subqueries, the remote query in the above example could be rewritten into something like this:
SELECT ss.c2 FROM t1 LEFT JOIN (SELECT t2.a, ROW(a, b) FROM t2) ss(c1, c2) ON (t1.a = ss.c1);
So we would no longer need "CASE WHEN r2.* IS NOT NULL THEN ROW(r2.a, r2.b) END" in the target list in the remote query.
Right. Although, it means that the query processor at the other end has to do extra work for pulling up the subqueries.
For the CASE WHEN conversion for a system column other than ctid, we could also address this by replacing the whole-row reference in the IS NOT NULL condition in that conversion with the system column reference.
That would not work again as the system column reference would make sense locally but may not be available at the foreign server e.g. foreign table targeting a view a tableoid is requested.
--
Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
