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: 
 <CAPmGK1759RxwEn2xH2nNqaK2LFOG1v9HkMMO4kpJAqrNfnho-Q@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>
 <3683993.1631203221@sss.pgh.pa.us>
 <CAPmGK17wvbzpb6jsTEX7bK4JvoTqJK3M=MZx9xnVS=GhWs9_Ww@mail.gmail.com>
 <CAPmGK1759RxwEn2xH2nNqaK2LFOG1v9HkMMO4kpJAqrNfnho-Q@mail.gmail.com>
Comments: In-reply-to Etsuro Fujita <etsuro.fujita@gmail.com>
	message dated "Fri, 24 Sep 2021 17:36:06 +0900"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <2438640.1632510585.0@sss.pgh.pa.us>
Date: Fri, 24 Sep 2021 15:11:33 -0400
Message-ID: <2438715.1632510693@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2438715.1632510693%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2438640.1632510585.1@sss.pgh.pa.us>

Etsuro Fujita <etsuro.fujita@gmail.com> writes:
> 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):
> ...
> 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.

I don't think there's anything really new there --- it's still assuming
that COLLATE "default" means the same locally and remotely.

As a short-term answer, I propose that we apply (and back-patch) the
attached documentation changes.

Longer-term, it seems like we really have to be able to represent
the notion of a remote column that has an "unknown" collation (that
is, one that doesn't match any local collation, or at least is not
known to do so).  My previous patch essentially makes "default" act
that way, but conflating "unknown" with "default" has too many
downsides.  A rough sketch for making this happen is:

1. Create a built-in "unknown" entry in pg_collation.  Insert some
hack or other to prevent this from being applied to any real, local
column; but allow foreign-table columns to have it.

2. Apply mods, probably fairly similar to my patch, that prevent
postgres_fdw from believing that "unknown" matches any local
collation.  (Hm, actually maybe no special code change will be
needed here, once "unknown" has its own OID?)

3. Change postgresImportForeignSchema so that it can substitute
the "unknown" collation at need.  The exact rules for this could
be debated depending on whether you'd rather prioritize safety or
ease-of-use, but I think at least we should use "unknown" whenever
import_collate is turned off.  Perhaps there should be an option
to substitute it for remote "default" as well.  (Further down the
road, perhaps that could be generalized to allow a user-controlled
mapping from remote to local collations.)

Anyway, I think I should withdraw the upthread patch; we don't
want to go that way.

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="postgres_fdw-collation-doc-warnings.patch";
	charset="us-ascii"
Content-ID: <2438640.1632510585.2@sss.pgh.pa.us>
Content-Description: postgres_fdw-collation-doc-warnings.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sg=
ml
index bf95da9721..dbc11694a0 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -95,8 +95,8 @@
   referenced columns of the remote table.  Although <filename>postgres_fd=
w</filename>
   is currently rather forgiving about performing data type conversions at
   need, surprising semantic anomalies may arise when types or collations =
do
-  not match, due to the remote server interpreting <literal>WHERE</litera=
l> clauses
-  slightly differently from the local server.
+  not match, due to the remote server interpreting query conditions
+  differently from the local server.
  </para>
 =

  <para>
@@ -537,6 +537,17 @@ OPTIONS (ADD password_required 'false');
        need to turn this off if the remote server has a different set of
        collation names than the local server does, which is likely to be =
the
        case if it's running on a different operating system.
+       If you do so, however, there is a very severe risk that the import=
ed
+       table columns' collations will not match the underlying data, resu=
lting
+       in anomalous query behavior.
+      </para>
+
+      <para>
+       Even when this parameter is set to <literal>true</literal>, import=
ing
+       columns whose collation is the remote server's default can be risk=
y.
+       They will be imported with <literal>COLLATE "default"</literal>, w=
hich
+       will select the local server's default collation, which could be
+       different.
       </para>
      </listitem>
     </varlistentry>

------- =_aaaaaaaaaa0--