Issues when migrating version 0.40.23 on Aurora Postgres 10.18

[Rodeoclash]

Environment

• Airbyte version: 0.40.23
• OS Version / Instance: Amazon Linux 2
• Step where error happened: Deploy / Migration

Current Behavior

As reported in https://www.linen.dev/s/airbytehq/t/4745407/hi-there-I-m-setting-up-Airbyte-https-github-com-airbytehq-a I'm getting the same error when attempting to run AirByte migrations on Aurora Postgres 10.18.

Crux of the problem seems to be the line:

Caused by: org.postgresql.util.PSQLException: ERROR: ALTER TYPE ... ADD cannot run inside a transaction block

Expected Behavior

Migrations should run as expected.

Logs

2022-12-05 05:01:30 INFO i.a.d.i.c.m.V0_40_12_001__AddWebhookOperationColumns(migrate):22 - Running migration: V0_40_12_001__AddWebhookOperationColumns
2022-12-05 05:01:30 ERROR o.f.c.i.l.s.Slf4jLog(error):57 - Migration of schema "public" to version "0.40.12.001 - AddWebhookOperationColumns" failed! Changes successfully rolled back.
Exception in thread "main" org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: Migration failed !
        at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:378)
        at org.flywaydb.core.internal.command.DbMigrate.lambda$applyMigrations$1(DbMigrate.java:271)
        at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:66)
        at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:270)
        at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:243)
        at org.flywaydb.core.internal.command.DbMigrate.lambda$migrateAll$0(DbMigrate.java:141)
        at org.flywaydb.core.internal.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:70)
        at org.flywaydb.core.internal.database.postgresql.PostgreSQLConnection.lock(PostgreSQLConnection.java:99)
        at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:141)
        at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:141)
        at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:101)
        at org.flywaydb.core.Flyway$1.execute(Flyway.java:217)
        at org.flywaydb.core.Flyway$1.execute(Flyway.java:168)
        at org.flywaydb.core.Flyway.execute(Flyway.java:584)
        at org.flywaydb.core.Flyway.migrate(Flyway.java:168)
        at io.airbyte.db.instance.FlywayDatabaseMigrator.migrate(FlywayDatabaseMigrator.java:36)
        at io.airbyte.bootloader.BootloaderApp.runFlywayMigration(BootloaderApp.java:397)
        at io.airbyte.bootloader.BootloaderApp.load(BootloaderApp.java:215)
        at io.airbyte.bootloader.BootloaderApp.main(BootloaderApp.java:300)
Caused by: org.flywaydb.core.api.FlywayException: Migration failed !
        at org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.executeOnce(JavaMigrationExecutor.java:75)
        at org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.lambda$execute$0(JavaMigrationExecutor.java:54)
        at org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:27)
        at org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.execute(JavaMigrationExecutor.java:53)
        at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:370)
        ... 18 more
Caused by: org.jooq.exception.DataAccessException: SQL [alter type "operator_type" add value 'webhook']; ERROR: ALTER TYPE ... ADD cannot run inside a transaction block
        at org.jooq_3.13.4.POSTGRES.debug(Unknown Source)
        at org.jooq.impl.Tools.translate(Tools.java:2753)
        at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:755)
        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:385)
        at io.airbyte.db.instance.configs.migrations.V0_40_12_001__AddWebhookOperationColumns.addWebhookOperationType(V0_40_12_001__AddWebhookOperationColumns.java:42)
        at io.airbyte.db.instance.configs.migrations.V0_40_12_001__AddWebhookOperationColumns.migrate(V0_40_12_001__AddWebhookOperationColumns.java:29)
        at org.flywaydb.core.internal.resolver.java.JavaMigrationExecutor.executeOnce(JavaMigrationExecutor.java:61)
        ... 22 more
Caused by: org.postgresql.util.PSQLException: ERROR: ALTER TYPE ... ADD cannot run inside a transaction block
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:167)
        at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:156)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
        at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:209)
        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:453)
        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:371)
        ... 25 more

Steps to Reproduce

1. Migrate after installation

Are you willing to submit a PR?

Yes

[Rodeoclash]

I have solved this by upgrading to Amazon Aurora Serverless V2.

I suspect (haven't confirmed) that this is actually an issue with running AirByte on Postgres 10.18

[voroninman]

The issue is indeed comes from PostgreSQL but I can't blame the database for it. The migration tool (here flywaydb) has to take into account that PostgreSQL before version 12 doesn't support adding values to ENUM columns in a transaction.

From flywaydb docs:

If Flyway detects that a specific statement cannot be run within a transaction due to technical limitations of your database, it won’t run that migration within a transaction. Instead it will be marked as non-transactional.

For Java migrations, the JavaMigration interface has a method canExecuteInTransaction. This determines whether the execution should take place inside a transaction. You can rely on BaseJavaMigration’s default behavior to return true or override canExecuteInTransaction to execute certain migrations outside a transaction by returning false.

If I understand this correctly adding something like this would address the issue.

public class V0_40_12_001__AddWebhookOperationColumns extends BaseJavaMigration {
    @Override
    public boolean canExecuteInTransaction() {
        return THE_CONNECTION_DATABASE == POSTGRESQL and THE_CONNECTION_DATABASE_VERSION >= 12;
    }
    ...

The flag is set per migration hence it might be a good idea to split the migration to keep running the other changes (adding webhook_operation_configs and operator_webhook columns) still in a transaction.

I don't have expirience with Java, but I hope this would help anyone to address the issue. 🙏

[jtv8]

I'm also experiencing this issue on Azure Database for PostgreSQL using Postgres 11. There is no workaround for this because Postgres 11.17 is the highest supported version on Azure.

As far as I can tell this means it is currently impossible to install the latest version of Airbyte on any Azure managed database which I'd hope would bump this up the priority list!

[voroninman]

This worked for me https://github.com/airbytehq/airbyte/issues/18217#issuecomment-1500722430