[postgis-users] operators and search paths

[Regina Obe]

The  && operator for postgis types is defined in the schema you have postgis
installed in.  In this case public.

operators for built-in types are stored in the pg_catalog schema.
pg_catalog is always in the search path even if you don't explicitly add it.

So this should work though it's horribly ugly.  It's what we do within
postgis code.

SELECT  'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::public.geometry
OPERATOR(public.&&)  'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'::public.geometry;

As to why your below example works,  I suspect it's our geometry auto cast
to  postgresql box/point/polygon thus using the pg_catalog built in &&
designed for postgres built-in geometric types.  We have a ticket in place
for this as it has another ugly side-effect with !=
https://trac.osgeo.org/postgis/ticket/5175 

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));

> -----Original Message-----
> From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On
Behalf
> Of Chris Mair
> Sent: Wednesday, October 5, 2022 3:27 PM
> To: postgis-users at lists.osgeo.org
> Subject: [postgis-users] operators and search paths
> 
> 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.
> 
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users