[postgis-tickets] [PostGIS] #4739: st_dwithin : no spatial operator found

[PostGIS]

#4739: st_dwithin : no spatial operator found
-----------------------+---------------------------
  Reporter:  grantfin  |      Owner:  pramsey
      Type:  defect    |     Status:  new
  Priority:  medium    |  Milestone:  PostGIS 3.1.0
 Component:  postgis   |    Version:  3.0.x
Resolution:            |   Keywords:
-----------------------+---------------------------

Comment (by ansel):

 A customer of us stumbled about the same issue in the context of
 materialized views.  While all objects are fully schema qualified by
 the user, postgis itself nevertheless performs a non-schema-qualified
 lookup of st_dwithin in this context.

 The problem is serious because recent versions of pg_restore force the
 search_path to empty for securty reasons.  So while one can work
 around this issue by setting search_path with normal sessions it's not
 possible to do that during a restore from backup, leading to
 pg_restore failures if postgis functions are part of index expressions
 or materialized views despite them being fully schema-qualified.

 Here's a testcase that demonstrates the problem in materialized views.
 Putting the postgis schema into search_path will make the example work
 but yield a database that cannot be successfully dumped and restored:

 {{{
 create database regression;
 \c regression
 create extension postgis schema postgis;
 create schema postgis;
 create extension postgis schema postgis;
 create table t gist (c postgis.geometry);
 create index on t using gist (c);
 select 1 from t where
 postgis.st_dwithin(postgis.st_geomfromtext('POINT(3373062 5626876)'::text,
 31467), c, 1000.);
 }}}

 The unqualified lookup appears to happen via postgis_index_supportfn in
 this context:

 {{{
 (gdb) bt
 #0  postgis_oid (typ=typ at entry=GEOMETRYOID) at lwgeom_pg.c:96
 #1  0x00007fb139a031b3 in get_strategy_by_type (index=1, first_type=16498)
 at gserialized_supportfn.c:382
 #2  postgis_index_supportfn (fcinfo=<optimized out>) at
 gserialized_supportfn.c:385
 #3  0x00005631d50ccd2d in FunctionCall1Coll (flinfo=0x7ffe158edd00,
 collation=<optimized out>, arg1=<optimized out>) at
 ./build/../src/backend/utils/fmgr/fmgr.c:1140
 #4  0x00005631d50cd535 in OidFunctionCall1Coll (functionId=<optimized
 out>, collation=collation at entry=0, arg1=arg1 at entry=140729260105088) at
 ./build/../src/backend/utils/fmgr/fmgr.c:1418
 }}}


 An ad-hoc fix that made the dump restorable was injecting the
 following statement between --section=data and --section=post-data
 during the restore.

 {{{
 alter function postgis.postgis_index_supportfn set search_path = postgis;
 }}}

 Not sure if adding this to the extension control file is the best fix
 though: Maybe making postgis_index_supportfn look in which schema it
 resides and using the same to find to qualify the postgis function
 lookup is a better way?

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4739#comment:2>
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.