[postgis-users] operators and search paths

[Chris Mair]

Hi,

here is something strange I found about operators and search paths.


-> Background.

I'm on AWS/RDS/PostgreSQL, PostgreSQL is "PostgreSQL 14.4 on
x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12),
64-bit". PostGIS is "3.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1". PostGIS has been
activated simply by "create extension postgis" from the version installed by
RDS.


-> The problem.

If I run:

    postgres=> select 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::public.geometry && 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::public.geometry;
     ?column? 
    ----------
     t
    (1 row)

all is fine. But, if I run the same query in a situation where I altered my
search path:

    postgres=> set search_path = chris;
    SET

Postgres doesn't find the operator:

    postgres=> select 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::public.geometry && 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::public.geometry;
    ERROR:  operator does not exist: public.geometry && public.geometry
    LINE 1: ...YGON((0 0, 1 0, 1 1, 0 1, 0 0))'::public.geometry && 'POLYGO...
                                                                 ^
    HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

However, other versions of && seem to run just fine (not completely sure which one this
is, I think the gidx && gidx one):

    postgres=> show search_path;                                                                                                                                                
     search_path 
    -------------
     chris
    (1 row)

    postgres=> select public.ST_3DMakeBox(public.ST_MakePoint(0,0,0), public.ST_MakePoint(2,2,2)) && public.ST_3DMakeBox(public.ST_MakePoint(0,0,0), public.ST_MakePoint(2,2,2));
     ?column? 
    ----------
     t
    (1 row)


-> The expectation.

Either nothing works or everything works. I was not expecting the inconsistent
behaviour of one operator needing "public" in the search path and the other one
not needing it.


-> Some investigation.

It looks like PostGIS stores its function in the schema public. Fine.
However, I see operators are mapped to functions sometimes with the explicit schema
"public" and sometimes without:

    postgres=> select 
                  (select typname from pg_type where oid = oprleft) as left_type,
                  oprname,
                  (select typname from pg_type where oid = oprright) as right_type,
                  oprcode
               from pg_operator 
               where oprname = '&&' and oprcode::text like '%geo%';

     left_type | oprname | right_type |       oprcode        
    -----------+---------+------------+----------------------
     geometry  | &&      | geometry   | geometry_overlaps
     geography | &&      | geography  | geography_overlaps
     geography | &&      | gidx       | public.overlaps_geog
     gidx      | &&      | geography  | public.overlaps_geog
     gidx      | &&      | gidx       | public.overlaps_geog
    (5 rows)

Wouldn't it make sense for PostGIS to hardwire the path to where the functions are
installed for *all* of the operators? So all the operators are available regardless
search path?

Or is something completely different happening here (then please explain :)?

Thanks & Bye,
Chris.