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

Corey Huinker <corey(dot)huinker(at)gmail(dot)com> 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. AFAIU, import foreign schema should have
>> set appropriate collation of the foreign table.

> That's what we saw. The query inside IMPORT FOREIGN SCHEMA assumes a NULL
> collation means default, without asking the server what that default is.

No, it's not NULL, it's pg_catalog.default. The problem is exactly that
that means something else on the remote server than it does locally.

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 datcollate string isn't a canonical spelling (eg, "en_US.UTF-8"
where the name we know about is "en_US.utf8"). In general, datcollate
is handled through other code paths than collation names, so it might
easily be that it doesn't match anything in the remote's pg_collation
catalog either :-(.

Another point is that when the servers' default collations do match, users
would likely not thank us for replacing "default" with something else.
Even if we picked a functionally equivalent collation, it would impede
query optimization because the planner wouldn't know it was equivalent.

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".

regards, tom lane