Bulk request: Cannot connect to postgres

[johnnychoque]

Hi all,

I am trying to create entities for about 1000 sensors in bulk mode. At first, the entities are created correctly but then the following error is displayed at each attempt to create a new entity:

• error.response.data

 {
  type: 'https://uri.etsi.org/ngsi-ld/errors/InternalError',
  title: 'There has been an error during the operation execution',
  status: 500,
  detail: 'io.r2dbc.postgresql.PostgresqlConnectionFactory$PostgresConnectionException: [08003] Cannot connect to postgres/<unresolved>:5432',
  instance: '/ngsi-ld/v1/entityOperations/upsert'
}

• error.response.status - 500
• error.response.headers - Object [AxiosHeaders]

{
  vary: 'Origin, Access-Control-Request-Method, Access-Control-Request-Headers',
  'content-type': 'application/problem+json',
  'content-length': '328',
  'cache-control': 'no-cache, no-store, max-age=0, must-revalidate',
  pragma: 'no-cache',
  expires: '0',
  'x-content-type-options': 'nosniff',
  'x-frame-options': 'DENY',
  'x-xss-protection': '0',
  'referrer-policy': 'no-referrer',
  connection: 'close'
}

I have tried adding a delay of up to 3000 ms between each request but the error still appears. Setting a longer delay is not suitable for the system I am developing.

I am using the latest version of Stellio CB.

$ docker ps -a --format 'table {{.Image}}\t{{.Command}}\t{{.Names}}'
IMAGE                                             COMMAND                  NAMES
stellio/stellio-search-service:latest-dev         "java -cp @/app/jib-…"   stellio-search-service
stellio/stellio-subscription-service:latest-dev   "java -cp @/app/jib-…"   stellio-subscription-service
confluentinc/cp-kafka:7.3.1                       "bash -c 'if [ ! -f …"   stellio-kafka
stellio/stellio-timescale-postgis:14-2.11.1-3.3   "/bin/bash /scripts/…"   stellio-postgres
stellio/stellio-api-gateway:latest-dev            "java -cp @/app/jib-…"   stellio-api-gateway

Could you give me some ideas to solve this issue?

[bobeal]

Hi @johnnychoque,

Weird error, never seen this.

Can you send me the logs of the search service and PG / Timescale when you have the error?

[johnnychoque]

Hi @bobeal,

In stellio-search-service, the following error message is displayed:

 2024-05-24 10:09:48,208 [or-http-epoll-2] INFO  c.e.stellio.shared.web.ExceptionHandler  - generateErrorResponse - Returning error https://uri.etsi.org/ngsi-ld/errors/InternalError (reactor.netty.channel.AbortedException: Connection has been closed)

No error message is displayed in stellio-postgres.

[thomasBousselin]

Hi @johnnychoque
Do you only have the postgres connexion issue on the "/ngsi-ld/v1/entityOperations/upsert" endpoint or is it a global issue?

The problem can come from the STELLIO_SEARCH_DB_DATABASE environment variable. (it should be ="stellio_search").
Can you check if the env variable SPRING_R2DBC_URL is equal to r2dbc:postgresql://postgres/stellio_search inside the stellio-search-service container? and restart your infra?

[thomasBousselin]

I have tried to create 1000 entities in one go and my upsert still works.
If you tried the solution above and it did not work i could use your actual sensors entities to reproduce the issue.