Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails

Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> writes:
> On Thu, Sep 2, 2021 at 5:42 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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