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

On Wed, Apr 13, 2016 at 1:36 PM, Robert Haas <robertmhaas(at)gmail(dot)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 | foo
4294967295 | 4294967295 | 2 | bar
4294967295 | 4294967295 | 3 | baz
4294967295 | 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