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

On Fri, Sep 10, 2021 at 8:42 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> 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.

One thing I noticed is that collatable operators/functions sent to the
remote might also cause an unexpected result when the default
collations are not compatible. Consider this example (even with your
patch):

explain verbose select chr(c1) from ft1 order by chr(c1);
QUERY PLAN
------------------------------------------------------------------------
Foreign Scan on public.ft1 (cost=100.00..212.91 rows=2925 width=32)
Output: chr(c1)
Remote SQL: SELECT c1 FROM public.t1 ORDER BY chr(c1) ASC NULLS LAST
(3 rows)

where ft1 is a foreign table with an integer column c1. As shown
above, the sort using the collatable function chr() is performed
remotely, so the select query might produce the result in an
unexpected sort order when the default collations are not compatible.

ISTM that we rely heavily on assumption (2).

Best regards,
Etsuro Fujita