Error saving temp patient list

1

When we try to save patients to a temporary list we receive the following error:

ERROR: [2020-10-16T12:02:20,025] edu.umich.med.emerse.rest.AllPatientSearchController.log()83 StatementCallback; SQL [insert into patient_list_entry (id, patient_id, patient_list_id, marked)   select entry_seq_id, patient.id,16059, 0  from  pt_list_import imp  inner join patient on imp.mrn=patient.external_id where imp.existing_flag='N'  ]; ORA-00001: unique constraint (EMERSE.PK26) violated
; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (EMERSE.PK26) violated

I tried emptying all the patient_list_* and pt_list_* tables. After that, the first save works but then subsequent ones fail with the above error.

Here’s what I did to empty the lists:

TRUNCATE TABLE PT_LIST_IMPORT
TRUNCATE TABLE PATIENT_LIST_SNAPSHOT 
TRUNCATE TABLE PATIENT_LIST_LAST_USED 
TRUNCATE TABLE PATIENT_LIST_HISTORY 
TRUNCATE TABLE PATIENT_LIST_ENTRY_COMMENT 
DELETE FROM PATIENT_LIST_ENTRY 
COMMIT
UPDATE LOGIN_ACCOUNT SET CURRENT_PT_LIST_ID=NULL
COMMIT
DELETE FROM PATIENT_LIST
COMMIT

It also works the first time after restarting tomcat, then fails after that. Any Ideas?

2

Hi! Sorry I didn’t get back to you quickly. I’m actually kinda stumped by this issue… Did you figure it out? I suspect that the increment size on the PATIENT_LIST_ENTRY_SEQ is wrong. It should be 50, but if it were 1, then that would probably lead to this issue. But, I’m guessing you didn’t make any changes to that.

You shouldn’t need to truncate PT_LIST_IMPORT since it’s a global temporary table, so each connection to the DB sees in a sense has “different” table, and when the connection is closed, that “table” is deleted. It’s only used to push the MRNs into the database but also do some validation on them in an easy way.

3

I did figure it out. I meant to come back here and post a follow up but I forgot.

The problem was mine. I needed to replicate our Oracle DB on another server but I didn’t have ssh access to that server. I decided to use liquibase to generate a snapshot of the the db and data. Evidently liquibase doesn’t handle temp tables correctly and created the PT_LIST_IMPORT table as a regular table. Once I fixed that everything started to work again.

Thanks for responding though!

Kevin