Failed to update DB because of duplicated patient identity

[Jack Pipes]

Dear team,

After a misconfiguration of a coersion rule, one of my archive seems to have created many duplicated patient identities, which results in many duplicate key exceptions in the logs (see below), and more annoyingly, it blocks the HL7 service from reconsciliating and storing any received HL7 object for these duplicated patients.

[org.dcm4chee.arc.store.impl.StoreServiceImpl] (EE-ManagedExecutorService-default-Thread-5) MSH|^~\&|XXX|XXX|XXX|XXX|202201071208||ORU^R01|XXXXXXXXXXXX|P|2.4||||||8859/15: Failed to update DB caused by org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uk_31gvi9falc03xs94m8l3pgoid" Detail: Key (pat_id, issuer_fk)=(100051205, 465162) already exists. - retry

I understand it is related to some ID constraints in the patient_id table and I wonder what are my options to fix the situation ? Is there an option in the archive configuration I can setup to resolve the issue ? Else, any SQL/REST request that can help to sort out these conflicts in batches ?

Many thanks for your help,

Best regards

JP

[Vrinda Nayak]

Which archive version are you using? For a unique patient identifier record existing in archive, duplicate record creation with same identifier/issuer is rejected. Instead if a given combination of Patient ID + Issuer already exists, this record is used for update / storing objects etc. You could also share the test steps how your archive ended up with exactly duplicate patient identifiers for us to reproduce it locally.

What are the results for the following query in your DB

select pid.pk, pid.pat_id, i.pk, i.entity_id, i.entity_uid, i.entity_uid_type
from patient_id pid
join issuer i on i.pk = pid.issuer_fk
where pid.pat_id='100051205';

[Jack Pipes]

Thank for your response. This archive runs the 5.23.3-secure-ui docker version.

Very hard to say what are the precise steps that leads to this situation as I'm not the only administrator of the archive. I know the HIS system made important changes around their issuer recently, from ABC to BCD.

When executing your request I obtain the following result:

   pk   |  pat_id   |   pk   |           entity_id            | entity_uid | entity_uid_type
--------+-----------+--------+--------------------------------+------------+-----------------
 379632 | 100051205 | 379630 | ABC                            |            |
 379642 | 100051205 | 379640 | BCD                            |            |
 529547 | 100051205 | 465162 | XXX                            |            |
(3 rows)

I have 3 different records for this patient, with different issuers. What I don't understand is why the archive is rejecting the coming object saying that it cannot create the patient as it already exists. Shouldn't it try to reconcile it with an already existing patient? When receiving ORU messages, what is the HL7 field is designated as the issuer ?

Many thank for your help,

JP

[Vrinda Nayak]

These results show that you have unique patient identifiers in your archive as the issuers are different from each other. Perhaps the PID segment in ORU messages do not contain any issuer but just the patient ID as 100051205, thus the archive does not know which patient identifier record is the correct one that should get associated with the incoming report. Please ensure that PID-3 contains both patient ID and an assigning authority as required by HL7 standard

[Jack Pipes]

Thank again for your response. 

The PID-3 field contains the following values : 

PID|||100051205^^^BCD^PI||...

Which seems correct, I have the issuer in PID-3-4 (Assigning Authority). Do you confirm that it should work properly ?

Any idea of other areas I could dig into in order to understand what is going on ?

JP 

[Vrinda Nayak]

Not reproducible. I started 5.23.3 dockerized archive on my setup with exactly 3 unique patient records like yours before sending ORU message to archive. The reports do get stored for 100051205^^^BCD

[Jack Pipes]

Okay, thank you for your time, I'll continue to investigate…

Best regards,

JP