--- /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: