GeoServer complains because osm2pgsql didn’t create primary keys

Question

I imported OSM data into a PostGIS database using osm2pgsql, then served it in WFS via GeoServer.

However, QGIS gets stalled when trying to view these layers. I found corresponding GeoServer errors in the logs :

2020-06-16 14:17:44,394 INFO [geoserver.servlets] - OutputStream was successfully aborted.
2020-06-16 14:17:44,394 ERROR [geoserver.ows] - 
java.lang.RuntimeException: java.lang.RuntimeException: java.io.IOException
    at org.geotools.xsd.Encoder.encode(Encoder.java:729)
    at org.geotools.xsd.Encoder.encode(Encoder.java:549)
    at org.geoserver.wfs.xml.GML32OutputFormat.encode(GML32OutputFormat.java:151)
    at org.geoserver.wfs.xml.GML3OutputFormat.write(GML3OutputFormat.java:312)
    at org.geoserver.wfs.WFSGetFeatureOutputFormat.write(WFSGetFeatureOutputFormat.java:195)
    at org.geoserver.ows.Dispatcher.response(Dispatcher.java:1030)
    at org.geoserver.ows.Dispatcher.handleRequestInternal(Dispatcher.java:269)
    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:1040)
    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:624)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.geoserver.filters.ThreadLocalsCleanupFilter.doFilter(ThreadLocalsCleanupFilter.java:26)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:69)
    at org.geoserver.wms.animate.AnimatorFilter.doFilter(AnimatorFilter.java:70)
    at org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:66)
    at org.geoserver.filters.SpringDelegatingFilter.doFilter(SpringDelegatingFilter.java:41)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.geoserver.platform.AdvancedDispatchFilter.doFilter(AdvancedDispatchFilter.java:37)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:70)
    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:74)
    at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:70)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:74)
    at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.geoserver.security.filter.GeoServerAnonymousAuthenticationFilter.doFilter(GeoServerAnonymousAuthenticationFilter.java:51)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:70)
    at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:215)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:74)
    at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
    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:70)
    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:74)
    at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:91)
    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.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.geoserver.filters.LoggingFilter.doFilter(LoggingFilter.java:101)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.geoserver.filters.XFrameOptionsFilter.doFilter(XFrameOptionsFilter.java:77)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.geoserver.filters.GZIPFilter.doFilter(GZIPFilter.java:47)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.geoserver.filters.SessionDebugFilter.doFilter(SessionDebugFilter.java:46)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.geoserver.filters.FlushSafeFilter.doFilter(FlushSafeFilter.java:42)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:218)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:498)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:962)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.RuntimeException: java.io.IOException
    at org.geotools.data.store.ContentFeatureCollection.features(ContentFeatureCollection.java:165)
    at org.geotools.data.store.ContentFeatureCollection.features(ContentFeatureCollection.java:52)
    at org.geotools.data.crs.ReprojectFeatureResults.openIterator(ReprojectFeatureResults.java:92)
    at org.geotools.feature.collection.AbstractFeatureCollection.features(AbstractFeatureCollection.java:65)
    at org.geotools.feature.collection.AbstractFeatureCollection.features(AbstractFeatureCollection.java:50)
    at org.geoserver.security.decorators.SecuredFeatureCollection.features(SecuredFeatureCollection.java:43)
    at org.geoserver.security.decorators.SecuredSimpleFeatureCollection.features(SecuredSimpleFeatureCollection.java:74)
    at org.geotools.feature.collection.DecoratingSimpleFeatureCollection.features(DecoratingSimpleFeatureCollection.java:88)
    at org.geotools.gml2.simple.FeatureCollectionEncoderDelegate.encode(FeatureCollectionEncoderDelegate.java:110)
    at org.geotools.xsd.Encoder.encode(Encoder.java:727)
    ... 100 more
Caused by: java.io.IOException
    at org.geotools.jdbc.JDBCFeatureSource.getReaderInternal(JDBCFeatureSource.java:624)
    at org.geotools.data.store.ContentFeatureSource.getReader(ContentFeatureSource.java:612)
    at org.geotools.data.store.ContentFeatureCollection.features(ContentFeatureCollection.java:163)
    ... 109 more
Caused by: java.io.IOException: Cannot do natural order without a primary key, please add it or specify a manual sort over existing attributes
    at org.geotools.jdbc.JDBCDataStore.sort(JDBCDataStore.java:3483)
    at org.geotools.jdbc.JDBCDataStore.selectSQL(JDBCDataStore.java:3320)
    at org.geotools.jdbc.JDBCFeatureSource.getReaderInternal(JDBCFeatureSource.java:591)
    ... 111 more

Searching in particular for IOException: Cannot do natural order without a primary key, please add it or specify a manual sort over existing attributes, I identified the issue as the lack of a primary key, needed for pagination with WFS 2.0.0 (a fix is to disable pagination by unchecking the relative box in the WFS connection settings in QGIS).

I am surprised osm2pgsql doesn’t take care of this, am I missing an option ? What is the best way to fix this ?

Update

I manually added primary keys, but GeoServer fails to see them, even after reloading the GeoServer webapp, restarting TomCat, using the "refresh" link in the GeoServer PostGIS layers, or removing and re-adding the layers.

I guess I am still missing something ?

@IanTurton Yes, I later found this helpful answer, the tables are currently being updated and I will test asap. I also found this related github issue. — Skippy le Grand Gourou, Jun 16 '20 at 15:45
Did you actually add a primary key or just a unique id column? — Ian Turton, Jun 16 '20 at 18:47
I used ALTER TABLE planet_osm_line ADD gid serial PRIMARY KEY; as suggested in the linked answer. \d planet_osm_line states gid | integer | | not null | nextval('planet_osm_line_gid_seq'::regclass) Indexes: "planet_osm_line_pkey" PRIMARY KEY, btree (gid). Anyway to my understanding even a standard new column should appear in GeoServer ? — Skippy le Grand Gourou, Jun 16 '20 at 19:16
@IanTurton Solution found, it was indeed GeoServer configuration. Thanks for the mention of the vacuum analysis, I learned something more. — Skippy le Grand Gourou, Jun 18 '20 at 13:35

score 1 · Accepted Answer · answered Oct 23 '20 at 21:16

The "Cannot do natural order without a primary key, please add it or specify a manual sort over existing attributes" exception may arise for a layer made of an SQL view.

In this case, it can be fixed by going to the "edit the SQL view" page and checking one (or several ?) of the rows of the table as "Identifier". Note that the selected field won’t be public anymore.

Skippy le Grand Gourou · Answer 2 · 2020-10-23T21:09:42.210

Disclaimer : this answer is likely wrong, as hinted by Ian. Indeed, using the same data with a fresh GeoServer installation, I don’t get the error anymore, although "Expose primary keys" is not checked.

By default, primary keys are not exposed by GeoServer. This is why they don’t appear in the list of properties of the layer.

They will appear after you check "Expose primary keys" in the PostGIS store configuration.

As for osm2pgsql not creating primary keys by default, it doesn’t seem to be on the roadmap for most backends, but it is straightforward with the new flex backend : just add

{ column = 'id', type = 'serial', create_only = true },

to your flex configuration.

GeoServer should use the primary key without needing to expose it — Ian Turton, Jun 18 '20 at 13:40

GeoServer complains because osm2pgsql didn’t create primary keys

Update

2 Answers2