Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/SQL/PrepareIndexV2.sql @ 444:2ca939d02d39 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
cleanup
author Alain Mazy <am@osimis.io>
date Wed, 10 Jan 2024 15:22:40 +0100
parents 2a48f8fcec6e
children
comparison
equal deleted inserted replaced
443:2a48f8fcec6e 444:2ca939d02d39
214 OUT remaining_ancestor_resource_type INTEGER, 214 OUT remaining_ancestor_resource_type INTEGER,
215 OUT remaining_anncestor_public_id TEXT) AS $body$ 215 OUT remaining_anncestor_public_id TEXT) AS $body$
216 216
217 DECLARE 217 DECLARE
218 deleted_row RECORD; 218 deleted_row RECORD;
219 locked_row RECORD;
219 220
220 BEGIN 221 BEGIN
221 222
222 SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping 223 SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping
223 224
233 -- clear the temporary table in case it has been created earlier in the session 234 -- clear the temporary table in case it has been created earlier in the session
234 DELETE FROM DeletedResources; 235 DELETE FROM DeletedResources;
235 236
236 -- create/clear the DeletedFiles temporary table 237 -- create/clear the DeletedFiles temporary table
237 PERFORM CreateDeletedFilesTemporaryTable(); 238 PERFORM CreateDeletedFilesTemporaryTable();
239
240 -- Before deleting an object, we need to lock its parent until the end of the transaction to avoid that
241 -- 2 threads deletes the last 2 instances of a series at the same time -> none of them would realize
242 -- that they are deleting the last instance and the parent resources would not be deleted.
243 -- Locking only the immediate parent is sufficient to prevent from this.
244 SELECT * INTO locked_row FROM resources WHERE internalid = (SELECT parentid FROM resources WHERE internalid = id) FOR UPDATE;
238 245
239 -- delete the resource itself 246 -- delete the resource itself
240 DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row; 247 DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row;
241 -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children + 248 -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children +
242 249
499 is_new_study := 1; 506 is_new_study := 1;
500 is_new_series := 1; 507 is_new_series := 1;
501 is_new_instance := 1; 508 is_new_instance := 1;
502 509
503 BEGIN 510 BEGIN
504 INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL); 511 INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL) RETURNING internalid INTO patient_internal_id;
505 EXCEPTION 512 EXCEPTION
506 WHEN unique_violation THEN 513 WHEN unique_violation THEN
507 is_new_patient := 0; 514 is_new_patient := 0;
515 SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction
508 END; 516 END;
509 SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id AND resourcetype = 0;
510 517
511 BEGIN 518 BEGIN
512 INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id); 519 INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id) RETURNING internalid INTO study_internal_id;
513 EXCEPTION 520 EXCEPTION
514 WHEN unique_violation THEN 521 WHEN unique_violation THEN
515 is_new_study := 0; 522 is_new_study := 0;
523 SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction END;
516 END; 524 END;
517 SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id AND resourcetype = 1;
518 525
519 BEGIN 526 BEGIN
520 INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id); 527 INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id) RETURNING internalid INTO series_internal_id;
521 EXCEPTION 528 EXCEPTION
522 WHEN unique_violation THEN 529 WHEN unique_violation THEN
523 is_new_series := 0; 530 is_new_series := 0;
531 SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction END;
524 END; 532 END;
525 SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id AND resourcetype = 2;
526 533
527 BEGIN 534 BEGIN
528 INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id); 535 INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id) RETURNING internalid INTO instance_internal_id;
529 EXCEPTION 536 EXCEPTION
530 WHEN unique_violation THEN 537 WHEN unique_violation THEN
531 is_new_instance := 0; 538 is_new_instance := 0;
539 SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction
532 END; 540 END;
533 SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id AND resourcetype = 3;
534 541
535 IF is_new_instance > 0 THEN 542 IF is_new_instance > 0 THEN
536 -- Move the patient to the end of the recycling order. 543 -- Move the patient to the end of the recycling order.
537 PERFORM PatientAddedOrUpdated(patient_internal_id, 1); 544 PERFORM PatientAddedOrUpdated(patient_internal_id, 1);
538 END IF; 545 END IF;