Thread: UDF calls and FDW

Hello,

I am using FDW to transparently access a remote Postgres database
containing a table indexed with the Q3C library.

On the remote server :

   postgres=# SELECT * FROM object_000 WHERE q3c_radial_query(ra_PS, decl_PS,
1.3, 3.4, .2) ;
   ...
   (2416 rows)

   Time: 130.300 ms

But on the FDW node the timing is bad :

   postgres=# SELECT * FROM master_object_000 WHERE q3c_radial_query(ra_PS,
decl_PS, 1.3, 3.4, .2) ;
   ...
   (2416 rows)

   Time: 130843.931 ms

postgres=# explain SELECT * FROM master_object_000 WHERE q3c_radial_query(ra_PS,
decl_PS, 1.3, 3.4, .2) ;
                                                    QUERY PLAN

-----------------------------------------------------------------------------------------------------------------
  Foreign Scan on master_object_000  (cost=100.00..383744.04 rows=377539 width=1084)
    Filter: q3c_radial_query(ra_ps, decl_ps, 1.3::double precision, 3.4::double
precision, 0.2::double precision)
(2 rows)


So the problem is that is  calls the UDF on the FDW node (Foreign
Scan on master_object_000 + Filter) and not on the remote server.

   - How to call the UDF on the remote server ?

Regards,
--

Emmanuel Medernach <medernac@clermont.in2p3.fr> writes:
> So the problem is that is  calls the UDF on the FDW node (Foreign
> Scan on master_object_000 + Filter) and not on the remote server.

Right.

>    - How to call the UDF on the remote server ?

postgres_fdw intentionally refuses to do this, because it has no way
to know whether q3c_radial_query() is the same function, or even
exists at all, on the remote server.  Only WHERE clauses involving
built-in functions/operators will be pushed across to the remote server.

Barring solutions for that philosophical question, you might be able
to do something like putting the function call into a view on the remote
side and then creating a foreign table that references the view.

            regards, tom lane