On 06/20/2015 10:20 AM, Paul Ramsey wrote:
I would like to enhance the postgres_fdw to allow more complete support for 
user-defined types.

Right now, postgres_fdw already does a good job of passing user-defined type 
data back and forth, which is pretty nice. However, it will not pass functions 
or operators that use user-defined types to the remote host. For a extension 
like PostGIS, that means that spatial filters cannot be executed on remote 
servers, which makes FDW not so useful for PostGIS.

I think the postgres_fdw extension should pass user-defined functions and 
operators, but only when it knows those functions and operators exist at the 
remote. One way would be to ask the remote what extensions it has, but the 
overhead of doing that is a bit high. A simpler way would be to just have the 
DBA declare what extensions the remote will have, when she creates the server 
definition, for example:

CREATE SERVER fire_department_server
   FOREIGN DATA WRAPPER postgres_fdw
   OPTIONS (
     host       'fire.city.gov',
     dbname     'infrastructure',
     port       '5432',
     extensions 'postgis, seg'
);

Once the local host knows what extensions to expect on the remote side, it can 
retain functions and operators in those extensions in the set of remote 
restrictions and deparse them for use in the query of the remote. Basically, 
everywhere there is a call to is_builtin(Oid oid) now, there's also be a call 
to is_allowed_extension() (or somesuch) as well.

The SQL/MED specification has a concept of routine mappings, for mapping functions. I'm not sure if it'd be better to implement CREATE ROUTINE MAPPING, and adapt it to operators too, or invent something entirely new and PostgreSQL-specific. But CREATE ROUTINE MAPPING at least deserves a look.
- Heikki



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to