Provide way of DB schema migration between releases

[bacek]

Describe the bug

Upgrading Polaris from 1.0.1 to 1.1.0 is virtually impossible without manually check difference between DB schemas.

Admin tool boostrap command is failing with:

java.lang.RuntimeException: Error executing sql script: Failed due to 'ERROR: column "location_without_scheme" does not exist                
  Position: 119' (error code 0, sql-state '42703'), after 1 attempts and 5000 milliseconds                                                   
    at org.apache.polaris.persistence.relational.jdbc.JdbcMetaStoreManagerFactory.bootstrapRealms(JdbcMetaStoreManagerFactory.java:158)      
    at org.apache.polaris.persistence.relational.jdbc.JdbcMetaStoreManagerFactory_ClientProxy.bootstrapRealms(Unknown Source)                
    at org.apache.polaris.admintool.BootstrapCommand.call(BootstrapCommand.java:149)                                                         
    at org.apache.polaris.admintool.BootstrapCommand.call(BootstrapCommand.java:32)                                                          
    at picocli.CommandLine.executeUserObject(CommandLine.java:2031)                                                                          
    at picocli.CommandLine.access$1500(CommandLine.java:148)                                                                                 
    at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2469)                                    
    at picocli.CommandLine$RunLast.handle(CommandLine.java:2461)                                                                             
    at picocli.CommandLine$RunLast.handle(CommandLine.java:2423)                                                                             
    at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2277)                                                         
    at picocli.CommandLine$RunLast.execute(CommandLine.java:2425)                                                                            
    at io.quarkus.picocli.runtime.PicocliRunner$EventExecutionStrategy.execute(PicocliRunner.java:26)                                        
    at picocli.CommandLine.execute(CommandLine.java:2174)                                                                                    
    at io.quarkus.picocli.runtime.PicocliRunner.run(PicocliRunner.java:40)                                                                   
    at io.quarkus.runtime.ApplicationLifecycleManager.run(ApplicationLifecycleManager.java:141)                                              
    at io.quarkus.runtime.Quarkus.run(Quarkus.java:80)                                                                                       
    at io.quarkus.runtime.Quarkus.run(Quarkus.java:51)                                                                                       
    at io.quarkus.runner.GeneratedMain.main(Unknown Source)                                                                                  
    at io.quarkus.bootstrap.runner.QuarkusEntryPoint.doRun(QuarkusEntryPoint.java:69)                                                        
    at io.quarkus.bootstrap.runner.QuarkusEntryPoint.main(QuarkusEntryPoint.java:37)                                                         
Caused by: java.sql.SQLException: Failed due to 'ERROR: column "location_without_scheme" does not exist                                      
  Position: 119' (error code 0, sql-state '42703'), after 1 attempts and 5000 milliseconds                                                   
    at org.apache.polaris.persistence.relational.jdbc.DatasourceOperations.withRetries(DatasourceOperations.java:300)                        
    at org.apache.polaris.persistence.relational.jdbc.DatasourceOperations.runWithinTransaction(DatasourceOperations.java:206)               
    at org.apache.polaris.persistence.relational.jdbc.DatasourceOperations.executeScript(DatasourceOperations.java:90)                       
    at org.apache.polaris.persistence.relational.jdbc.JdbcMetaStoreManagerFactory.bootstrapRealms(JdbcMetaStoreManagerFactory.java:152)      
    ... 19 more                                                                                                                              
Caused by: java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: column "location_without_scheme" does not exist             
  Position: 119                                                                                                                              
    at org.apache.polaris.persistence.relational.jdbc.DatasourceOperations.lambda$executeScript$0(DatasourceOperations.java:106)             
    at org.apache.polaris.persistence.relational.jdbc.DatasourceOperations.lambda$runWithinTransaction$4(DatasourceOperations.java:214)      
    at org.apache.polaris.persistence.relational.jdbc.DatasourceOperations.withRetries(DatasourceOperations.java:271)                        
    ... 22 more                                                                                                                              
Caused by: org.postgresql.util.PSQLException: ERROR: column "location_without_scheme" does not exist                                         
  Position: 119                                                                                                                              
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2734)                                            
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2421)                                                  
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)                                                          
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:518)                                                                 
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:435)                                                                         
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:357)                                                                
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:342)                                                                
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:318)                                                                
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:313)                                                                         
    at io.agroal.pool.wrapper.StatementWrapper.execute(StatementWrapper.java:220)                                                            
    at org.apache.polaris.persistence.relational.jdbc.DatasourceOperations.lambda$executeScript$0(DatasourceOperations.java:104)             
    ... 24 more                                                                                                                              
Bootstrap encountered errors during operation.                                                                                               

Digging into code revealed added location_without_scheme fields in schema v2. But polaris-admin doesn't provide schema migration feature.

To Reproduce

No response

Actual Behavior

No response

Expected Behavior

No response

Additional context

No response

System information

No response

[flyrain]

Thanks for reporting it. We are working toward that.

1. Either 1.2.0 or 1.1.1 could give a better solution to allow old schema still works.
2. Adding test to ensure the schema compatibilities Run JDBCPersistence test against all schema version (not just the latest) #2675
3. Migration tool to users who opt-in new schema.

[dimas-b]

#2714 should make it possible for Polaris to run on older schemas. I propose to move this issue out of 1.2.0 and work on tooling for automated schema migration later.

[flyrain]

Agreed that migration tool could come after 1.2. Feel free to move it to 1.3.

[dimas-b]

Moved out of 1.4.0 to 1.5.0 since no PR is available for this yet and the 1.4.0 release is in progress.