I’ve noticed a number of entries in the log file referring to being unable to drop certain SQL views “because other objects depend on it”. It seems it’s related to one or two views that are used as sources for other views. Editing the view through the UI produces an on-screen error as well, presumably because it attempts to drop it before creating it.
Here are some examples of log entries:
- WARN 2020-09-14 00:00:12,672 Could not drop view: _view_myview (HibernateSqlViewStore.java [taskScheduler-21])
org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [DROP VIEW IF EXISTS “_view_myview”]; SQL state [2BP01]; error code [0]; ERROR: cannot drop view _view_myview because other objects depend on it
Detail: view _view_institution_et_hierarchie depends on view _view_myview - INFO 2020-09-14 00:00:12,688 [Level: ERROR, category: RESOURCE_TABLE, time: Mon Sep 14 00:00:12 EDT 2020, message: Process failed: StatementCallback; uncategorized SQLException for SQL [CREATE VIEW “_view_myview” AS SELECT * from organisationunit where parentid IN(select organisationunitid from organisationunit where parentid IN(select organisationunitid from organisationunit where parentid=50))]; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block] (InMemoryNotifier.java [taskScheduler-21])
- INFO 2020-09-14 00:00:12,702 ‘system-process’ create org.hisp.dhis.message.MessageConversation, name: Resource table process failed, uid: adfpBXwQXTR (AuditLogUtil.java [taskScheduler-21])
- INFO 2020-09-14 00:00:13,141 ‘system-process’ create org.hisp.dhis.message.MessageConversation, name: Job ‘Resource table’ failed, uid: NV7Xw25G08Z (AuditLogUtil.java [taskScheduler-21])
- ERROR 2020-09-14 00:00:13,188 Job ‘Resource table’ failed (DefaultJobInstance.java [taskScheduler-21])
org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [CREATE VIEW “_view_myview” AS SELECT * from organisationunit where parentid IN(select organisationunitid from organisationunit where parentid IN(select organisationunitid from organisationunit where parentid=50))]; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
What kind of effect will this have on the system overall? We are currently troubleshooting various issues with analytics in version 2.33.4 and noticed these in the log during an analytics run. Is there a chance these could be related?