ERROR: cannot drop columns from view from marquez/db/migration/R__3_Datasets_view.sql

[Esaych]

Issue

With existing local database instance, starting up my lineage backend service throws this error. Work around for me is to delete my database and recreate it. Ideally this shouldn't need to happen.

! org.postgresql.util.PSQLException: ERROR: cannot drop columns from view
! at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
! at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
! at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
! at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
! at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
! at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333)
! at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319)
! at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:295)
! at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:290)
! at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
! at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
! at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
! at java.base/java.lang.reflect.Method.invoke(Method.java:568)
! at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
! at jdk.proxy2/jdk.proxy2.$Proxy46.execute(Unknown Source)
! at org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:201)
! at org.flywaydb.core.internal.sqlscript.ParsedSqlStatement.execute(ParsedSqlStatement.java:95)
! at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:210)
! ... 28 common frames omitted
! Causing: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: Migration R__3_Datasets_view.sql failed
! ---------------------------------------
! SQL State  : 42P16
! Error Code : 0
! Message    : ERROR: cannot drop columns from view
! Location   : marquez/db/migration/R__3_Datasets_view.sql (/usr/src/app/file:/usr/src/app/datakin-api.jar!/marquez/db/migration/R__3_Datasets_view.sql)
! Line       : 1
! Statement  : CREATE OR REPLACE VIEW datasets_view
! AS
! SELECT uuid,
!        type,
!        created_at,
!        updated_at,
!        namespace_uuid,
!        source_uuid,
!        name,
!        physical_name,
!        description,
!        current_version_uuid,
!        last_modified_at,
!        namespace_name,
!        source_name,
!        is_deleted
! FROM datasets
! WHERE is_hidden IS FALSE
! 
! at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:275)
! at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:222)
! at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:126)
! at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.executeOnce(SqlMigrationExecutor.java:69)
! at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.lambda$execute$0(SqlMigrationExecutor.java:58)
! at org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:27)
! at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:57)
! at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:377)
! ... 22 common frames omitted

How to reproduce

Run migration with existing database instance

Expected behaviour

No errors should be thrown and backend should start without problem

[boring-cyborg]

Thanks for opening your first issue in the Marquez project! Please be sure to follow the issue template!

[pawel-big-lebowski]

• https://stackoverflow.com/questions/65117930/postgresql-error-cannot-drop-columns-from-view
• https://www.postgresql.org/docs/current/sql-createview.html

CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. The calculations giving rise to the output columns may be completely different.

[collado-mike]

This means the latest change to the migration script is not backwards compatible for any users that had already applied the previous version of the migration. Any attempts to update their Marquez installation will fail the application.

…

On Wed, Oct 12, 2022 at 11:11 PM pawel.leszczynski ***@***.***> wrote: - https://stackoverflow.com/questions/65117930/postgresql-error-cannot-drop-columns-from-view - https://www.postgresql.org/docs/current/sql-createview.html CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. The calculations giving rise to the output columns may be completely different. — Reply to this email directly, view it on GitHub <#2183 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AJT2EJF4Q3YEHRQBYJREAYTWC6RXZANCNFSM6AAAAAARDTCCPA> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[pawel-big-lebowski]

Fortunately, this affects only people who do have previous version installed and are now upgrading from main branch. So it does not affect any released versions of Marquez.

It's great, you were able to spot it, @Esaych 👍
Dropping view before should be the fix.