OHDSI Home | Forums | Wiki | Github

Atlas error - Data Integrity Violation Exception

Implementers
, , , ,
#1

Hello Everyone,

We recently updated our OMOP CDM instance (tables, constraints, PKs etc)

The data was deleted completely and uploaded again.

However, when I run Atlas to create a new cohort, I get the below error

java.lang.RuntimeException: java.util.concurrent.ExecutionException: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [CREATE TEMP TABLE Codesets  (codeset_id int NOT NULL,
  concept_id bigint NOT NULL
)
; INSERT INTO Codesets (codeset_id, concept_id)
SELECT 0 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
( 
  select concept_id from vocab.CONCEPT where concept_id in (1529331,1510202)
UNION  select c.concept_id
  from vocab.CONCEPT c
  join vocab.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
  and ca.ancestor_concept_id in (1529331,1510202)
  and c.invalid_reason is null

) I
) C; CREATE TEMP TABLE qualified_events

AS
WITH primary_events (event_id, person_id, start_date, end_date, op_start_date, op_end_date, visit_occurrence_id)  AS (
-- Begin Primary Events
select P.ordinal as event_id, P.person_id, P.start_date, P.end_date, op_start_date, op_end_date, cast(P.visit_occurrence_id as bigint) a... [truncated] ...finition_id = 173) RuleTotal
  where sr.mode_id = 1 and sr.cohort_definition_id = 173 and sr.inclusion_rule_mask = POWER(cast(2 as bigint),RuleTotal.total_rules)-1
) FC
; TRUNCATE TABLE best_events; DROP TABLE best_events; TRUNCATE TABLE cohort_rows; DROP TABLE cohort_rows; TRUNCATE TABLE final_cohort; DROP TABLE final_cohort; TRUNCATE TABLE inclusion_events; DROP TABLE inclusion_events; TRUNCATE TABLE qualified_events; DROP TABLE qualified_events; TRUNCATE TABLE included_events; DROP TABLE included_events; TRUNCATE TABLE Codesets; DROP TABLE Codesets]; ERROR: insert or update on table "cohort" violates foreign key constraint "fpk_cohort_definition"
  Detail: Key (cohort_definition_id)=(173) is not present in table "cohort_definition".; nested exception is org.postgresql.util.PSQLException: ERROR: insert or update on table "cohort" violates foreign key constraint "fpk_cohort_definition"
  Detail: Key (cohort_definition_id)=(173) is not present in table "cohort_definition".

a) I am creating a new cohort (whose auto generated id is 173) but why is it expected to be in cohort_definition table (even before we could create)?

b) What has gone wrong here? Should I restart my Atlas as well? I didn’t restart Atlas because I thought it has nothing to do with brand new data uploads. Do I have to still restart Atlas?

#2

Maybe this is some odd behavior of the CDM ddl, but we don’t write any records into cohort_definition in the CDM schema. Therefore, I would drop the constraint fpk_cohort_definition.

1 Like
t