How to fix "unique constraint OS_POS_ST_CONTS_OSPID_UQ violated" error?

This problem occurs when one or more specimens are "incorrectly" assigned multiple storage locations. In caTissue, there were no checks or constraints enforced on a specimen having single storage location. As a result, when upgrading to more stricter OpenSpecimen, migration of such inconsistent data fails.

To solve the problem, duplicate specimen storage location entries must be cleaned up. Given below are the steps to achieve this:

Step 1. Find specimens that are assigned multiple storage locations

select
	pos.identifier as position_id, spmn.label as specimen_label, con.name as container_name,
	cap.position_dimension_two as pos1, cap.position_dimension_one as pos2
from
	catissue_specimen_position pos
	inner join catissue_specimen spmn on pos.specimen_id = spmn.identifier
	inner join catissue_container con on con.identifier = pos.container_id
	inner join catissue_abstract_position cap on cap.identifier = pos.identifier
where 
	pos.specimen_id in (
      select
        specimen_id 
      from
        catissue_specimen_position
      group by
        specimen_id
      having
        count(identifier) > 1
    );

Step 2. Delete the incorrect position entries

To delete the incorrect positions, take the all incorrect position identifiers from the result of the query given in step 1.

delete from catissue_specimen_position where identifier in (<incorrect_position_identifiers>);
delete from catissue_abstract_position where identifier in (<incorrect_position_identifiers>);

Step 3. Restart Tomcat server to continue the upgrade