From: | Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Odd system-column handling in postgres_fdw join pushdown patch |
Date: | 2016-03-25 08:16:21 |
Message-ID: | 56F4F3D5.40203@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rahila Syed | 2016-03-25 08:51:51 | Re: [PROPOSAL] VACUUM Progress Checker. |
Previous Message | Matthias Kurz | 2016-03-25 08:13:50 | Re: Alter or rename enum value |