From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Cc: jurafejfar@gmail.com
Subject: Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
Date: Thu, 28 Jan 2021 13:31:46 +0100
Message-ID: <facc1d5c-cf6a-46ad-a8a1-cc01617b5ddb@2ndquadrant.com> (raw)
In-Reply-To: <1311836.1597781384@sss.pgh.pa.us>
References: <16583-4dc8f6c3b8ed24fa@postgresql.org>
<1072831.1597678008@sss.pgh.pa.us>
<1156217.1597703867@sss.pgh.pa.us>
<1311836.1597781384@sss.pgh.pa.us>
On 2020-08-18 22:09, Tom Lane wrote:
> Here's a full patch addressing this issue. I decided that the best
> way to address the test-instability problem is to explicitly give
> collations to all the foreign-table columns for which it matters
> in the postgres_fdw test. (For portability's sake, that has to be
> "C" or "POSIX"; I mostly used "C".) Aside from ensuring that the
> test still passes with some other prevailing locale, this seems like
> a good idea since we'll then be testing the case we are encouraging
> users to use.
I have studied this patch and this functionality. I don't think
collation differences between remote and local instances are handled
sufficiently. This bug report and patch addresses one particular case,
where the database-wide collation of the remote and local instance are
different. But it doesn't handle cases like the same collation name
doing different things, having different versions, or different
attributes. This probably works currently because the libc collations
don't have much functionality like that, but there is a variety of work
conceived (or, in the case of version tracking, already done since the
bug was first discussed) that would break that.
Taking a step back, I think there are only two ways this could really
work: Either, the admin makes a promise that all the collations match on
all the instances; then the planner can take advantage of that. Or,
there is no such promise, and then the planner can't. I don't
understand what the currently implemented approach is. It appears to be
something in the middle, where certain representations are made that
certain things might match, and then there is some nontrivial code that
analyzes expressions whether they conform to those rules. As you said,
the description of the import_collate option is kind of hand-wavy about
all this.
--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/
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: peter.eisentraut@2ndquadrant.com, tgl@sss.pgh.pa.us, pgsql-hackers@lists.postgresql.org, jurafejfar@gmail.com
Subject: Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
In-Reply-To: <facc1d5c-cf6a-46ad-a8a1-cc01617b5ddb@2ndquadrant.com>
* 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