Re: Postgres_fdw join pushdown - wrong results with whole-row reference

On Fri, Jun 24, 2016 at 1:59 PM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp
> wrote:

> On 2016/06/24 15:44, Ashutosh Bapat wrote:
> >>
> >> I think the proposed idea of applying record::text explicit coercion to
> a
> >> whole-row reference in the IS NOT NULL condition in the CASE WHEN
> >> conversion would work as expected as you explained, but I'm concerned
> that
> >> the cost wouldn't be negligible when the foreign table has a lot of
> columns.
> >
> > That's right, if the foreign server doesn't optimize the case for IS NOT
> > NULL, which it doesn't :)
> >
> > I am happy to use any cheaper means e.g a function which counts number of
> > columns in a record. All we need here is a way to correctly identify
> when a
> > record is null and not null in the way we want (as described upthread). I
> > didn't find any quickly. Do you have any suggestions?
>
> I'm now starting to wonder if it would be outright wrong to just use the
> alias names of corresponding foreign tables directly for whole-row
> references? So, instead of these in target lists of remote queries:
>
> SELECT CASE WHEN (r1.*)::text IS NOT NULL THEN *ROW (r1.*)* END, ...
>
>
This is wrong. The deparsed query looks like
SELECT CASE WHEN (r1.*)::text IS NOT NULL THEN *ROW (r1.col1, r1.col2, ...)*
END,

The reason for this is that the foreign table definition may not match the
target table definition. This has been explained in the comments that you
have deleted in your patch. Am I missing something?

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company