Hi Hans, Julian,
Thank you, Hans, for the elaborate reply.
* Activating "NumberMatched skip" as a workaround is successful under
geosever 2.21.2
* Julian: the WFS query is
http://localhost:8080/geoserver/eposgo/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=eposgo%3AMagnetotelluricStationsFeature&maxFeatures=5&outputFormat=application%2Fjson
(this is copied from the layer preview and any format will generate
the error; same if I remove maxFeatures)
* Although I have never touched "NumberMatched skip" before, I checked
the setting under geoserver 2.21.1, and it is as expected
deactivated. Thus,
o the query above under geoserver 2.21.1 works totally fine
o the query above under geoserver 2.21.2 works only with
"NumberMatched skip" activated
I have now a workaround and will try to follow up according to Hans's
suggestion. But this still leaves two open questions:
* Why has the behaviour of geoserver changed from 2.21.1 to 2.21.2?
* Did the change introduce a bug, or is this expected behaviour?
(which would mean that the query worked under 2.21.1 because of a
bug that was eliminated in 2.21.2)
Best wishes,
Henning
On 2022-11-17 15:14, Hans Yperman wrote:
Page Title
I'll add my 2 cents.
The problem is not with the driver, but with the query generated by
geoserver: This text:
ERROR: function count(character varying, character varying, character
varying) does not exist Hint: No function matches the given name and
argument types. You might need to add explicit type casts. Position: 8
is an SQL error message from postgres. The postgres jdbc driver is
just the transport of this message, not the cause. The SQL is
generated by
at
org.geotools.appschema.jdbc.JoiningJDBCFeatureSource.getCountInternal(JoiningJDBCFeatureSource.java:1523)
so let's look at that
file:https://github.com/geotools/geotools/blob/main/modules/extension/app-schema/app-schema/src/main/java/org/geotools/appschema/jdbc/JoiningJDBCFeatureSource.java#L1523
The query comes from either createCountQuery or
createJoiningCountQuery, and both generate a query with this shape:
SELECT COUNT(DISTINCT idcolumn,idcolumn,…) FROM (…)
Notice how COUNT is on position 8 in this query, which matches the
error message. The error also claims there are 3 arguments given to
count. Now, 3 args seems strange: AFAIK the count syntax accepts
only 1 column. If there are multiple ID columns, geoserver does
something I can't help you with.
Next steps could be:
1) Try to get hold of the actual query, e.g. grab it from the network
with wireshark, log it on the postgres server, put a java breakpoint
on that line 1523, …
2) Once you have it, run it on your database with pgadmin or psql or
… . Validate you get the same error message. Play with it until the
error goes away.
3) Convince geoserver to generate a valid query based on whatever you
learned from 2). I'd guess this would do something with the id columns.
There might be a workaround: Under Layer settings > Publish, there is
a setting that lets you skip the NumberMatched counting. If you can
live without this count, you can hide the problem by enabling that
setting.
Good luck,
Hans
*Hans Yperman*
Department IT
*Vlaams Instituut voor de Zee vzw*
InnovOcean Campus, Jacobsenstraat 1
8400 Oostende, België
☎+32 (0) 59 33 61 13
📧hans.yper...@vliz.be <mailto:hans.yper...@vliz.be>
*www.vliz.be <http://www.vliz.be>***
*From:* Henning Lorenz <henning.lor...@geo.uu.se>
*Sent:* donderdag 17 november 2022 12:07
*To:* geoserver-users@lists.sourceforge.net
*Subject:* [Geoserver-users] "org.postgresql.util.PSQLException" in
geoserver 2.21.2 (not in 2.21.1)
Hello,
I have a fully functional WFS service under geoserver 2.21.1 that
results in a postgresql-related error in 2.22.2.
Setup: geoserver, extensions: app-schema, features-templating,
ogc-api, smart-data-loader; PostgreSQL 15 (same for 14); jdk 11 (same
for 17)
The service works under version 2.21.1 in Tomcat 9. The service fails
under version 2.21.2 in Tomcat 9 and standalone with the following
error (no other change to underlying software, configuration):
<ServiceExceptionReport version="1.2.0"
xsi:schemaLocation="http://www.opengis.net/ogc
http://schemas.opengis.net/wfs/1.0.0/OGC-exception.xsd";
<http://www.opengis.net/ogchttp:/schemas.opengis.net/wfs/1.0.0/OGC-exception.xsd>>
<ServiceException>
java.lang.RuntimeException: java.io.IOException
java.io.IOExceptionERROR: function count(character varying,
character varying, character varying) does not exist Hint: No
function matches the given name and argument types. You might need
to add explicit type casts. Position: 8
</ServiceException>
</ServiceExceptionReport>
According to the log, this is a "org.postgresql.util.PSQLException". I
paste the complete entry from the log (geoserver developer logging)
below. It appears to be a problem with the PostgreSQL driver, but I am
not sure how to proceed and how to track the problem down, so any
advice is highly appreciated. (Was the driver changed during 2.21.1/2
development? Is it possible to downgrade the driver and test?)
Henning
ERROR [geoserver.ows] -
java.lang.RuntimeException: java.io.IOException
at
org.geotools.data.complex.MappingFeatureCollection.size(MappingFeatureCollection.java:329)
at org.geoserver.wfs.CountExecutor.getCount(CountExecutor.java:44)
at org.geoserver.wfs.GetFeature.getTotalCount(GetFeature.java:912)
at org.geoserver.wfs.GetFeature.updateTotalCount(GetFeature.java:725)
at org.geoserver.wfs.GetFeature.run(GetFeature.java:637)
at
org.geoserver.wfs.DefaultWebFeatureService.getFeature(DefaultWebFeatureService.java:109)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at
org.geoserver.ows.util.RequestObjectLogger.invoke(RequestObjectLogger.java:51)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at jdk.proxy3/jdk.proxy3.$Proxy126.getFeature(Unknown Source)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.geoserver.ows.Dispatcher.execute(Dispatcher.java:867)
at
org.geoserver.ows.Dispatcher.handleRequestInternal(Dispatcher.java:268)
at
org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:177)
at
org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:52)
at
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1043)
at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
at
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at
org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1459)
at
org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:799)
at
org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1656)
at
org.geoserver.filters.ThreadLocalsCleanupFilter.doFilter(ThreadLocalsCleanupFilter.java:28)
at
org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
at
org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
at
org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:73)
at
org.geoserver.ows.HTTPHeadersCollector.doFilter(HTTPHeadersCollector.java:48)
at
org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:70)
at org.geoserver.filters.LoggingFilter.doFilter(LoggingFilter.java:194)
at
org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:70)
at
org.geoserver.filters.SpringDelegatingFilter.doFilter(SpringDelegatingFilter.java:43)
at
org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
at
org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
at
org.geoserver.platform.AdvancedDispatchFilter.doFilter(AdvancedDispatchFilter.java:39)
at
org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
at
org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:71)
at
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
at
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:75)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:71)
at
org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:75)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.geoserver.security.filter.GeoServerAnonymousAuthenticationFilter.doFilter(GeoServerAnonymousAuthenticationFilter.java:53)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:71)
at
org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:158)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:75)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
at
org.geoserver.security.filter.GeoServerBasicAuthenticationFilter.doFilter(GeoServerBasicAuthenticationFilter.java:81)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:71)
at
org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
at
org.geoserver.security.filter.GeoServerSecurityContextPersistenceFilter$1.doFilter(GeoServerSecurityContextPersistenceFilter.java:52)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:75)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
at
org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215)
at
org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178)
at
org.geoserver.security.GeoServerSecurityFilterChainProxy.doFilter(GeoServerSecurityFilterChainProxy.java:142)
at
org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358)
at
org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271)
at
org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
at
org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
at
org.geoserver.filters.XFrameOptionsFilter.doFilter(XFrameOptionsFilter.java:77)
at
org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
at
org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
at org.geoserver.filters.GZIPFilter.doFilter(GZIPFilter.java:48)
at
org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
at
org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
at
org.geoserver.filters.SessionDebugFilter.doFilter(SessionDebugFilter.java:49)
at
org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
at
org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
at
org.geoserver.filters.FlushSafeFilter.doFilter(FlushSafeFilter.java:42)
at
org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)
at
org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
at
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at
org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:201)
at
org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)
at
org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:552)
at
org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at
org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:600)
at
org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
at
org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1624)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
at
org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1440)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
at
org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:505)
at
org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
at
org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1355)
at
org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at
org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:191)
at
org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)
at
org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.Server.handle(Server.java:516)
at
org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:487)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:732)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:479)
at
org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277)
at
org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
at
org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:338)
at
org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:315)
at
org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:173)
at
org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)
at
org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:409)
at
org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883)
at
org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034)
at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.io.IOException
at
org.geotools.appschema.jdbc.JoiningJDBCFeatureSource.getCountInternal(JoiningJDBCFeatureSource.java:1535)
at
org.geotools.data.store.ContentFeatureSource.getCount(ContentFeatureSource.java:488)
at
org.geotools.data.complex.AppSchemaDataAccess.getCount(AppSchemaDataAccess.java:314)
at
org.geotools.data.complex.MappingFeatureCollection.size(MappingFeatureCollection.java:327)
... 131 more
Caused by: org.postgresql.util.PSQLException: ERROR: function count(character
varying, character varying, character varying) does not exist
Hint: No function matches the given name and argument types. You might need
to add explicit type casts.
Position: 8
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at
org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at
org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)
at
org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)
at
org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:243)
at
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at
org.geotools.appschema.jdbc.JoiningJDBCFeatureSource.getCountInternal(JoiningJDBCFeatureSource.java:1523)
... 134 more
GnuPG-key:
http://pgp.mit.edu:11371/pks/lookup?search=0x484D3AF03B32FD38&op=index
<http://pgp.mit.edu:11371/pks/lookup?search=0x484D3AF03B32FD38&op=index>
När du har kontakt med oss på Uppsala universitet med e-post så
innebär det att vi behandlar dina personuppgifter. För att läsa mer om
hur vi gör det kan du läsa här:
http://www.uu.se/om-uu/dataskydd-personuppgifter/
E-mailing Uppsala University means that we will process your personal
data. For more information on how this is performed, please read here:
http://www.uu.se/en/about-uu/data-protection-policy
--
Henning Lorenz
The Swedish Scientific Drilling Program (www.ssdp.se), scientific coordinator
EPOS-Sweden (Swedish participation in EPOS-ERIC;www.epos-se.se), infrastructure
manager
Uppsala University
Department of Earth Sciences
Villavägen 16
752 36 Uppsala
Sweden
mobile: +46 (0) 701 679 320
e-mail:henning.lor...@geo.uu.se
GnuPG-key:
http://pgp.mit.edu:11371/pks/lookup?search=0x484D3AF03B32FD38&op=index
_______________________________________________
Geoserver-users mailing list
Please make sure you read the following two resources before posting to this
list:
- Earning your support instead of buying it, but Ian Turton:
http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines:
http://geoserver.org/comm/userlist-guidelines.html
If you want to request a feature or an improvement, also see this:
https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
