Re: pgsql_fdw, FDW for PostgreSQL server

On Wed, Dec 7, 2011 at 2:34 AM, Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com> wrote:
> Sorry for delayed response.
>
> 2011/11/29 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>:
>> I think that this is not always safe even from PostgreSQL to PostgreSQL.
>> If two databases have different collation, "<" on strings will behave
>> differently.
>
> Indeed.  I think that only the owner of foreign table can keep collation
> consistent between foreign and local, like data type of column.

+1.

> We need to
> support per-column-collation on foreign tables too, or should deny pushing
> down condition which is collation-sensitive...

It seems that we already do:

rhaas=# create foreign table ft1 (a text collate "de_DE") server s1;
CREATE FOREIGN TABLE

It does seem like this might not be enough information for the FDW to
make good decisions about pushdown. Even supposing the server on the
other hand is also PostgreSQL, the collation names might not match
(if, say, one is running Windows, and the other, Linux). And even if
they do, there is no guarantee that two collations with the same name
have the same behavior on two different machines; they probably
should, but who knows? And if we're using an FDW to talk to some
other database server, the problem is much worse; it's not clear that
we'll even begin to be able to guess whether the remote side has
compatible semantics. I feel like we might need a system here that
allows for more explicit user control about what to push down vs. not,
rather than assuming we'll be able to figure it out behind the scenes.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company