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.
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; geometry Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Where: SQL function &quot;st_intersects&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
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.
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.