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

Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
> 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.

Yeah, agreed. I don't think it's practical to have a 100% solution.
I'd make a couple of points:

* The design philosophy of postgres_fdw, to the extent it has one,
is that it's the user's responsibility to make sure that the local
declaration of a foreign table is a faithful model of the actual
remote object. There are certain variances you can get away with,
but in general, if it breaks it's your fault. (Admittedly, if the
local declaration was created via IMPORT FOREIGN SCHEMA, we would
like to be sure that it's right without help. But there's only
so much we can do there. There are already plenty of ways to
fool IMPORT FOREIGN SCHEMA anyway, for example if the same type
name refers to something different on the two systems.)

* Not being able to ship any qual conditions involving collatable
datatypes seems like an absolutely unacceptable outcome. Thus,
I don't buy your alternative of not letting the planner make
any assumptions at all about compatibility of remote collations.

I think that what this patch is basically doing is increasing the
visibility of collation compatibility as something that postgres_fdw
users need to take into account. Sure, it's not a 100% solution,
but it improves the situation, and it seems like we'd have to do
this anyway along the road to any better solution.

If you've got ideas about how to improve things further, by all
means let's discuss that ... but let's not make the perfect be
the enemy of the good.

regards, tom lane