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

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".
>
> I like this idea. One suggestion might be to do a check for the remote
LC_COLLATE and the local LC_COLLATE at the beginning of an IMPORT FOREIGN
SCHEMA call and at least raise a warning if the default collations do not
match. That wouldn't break anything, but at least would notify the user
that something bad could be happening and pointing them to that variable.
Actually, instead of an import option, this might make more sense as an
option on the foreign server, add a default_collation_mapping option for
the foreign server and raise a warning either at foreign server creation
time or at import foreign schema time (probably the latter as I don't think
we actually connect to the remote when we create the foreign server).

D

On Wed, Sep 20, 2017 at 12:28 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Corey Huinker <corey(dot)huinker(at)gmail(dot)com> 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 says".
> Non-default collations go through strcoll_l(), which might not even
> exist on a given platform. So they're entirely separate code paths.
>
> regards, tom lane
>

--
David Kohn | Data Engineer | MOAT
63 Madison Ave, 15th Floor, NYC