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 [cid:image001.png@01D8FA94.9129F720] 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 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
_______________________________________________ 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
