Re: Odd system-column handling in postgres_fdw join pushdown patch

On Tue, Apr 5, 2016 at 4:54 AM, Ashutosh Bapat
<ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
> 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?

I tend to favor zeroes rather than NULLs, because that's what we
typically use to represent an invalid value of those types, and I'm
not aware of any current case where those values are NULL.

Ashutosh's comment that "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." seems like something that
must be addressed before we can proceed here.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company