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/
