date_time_started problem in charts

[Mark Pearson]

When chart types Acquisition kVp over time or Acquisition mAs over time are selected an error for each row is generated. This causes CPU usage for postgresql to go to 100%. This is a typical error:

2019-12-30 11:28:53.766 AEDT [27703] openremuser@openremdb ERROR: column "projectionxrayradiationdose__irradeventxraydata__date_time_star" does not exist at character 38
2019-12-30 11:28:53.766 AEDT [27703] openremuser@openremdb STATEMENT:
SELECT DISTINCT (date_trunc('month', projectionxrayradiationdose__irradeventxraydata__date_time_started)) AS "d", (Median("remapp_exposure"."exposure") * 0.001) AS "agg" FROM "remapp_generalstudymoduleattr" INNER JOIN "remapp_generalequipmentmoduleattr" ON ( "remapp_generalstudymoduleattr"."id" = "remapp_generalequipmentmoduleattr"."general_study_module_attributes_id" ) INNER JOIN "remapp_uniqueequipmentnames" ON ( "remapp_generalequipmentmoduleattr"."unique_equipment_name_id" = "remapp_uniqueequipmentnames"."id" ) LEFT OUTER JOIN "remapp_projectionxrayradiationdose" ON ( "remapp_generalstudymoduleattr"."id" = "remapp_projectionxrayradiationdose"."general_study_module_attributes_id" ) LEFT OUTER JOIN "remapp_irradeventxraydata" ON ( "remapp_projectionxrayradiationdose"."id" = "remapp_irradeventxraydata"."projection_xray_radiation_dose_id" ) INNER JOIN "remapp_projectionxrayradiationdose" T6 ON ( "remapp_generalstudymoduleattr"."id" = T6."general_study_module_attributes_id" ) INNER JOIN "remapp_irradeventxraydata" T7 ON ( T6."id" = T7."projection_xray_radiation_dose_id" ) LEFT OUTER JOIN "remapp_irradeventxraysourcedata" ON ( "remapp_irradeventxraydata"."id" = "remapp_irradeventxraysourcedata"."irradiation_event_xray_data_id" ) LEFT OUTER JOIN "remapp_exposure" ON ( "remapp_irradeventxraysourcedata"."id" = "remapp_exposure"."irradiation_event_xray_source_data_id" ) WHERE (("remapp_generalstudymoduleattr"."modality_type" = 'DX' OR "remapp_generalstudymoduleattr"."modality_type" = 'CR') AND "remapp_generalstudymoduleattr"."study_date" >= '2019-12-09'::date AND "remapp_generalstudymoduleattr"."study_date" <= '2019-12-09'::date AND UPPER("remapp_generalstudymoduleattr"."study_description"::text) LIKE UPPER('%Chest%') AND UPPER("remapp_uniqueequipmentnames"."display_name"::text) LIKE UPPER('%ROYAL HOBART HOSPITAL DiDiEleva02%') AND NOT ("remapp_generalstudymoduleattr"."id" IN (SELECT U1."general_study_module_attributes_id" AS Col1 FROM "remapp_projectionxrayradiationdose" U1 INNER JOIN "remapp_irradeventxraydata" U2 ON ( U1."id" = U2."projection_xray_radiation_dose_id" ) INNER JOIN "remapp_irradeventxraysourcedata" U3 ON ( U2."id" = U3."irradiation_event_xray_data_id" ) INNER JOIN "remapp_exposure" U4 ON ( U3."id" = U4."irradiation_event_xray_source_data_id" ) WHERE U4."exposure" = 0)) AND CONCAT("remapp_irradeventxraydata"."acquisition_protocol", NULL) = 'Chest' AND T7."date_time_started" BETWEEN '2019-12-01T00:00:00'::timestamp AND '2019-12-31T23:59:59.999999'::timestamp) GROUP BY CONCAT("remapp_irradeventxraydata"."acquisition_protocol", NULL), (date_trunc('month', projectionxrayradiationdose__irradeventxraydata__date_time_started));

I thought this might be a name truncation problem but there is no column date_time_started in remapp_generalstudymoduleattr.

Tested on 0.10.0 on linux with a postgesql database.

[David Platten]

Dear Mark,

Thanks for the message. The “date_time_started” field is in the “remapp_irradeventxraydata”.

I’ve just tried a chart like this on my 0.10.0 Windows-based live installation which uses PostgreSQL. I restricted the date range to 1/12/2018 - 1/12/2019, “chest” requested procedure, and a single x-ray room. The chart worked without error.

I’m suspicious of the truncation of the initial part of your error message, and am wondering if your database has some restriction on the maximum number of characters in the query statement. A quick Google suggests to me that the length of your query shouldn’t be a problem.

Kind regards,

David

[Mark Pearson]

Hi David,

My knowledge of SQL is basic but my reading of the query is that it is trying to read date_time_started from “remapp_generalstudymoduleattr” not “remapp_irradeventxraydata”. I will try modifying the query when I am at work tomorrow.

Mark