Yugabyte as backend for Orthanc function error

General
1

Hi,

Me = Not SQL Wiz, and not an Orthanc contributor.

I’m currently experimenting yugabyte as a backend for the Orthanc software (it uses PostgreSQL).
Everything seems to works fine with some fine-tuning of parameters

CREATE ROLE orthanc WITH ENCRYPTED PASSWORD 'orthanc';
CREATE DATABASE orthanc OWNER orthanc;
ALTER ROLE orthanc SET yb_silence_advisory_locks_not_supported_error=on;
GRANT yb_extension TO orthanc;
ALTER ROLE orthanc LOGIN;

And the yugabyte master and tserver are started from the charts with these specific values

...
gflags:
  master:
    ysql_enable_auth: true
    default_memory_limit_to_ram_ratio: 0.85
    yb_enable_read_committed_isolation: true
  tserver:
    ysql_enable_auth: true
    yb_enable_read_committed_isolation: true
...

However there is one function that causes an error message. This function update some statistics and does not impact the behavior of the orthanc software to receive and send images.

W0809 13:26:02.213940           HTTP-5 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  unrecognized node type: 8388615
CONTEXT:  SQL statement "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"
PL/pgSQL function updatesinglestatistic(integer) line 6 at SQL statement
SQL statement "SELECT UpdateSingleStatistic(0)"
PL/pgSQL function updatestatistics() line 4 at SQL statement

Is there something not supported by Yugabyte? Or something that can be fixed in the code of the function?

Function code from the DB 
orthanc=> \sf+ updatestatistics
        CREATE OR REPLACE FUNCTION public.updatestatistics(OUT patients_cunt bigint, OUT studies_count bigint, OUT series_count bigint, OUT instances_count bigint, OUT total_compressed_size bigint, OUT total_uncompressed_size bigint)
         RETURNS record
         LANGUAGE plpgsql
1       AS $function$
2       BEGIN
3
4         SELECT UpdateSingleStatistic(0) INTO total_compressed_size;
5         SELECT UpdateSingleStatistic(1) INTO total_uncompressed_size;
6         SELECT UpdateSingleStatistic(2) INTO patients_cunt;
7         SELECT UpdateSingleStatistic(3) INTO studies_count;
8         SELECT UpdateSingleStatistic(4) INTO series_count;
9         SELECT UpdateSingleStatistic(5) INTO instances_count;
10
11      END;
12      $function$

orthanc=> \sf+ updatesinglestatistic
        CREATE OR REPLACE FUNCTION public.updatesinglestatistic(statistics_key integer, OUT new_value bigint)
         RETURNS bigint
         LANGUAGE plpgsql
1       AS $function$
2       BEGIN
3
4         -- Delete the current changes, sum them and update the GlobalIntegers row.
5         -- New rows can be added in the meantime, they won't be deleted or summed.
6         WITH deleted_rows AS (
7             DELETE FROM GlobalIntegersChanges
8             WHERE GlobalIntegersChanges.key = statistics_key
9             RETURNING value
10        )
11        UPDATE GlobalIntegers
12        SET value = value + (
13            SELECT COALESCE(SUM(value), 0)
14            FROM deleted_rows
15        )
16        WHERE GlobalIntegers.key = statistics_key
17        RETURNING value INTO new_value;
18
19      END;
20      $function$
2

Hi @czotti

Does this happen everytime that function gets executed? Or sometimes?

3

Hi, thanks for reporting. It may be hitting a bug. We will try to reproduce it.
Can you try to:

explain
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

to get the execution plan (in case this doesn’t crash)?

4

@dorian_yugabyte it happen every time the function is executed as far as I can see.

@FranckPachot This is the new error message now

W0809 14:37:52.006730          HTTP-13 PluginsManager.cpp:157] An SQL transaction failed and will likely be retried: ERROR:  invalid input syntax for integer: "Update on globalintegers  (cost=125.01..129.13 rows=1 width=44)"
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
5

Which version of Orthanc is it?
I looked at the source of 1.12.4 and cannot see those updatestatistics() functions and also no GlobalIntegersChanges table

6

It’s not directly in the orthanc repository but in there orthanc-database repo

UpdateSingleStatistic
UpdateStatistics

7

Thanks, I’m able to reproduce. I’ll look at it.

1 Like
8

I opened [YSQL] unrecognized node type: 8388615 · Issue #23461 · yugabyte/yugabyte-db · GitHub

One workaround is adding an explicit ::bigint type case in the function:

CREATE OR REPLACE FUNCTION UpdateSingleStatistic(
    IN statistics_key INTEGER,
    OUT new_value BIGINT
) AS $body$
BEGIN
  -- Delete the current changes, sum them and update the GlobalIntegers row.
  -- New rows can be added in the meantime, they won't be deleted or summed.
  WITH deleted_rows AS (
      DELETE FROM GlobalIntegersChanges
      WHERE GlobalIntegersChanges.key = statistics_key
      RETURNING value
  )
  UPDATE GlobalIntegers
  SET value = value + (
      SELECT COALESCE(SUM(value)::bigint, 0)
      FROM deleted_rows
  )
  WHERE GlobalIntegers.key = statistics_key
  RETURNING value INTO new_value;
END;
$body$ LANGUAGE plpgsql;

or if you can’t, disable predicate pushdown

ALTER ROLE orthanc SET yb_enable_expression_pushdown =off;

(updated: it’s not distinct pushdown but expression pushdown)
but I prefer the workaround at the scope of the function.

9

Thanks for the investigation. I tried to replace the function with your solution and encounter another error

E0812 12:00:31.731587          HTTP-12 PluginsManager.cpp:153] Internal error: The returned field is not of the correct type (Integer64)
E0812 12:00:31.731750          HTTP-12 PluginsManager.cpp:153] Exception in database back-end: Internal error

I’ll try to recreate my instance from scratch then the other solution to disable distinct pushdown.

Thanks a lot for the everything. I also made a post on the Orthanc discourse I’ll add a link to your findings.

1 Like
10

So your fix works for the Database part but unfortunately, orthanc produce an error with the type bigint I’ll try to investigate further.

orthanc=> SELECT UpdateSingleStatistic(0);
 updatesinglestatistic 
-----------------------
                      
(1 row)
1 Like
11

I realized I made a typo. It’s not a distinct pushdown but an expression pushdown. I edited my message. Sorry.
It’s strange that the ::bigint is not good for the application because the result is bigint anyway.

12

Quick update: the issue has been fixed and backported. It will be in the next minor releases. Thanks a lot for reporting it.

1 Like
13

Thanks a lot for resolving the issue this quickly.