From: Tom Lane <tgl@sss.pgh.pa.us>
To: 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>
Subject: Re: BUG #16583: merge join on tables with different DB collation
 behind postgres_fdw fails
In-reply-to: 
 <CAPmGK15ohpwdAPYA=Fw+C+DTdoPMWrpr6_q_XR6u3eJH7MSkVg@mail.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>
Comments: In-reply-to Etsuro Fujita <etsuro.fujita@gmail.com>
	message dated "Fri, 10 Sep 2021 00:45:32 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <3683992.1631203221.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Thu, 09 Sep 2021 12:00:21 -0400
Message-ID: <3683993.1631203221@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3683993.1631203221%40sss.pgh.pa.us>
Precedence: bulk

Etsuro Fujita <etsuro.fujita@gmail.com> writes:
> On Thu, Sep 2, 2021 at 5:42 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The real reason that this hasn't gotten committed is that I remain
>> pretty uncomfortable about whether it's an acceptable solution to
>> the problem.  Suddenly asking people to plaster COLLATE clauses
>> on all their textual remote columns seems like a big compatibility
>> gotcha.

> I think so too.

Yeah :-(.  It seems like a very unpleasant change.

> Having said that, I think another option for this would be to left the
> code as-is; assume that 1) the foreign var has "COLLATE default”, not
> an unknown collation, when labeled with "COLLATE default”, and 2)
> "COLLATE default” on the local database matches "COLLATE default” on
> the remote database.

The fundamental complaint that started this thread was exactly that
assumption (2) isn't safe.  So it sounds to me like you're proposing
that we do nothing, which isn't a great answer either.  I suppose
we could try documenting our way out of this, but people will
continue to get bit because they won't read or won't understand
the limitation.

I'd be happier if we had a way to check whether the local and remote
default collations are compatible.  But it seems like that's a big ask,
especially in cross-operating-system situations.

			regards, tom lane