ED2 KC- ERROR: cannot drop table because other objects depend on it

EXAMPLE 1 : Error when generating Analytics

An real example of this error: ERROR: cannot drop table analytics_event_pzg2cz4igrh_2099 because other objects depend on it

This error is caused by some kind of mix up in analytics. I’m not sure why analytics fails but the result is that DHIS2 creates a period structure for the “gap years” in your data. For example, if the current year is 2022, but someone mis-entered data for 2099, DHIS2 creates a period for years 2023-2099. Analytics then has to create temp tables for all of those years. I don’t know why it breaks down and fails but it does, often when trying to drop tables, re-run analytics, or drop temp tables (there are many points of failure in analytics.

In short, this is caused by outlying, bad data.

To resolve this issue, identify the outlying data and remove it. Then re-run analytics.

Below is an example SQL query to identify events with an event date greater than the current date.

select uid, executiondate from programstageinstance where executiondate > 'ENTER_CURRENT_DATE_HERE' order by executiondate;

EXAMPLE 2 : Error when generating Resource tables

This can also occur when generating resource tables too, as a result of SQL views reference the resource table helpers

e.g.

ERROR: cannot drop table _orgunitstructure because other objects depend on it\n  Detail: view _view_org_unit_with_uid_by_hierarchy depends on table _orgunitstructure\n  Hint: Use DROP ... CASCADE to drop the dependent objects too.

This is normally be a quick fix, use the data admin app >> maintenance to drop SQL views.

It seems "drop SQL views" doesn't drop all SQL views, but only those defined as views in the SQL view app. So, if you have a view that was once run, but since deleted, the underlying SQL view it created does not get deleted.

Rather than drop manually (especially in a production system where we shouldn’t really tinker directly in the database) there is a workaround. You can recreate the missing "SQL view" with the same name, and then drop SQL views

e.g. for the above error referencing _view_org_unit_with_uid_by_hierarchy

  1. Maintenance >> SQL views >> Create a new SQL view named "org unit with uid by hierarchy" (remember to drop "_view_" from the title and replace underscores with spaces in the view name
  2. Data Admin >> Maintenance >> Drop SQL views
  3. Check analytics will now run
  4. Delete the view you just created.
  5. Pat yourself on the back for a job well done