|
431
|
1 Resources:
|
|
|
2 *********
|
|
|
3 - PG transaction modes explained: https://www.postgresql.org/files/developer/concurrency.pdf
|
|
|
4 - Isoolation level explained (PG + MySQL): https://amirsoleimani.medium.com/understanding-database-isolation-level-via-examples-mysql-and-postgres-a86b5502d404
|
|
|
5
|
|
|
6
|
|
|
7 Sample SQL code that you can execute in DBeaver to test new functions/procedures:
|
|
|
8
|
|
|
9 CreateInstance
|
|
|
10 ************************************************************************
|
|
|
11
|
|
|
12 CREATE OR replace FUNCTION CreateInstance(
|
|
|
13 IN patient TEXT,
|
|
|
14 IN study TEXT,
|
|
|
15 IN series TEXT,
|
|
|
16 IN instance TEXT,
|
|
|
17 OUT isNewPatient BIGINT,
|
|
|
18 OUT isNewStudy BIGINT,
|
|
|
19 OUT isNewSeries BIGINT,
|
|
|
20 OUT isNewInstance BIGINT,
|
|
|
21 OUT patientKey BIGINT,
|
|
|
22 OUT studyKey BIGINT,
|
|
|
23 OUT seriesKey BIGINT,
|
|
|
24 OUT instanceKey BIGINT)
|
|
|
25 AS $$
|
|
|
26 begin
|
|
|
27 isNewPatient := 1;
|
|
|
28 isNewStudy := 1;
|
|
|
29 isNewSeries := 1;
|
|
|
30 isNewInstance := 1;
|
|
|
31
|
|
|
32 BEGIN
|
|
|
33 INSERT INTO "resources" VALUES (DEFAULT, 0, patient, NULL);
|
|
|
34 exception
|
|
|
35 when unique_violation then
|
|
|
36 isNewPatient := 0;
|
|
|
37 end;
|
|
|
38 select internalid into patientKey from "resources" where publicId=patient and resourcetype = 0;
|
|
|
39
|
|
|
40 BEGIN
|
|
|
41 INSERT INTO "resources" VALUES (DEFAULT, 1, study, patientKey);
|
|
|
42 exception
|
|
|
43 when unique_violation then
|
|
|
44 isNewStudy := 0;
|
|
|
45 end;
|
|
|
46 select internalid into studyKey from "resources" where publicId=study and resourcetype = 1;
|
|
|
47
|
|
|
48 BEGIN
|
|
|
49 INSERT INTO "resources" VALUES (DEFAULT, 2, series, studyKey);
|
|
|
50 exception
|
|
|
51 when unique_violation then
|
|
|
52 isNewSeries := 0;
|
|
|
53 end;
|
|
|
54 select internalid into seriesKey from "resources" where publicId=series and resourcetype = 2;
|
|
|
55
|
|
|
56 BEGIN
|
|
|
57 INSERT INTO "resources" VALUES (DEFAULT, 3, instance, seriesKey);
|
|
|
58 exception
|
|
|
59 when unique_violation then
|
|
|
60 isNewInstance := 0;
|
|
|
61 end;
|
|
|
62 select internalid into instanceKey from "resources" where publicId=instance and resourcetype = 3;
|
|
|
63
|
|
|
64 END;
|
|
|
65 $$ LANGUAGE plpgsql;
|
|
|
66
|
|
|
67 DO $$
|
|
|
68 DECLARE
|
|
|
69 result record;
|
|
|
70 begin
|
|
|
71 delete from "resources";
|
|
|
72
|
|
|
73 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance1');
|
|
|
74
|
|
|
75 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patientKey, result.isNewPatient;
|
|
|
76 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.studyKey, result.isNewStudy;
|
|
|
77 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.seriesKey, result.isNewSeries;
|
|
|
78 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instanceKey, result.isNewInstance;
|
|
|
79 RAISE NOTICE '--------------';
|
|
|
80
|
|
|
81 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2');
|
|
|
82
|
|
|
83 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patientKey, result.isNewPatient;
|
|
|
84 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.studyKey, result.isNewStudy;
|
|
|
85 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.seriesKey, result.isNewSeries;
|
|
|
86 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instanceKey, result.isNewInstance;
|
|
|
87 RAISE NOTICE '--------------';
|
|
|
88
|
|
|
89 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2');
|
|
|
90
|
|
|
91 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patientKey, result.isNewPatient;
|
|
|
92 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.studyKey, result.isNewStudy;
|
|
|
93 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.seriesKey, result.isNewSeries;
|
|
|
94 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instanceKey, result.isNewInstance;
|
|
|
95 RAISE NOTICE '--------------';
|
|
|
96
|
|
|
97 END $$;
|
|
|
98
|
|
|
99 -- \set patient_key 'patient_key'
|
|
|
100 -- SELECT CreateInstance('patient', 'study', 'series', 'instance', patient_key) ;
|
|
|
101
|
|
|
102 -- drop function CreateInstance
|
|
|
103 -- select * from "resources";
|
|
|
104 -- delete from "resources";
|
|
|
105 -- INSERT INTO "resources" VALUES (DEFAULT, 0, 'patient', NULL)
|
|
|
106
|
|
|
107
|
|
|
108
|
|
|
109 ************************************************************************
|
|
|
110
|
|
|
111 In debug, no verbose logs
|
|
|
112 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 51.703 s
|
|
|
113 Orthanc mainline + PG maineline (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 51.913 s
|
|
|
114 Orthanc mainline + PG maineline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 24.754 s
|
|
|
115
|
|
|
116 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_uploads_same_study with 40 workers and 1x repeat: 55.932 s
|
|
|
117 Orthanc mainline + PG maineline (serializable mode) : fails: No new instance while overwriting; this should not happen
|
|
|
118 Orthanc mainline + PG maineline (read-committed mode) :
|
|
|
119
|
|
|
120 TODO:
|
