Mercurial > hg > orthanc-databases

comparison NOTES @ 432:8b7c1c423367 pg-transactions

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