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

2016-07-05 Thread Etsuro Fujita
On 2016/07/02 0:32, Robert Haas wrote: On Wed, Jun 29, 2016 at 1:38 AM, Ashutosh Bapat wrote: On Tue, Jun 28, 2016 at 12:52 PM, Etsuro Fujita wrote: Please find attached an updated version. This looks good to me. Regression

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

2016-07-01 Thread Robert Haas
On Wed, Jun 29, 2016 at 1:38 AM, Ashutosh Bapat wrote: > On Tue, Jun 28, 2016 at 12:52 PM, Etsuro Fujita > wrote: >> On 2016/06/28 15:23, Ashutosh Bapat wrote: >>> >>> The wording "column "whole-row reference ..." doesn't look good.

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

2016-07-01 Thread Ashutosh Bapat
On Fri, Jul 1, 2016 at 7:45 PM, Robert Haas wrote: > On Tue, Jun 28, 2016 at 8:20 AM, Ashutosh Bapat > wrote: > >> > postgres_fdw resets the search path to pg_catalog while opening > >> > connection > >> > to the server. The reason behind

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

2016-07-01 Thread Robert Haas
On Tue, Jun 28, 2016 at 8:20 AM, Ashutosh Bapat wrote: >> > postgres_fdw resets the search path to pg_catalog while opening >> > connection >> > to the server. The reason behind this is explained in deparse.c >> > >> > * We assume that the remote session's

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

2016-06-28 Thread Ashutosh Bapat
On Tue, Jun 28, 2016 at 12:52 PM, Etsuro Fujita wrote: > On 2016/06/28 15:23, Ashutosh Bapat wrote: > >> The wording "column "whole-row reference ..." doesn't look good. >> Whole-row reference is not a column. The error context itself should be >> "whole row

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

2016-06-28 Thread Ashutosh Bapat
> > > > > postgres_fdw resets the search path to pg_catalog while opening > connection > > to the server. The reason behind this is explained in deparse.c > > > > * We assume that the remote session's search_path is exactly > "pg_catalog", > > * and thus we need schema-qualify all and only names

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

2016-06-28 Thread Etsuro Fujita
On 2016/06/28 15:23, Ashutosh Bapat wrote: The wording "column "whole-row reference ..." doesn't look good. Whole-row reference is not a column. The error context itself should be "whole row reference for foreign table ft1". Ah, you are right. Please find attached an updated version. Best

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

2016-06-28 Thread Ashutosh Bapat
On Tue, Jun 28, 2016 at 11:43 AM, Etsuro Fujita wrote: > On 2016/06/28 13:53, Ashutosh Bapat wrote: > >> Ideally, we should point out the specific column that faced the >> conversion problem and report it, instead of saying the whole row >> reference conversion

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

2016-06-28 Thread Etsuro Fujita
On 2016/06/28 13:53, Ashutosh Bapat wrote: Ideally, we should point out the specific column that faced the conversion problem and report it, instead of saying the whole row reference conversion caused the problem. But that may be too difficult. I think so too. Or at least the error message

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

2016-06-27 Thread Ashutosh Bapat
On Tue, Jun 28, 2016 at 9:00 AM, Etsuro Fujita wrote: > On 2016/06/27 18:56, Ashutosh Bapat wrote: > >> On Mon, Jun 27, 2016 at 3:06 PM, Etsuro Fujita >> > wrote: >> > > I found another bug in error

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

2016-06-27 Thread Etsuro Fujita
On 2016/06/27 18:56, Ashutosh Bapat wrote: On Mon, Jun 27, 2016 at 3:06 PM, Etsuro Fujita > wrote: I found another bug in error handling of whole-row references in join pushdown; conversion_error_callback fails to take

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

2016-06-27 Thread Robert Haas
On Mon, Jun 27, 2016 at 2:47 AM, Ashutosh Bapat wrote: > On Sat, Jun 25, 2016 at 12:44 AM, Robert Haas wrote: >> On Wed, Jun 22, 2016 at 4:11 AM, Amit Langote >> wrote: >> >> In an outer join we have to

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

2016-06-27 Thread Ashutosh Bapat
On Mon, Jun 27, 2016 at 3:06 PM, Etsuro Fujita wrote: > On 2016/06/25 4:14, Robert Haas wrote: > >> Committed that way. >> > > Thanks for taking care of this! > > I found another bug in error handling of whole-row references in join > pushdown;

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

2016-06-27 Thread Etsuro Fujita
On 2016/06/25 4:14, Robert Haas wrote: Committed that way. Thanks for taking care of this! I found another bug in error handling of whole-row references in join pushdown; conversion_error_callback fails to take into account that get_relid_attribute_name(Oid relid, AttrNumber attnum) can't

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

