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

On Fri, Sep 10, 2021 at 1:00 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> writes:
> > 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.

Yeah, but I think it’s the user’s responsibility to make sure that the
local and remote default collations match if labeling collatable
columns with “COLLATE default” when defining foreign tables manually
IMO.

> 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.

Agreed.

Best regards,
Etsuro Fujita