Connection drops during batch post

[C-Loftus]

Hello,

I am trying to batch insert around ~2000 datastreams however I am having an error each time with the SQL connection internal to FROST and isn't tied to any specific datastream or batch post.

2025-05-19 12:19:38 16:19:38.259 [http-nio-8080-exec-8] ERROR      d.f.i.i.f.service.Service - Failed to handle request (details in debug): SQL [select count(*) from "OBS_PROPERTIES" where "OBS_PROPERTIES"."ID" = ?]; Connection is null.

I can typically connect/batch post fine with an http client so presumably something is failing due to either size or internal timeouts.

I have tried to enable debug logging but there is quite a bit of log spam so I can't sort through it well to determine what the error is.

My own application level logging (i.e. I wrote this myself to trace the payload) outputs a log usually like this

Got error msg: {'id': '1', 'status': 500} when posting batch item: {'id': '1', 'method': 'post', 'url': 'Datastreams', 'body': {'@iot.id': 'LANE0011612', 'name': 'Waterlevel below land surface for well 11612', 'description': 'Type of log: Water Well', 'observationType': 'http://www.opengis.net/def/observationType/OGC-OM/2.0/OM_Measurement', 'unitOfMeasurement': {'name': 'feet', 'symbol': 'ft', 'definition': 'https://qudt.org/vocab/unit/FT'}, 'ObservedProperty': {'@iot.id': 77608, 'name': 'Groundwater Depth', 'definition': 'Groundwater depth is the distance between the water surface and the ground surface at a specific location specified by the site location and offset.', 'description': 'Originally from the CUAHSI HIS VariableNameCV. See: http://his.cuahsi.org/mastercvreg/edit_cv11.aspx?tbl=VariableNameCV.', 'properties': {'uri': 'http://vocabulary.odm2.org/api/v1/variablename/groundwaterDepth'}}, 'Sensor': {'@iot.id': 0, 'name': 'Unknown', 'description': 'Unknown', 'encodingType': 'Unknown', 'metadata': 'Unknown'}, 'Thing': {'@iot.id': 'LANE0011612', 'name': 'Well LANE0011612', 'description': 'Well LANE0011612'}}}

I have tried changing the batch size from 50, 200, 500, 800 or 1000 and all fail with the same error but sometimes it is on a different batch, so it doesn't appear to be an issue with a specific payload.

My client code just posts a json to the batch endpoint and isn't using any particularly complex features of the sensorthings spec. I know the batch syntax I wrote is correct since it works in test code with a dummy post to FROST.

I am using a local postgres database and this is my config for my open source project called OWDP

services:
  owdp-frost:
    container_name: owdp-frost
    build:
      context: ./docker/frost
    environment:
      - serviceRootUrl=${OWDP_URL}/FROST-Server
      - persistence_db_url=jdbc:postgresql://owdp-database:5432/sensorthings
      - persistence_db_username=sensorthings
      - persistence_db_password=ChangeMe
      - persistence_autoUpdateDatabase=true
    ports:
      - 1883:1883
      - 8080:8080
    depends_on:
      - owdp-database

  owdp-database:
    container_name: owdp-database
    build:
      context: ./docker/database
    environment:
      - POSTGRES_DB=sensorthings
      - POSTGRES_USER=sensorthings
      - POSTGRES_PASSWORD=ChangeMe
    volumes:
      - postgis_volume:/var/lib/postgresql/data

I am using FROM postgis/postgis:14-3.2-alpine for my db image and this is my dockerfile for Frost;

FROM fraunhoferiosb/frost-server

ENV plugins_multiDatastream_enable=false
ENV http_cors_enable=true
ENV http_cors_allowed_origins=*
ENV persistence_db_driver=org.postgresql.Driver
ENV persistence_autoUpdateDatabase=true
ENV persistence_idGenerationMode=ServerAndClientGenerated
ENV plugins_coreModel_idType_thing=STRING
ENV plugins_coreModel_idType_location=STRING
ENV plugins_coreModel_idType_datastream=STRING
ENV plugins_coreModel_idType_feature=STRING
ENV mqtt_Enabled=false
ENV persistence.slowQueryThreshold=2300

[hylkevds]

Hmm, the "Connection is null" error is odd. Especially since for a single batch request FROST will be using a single connection, since the entire batch runs in a transaction.

Does the error (always) happen on the first item in the batch?
Are you sending multiple batch requests in parallel?
Which FROST version are you using?
Could you try adding a PGAdmin4 container to your setup, and check the telemetry to see if there is anything unusual?

[C-Loftus]

Thank you very much for your response

Does the error (always) happen on the first item in the batch?

No it does not; sometimes it is the first and sometimes it happens midway through at a random index. Here's a screenshot of an example from my logs for context.

Are you sending multiple batch requests in parallel?

No I am not; I am inserting them in a for loop with a blocking post request. There are no other clients connected since it is all local testing at the moment.

Which FROST version are you using?

2025-05-19 14:06:49 18:06:49.102 [            main] INFO   d.f.i.i.f.util.GitVersionInfo - FROST-Server.Core Version: v2.5.0-61-g1897f9e

Could you try adding a PGAdmin4 container to your setup, and check the telemetry to see if there is anything unusual?

I am honestly not super familiar with pgadmin or what stats I should be looking to see, but I did manage to get it set up.

That image above shows the stats of what happens when I start the batch request. Note that some of the higher numbers may be coming from me checking which datastream id are in the db before inserting, but this does not give an error for me and completes fine.

[hylkevds]

Can you try with the logging level for the Service on DEBUG, and sending the stacktrace?

FROST_LL_service=DEBUG

[C-Loftus]

Thanks for your response. I have since changed my code so it is a bit harder to reproduce now but if I find some time to switch it back and I will let you know and post it here. Sorry about that.