2016-06-27 Thread Ashutosh Bapat
On Sat, Jun 25, 2016 at 12:44 AM, Robert Haas wrote: > On Wed, Jun 22, 2016 at 4:11 AM, Amit Langote > wrote: > >> In an outer join we have to differentiate between a row being null > (because > >> there was no joining row on nullable side)

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

2016-06-24 Thread Robert Haas
On Wed, Jun 22, 2016 at 5:16 AM, Ashutosh Bapat wrote: >> 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)

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

2016-06-24 Thread Robert Haas
On Wed, Jun 22, 2016 at 4:11 AM, Amit Langote wrote: >> In an outer join we have to differentiate between a row being null (because >> there was no joining row on nullable side) and a non-null row with all >> column values being null. If we cast the whole-row

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

2016-06-24 Thread Amit Langote
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

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

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

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

2016-06-24 Thread Amit Langote
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

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

2016-06-24 Thread Ashutosh Bapat
> > 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

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

2016-06-24 Thread Etsuro Fujita
On 2016/06/22 19:37, Ashutosh Bapat wrote: On Wed, Jun 22, 2016 at 3:57 PM, Etsuro Fujita Maybe I'm confused, but I think that in the system-column case it's the user's responsibility to specify system columns for foreign tables in a local query only when that makes sense on the

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

2016-06-22 Thread Ashutosh Bapat
On Wed, Jun 22, 2016 at 3:57 PM, Etsuro Fujita wrote: > On 2016/06/22 18:16, Ashutosh Bapat wrote: > >> On Wed, Jun 22, 2016 at 2:26 PM, Etsuro Fujita >> > wrote: >> > > I think we could address

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

2016-06-22 Thread Etsuro Fujita
On 2016/06/22 18:16, Ashutosh Bapat wrote: On Wed, Jun 22, 2016 at 2:26 PM, Etsuro Fujita > wrote: I think we could address this in another way once we support deparsing subqueries; rewrite the remote query into

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

2016-06-22 Thread Amit Langote
On 2016/06/22 18:14, Ashutosh Bapat wrote: > 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,

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

2016-06-22 Thread Ashutosh Bapat
On Wed, Jun 22, 2016 at 2:26 PM, Etsuro Fujita wrote: > On 2016/06/22 17:11, Amit Langote wrote: > >> 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

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

2016-06-22 Thread Ashutosh Bapat
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

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

2016-06-22 Thread Etsuro Fujita
On 2016/06/22 17:11, Amit Langote wrote: 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

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

2016-06-22 Thread Amit Langote
On 2016/06/21 20:42, Ashutosh Bapat wrote: > On Tue, Jun 21, 2016 at 4:36 PM, Amit Langote wrote: >> On 2016/06/21 16:27, Rushabh Lathia wrote: >>> >>> And as above documentation clearly says that IS NULL and IS NOT NULL do >> not >>> always return inverse results for row-valued expressions. So

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

2016-06-21 Thread Etsuro Fujita
On 2016/06/21 21:37, Ashutosh Bapat wrote: How about using a system column eg, ctid, for the CASE WHEN conversion; in Rushabh's example the reference to "r1" would be converted with "CASE WHEN r1.ctid IS NOT NULL THEN ROW(r1.empno, r1.ename, r1.job, r1.mgr, r1.hiredate, r1.sal,

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

2016-06-21 Thread Ashutosh Bapat
> How about using a system column eg, ctid, for the CASE WHEN conversion; in > Rushabh's example the reference to "r1" would be converted with "CASE WHEN > r1.ctid IS NOT NULL THEN ROW(r1.empno, r1.ename, r1.job, r1.mgr, > r1.hiredate, r1.sal, r1.comm, r1.deptno) END". IMO I think that that would

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

2016-06-21 Thread Etsuro Fujita
On 2016/06/21 20:42, Ashutosh Bapat wrote: On Tue, Jun 21, 2016 at 4:36 PM, Amit Langote > wrote: On 2016/06/21 16:27, Rushabh Lathia wrote: > Now I was under impression the IS NOT NULL should be always in

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

2016-06-21 Thread Ashutosh Bapat
On Tue, Jun 21, 2016 at 4:36 PM, Amit Langote wrote: > On 2016/06/21 16:27, Rushabh Lathia wrote: > > Now I was under impression the IS NOT NULL should be always in inverse of > > IS NULL, but clearly here its not the case with wholerow. But further > > looking at

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

2016-06-21 Thread Amit Langote
On 2016/06/21 16:27, Rushabh Lathia wrote: > Now I was under impression the IS NOT NULL should be always in inverse of > IS NULL, but clearly here its not the case with wholerow. But further > looking at > the document its saying different thing for wholerow: > >