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

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>
Cc: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>, Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Postgres_fdw join pushdown - wrong results with whole-row reference
Date: 2016-06-24 09:24:06
Message-ID: 6039730f-c822-8f88-26d4-8406393b2e14@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 2016/06/24 17:38, Ashutosh Bapat wrote:
> On Fri, Jun 24, 2016 at 1:59 PM, Amit Langote wrote:
>> 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,

Yeah, I had noticed that in explain outputs (should have written like
that). My point though is why we don't consider dropping the CASE WHEN
... END target list item solution in favor of simply using the alias name
for a whole-row reference without affecting the correctness behavior wrt
outer joins. Using CASE WHEN to emit the correct result has revealed its
downside (this thread) although a simple whole-row reference would just
work without any special consideration.

> 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?

What may go wrong if we requested r1 (an alias name) in target list of the
sent query instead of ROW(r1.col1, ...) for a whole-row reference in the
original query? Fear of wrong set of data arriving or in wrong order or
something like that? This part, I'm not quite sure about.

Thanks,
Amit

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Haroon Muhammad 2016-06-24 10:41:00 Re: initdb issue on 64-bit Windows - (Was: [pgsql-packagers] PG 9.6beta2 tarballs are ready)
Previous Message Mithun Cy 2016-06-24 09:08:34 Re: Hash Indexes