Resource table : _orgunitstructure_temp duplicate key value violates

Support - Assistance technique
1

when launch the resource table I’m getting a weird error about duplicates in orgunit structure

sql
PreparedStatementCallback; SQL [insert into _orgunitstructure_temp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; ERROR: duplicate key value violates unique constraint "_orgunitstructure_temp_pkey" Détail : Key (organisationunitid)=(98691) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "_orgunitstructure_temp_pkey" Détail : Key (organisationunitid)=(98691) already exists.

its a dhis2 2.40.9

1 Like
2

by looking at the table structure, I don’t really see how that could happen

except a big issue in the parents creating a kind of endless loop
the orgunit is there since 2023 so hard to understand

I didn’t manage to find how the list of orgunit is found (the code in 2.40 is hardly searchable, master and other branch have already be rewritten for the new analytics db.

dhis2_sandbox> \d _orgunitstructure
+---------------------+---------------+-----------+
| Column              | Type          | Modifiers |
|---------------------+---------------+-----------|
| organisationunitid  | bigint        |  not null |
| organisationunituid | character(11) |           |
| level               | integer       |           |
| idlevel1            | bigint        |           |
| uidlevel1           | character(11) |           |
| namelevel1          | text          |           |
| idlevel2            | bigint        |           |
| uidlevel2           | character(11) |           |
| namelevel2          | text          |           |
| idlevel3            | bigint        |           |
| uidlevel3           | character(11) |           |
| namelevel3          | text          |           |
| idlevel4            | bigint        |           |
| uidlevel4           | character(11) |           |
| namelevel4          | text          |           |
| idlevel5            | bigint        |           |
| uidlevel5           | character(11) |           |
| namelevel5          | text          |           |
| idlevel6            | bigint        |           |
| uidlevel6           | character(11) |           |
| namelevel6          | text          |           |
+---------------------+---------------+-----------+
Indexes:
    "_orgunitstructure_temp_pkey" PRIMARY KEY, btree (organisationunitid)
    "in_orgunitstructure_organisationunituid_gce0w" UNIQUE, btree (organisationunituid)
1 Like
3

So I investigated a bit further and try to find the sql


db-1    | 2026-03-11 07:46:27.994 UTC [29] LOG:  duration: 0.171 ms  parse <unnamed>: select max(organisati0_.hierarchylevel) as col_0_0_ from organisationunit organis
ati0_
db-1    | 2026-03-11 07:46:27.994 UTC [29] LOG:  duration: 0.120 ms  bind <unnamed>: select max(organisati0_.hierarchylevel) as col_0_0_ from organisationunit organisa
ti0_
db-1    | 2026-03-11 07:46:27.994 UTC [29] LOG:  execute <unnamed>: select max(organisati0_.hierarchylevel) as col_0_0_ from organisationunit organisati0_
db-1    | 2026-03-11 07:46:27.994 UTC [29] LOG:  duration: 0.039 ms
db-1    | 2026-03-11 07:46:28.000 UTC [29] LOG:  duration: 0.391 ms  parse <unnamed>: select distinct organisati0_.organisationunitid as organisa1_135_, organisati0_.u
id as uid2_135_, organisati0_.code as code3_135_, organisati0_.created as created4_135_, organisati0_.lastUpdated as lastupda5_135_, organisati0_.lastupdatedby as last
upda6_135_, organisati0_.name as name7_135_, organisati0_.shortname as shortnam8_135_, organisati0_.parentid as parentid9_135_, organisati0_.path as path10_135_, organ
isati0_.hierarchylevel as hierarc11_135_, organisati0_.description as descrip12_135_, organisati0_.openingdate as opening13_135_, organisati0_.closeddate as closedd14_
135_, organisati0_.comment as comment15_135_, organisati0_.geometry as geometr16_135_, organisati0_.url as url17_135_, organisati0_.contactPerson as contact18_135_, or
ganisati0_.address as address19_135_, organisati0_.email as email20_135_, organisati0_.phoneNumber as phonenu21_135_, organisati0_.translations as transla22_135_, orga
nisati0_.image as image23_135_, organisati0_.userid as userid24_135_, organisati0_.attributeValues as attribu25_135_ from organisationunit organisati0_ where organisat
i0_.hierarchylevel in ($1) order by organisati0_.name
db-1    | 2026-03-11 07:46:28.000 UTC [29] LOG:  duration: 0.236 ms  bind <unnamed>: select distinct organisati0_.organisationunitid as organisa1_135_, organisati0_.ui
d as uid2_135_, organisati0_.code as code3_135_, organisati0_.created as created4_135_, organisati0_.lastUpdated as lastupda5_135_, organisati0_.lastupdatedby as lastu
pda6_135_, organisati0_.name as name7_135_, organisati0_.shortname as shortnam8_135_, organisati0_.parentid as parentid9_135_, organisati0_.path as path10_135_, organi
sati0_.hierarchylevel as hierarc11_135_, organisati0_.description as descrip12_135_, organisati0_.openingdate as opening13_135_, organisati0_.closeddate as closedd14_1
35_, organisati0_.comment as comment15_135_, organisati0_.geometry as geometr16_135_, organisati0_.url as url17_135_, organisati0_.contactPerson as contact18_135_, org
anisati0_.address as address19_135_, organisati0_.email as email20_135_, organisati0_.phoneNumber as phonenu21_135_, organisati0_.translations as transla22_135_, organ
isati0_.image as image23_135_, organisati0_.userid as userid24_135_, organisati0_.attributeValues as attribu25_135_ from organisationunit organisati0_ where organisati
0_.hierarchylevel in ($1) order by organisati0_.name
db-1    | 2026-03-11 07:46:28.000 UTC [29] DETAIL:  parameters: $1 = '1'
db-1    | 2026-03-11 07:46:28.000 UTC [29] LOG:  execute <unnamed>: select distinct organisati0_.organisationunitid as organisa1_135_, organisati0_.uid as uid2_135_, o
rganisati0_.code as code3_135_, organisati0_.created as created4_135_, organisati0_.lastUpdated as lastupda5_135_, organisati0_.lastupdatedby as lastupda6_135_, organi
sati0_.name as name7_135_, organisati0_.shortname as shortnam8_135_, organisati0_.parentid as parentid9_135_, organisati0_.path as path10_135_, organisati0_.hierarchyl
evel as hierarc11_135_, organisati0_.description as descrip12_135_, organisati0_.openingdate as opening13_135_, organisati0_.closeddate as closedd14_135_, organisati0_
.comment as comment15_135_, organisati0_.geometry as geometr16_135_, organisati0_.url as url17_135_, organisati0_.contactPerson as contact18_135_, organisati0_.address
 as address19_135_, organisati0_.email as email20_135_, organisati0_.phoneNumber as phonenu21_135_, organisati0_.translations as transla22_135_, organisati0_.image as
image23_135_, organisati0_.userid as userid24_135_, organisati0_.attributeValues as attribu25_135_ from organisationunit organisati0_ where organisati0_.hierarchylevel
 in ($1) order by organisati0_.name
db-1    | 2026-03-11 07:46:28.000 UTC [29] DETAIL:  parameters: $1 = '1'
db-1    | 2026-03-11 07:46:28.000 UTC [29] LOG:  duration: 0.069 ms
db-1    | 2026-03-11 07:46:28.004 UTC [29] LOG:  duration: 0.095 ms  parse <unnamed>: select distinct organisati0_.organisationunitid as organisa1_135_, organisati0_.uid as uid2_135_, organisati0_.code as code3_135_, organisati0_.created as created4_135_, organisati0_.lastUpdated as lastupda5_135_, organisati0_.lastupdatedby as lastupda6_135_, organisati0_.name as name7_135_, organisati0_.shortname as shortnam8_135_, organisati0_.parentid as parentid9_135_, organisati0_.path as path10_135_, organisati0_.hierarchylevel as hierarc11_135_, organisati0_.description as descrip12_135_, organisati0_.openingdate as opening13_135_, organisati0_.closeddate as closedd14_135_, organisati0_.comment as comment15_135_, organisati0_.geometry as geometr16_135_, organisati0_.url as url17_135_, organisati0_.contactPerson as contact18_135_, organisati0_.address as address19_135_, organisati0_.email as email20_135_, organisati0_.phoneNumber as phonenu21_135_, organisati0_.translations as transla22_135_, organisati0_.image as image23_135_, organisati0_.userid as userid24_135_, organisati0_.attributeValues as attribu25_135_ from organisationunit organisati0_ where organisati0_.hierarchylevel in ($1) order by organisati0_.name

the process seem to be done by “hierarchylevel”

but clearly don’t understand how it might happen to have duplicates, there’s no join from what I see
(still haven’t located in the code what is loading the orgunits)

since the offending orgunit seem to be in a part of the pyramid we don’t want anymore, I’ll try to advise the admin to delete the “dead” pyramid

1 Like
4

Hi
This is an interesting investigation you’ve embarked on. Could it be something specific to your instance or is this definitely something in the source code? :eyes:
Thanks!

5

I don’t have access to the db so it’s hard to tell what is really going on. I have only sqlViews to try to understand it.

1 Like
6

I think that sqlViews might be help, and maybe the API? The point is I think we have better luck understanding what’s happening when debugging the issue in the same instance.

7

So I still don’t have the root cause. Not sure I’ll have it (won’t get a dump to reproduce)
But cleaning the orgunits solved the issue.

Note I had to adapt the dhis2-utils/resources/sql/delete_orgunittree_with_data.sql at master · dhis2/dhis2-utils · GitHub

here is the modified version that seem to cover 2.40.x

1 Like
8

Did this happen after an upgrade? I remember in one of the upgrade notes from version to version, running a number of SQL queries was actually required.

9

indeed but I don’t what was the initial version, I’ll try to find that.

10

was 2.32 so already with flyway

11

Yes, but even with flyway, I think sometimes there needs to be some things to look into before the upgrades are initialized.

For example, Important database upgrade for tracker performance (this was linked in dhis2-releases/releases/2.33/README.md at master · dhis2/dhis2-releases · GitHub)

Another example when upgrading to 2.37 https://github.com/dhis2/dhis2-releases/blob/master/releases/2.37/README.md:

migration scripts try to remove ReportTable and Chart tables
migration scripts try to remove ReportTable and Chart tables1661×728 88.4 KB

In 2.40, note about Dashboard sharing (and that admins can use a ‘cascade sharing script’ to grant permissions): dhis2-releases/releases/2.40/README.md at master · dhis2/dhis2-releases · GitHub … additional notes in v41 and in v42.

The list goes on and on…and that’s the reason why I’m guessing it might be related; however, if this is not specific to an ‘instance’ (can be reproduced on other instances of the same version) and the upgrade notes are followed as expected then it might actually be a bug issue… :thinking:

Thanks for sharing all these updates..

1 Like