ST_ConcaveHull fails to execute through FDW

[Algunenano]

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.

[Algunenano]

PR proposal: ​https://github.com/postgis/postgis/pull/497

[Raul Marin]

In 17925:

Fix ST_ConcaveHull under FDW

References #4546
Closes ​https://github.com/postgis/postgis/pull/497

[Raul Marin]

In 17926:

Fix ST_ConcaveHull under FDW

References #4546

[Raul Marin]

In 17927:

Fix ST_ConcaveHull under FDW

References #4546

[Raul Marin]

In 17928:

Fix ST_ConcaveHull under FDW

References #4546

[Raul Marin]

In 17929:

Fix ST_ConcaveHull under FDW

Closes #4546

[Raul Marin]

In 17945:

Revert r17925

References #4546

[Algunenano]

Reopening to remove the search_path and use a proper solution instead. First I'll revert all commits, then push the C fix and then qualify all variables using extension types.

[Raul Marin]

In 17946:

Reverts r17926

References #4546

[Raul Marin]

In 17947:

Revert r17927

References #4546

[Raul Marin]

In 17948:

Revert r17928

References #4546

[Raul Marin]

In 17949:

Revert r17929

References #4546

[Algunenano]

C changes: ​https://github.com/postgis/postgis/pull/499 SQL changes: ​https://github.com/postgis/postgis/pull/500

These PRs will also address #4549 and several other qualifications that were missing in both the core and the raster extension.

[Raul Marin]

In 17951:

Use get_call_result_type to retrieve tuple descriptions

Closes #499
References #4549
References #4546

[Raul Marin]

In 17972:

Fix PLPGSQL functions missing the schema qualification

References #4546
Closes ​https://github.com/postgis/postgis/pull/500

[Raul Marin]

In 17976:

Fix PLPGSQL functions missing the schema qualification

References #4546

[Raul Marin]

In 17977:

Fix PLPGSQL functions missing the schema qualification

References #4546

[Raul Marin]

In 17978:

Fix PLPGSQL functions missing the schema qualification

References #4546

[Raul Marin]

In 17979:

Fix PLPGSQL functions missing the schema qualification

Closes #4546