ED2 KC- Errors when running resource tables & analytics (e.every is not a function / Process failed: Statement Callback; uncategorised SQLException for SQL [drop table _someSystemTable])

Issue:

When running resource tables or analytics an error in red pops up at the bottom of the screen

Analytics reports an issue with a system table being referenced by a view

ERROR 2022-01-21 14:13:52,967 Analytics table process failed:.....

Caused by: org.postgresql.util.PSQLException: 
ERROR: cannot drop table _dataelementcategoryoptioncombo because other objects depend on it 
Detail: view _view_data_elements_by_cocs depends on table _dataelementcategoryoptioncombo 
Hint: Use DROP ... CASCADE to drop the dependent objects too. 
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) at

Resolution

Safest way to drop the offending view is to use the maintenance app >> SQL view to re-execute the view. Whilst this not only drops but recreates the view, this seemed to do the trick!

Steps

  1. Identify the offending view by checking the analytics logs and look for a line of format
    Detail: view _[viewName] depends on table _[someSystemTable]
  2. Belt and braces (there may be more than one view referencing this) - download the sqlView metadata with the SQL definition https://[myServer]/api/sqlViews?fields=id,name,sqlQuery. Search for any references to the table _[someSystemTable]

  3. Head to the maintenance app >> SQL view Management (https://[myServer]/dhis-web-maintenance/index.html#/list/otherSection/sqlView), right click on each view found in step 2, and select Execute query

  4. That’s it! Retry running the resource tables / analytics