"ERROR: operator does not exist: geometry public.&& geometry" after changing PostGIS extension schema

Question

I just moved PostGIS extension (I have version 2.5) from public schema to another (mob) schema by following the docs at this official PostGIS page.

I did:

ALTER DATABASE mob_odm 
SET search_path = public,mob;

UPDATE pg_extension 
  SET extrelocatable = TRUE 
    WHERE extname = 'postgis';
   
ALTER EXTENSION postgis 
  SET SCHEMA mob;
 
 ALTER EXTENSION postgis 
  UPDATE TO "2.5.4";

Now GeoServer (version 2.17.2) seems to raise errors everytime I make getFeature requests to a WFS. This used to work just before this change. It fails with this exception:

<?xml version="1.0" encoding="UTF-8"?><ows:ExceptionReport xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:ows="http://www.opengis.net/ows/1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0.0" xsi:schemaLocation="http://www.opengis.net/ows/1.1 http://localhost:8080/geoserver/schemas/ows/1.1.0/owsAll.xsd">
<ows:Exception exceptionCode="NoApplicableCode">
<ows:ExceptionText>java.lang.RuntimeException: java.io.IOException
java.io.IOExceptionERROR: operator does not exist: geometry public.&amp;amp;&amp;amp; geometry
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Where: SQL function &amp;quot;st_intersects&amp;quot; during inlining</ows:ExceptionText>
</ows:Exception>
</ows:ExceptionReport>

The query in the log is this one:

SELECT count(*) AS gt_result_ FROM (SELECT * FROM "mob"."signal_geo_outbound" WHERE ("level" IN ('level1', 'level2', 'level3') AND "level" IS NOT NULL  AND "db" = 'XXX' AND "db" IS NOT NULL  AND "freq" = 'daily' AND "freq" IS NOT NULL  AND "isdiag" = 0 AND "isdiag" IS NOT NULL  AND "geom" && ST_GeomFromText('POINT (1389121.0076528117 5486502.466631511)', 3857) AND ST_Intersects("geom", ST_GeomFromText('POINT (1389121.0076528117 5486502.466631511)', 3857)) AND "time" = '2020-09-20 00:00:00.0' AND "time" IS NOT NULL ) LIMIT 1000000) gt_limited_

The problem seems to be in the call to ST_Intersects:

Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Where: SQL function "st_intersects" during inlining

I am not that expert in using PostgreSQL, but from this answer I understand I would need to check that

PostGIS extension must be installed in a schema that must also be in the user search path

select e.extname,n.* 
from pg_extension e, pg_namespace n 
where e.extnamespace = n.oid and e.extname='postgis';

>>> extname | nspname | nspowner | nspacl
>>> postgis | mob     | 16,384   | NULL

And you can check if the schema where it is installed is in the user search path by issuing

show search_path;

>>> search_path
>>> "$user",mob,public

I don't know if I am supposed to do anything else, or if I have to move postgis back to the public schema in order to make GeoServer work.

The reason why I did that is that I am performing spatial queries to the database with a JavaScript library called pg-promise that is initialized in my case to work in the mob schema and could not work before making this change (now it works).

I also tried to restart GeoServer after moving the postgis extension to the different schema, but it did not solve the problem.

Answer 1

You need to check that the Postgres role that GeoServer is using to connect to the database has the search path set correctly. One way to check that is to connect using that role via psql or pgadmin and try to run that query. All the time it fails at the "commandline" for that role it will fail for GeoServer. You will then probably need to restart GeoServer to make sure that the updated role setting are picked up by GeoServer's connections.

Answer 2

I was hitting the same error, it seems I was missing one crucial step of the "change postgis schema" procedure. Steps to properly change the postgis schema (replace 3.1.4 with your version):

UPDATE pg_extension 
  SET extrelocatable = TRUE 
    WHERE extname = 'postgis';
 
ALTER EXTENSION postgis 
  SET SCHEMA postgis;
 
ALTER EXTENSION postgis 
  UPDATE TO "3.1.4next";
 
ALTER EXTENSION postgis 
  UPDATE TO "3.1.4";

ALTER USER xxx SET search_path TO "$user", postgis, topology, public;
SET search_path TO "$user", postgis, topology, public;

The crucial step is the update to [version]next part. As per the docs:

Note the use of the extension version that includes next. The next version step is needed in order to upgrade all the schema qualified function references to the new schema location. next is designed to allow upgrading a postgis extension to a version it is already on.

Answer 3

Not a real answer but after trying also other steps (see below), I decided to move back the postgis extension to the public schema where it was previously, and changing my web application to prefix the mob schema all the time I reference a table (e.g. my_table > mob.my_table).

The way I reverted my changes was:

UPDATE pg_extension 
  SET extrelocatable = TRUE 
    WHERE extname = 'postgis';
   
ALTER EXTENSION postgis 
  SET SCHEMA public;
 
 ALTER EXTENSION postgis 
  UPDATE TO "2.5.4";

The steps before I decided to give up were (taken from the answer Ian suggested in his comments):

ALTER USER umberto SET search_path TO "$user", public, mob;
SET search_path TO "$user", public, mob;

but unfortunately, it did not work.