From: Tom Lane <tgl@sss.pgh.pa.us>
To: Etsuro Fujita <etsuro.fujita@gmail.com>
Cc: Ibrar Ahmed <ibrar.ahmad@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Neil Chen <carpenter.nail.cz@gmail.com>
Subject: Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
Date: Thu, 09 Sep 2021 12:00:21 -0400
Message-ID: <3683993.1631203221@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, etsuro.fujita@gmail.com, ibrar.ahmad@gmail.com, pgsql-hackers@lists.postgresql.org, carpenter.nail.cz@gmail.com
Subject: Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
In-Reply-To: <3683993.1631203221@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox