[postgis-users] operators and search paths
Chris Mair
chris at 1006.org
Wed Oct 5 12:27:04 PDT 2022
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.
More information about the postgis-users
mailing list