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

On Wed, Apr 13, 2016 at 1:36 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> 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.

Actually, come to think of it, what we *really* need to do here is
make sure that the behavior in the join-pushdown case matches the
behavior in the join-not-pushed-down case.

CREATE EXTENSION postgres_fdw;
CREATE SERVER s1 FOREIGN DATA WRAPPER postgres_fdw;
CREATE USER MAPPING FOR public SERVER s1;
CREATE TABLE t1 (a integer, b text);
CREATE FOREIGN TABLE ft1 (a integer, b text) SERVER s1 OPTIONS
(table_name 't1');
INSERT INTO t1 VALUES (1, 'foo'), (2, 'bar'), (3, 'baz'), (4, 'quux');

Without join pushdown - this is what gets selected by default, sadly,
so the costing isn't working as hoped in this case:

rhaas=# select ft1.xmax, ft2.xmax, ft1.* from ft1, ft1 ft2 where ft1.a = ft2.a;   xmax    |    xmax    | a |  b
------------+------------+---+------4294967295 | 4294967295 | 1 | foo4294967295 | 4294967295 | 2 | bar4294967295 |
4294967295| 3 | baz4294967295 | 4294967295 | 4 | quux
 
(4 rows)

With join pushdown, after disabling merge and hash joins:

rhaas=# select ft1.xmax, ft2.xmax, ft1.* from ft1, ft1 ft2 where ft1.a
= ft2.a;xmax | xmax | a |  b
------+------+---+------   0 |    0 | 1 | foo   0 |    0 | 2 | bar   0 |    0 | 3 | baz   0 |    0 | 4 | quux
(4 rows)

So, clearly that's not good.  It should at least be consistent.  But
more than that, the fact that postgres_fdw sets the xmax to 0xffffffff
is also pretty wacky.  We might use such a value as a sentinel for
some data type, but for transaction IDs that's just some random normal
transaction ID, and it's NOT coming from t1.  I haven't tracked down
where it *is* coming from yet, but can't imagine it's any place very
principled.

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