Mercurial > hg > orthanc-databases

comparison NOTES @ 431:7c1fe5d6c12c pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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: