Postgres Pro
Downloads
Home   >   mailing lists

Re: Odd system-column handling in postgres_fdw join pushdown patch - Mailing list pgsql-hackers

From Ashutosh Bapat
Subject Re: Odd system-column handling in postgres_fdw join pushdown patch
Date
Msg-id CAFjFpRf7sqOZFXoxaUuNoSBzQWieQ_p+BUkk1q0H6Fw5F1kUnA@mail.gmail.com
Whole thread Raw
In response to Re: Odd system-column handling in postgres_fdw join pushdown patch  (Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>)
Responses Re: Odd system-column handling in postgres_fdw join pushdown patch  (Robert Haas <robertmhaas@gmail.com>)
List pgsql-hackers
Tree view
With this patch, all instances of tableoid, cmin, cmax etc. will get a non-NULL value irrespective of whether they appear on nullable side of the join or not.

e.g. select t1.c1, t1.tableoid, t2.c1, t2.tableoid from ft4 t1 left join ft5 t2 on (t1.c1 = t2.c1); run in contrib_regression gives output
 c1  | tableoid | c1 | tableoid
-----+----------+----+----------
   2 |    54282 |    |    54285
   4 |    54282 |    |    54285
   6 |    54282 |  6 |    54285
   8 |    54282 |    |    54285
  10 |    54282 |    |    54285
  12 |    54282 | 12 |    54285

but the same query run on local tables select t1.c1, t1.tableoid, t2.c1, t2.tableoid from "S 1"."T 3" t1 left join "S 1"."T 4" t2 on (t1.c1 = t2.c1); gives output
 c1  | tableoid | c1 | tableoid
-----+----------+----+----------
   2 |    54258 |    |        
   4 |    54258 |    |        
   6 |    54258 |  6 |    54266
   8 |    54258 |    |        
  10 |    54258 |    |        
  12 |    54258 | 12 |    54266

BTW, why do we want to set the column values with invalid values, and not null? Wouldn't setting them NULL will be a better way?

On Tue, Apr 5, 2016 at 12:11 PM, Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> wrote:
On 2016/03/29 15:37, Etsuro Fujita wrote:
I added two helper functions: GetFdwScanTupleExtraData and
FillFdwScanTupleSysAttrs.  The FDW author could use the former to get
info about system attributes other than ctids and oids in fdw_scan_tlist
during BeginForeignScan, and the latter to set values for these system
attributes during IterateForeignScan (InvalidTransactionId for
xmins/xmaxs, InvalidCommandId for cmins/cmaxs, and valid values for
tableoids).  Attached is a proposed patch for that.  I also slightly
simplified the changes to make_tuple_from_result_row and
conversion_error_callback made by the postgres_fdw join pushdown patch.
  What do you think about that?

 I revised comments a little bit.  Attached is an updated version of the patch.  I think this issue should be fixed in advance of the PostgreSQL 9.6beta1 release.

Best regards,
Etsuro Fujita



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

pgsql-hackers by date:

Previous
From: Fujii Masao
Date:
Subject: Re: Support for N synchronous standby servers - take 2
Next
From: Magnus Hagander
Date:
Subject: Re: Typo in src/interfaces/libpq/fe-connect.c