Concurrent receive of first objects belonging to different Studies of same Patient creates duplicate Patient Records

[gunterze]

Concurrent receive of first objects belonging to different studies of same Patient creates duplicate Patient Records.

Reported in dcm4che - Google Group

Reproducable by:

$ cd ~/testdata/MESA-storage-A_11_5_0/modality/CR
$ ls
CR1  CR2  CR3  CR4
$ ls | xargs -n1 -P4 storescu -s00100020=1234 -cDCM4CHEE@localhost:11112
...

pacsdb=> select study_iuid, pat_id, patient_fk, patient_id_fk, pat_name_fk, patient.dicomattrs_fk
from patient_id join patient on patient_id_fk=patient_id.pk
join study on patient_fk=patient.pk
where pat_id='1234';
              study_iuid               | pat_id | patient_fk | patient_id_fk | pat_name_fk | dicomattrs_fk 
---------------------------------------+--------+------------+---------------+-------------+---------------
 1.2.392.200036.9125.0.199302241758.16 | 1234   |         19 |            19 |          19 |           104
 1.2.392.200036.9125.0.199402091242.1  | 1234   |         20 |            20 |          20 |           109
 1.2.392.200036.9125.0.199212251200.28 | 1234   |         18 |            18 |          18 |           103
 1.2.392.200036.9125.0.198811291108.7  | 1234   |         17 |            17 |          17 |            99
(4 rows)

Remove duplicates by SQL:

pacsdb=> create table tmp1 (patient_fk bigint, patient_id_fk bigint, pat_name_fk bigint, dicomattrs_fk bigint);
CREATE TABLE
pacsdb=> insert into tmp1
select patient.pk, patient_id.pk, patient.pat_name_fk, patient.dicomattrs_fk
from patient_id join patient on patient_id_fk=patient_id.pk
where pat_id='1234';
INSERT 0 4
pacsdb=> delete from tmp1 where patient_fk=17;
DELETE 1
pacsdb=> update study set patient_fk=17 where patient_fk in (select patient_fk from tmp1);
UPDATE 3
pacsdb=> delete from patient where pk  in (select patient_fk from tmp1);
DELETE 3
pacsdb=> delete from patient_id where pk  in (select patient_id_fk from tmp1);
DELETE 3
pacsdb=> delete from dicomattrs where pk in (select dicomattrs_fk from tmp1);
DELETE 3
pacsdb=> delete from soundex_code where person_name_fk in (select pat_name_fk from tmp1);
DELETE 6
pacsdb=> delete from person_name where pk in (select pat_name_fk from tmp1);
DELETE 3
pacsdb=> drop table tmp1;
DROP TABLE

The error does not occur, if the Patient ID is qualified by an Issuer of Patient ID or by an Universal Entity ID and Universal Entity ID Type in an item of the Issuer of Patient ID Qualifier Sequence:

$ ls | xargs -n1 -P4 storescu -s00100020=1234 -s00100021=ABCD -cDCM4CHEE@localhost:11112

pacsdb=> select study_iuid, pat_id, patient_fk, patient_id_fk, pat_name_fk, patient.dicomattrs_fk
from patient_id
join patient on patient_id_fk=patient_id.pk
join study on patient_fk=patient.pk
where pat_id='1234';
              study_iuid               | pat_id | patient_fk | patient_id_fk | pat_name_fk | dicomattrs_fk 
---------------------------------------+--------+------------+---------------+-------------+---------------
 1.2.392.200036.9125.0.198811291108.7  | 1234   |         21 |            21 |          21 |           123
 1.2.392.200036.9125.0.199302241758.16 | 1234   |         21 |            21 |          21 |           123
 1.2.392.200036.9125.0.199212251200.28 | 1234   |         21 |            21 |          21 |           123
 1.2.392.200036.9125.0.199402091242.1  | 1234   |         21 |            21 |          21 |           123
(4 rows)

Related #2847, #2850

[gunterze]

Duplicate Patient Records may not be detected by the limitation of the query for duplicate patient records by created_time > current time on starting the transaction for inserting DB records of the received object, because created_time does not reflect the time when the patient record becomes readable!

Replace limitation by created_time by check for equal attributes blob.