Message-ID: <f7eb4902-8d09-d713-1b55-d2a00ca34a93@amazon.com>
Date: Fri, 1 Oct 2021 12:37:31 -0700
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:91.0)
 Gecko/20100101 Thunderbird/91.1.2
Subject: Re: BUG #16583: merge join on tables with different DB collation
 behind postgres_fdw fails
Content-Language: en-US
To: Tom Lane <tgl@sss.pgh.pa.us>, Etsuro Fujita <etsuro.fujita@gmail.com>
CC: Ibrar Ahmed <ibrar.ahmad@gmail.com>, PostgreSQL Hackers
	<pgsql-hackers@lists.postgresql.org>, Neil Chen <carpenter.nail.cz@gmail.com>
References: <16583-4dc8f6c3b8ed24fa@postgresql.org>
 <422379.1612684550@sss.pgh.pa.us>
 <161476090989.29967.7097745522340560681.pgcf@coridan.postgresql.org>
 <CALtqXTdeSReq0Y1-mRAMO-SivqDRX_caamymdH9f=zsfLxsjow@mail.gmail.com>
 <3404441.1626208861@sss.pgh.pa.us>
 <CALtqXTdV2v5KmBNF1c_1uA=eYaLUZE7ecpxaNbLXo823=CEVzQ@mail.gmail.com>
 <CAPmGK15KFjpiHcu+fLVOJzH=41gTgTOqbbaUSwib1CKX0io69A@mail.gmail.com>
 <2201880.1630528969@sss.pgh.pa.us>
 <CAPmGK15ohpwdAPYA=Fw+C+DTdoPMWrpr6_q_XR6u3eJH7MSkVg@mail.gmail.com>
 <3683993.1631203221@sss.pgh.pa.us>
 <CAPmGK17wvbzpb6jsTEX7bK4JvoTqJK3M=MZx9xnVS=GhWs9_Ww@mail.gmail.com>
 <CAPmGK1759RxwEn2xH2nNqaK2LFOG1v9HkMMO4kpJAqrNfnho-Q@mail.gmail.com>
 <2438715.1632510693@sss.pgh.pa.us>
 <CAPmGK16g4qDfCqL7hAAdBpkCjz62gA1FSBpKtH=Bfnm6L=qkfA@mail.gmail.com>
 <2501728.1632578373@sss.pgh.pa.us>
From: Jeremy Schneider <schnjere@amazon.com>
In-Reply-To: <2501728.1632578373@sss.pgh.pa.us>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/f7eb4902-8d09-d713-1b55-d2a00ca34a93%40amazon.com>
Precedence: bulk

On 9/25/21 06:59, Tom Lane wrote:
> Etsuro Fujita <etsuro.fujita@gmail.com> writes:
>> On Sat, Sep 25, 2021 at 4:11 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Longer-term, it seems like we really have to be able to represent
>>> the notion of a remote column that has an "unknown" collation (that
>>> is, one that doesn't match any local collation, or at least is not
>>> known to do so).
> 
>> +1
> 
>> In addition, a) we should detect whether local “default” matches
>> remote “default”,
> 
> If we had a way to do that, most of the problem here wouldn't exist.
> I don't believe we can do it reliably.  (Maybe we could put it on
> the user to tell us, say via a foreign-server property?)

A related situation is local and remote servers having different
versions of glibc - in particular, pre versus post 2.28. I think there's
still a major brewing storm here that hasn't yet fully hit the world of
PG users.

I know PG throws the warning message for queries using the wrong
collation library version, but I can't remember - does the query still
execute? If so, then glibc 2.28 seems to significnatly raise the
likelihood of wrong query results across the entire global PG install base.

Does PostgreSQL handle cases which involve FDWs (ala this thread) or hot
standbys? Would be nice if some approach could be found to solve that
problem at the same time as the one discussed on this thread.

-Jeremy


-- 
Jeremy Schneider
Database Engineer
Amazon Web Services