Re: Odd system-column handling in postgres_fdw join pushdown patch

On 2016/03/25 13:37, Ashutosh Bapat wrote:
> A much simpler solution, that will work with postgres_fdw, might be to
> just deparse these columns with whatever random values (except for
> tableoid) they are expected to have in those places. Often these values
> can simply be NULL or 0. For tableoid deparse it to 'oid value'::oid.
> Thus for a user query
>
> select t1.taleoid, t2.xmax, t1.c1, t2.c2 from t1 join t2 on (...) ... --
> where t1 and t2 are foreign tables with same names on the foreign server.
>
> the query sent to the foreign server would look like
>
> select '15623'::oid, NULL, t1.c1, t2.c2 from t1 join t2 on (...) ... --
> where '15623' is oid of t1 on local server.
>
> This does spend more bandwidth than necessary and affect performance,
> here is why the approach might be better,
> 1. It's not very common to request these system columns in a "join"
> query involving foreign tables. Usually they will have user columns or
> ctid (DMLs) but very rarely other system columns.

That may be true for now, but once we implement pair-wise join for two
distributedly-partitioned tables in which we can push down pair-wise
foreign joins, tableoid would be used in many cases, to identify child
tables for rows to come from.

> 2. This allows expressions involving these system columns to be pushed
> down, whenever we will start pushing them down in the targetlist.
>
> 3. The changes to the code are rather small. deparseColumnRef() will
> need to produce the strings above instead of actual column names.
>
> 4. The approach will work with slight change, if and when, we need the
> actual system column values from the foreign server. That time the above
> function needs to deparse the column names instead of constant values.

As you pointed out, spending more bandwidth than necessary seems a bit
inefficient.

The approach that we discussed would minimize the code for the FDW
author to write, by providing the support functions you proposed. I'll
post a patch for that early next week. (It would also minimize the
patch to push down UPDATE/DELETE on a foreign join, proposed in [1],
which has the same issue as for handling system columns in a RETURNING
clause in such pushed-down UPDATE/DELETE. So I'd like to propose that
approach as a common functionality.)

> Sorry for bringing this solution late to the table.

No problem.

Best regards,
Etsuro Fujita

[1] http://www.postgresql.org/message-id/56D57C4A.9000500@lab.ntt.co.jp