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
