Re: [HACKERS] Varying results when using merge joins over postgres_fdw vs hash joins

2017-09-20 Thread Tom Lane
Peter Eisentraut writes: > On 9/20/17 12:06, Tom Lane wrote: >> I'm tempted to propose that we invent some kind of "unknown" >> collation, which the planner would have to be taught to not equate to any >> other column collation (not even other instances of

Re: [HACKERS] Varying results when using merge joins over postgres_fdw vs hash joins

2017-09-20 Thread Peter Eisentraut
On 9/20/17 12:06, Tom Lane wrote: > I'm tempted to propose that we invent some kind of "unknown" > collation, which the planner would have to be taught to not equate to any > other column collation (not even other instances of "unknown"), and that > postgres_fdw's IMPORT ought to label remote

Re: [HACKERS] Varying results when using merge joins over postgres_fdw vs hash joins

2017-09-20 Thread David Kohn
Yeah. Definitely went down a fun rabbit hole on that separate code paths issue. Perhaps, rather than trying to fix this automatically, we should > leave it to the user. We could invent another import option that > says what to translate "default" to, with the default being, > uh, "default". > >

Re: [HACKERS] Varying results when using merge joins over postgres_fdw vs hash joins

2017-09-20 Thread Peter Geoghegan
On Wed, Sep 20, 2017 at 9:35 AM, Tom Lane wrote: > TBH, the more I learn about ICU, the less faith I have in the proposition > that it's going to fix anything at all for us in this area. It seems to > be just about as squishy as glibc in terms of locale identification, > if

Re: [HACKERS] Varying results when using merge joins over postgres_fdw vs hash joins

2017-09-20 Thread Tom Lane
Peter Geoghegan writes: > I would like postgres_fdw to be taught about collation versioning, so > that postgres_fdw's IMPORT could automatically do the right thing when > ICU is in use. Maybe it's too early to discuss that, because we don't > even support alternative collation

Re: [HACKERS] Varying results when using merge joins over postgres_fdw vs hash joins

2017-09-20 Thread Tom Lane
Corey Huinker writes: > We had difficulty finding the place in the code were LC_COLLATE gets > recombobulated into a recognized collation. That's because it isn't. The DB's default collation boils down to "call strcoll(), having set LC_COLLATE to whatever pg_database

Re: [HACKERS] Varying results when using merge joins over postgres_fdw vs hash joins

2017-09-20 Thread Peter Geoghegan
On Wed, Sep 20, 2017 at 9:06 AM, Tom Lane wrote: > It is that. I'm tempted to propose that we invent some kind of "unknown" > collation, which the planner would have to be taught to not equate to any > other column collation (not even other instances of "unknown"), and that >

Re: [HACKERS] Varying results when using merge joins over postgres_fdw vs hash joins

2017-09-20 Thread Corey Huinker
> > I'm not sure whether there's a way to fix this that doesn't break other > cases. We could retrieve the pg_database.datcollate string from the > remote, but that doesn't necessarily match up with any collation name > we know about locally. One pretty common failure mode would be that > the

Re: [HACKERS] Varying results when using merge joins over postgres_fdw vs hash joins

2017-09-20 Thread Tom Lane
Peter Geoghegan writes: > I think that Corey describes a user hostile behavior. I feel that we > should try to do better here. It is that. I'm tempted to propose that we invent some kind of "unknown" collation, which the planner would have to be taught to not equate to any other

Re: [HACKERS] Varying results when using merge joins over postgres_fdw vs hash joins

2017-09-20 Thread Tom Lane
Corey Huinker writes: >> The collation column is empty here, which means that collation for >> str* columns is default collation i.e. C. This isn't true, since the >> default ncollation on the foreign server is different from the default >> collation of local database.

Re: [HACKERS] Varying results when using merge joins over postgres_fdw vs hash joins

2017-09-20 Thread Peter Geoghegan
On Wed, Sep 20, 2017 at 2:06 AM, Ashutosh Bapat wrote: > In this case, both tables use same collation while comparing the rows, > so result is different from the merge join result. Hash join executed > on local server and the same executed on foreign server (by

Re: [HACKERS] Varying results when using merge joins over postgres_fdw vs hash joins

2017-09-20 Thread Corey Huinker
> > > The collation column is empty here, which means that collation for > str* columns is default collation i.e. C. This isn't true, since the > default ncollation on the foreign server is different from the default > collation of local database. AFAIU, import foreign schema should have > set

Re: [HACKERS] Varying results when using merge joins over postgres_fdw vs hash joins

2017-09-20 Thread Ashutosh Bapat
On Wed, Sep 20, 2017 at 5:07 AM, Corey Huinker wrote: > We are having an issue with a query that will return no results when the > query does a merge join with a foreign table, but (correctly) returns > results when using a hash join. > > Here is the situation on the

[HACKERS] Varying results when using merge joins over postgres_fdw vs hash joins

2017-09-19 Thread Corey Huinker
We are having an issue with a query that will return no results when the query does a merge join with a foreign table, but (correctly) returns results when using a hash join. Here is the situation on the "remote" database (9.5): # \d+ table_with_en_us_utf8_encoding Table