Mercurial > hg > orthanc-databases
comparison 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 |
comparison
equal
deleted
inserted
replaced
431:7c1fe5d6c12c | 432:8b7c1c423367 |
---|---|
1 Resources: | 1 Resources: |
2 ********* | 2 ********* |
3 - PG transaction modes explained: https://www.postgresql.org/files/developer/concurrency.pdf | 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 | 4 - Isoolation level explained (PG + MySQL): https://amirsoleimani.medium.com/understanding-database-isolation-level-via-examples-mysql-and-postgres-a86b5502d404 |
5 | 5 |
6 Create and delete instances Internals: | |
7 ************************************* | |
8 | |
9 isNewInstance = CreateInstance(...) | |
10 | |
11 if (!isNewInstance && overwriteInstances) | |
12 DeleteResource(instance) | |
13 -> ClearDeletedFiles(manager); | |
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!!! | |
15 ClearDeletedResources(manager); | |
16 DELETE FROM DeletedResources ------> this is not a temporary table in postgres but it is supposed to be empty before and after each transaction !!! | |
17 | |
18 DELETE FROM RemainingAncestor ------> this is not a temporary table in postgres but it is supposed to be empty before and after each transaction !!! | |
19 DELETE FROM Resources WHERE internalId=.. | |
20 -> cascades delete the MainDicomTags, the Metadata and the AttachedFiles | |
21 -> this triggers AttachedFileDeletedFunc | |
22 INSERT INTO DeletedFiles VALUES | |
23 (old.uuid, old.filetype, old.compressedSize, | |
24 old.uncompressedSize, old.compressionType, | |
25 old.uncompressedHash, old.compressedHash); | |
26 RETURN NULL; | |
27 -> this triggers a SQL trigger: ResourceDeletedFunc | |
28 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); | |
29 IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN | |
30 -- Signal that the deleted resource has a remaining parent | |
31 -- (a parent that must not be deleted but whose LastUpdate must be updated) | |
32 INSERT INTO RemainingAncestor | |
33 SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; | |
34 ELSE | |
35 -- Delete a parent resource when its unique child is deleted | |
36 DELETE FROM Resources WHERE internalId = old.parentId; | |
37 END IF; | |
38 | |
39 SELECT * FROM RemainingAncestor | |
40 -> SignalRemainingAncestor() // There is at most 1 remaining ancestor | |
41 -> ServerIndex::TransactionContext::SignalRemainingAncestor() | |
42 -> stores remainingType and remainingPublicId (this is used in StatelessDatabaseOperations::DeleteResource to build the Rest Response of /delete | |
43 and to update the LastUpdate of all parent (only when deleted from /delete)) | |
44 | |
45 SignalDeletedFiles(output, manager); | |
46 SELECT * FROM DeletedFiles | |
47 -> SignalDeletedAttachment() | |
48 -> ServerIndex::TransactionContext::SignalAttachmentDeleted() | |
49 -> pendingFilesToRemove_.push_back(FileToRemove(info)) (files are deleted in CommitFilesToRemove in the ServerIndex::TransactionContext::Commit) | |
50 | |
51 SignalDeletedResources(output, manager); | |
52 SELECT resourceType, publicId FROM DeletedResources | |
53 -> SignalDeletedResource() | |
54 -> Emit DeletedResource event (lua) | |
55 | |
56 | |
57 if (!CreateInstance(...)) | |
58 Error: "No new instance while overwriting; this should not happen" | |
59 | |
60 if isNewInstance -> LogChange | |
61 if isNewSeries -> LogChange | |
62 .... | |
6 | 63 |
7 Sample SQL code that you can execute in DBeaver to test new functions/procedures: | 64 Sample SQL code that you can execute in DBeaver to test new functions/procedures: |
8 | 65 |
9 CreateInstance | 66 CreateInstance |
10 ************************************************************************ | 67 ************************************************************************ |
111 In debug, no verbose logs | 168 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 | 169 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 | 170 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 | 171 Orthanc mainline + PG maineline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 24.754 s |
115 | 172 |
116 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_uploads_same_study with 40 workers and 1x repeat: 55.932 s | 173 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 21.214 s |
117 Orthanc mainline + PG maineline (serializable mode) : fails: No new instance while overwriting; this should not happen | 174 Orthanc mainline + PG maineline (serializable mode) : fails: No new instance while overwriting; this should not happen |
118 Orthanc mainline + PG maineline (read-committed mode) : | 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) |
119 | 176 |
120 TODO: | 177 TODO: |
178 - assert((statement.Next(), statement.IsDone())) commented out -> create temporary tables ? | |
179 - PatientAddedFunc contains an IF | |
180 - validate upgrade DB from previous Orthanc and from scratch | |
181 - test with older version of PG | |
182 - force the create/update DB transaction to be serializable (not needed: this is handled by POSTGRESQL_LOCK_DATABASE_SETUP) |