Mercurial > hg > orthanc-databases
annotate NOTES @ 432:8b7c1c423367 pg-transactions
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
author | Alain Mazy <am@osimis.io> |
---|---|
date | Mon, 11 Dec 2023 14:39:27 +0100 |
parents | 7c1fe5d6c12c |
children | 5964ce6385a5 |
rev | line source |
---|---|
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 | |
432
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
6 Create and delete instances Internals: |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
7 ************************************* |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
8 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
9 isNewInstance = CreateInstance(...) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
10 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
11 if (!isNewInstance && overwriteInstances) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
12 DeleteResource(instance) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
13 -> ClearDeletedFiles(manager); |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
14 DELETE FROM DeletedFiles ------> this is not a temporary table in postgres but it is supposed to be empty before and after each transaction -> it is empty when taking a snapshot of the DB in READ COMMITTED mode!!! |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
15 ClearDeletedResources(manager); |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
16 DELETE FROM DeletedResources ------> this is not a temporary table in postgres but it is supposed to be empty before and after each transaction !!! |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
17 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
18 DELETE FROM RemainingAncestor ------> this is not a temporary table in postgres but it is supposed to be empty before and after each transaction !!! |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
19 DELETE FROM Resources WHERE internalId=.. |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
20 -> cascades delete the MainDicomTags, the Metadata and the AttachedFiles |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
21 -> this triggers AttachedFileDeletedFunc |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
22 INSERT INTO DeletedFiles VALUES |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
23 (old.uuid, old.filetype, old.compressedSize, |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
24 old.uncompressedSize, old.compressionType, |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
25 old.uncompressedHash, old.compressedHash); |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
26 RETURN NULL; |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
27 -> this triggers a SQL trigger: ResourceDeletedFunc |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
28 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
29 IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
30 -- Signal that the deleted resource has a remaining parent |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
31 -- (a parent that must not be deleted but whose LastUpdate must be updated) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
32 INSERT INTO RemainingAncestor |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
33 SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
34 ELSE |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
35 -- Delete a parent resource when its unique child is deleted |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
36 DELETE FROM Resources WHERE internalId = old.parentId; |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
37 END IF; |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
38 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
39 SELECT * FROM RemainingAncestor |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
40 -> SignalRemainingAncestor() // There is at most 1 remaining ancestor |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
41 -> ServerIndex::TransactionContext::SignalRemainingAncestor() |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
42 -> stores remainingType and remainingPublicId (this is used in StatelessDatabaseOperations::DeleteResource to build the Rest Response of /delete |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
43 and to update the LastUpdate of all parent (only when deleted from /delete)) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
44 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
45 SignalDeletedFiles(output, manager); |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
46 SELECT * FROM DeletedFiles |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
47 -> SignalDeletedAttachment() |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
48 -> ServerIndex::TransactionContext::SignalAttachmentDeleted() |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
49 -> pendingFilesToRemove_.push_back(FileToRemove(info)) (files are deleted in CommitFilesToRemove in the ServerIndex::TransactionContext::Commit) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
50 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
51 SignalDeletedResources(output, manager); |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
52 SELECT resourceType, publicId FROM DeletedResources |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
53 -> SignalDeletedResource() |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
54 -> Emit DeletedResource event (lua) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
55 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
56 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
57 if (!CreateInstance(...)) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
58 Error: "No new instance while overwriting; this should not happen" |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
59 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
60 if isNewInstance -> LogChange |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
61 if isNewSeries -> LogChange |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
62 .... |
431 | 63 |
64 Sample SQL code that you can execute in DBeaver to test new functions/procedures: | |
65 | |
66 CreateInstance | |
67 ************************************************************************ | |
68 | |
69 CREATE OR replace FUNCTION CreateInstance( | |
70 IN patient TEXT, | |
71 IN study TEXT, | |
72 IN series TEXT, | |
73 IN instance TEXT, | |
74 OUT isNewPatient BIGINT, | |
75 OUT isNewStudy BIGINT, | |
76 OUT isNewSeries BIGINT, | |
77 OUT isNewInstance BIGINT, | |
78 OUT patientKey BIGINT, | |
79 OUT studyKey BIGINT, | |
80 OUT seriesKey BIGINT, | |
81 OUT instanceKey BIGINT) | |
82 AS $$ | |
83 begin | |
84 isNewPatient := 1; | |
85 isNewStudy := 1; | |
86 isNewSeries := 1; | |
87 isNewInstance := 1; | |
88 | |
89 BEGIN | |
90 INSERT INTO "resources" VALUES (DEFAULT, 0, patient, NULL); | |
91 exception | |
92 when unique_violation then | |
93 isNewPatient := 0; | |
94 end; | |
95 select internalid into patientKey from "resources" where publicId=patient and resourcetype = 0; | |
96 | |
97 BEGIN | |
98 INSERT INTO "resources" VALUES (DEFAULT, 1, study, patientKey); | |
99 exception | |
100 when unique_violation then | |
101 isNewStudy := 0; | |
102 end; | |
103 select internalid into studyKey from "resources" where publicId=study and resourcetype = 1; | |
104 | |
105 BEGIN | |
106 INSERT INTO "resources" VALUES (DEFAULT, 2, series, studyKey); | |
107 exception | |
108 when unique_violation then | |
109 isNewSeries := 0; | |
110 end; | |
111 select internalid into seriesKey from "resources" where publicId=series and resourcetype = 2; | |
112 | |
113 BEGIN | |
114 INSERT INTO "resources" VALUES (DEFAULT, 3, instance, seriesKey); | |
115 exception | |
116 when unique_violation then | |
117 isNewInstance := 0; | |
118 end; | |
119 select internalid into instanceKey from "resources" where publicId=instance and resourcetype = 3; | |
120 | |
121 END; | |
122 $$ LANGUAGE plpgsql; | |
123 | |
124 DO $$ | |
125 DECLARE | |
126 result record; | |
127 begin | |
128 delete from "resources"; | |
129 | |
130 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance1'); | |
131 | |
132 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patientKey, result.isNewPatient; | |
133 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.studyKey, result.isNewStudy; | |
134 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.seriesKey, result.isNewSeries; | |
135 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instanceKey, result.isNewInstance; | |
136 RAISE NOTICE '--------------'; | |
137 | |
138 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2'); | |
139 | |
140 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patientKey, result.isNewPatient; | |
141 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.studyKey, result.isNewStudy; | |
142 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.seriesKey, result.isNewSeries; | |
143 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instanceKey, result.isNewInstance; | |
144 RAISE NOTICE '--------------'; | |
145 | |
146 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2'); | |
147 | |
148 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patientKey, result.isNewPatient; | |
149 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.studyKey, result.isNewStudy; | |
150 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.seriesKey, result.isNewSeries; | |
151 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instanceKey, result.isNewInstance; | |
152 RAISE NOTICE '--------------'; | |
153 | |
154 END $$; | |
155 | |
156 -- \set patient_key 'patient_key' | |
157 -- SELECT CreateInstance('patient', 'study', 'series', 'instance', patient_key) ; | |
158 | |
159 -- drop function CreateInstance | |
160 -- select * from "resources"; | |
161 -- delete from "resources"; | |
162 -- INSERT INTO "resources" VALUES (DEFAULT, 0, 'patient', NULL) | |
163 | |
164 | |
165 | |
166 ************************************************************************ | |
167 | |
168 In debug, no verbose logs | |
169 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 51.703 s | |
170 Orthanc mainline + PG maineline (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 51.913 s | |
171 Orthanc mainline + PG maineline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 24.754 s | |
172 | |
432
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
173 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 21.214 s |
431 | 174 Orthanc mainline + PG maineline (serializable mode) : fails: No new instance while overwriting; this should not happen |
432
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
175 Orthanc mainline + PG maineline (read-committed mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 1.394 s (with assert((statement.Next(), statement.IsDone())) commented out) |
431 | 176 |
177 TODO: | |
432
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
178 - assert((statement.Next(), statement.IsDone())) commented out -> create temporary tables ? |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
179 - PatientAddedFunc contains an IF |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
180 - validate upgrade DB from previous Orthanc and from scratch |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
181 - test with older version of PG |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
182 - force the create/update DB transaction to be serializable (not needed: this is handled by POSTGRESQL_LOCK_DATABASE_SETUP) |