Mercurial > hg > orthanc-databases
comparison NOTES @ 431:7c1fe5d6c12c pg-transactions
PG: IncrementGlobalProperty
author | Alain Mazy <am@osimis.io> |
---|---|
date | Thu, 07 Dec 2023 12:13:43 +0100 |
parents | |
children | 8b7c1c423367 |
comparison
equal
deleted
inserted
replaced
430:f1f3c5554283 | 431:7c1fe5d6c12c |
---|---|
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: |