Extension support for postgres_fdw

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.

There is a PostGIS-specific implementation of this concept here:

  https://github.com/pramsey/postgres/blob/9.4-postgres-fdw-postgis/contrib/postgres_fdw

If the approach above sounds OK, I'll genericize my PostGIS specific code to hand arbitrary extensions and submit a patch.

Thanks!

Paul

--
Paul Ramsey
http://cleverelephant.ca
http://postgis.net