Error establishing connection pool - Smart Data loader

GeoServer GeoServer User
, ,
1

I have GeoServer 2.26 with plugins app-schema, and 2 comunity plugins, smart-data-loader and ogcapi-features.
I have a PostGIS store and try to add a Smart Data Loader. I get the domain model view in the GUI. On attempt to save the store I get errors cited below.
I see no error in the database log, (but a bunch of SELECT statements).
Postgres is version 13.15.
Both GeoServer and Postgres/PostGIS are in running in docker containers.

Am I in the right forum for such a question ?
The reWriteBatchedInserts seems to be not set correctly in the plugin’s Java code (?)

GeoServers Error message:
Error creating data store, check the parameters. Error message: Unable to obtain connection: Cannot create PoolableConnectionFactory (oid type bytea?reWriteBatchedInserts=false not known and not a number)

The server log has these:
16 Nov 16:20:34 WARN [data.store] - Error obtaining new data store
java.io.IOException
. . .
Caused by: java.lang.RuntimeException: Unable to obtain connection: Cannot create PoolableConnectionFactory (oid type bytea?reWriteBatchedInserts=false not known and not a number)
at org.geotools.jdbc.JDBCDataStore.createConnection(JDBCDataStore.java:2343)
. . .
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (oid type bytea?reWriteBatchedInserts=false not known and not a number)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
. . .
Caused by: org.postgresql.util.PSQLException: oid type bytea?reWriteBatchedInserts=false not known and not a number
at org.postgresql.core.Oid.valueOf(Oid.java:142)
at org.postgresql.jdbc.PgConnection.getOidSet(PgConnection.java:479)
at org.postgresql.jdbc.PgConnection.getBinaryEnabledOids(PgConnection.java:449)
at org.postgresql.jdbc.PgConnection.(PgConnection.java:291)
at org.postgresql.Driver.makeConnection(Driver.java:446)

2

Hi @mbwiese

Looks like PostGisNG GeoTools driver automatically adds the postgresql connection URL configurations query parameter: ?reWriteBatchedInserts=true/false
This looks a recent addition to latest GeoServer versions during this (2024) year.

You can start checking if you are adding some extra values on the database field on the postgis store, in any case this url parameter will be added by the driver causing this wrong behavior building a malformed JDBC URL. Same check applies if a JNDI connection is in use.

Maybe would you like to share your datastore parameters to build the postgisNG datastore on GeoServer? (you can omit passwords and host names).

3

Thanks for answering !
This is my underlying PostGIS datastore, which apparently works, since the Smart Data Loader can connect and build xsd and xml:

  <type>PostGIS</type>
  <enabled>true</enabled>
  <workspace>
    <id>WorkspaceInfoImpl--37ea96ed:1932baa1207:-8000</id>
  </workspace>
  <connectionParameters>
    <entry key="schema">litho</entry>
    <entry key="Evictor run periodicity">300</entry>
    <entry key="Max open prepared statements">50</entry>
    <entry key="encode functions">true</entry>
    <entry key="Batch insert size">1</entry>
    <entry key="preparedStatements">false</entry>
    <entry key="database">hvd</entry>
    <entry key="reWriteBatchedInserts">false</entry>
    <entry key="host">10.23.4.35</entry>
    <entry key="Loose bbox">true</entry>
    <entry key="SSL mode">DISABLE</entry>
    <entry key="Estimated extends">true</entry>
    <entry key="fetch size">1000</entry>
    <entry key="Expose primary keys">false</entry>
    <entry key="validate connections">true</entry>
    <entry key="Support on the fly geometry simplification">true</entry>
    <entry key="Connection timeout">20</entry>
    <entry key="create database">false</entry>
    <entry key="Method used to simplify geometries">FAST</entry>
    <entry key="port">5432</entry>
    <entry key="passwd">crypt1:WTO9opI8m6/a9/vBVGYB/8v9mU6ITuAZ</entry>
    <entry key="min connections">1</entry>
    <entry key="dbtype">postgis</entry>
    <entry key="namespace">http://data.geus.dk/hvd_litho</entry>
    <entry key="max connections">10</entry>
    <entry key="Evictor tests per run">3</entry>
    <entry key="Test while idle">true</entry>
    <entry key="user">gis_owner</entry>
    <entry key="Max connection idle time">300</entry>
  </connectionParameters>
  <__default>false</__default>
  <dateCreated>2024-11-14 17:15:07.490 UTC</dateCreated>
  <dateModified>2024-11-18 15:52:49.975 UTC</dateModified>
  <disableOnConnFailure>false</disableOnConnFailure>
</dataStore>

And this is the connection mentioned in the xml file that the Smart Data Loader produces, which seems to fail:

<sourceDataStores>
    <DataStore>
      <id>litho</id>
      <parameters>
        <Parameter>
          <name>schema</name>
          <value>litho</value>
        </Parameter>
        <Parameter>
          <name>database</name>
          <value>hvd?reWriteBatchedInserts=false&amp;sslmode=DISABLE&amp;binaryTransferEnable=bytea</value>
        </Parameter>
        <Parameter>
          <name>port</name>
          <value>5432</value>
        </Parameter>
        <Parameter>
          <name>passwd</name>
          <value>xxxxxxxx</value>
        </Parameter>
        <Parameter>
          <name>Expose primary keys</name>
          <value>true</value>
        </Parameter>
        <Parameter>
          <name>dbtype</name>
          <value>postgis</value>
        </Parameter>
        <Parameter>
          <name>host</name>
          <value>10.23.4.35</value>
        </Parameter>
        <Parameter>
          <name>user</name>
          <value>gis_owner</value>
        </Parameter>
      </parameters>
    </DataStore>
  </sourceDataStores>
4

Oh, I forgot about the preformatted text markdown . .

5

So I edited my text

7

Hi @mbwiese,

I see your datastore definition is correct. Due to the new added URL parameters to the JDBC connection URL coming from Postgresql geotools store module, smart data loader is not able to recognize and separate the database identifier from the remaining text adding parameters.

This looks like a Smart Data Loader bug to me. Please can you open a bug issue on GeoServer JIRA detailing this? (you can also link this thread).

8

I have created an issue as you requested, where I link to this issue.