Serialization errors in Orthanc server

[kabaluyot]

Setting up Orthanc server is fine but after some hours, there are lots of serialization errors and the Orthanc server doesn't work anymore. Here are the logs:

QUERY:  WITH deleted_rows AS (
      DELETE FROM GlobalIntegersChanges
      WHERE GlobalIntegersChanges.key = statistics_key
      RETURNING value
  )
  UPDATE GlobalIntegers
  SET value = value + (
      SELECT COALESCE(SUM(value), 0)
      FROM deleted_rows
  )
  WHERE GlobalIntegers.key = statistics_key
  RETURNING value
CONTEXT:  PL/pgSQL function updatesinglestatistic(integer) line 6 at SQL statement
SQL statement "SELECT UpdateSingleStatistic(0)"
PL/pgSQL function updatestatistics() line 4 at SQL statement

W0605 23:40:54.155419          HTTP-25 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "labels" does not exist at character 28

W0605 23:40:54.187205          HTTP-24 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "changes" does not exist at character 101

W0605 23:40:54.485109          HTTP-23 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "globalintegerschanges" does not exist
LINE 2:       DELETE FROM GlobalIntegersChanges
                          ^
QUERY:  WITH deleted_rows AS (
      DELETE FROM GlobalIntegersChanges
      WHERE GlobalIntegersChanges.key = statistics_key
      RETURNING value
  )
  UPDATE GlobalIntegers
  SET value = value + (
      SELECT COALESCE(SUM(value), 0)
      FROM deleted_rows
  )
  WHERE GlobalIntegers.key = statistics_key
  RETURNING value
CONTEXT:  PL/pgSQL function updatesinglestatistic(integer) line 6 at SQL statement
SQL statement "SELECT UpdateSingleStatistic(0)"
PL/pgSQL function updatestatistics() line 4 at SQL statement

W0605 23:40:54.486717          HTTP-25 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "labels" does not exist at character 28

W0605 23:40:54.487967          HTTP-24 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "changes" does not exist at character 101

W0605 23:40:54.903037          HTTP-25 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "labels" does not exist at character 28

W0605 23:40:54.927185          HTTP-23 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "globalintegerschanges" does not exist

W0605 23:41:05.579820          HTTP-27 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "changes" does not exist at character 101

E0605 23:41:05.580068          HTTP-27 StatelessDatabaseOperations.cpp:635] Maximum transactions retries reached
W0605 23:41:05.765784          HTTP-26 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "changes" does not exist at character 101

W0605 23:41:05.912086          HTTP-26 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "changes" does not exist at character 101

W0605 23:41:06.156054          HTTP-26 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "changes" does not exist at character 101

W0605 23:41:06.556157          HTTP-26 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "changes" does not exist at character 101

W0605 23:41:06.559837        SAVE-JOBS PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "serverproperties" does not exist at character 13

W0605 23:41:06.972664          HTTP-26 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "changes" does not exist at character 101

W0605 23:41:07.499249          HTTP-26 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "changes" does not exist at character 101

W0605 23:41:07.591456        SAVE-JOBS PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "serverproperties" does not exist at character 13

E0605 23:41:07.591752        SAVE-JOBS StatelessDatabaseOperations.cpp:635] Maximum transactions retries reached
E0605 23:41:07.591859        SAVE-JOBS ServerContext.cpp:341] Cannot serialize the jobs engine: Database could not serialize access due to concurrent update, the transaction should be retried
W0605 23:41:08.121023          HTTP-26 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "changes" does not exist at character 101

W0605 23:41:08.827233          HTTP-26 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "changes" does not exist at character 101

W0605 23:41:09.643659          HTTP-26 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "changes" does not exist at character 101

W0605 23:41:10.559972          HTTP-26 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "changes" does not exist at character 101

W0605 23:41:11.601684          HTTP-26 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  relation "changes" does not exist at character 101

E0605 23:41:11.601944          HTTP-26 StatelessDatabaseOperations.cpp:635] Maximum transactions retries reached

[kabaluyot]

Here is the docker compose config:

version: "3.8"

services:
  orthanc:
    image: orthancteam/orthanc:latest
    container_name: orthanc
    restart: unless-stopped
    ports:
      - 8053:8042
    volumes:
      - orthanc-storage:/var/lib/orthanc/db/
    environment:
      ORTHANC__NAME: "PACS Orthanc"
      ORTHANC__DICOM_AET: "PACS_AI"
      ORTHANC__DICOM_MODALITIES: |
        {
          "hospital-1": ["HOSPITAL_1", "orthanc-hospital-1", 4242],
          "hospital-2": ["HOSPITAL_2", "orthanc-hospital-2", 4242]
        }
      DICOM_WEB_PLUGIN_ENABLED: "true"
      ORTHANC__DICOM_WEB__PUBLIC_ROOT: "/orthanc/dicom-web/"
      ORTHANC__ORTHANC_EXPLORER_2__UI_OPTIONS__ENABLE_OPEN_IN_OHIF_VIEWER_3: "true"
      ORTHANC__ORTHANC_EXPLORER_2__UI_OPTIONS__OHIF_VIEWER_3_PUBLIC_ROOT: "http://localhost/orthanc/ohif/"
      STONE_WEB_VIEWER_PLUGIN_ENABLED: "true"
      OHIF_PLUGIN_ENABLED: "true"
      ORTHANC__OHIF: |
        {
          "RouterBasename": "/orthanc/ohif/",
          "DataSource" : "dicom-web"
        }
      ORTHANC__AUTHENTICATION_ENABLED: "false"
      ORTHANC__POSTGRESQL: |
        {
          "Host": "orthanc-index"
        }
      VERBOSE_STARTUP: "true"
    depends_on:
      - orthanc-index

  orthanc-index:
    image: postgres:14
    container_name: orthanc-index
    restart: unless-stopped
    ports: ["5432:5432"]
    volumes: ["orthanc-index:/var/lib/postgresql/data"]
    command: -c 'max_connections=200'
    environment:
      POSTGRES_HOST_AUTH_METHOD: "trust"

volumes:
  orthanc-storage:
  orthanc-index:

Was playing arouond with postgres version but still doesn't solve the issue for Postgres 14, 15, 16 ..

Also to be clear, spinning up works fine but its just after some time (like several hours), the errors started showing

[amazy]

Could you try with this option ?

      ORTHANC__POSTGRESQL: |
        {
          "Host": "orthanc-index",
          "TransactionMode": "ReadCommitted"
        }

That should clearly reduce the number of collisions.