Mercurial > hg > orthanc-databases
diff 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 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/NOTES Thu Dec 07 12:13:43 2023 +0100 @@ -0,0 +1,120 @@ +Resources: +********* +- PG transaction modes explained: https://www.postgresql.org/files/developer/concurrency.pdf +- Isoolation level explained (PG + MySQL): https://amirsoleimani.medium.com/understanding-database-isolation-level-via-examples-mysql-and-postgres-a86b5502d404 + + +Sample SQL code that you can execute in DBeaver to test new functions/procedures: + +CreateInstance +************************************************************************ + +CREATE OR replace FUNCTION CreateInstance( + IN patient TEXT, + IN study TEXT, + IN series TEXT, + IN instance TEXT, + OUT isNewPatient BIGINT, + OUT isNewStudy BIGINT, + OUT isNewSeries BIGINT, + OUT isNewInstance BIGINT, + OUT patientKey BIGINT, + OUT studyKey BIGINT, + OUT seriesKey BIGINT, + OUT instanceKey BIGINT) +AS $$ +begin + isNewPatient := 1; + isNewStudy := 1; + isNewSeries := 1; + isNewInstance := 1; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 0, patient, NULL); + exception + when unique_violation then + isNewPatient := 0; + end; + select internalid into patientKey from "resources" where publicId=patient and resourcetype = 0; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 1, study, patientKey); + exception + when unique_violation then + isNewStudy := 0; + end; + select internalid into studyKey from "resources" where publicId=study and resourcetype = 1; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 2, series, studyKey); + exception + when unique_violation then + isNewSeries := 0; + end; + select internalid into seriesKey from "resources" where publicId=series and resourcetype = 2; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 3, instance, seriesKey); + exception + when unique_violation then + isNewInstance := 0; + end; + select internalid into instanceKey from "resources" where publicId=instance and resourcetype = 3; + +END; +$$ LANGUAGE plpgsql; + +DO $$ +DECLARE + result record; +begin + delete from "resources"; + + SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance1'); + + RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patientKey, result.isNewPatient; + RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.studyKey, result.isNewStudy; + RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.seriesKey, result.isNewSeries; + RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instanceKey, result.isNewInstance; + RAISE NOTICE '--------------'; + + SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2'); + + RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patientKey, result.isNewPatient; + RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.studyKey, result.isNewStudy; + RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.seriesKey, result.isNewSeries; + RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instanceKey, result.isNewInstance; + RAISE NOTICE '--------------'; + + SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2'); + + RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patientKey, result.isNewPatient; + RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.studyKey, result.isNewStudy; + RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.seriesKey, result.isNewSeries; + RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instanceKey, result.isNewInstance; + RAISE NOTICE '--------------'; + +END $$; + +-- \set patient_key 'patient_key' +-- SELECT CreateInstance('patient', 'study', 'series', 'instance', patient_key) ; + +-- drop function CreateInstance +-- select * from "resources"; +-- delete from "resources"; +-- INSERT INTO "resources" VALUES (DEFAULT, 0, 'patient', NULL) + + + +************************************************************************ + +In debug, no verbose logs +Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 51.703 s +Orthanc mainline + PG maineline (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 51.913 s +Orthanc mainline + PG maineline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 24.754 s + +Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_uploads_same_study with 40 workers and 1x repeat: 55.932 s +Orthanc mainline + PG maineline (serializable mode) : fails: No new instance while overwriting; this should not happen +Orthanc mainline + PG maineline (read-committed mode) : + +TODO: