[postgis-tickets] [PostGIS] #4546: ST_ConcaveHull fails to execute through FDW

[PostGIS]

#4546: ST_ConcaveHull fails to execute through FDW
------------------------+---------------------------
 Reporter:  Algunenano  |      Owner:  pramsey
     Type:  defect      |     Status:  new
 Priority:  medium      |  Milestone:  PostGIS 2.4.9
Component:  postgis     |    Version:  trunk
 Keywords:              |
------------------------+---------------------------
 Source server: PG12 + Postgis 3
 Remove server: Tested both PG12 with Postgis 3 and PG11 with Postgis 2.5,
 same result.

 The FDW server is created with `extensions: 'postgis'` so the function can
 be shipped to the remote, e.g.:
 {{{
 CREATE server remote_server foreign data wrapper postgres_fdw options
 (host '127.0.0.1', port '5432', dbname
 'cartodb_dev_user_3e4a6fc6-4137-4c59-bc63-066f80efb90e_db', extensions
 'postgis', fetch_size '10000', use_remote_estimate 'true');
 }}}


 Example plan:
 {{{
 explain (verbose) Select ST_ConcaveHull(ST_Collect(the_geom), 0.7, true)
 from remote_local.populated;
                                                          QUERY PLAN

 ---------------------------------------------------------------------------------------------------------------------
 -------
  Foreign Scan  (cost=594.53..594.83 rows=1 width=32)
    Output: (st_concavehull(st_collect(the_geom), '0.7'::double precision,
 true))
    Relations: Aggregate on (remote_local.populated)
    Remote SQL: SELECT public.st_concavehull(public.st_collect(the_geom),
 0.7::double precision, true) FROM public.populated
 (4 rows)
 }}}

 Error:
 {{{
 ERROR:  type "geometry" does not exist
 CONTEXT:  compilation of PL/pgSQL function "_st_concavehull" near line 3
 PL/pgSQL function public.st_concavehull(public.geometry,double
 precision,boolean) line 152 at assignment
 SQL statement "SELECT
 public.ST_Buffer(public.ST_ConcaveHull(var_geoms[1],least(param_pctconvex
 + param_pctconvex/var_div),true),var_buf, 'quad_segs=2')"
 PL/pgSQL function public.st_concavehull(public.geometry,double
 precision,boolean) line 114 at assignment
 SQL statement "SELECT
 public.ST_Buffer(public.ST_ConcaveHull(var_geoms[1],least(param_pctconvex
 + param_pctconvex/var_div),true),var_buf, 'quad_segs=2')"
 PL/pgSQL function public.st_concavehull(public.geometry,double
 precision,boolean) line 114 at assignment
 PL/pgSQL function public.st_concavehull(public.geometry,double
 precision,boolean) line 114 at assignment
 remote SQL command: SELECT
 public.st_concavehull(public.st_collect(the_geom), 0.7::double precision,
 true) FROM public.all_month
 }}}

 This is happening because postgres_fdw removes all schemas from the
 search_path, and although we qualify function calls using @extschema@, we
 don't do it for types.


 If I then qualified all geometry usages, the following error arises:
 {{{
 explain (analyze, verbose) Select ST_ConcaveHull(ST_Collect(the_geom),
 0.7, true) from remote_local.all_month;
 ERROR:  relation "geometry_dump" does not exist
 CONTEXT:  SQL statement "SELECT public.ST_MakeLine(geom)
                         FROM public.ST_Dump(cavering) As foo"
 PL/pgSQL function public._st_concavehull(public.geometry) line 49 at
 assignment
 PL/pgSQL function public.st_concavehull(public.geometry,double
 precision,boolean) line 152 at assignment
 SQL statement "SELECT
 public.ST_Buffer(public.ST_ConcaveHull(var_geoms[1],least(param_pctconvex
 + param_pctconvex/var_div),true),var_buf, 'quad_segs=2')"
 PL/pgSQL function public.st_concavehull(public.geometry,double
 precision,boolean) line 114 at assignment
 SQL statement "SELECT
 public.ST_Buffer(public.ST_ConcaveHull(var_geoms[1],least(param_pctconvex
 + param_pctconvex/var_div),true),var_buf, 'quad_segs=2')"
 PL/pgSQL function public.st_concavehull(public.geometry,double
 precision,boolean) line 114 at assignment
 PL/pgSQL function public.st_concavehull(public.geometry,double
 precision,boolean) line 114 at assignment
 remote SQL command: SELECT
 public.st_concavehull(public.st_collect(the_geom), 0.7::double precision,
 true) FROM public.all_month
 }}}


 As far as I know, FDW doesn't have an options to add extra schema to the
 search_path (https://www.postgresql.org/docs/12/postgres-
 fdw.html#id-1.11.7.42.10.4) so we can either find a way to qualify all
 types used in SQL/PLPGSQL functions, even the implicit ones like
 `geometry_dump` above, or we add `SET search_path = @extschema@;` to those
 functions which would ensure that we use the types and functions created
 by the function.

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4546>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.