Dear All,
I am experiencing this error below; upgrade note said to drop the SQL views but i don’t know how to do it. Please can someone help.
Migration V2_31_1__Migrations_for_release_v31.sql failed
SQL State : 0A000
Error Code : 0
Message : ERROR: cannot alter type of a column used by a view or rule
Detail: rule _RETURN on view _view_check_orgunit_open_close_dates_vs_data depends on column “openingdate”
Location : org/hisp/dhis/db/migration/2.31/V2_31_1__Migrations_for_release_v31.sql (/home/gerald/file:/var/lib/dhis2/hmis/webapps/hmis/WEB-INF/lib/dhis-support-db-migration-2.31.2-SNAPSHOT.jar!/org/hisp/dhis/db/migration/2.31/V2_31_1__Migrations_for_release_v31.sql)
Line : 115
Statement : alter table organisationunit
alter column openingdate TYPE date,
alter column closeddate TYPE date
at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:249)
at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:202)
at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:125)
at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:77)
at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:367)
... 91 more
Caused by: org.postgresql.util.PSQLException: ERROR: cannot alter type of a column used by a view or rule
Detail: rule _RETURN on view _view_check_orgunit_open_close_dates_vs_data depends on column “openingdate”
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266)
at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)
at org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:235)
at org.flywaydb.core.internal.sqlscript.StandardSqlStatement.execute(StandardSqlStatement.java:42)
at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:189)
… 94 more