Error duplicate key violates unique constraint after restart docker with dhis2

Support - Assistance technique
1

We have been observing a duplicates error in some tables with auto-incremental or sec indexes after restarting our instances, but we cannot identify the moment when the indexes get corrupted.

For example, we can see errors similar to:

Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “trackedentityinstance_pkey”.
core_1 | Detail: Key (trackedentityinstanceid)=(33842) already exists.when the last uid of the table is 33884

It happens with several indices, like audit_auditid_seq, trackedentityinstanceaudit_sequence, programinstance_sequence…

To fix it we run a series of sql statements to fix the indexes:

create sequence if not exists audit_auditid_seq;
select setval(‘audit_auditid_seq’, max(auditid)) FROM audit;

create sequence if not exists datavalueaudit_sequence;
select setval(‘datavalueaudit_sequence’, max(datavalueauditid)) FROM datavalueaudit;

create sequence if not exists programstageinstance_sequence;
select setval(‘programstageinstance_sequence’, max(programstageinstanceid)) FROM programstageinstance;

create sequence if not exists trackedentitydatavalueaudit_sequence;
select setval(‘trackedentitydatavalueaudit_sequence’, max(trackedentitydatavalueauditid)) FROM trackedentitydatavalueaudit;

create sequence if not exists trackedentityinstance_sequence;
select setval(‘trackedentityinstance_sequence’, max(trackedentityinstanceid)) FROM trackedentityinstance;

create sequence if not exists trackedentityinstanceaudit_sequence;
select setval(‘trackedentityinstanceaudit_sequence’, max(trackedentityinstanceauditid)) FROM trackedentityinstanceaudit;

create sequence if not exists programinstance_sequence;
select setval(‘programinstance_sequence’, max(programinstanceid)) FROM programinstance;

create sequence if not exists deletedobject_sequence;
select setval(‘deletedobject_sequence’, max(deletedobjectid)) FROM deletedobject;

create sequence if not exists usermessage_sequence;
select setval(‘usermessage_sequence’, max(usermessageid)) FROM usermessage;

create sequence if not exists messageconversation_sequence;
select setval(‘messageconversation_sequence’, max(messageconversationid)) FROM messageconversation;

create sequence if not exists message_sequence;
select setval(‘message_sequence’, max(messageid)) FROM message;

create sequence if not exists reservedvalue_sequence;
select setval(‘reservedvalue_sequence’, max(reservedvalueid)) FROM reservedvalue;

create sequence if not exists potentialduplicatesequence;
select setval(‘potentialduplicatesequence’, max(potentialduplicateid)) FROM potentialduplicate;

create sequence if not exists hibernate_sequence;
select setval(‘hibernate_sequence’, max(hibernateid)) FROM hibernate;

create index if not exists “in_trackedentityprogramowner_program_orgunit” on trackedentityprogramowner (programid, organisationunitid);

We use docker, instances of dhis2 in 2.35, 2.36, and PostgreSQL 10.20.
any clues as to what might be happening and how to avoid it?
Is it safe to redo the indexes?

2

@idelcano Your approach to reset the sequence is correct. It is not the index that is getting corrupted. The sequence seems to generate values that are lower than the max values. Do you know how long does it take for the issue to resurface after running those scripts to reset the sequences?
Next time, can you try dropping all those sequences and recreating them in the same way and setting it to the max value?

1 Like