search

zonkyio / embedded-postgres

Java embedded PostgreSQL component for testing
Apache License 2.0
349 stars 46 forks source link

Migrations that concurrently create indices hang in Flyway 9.1.2 #96

Closed nickelsen closed 1 year ago

nickelsen commented 2 years ago

Hi. After upgrading from Flyway 9.0.3 to 9.1.2, a migration that concurrently creates an index hangs in the preparer.

The behavior can be seen in the two commits here: https://github.com/nickelsen/embedded-postgres-flyway-test

I'm not sure if Flyway 9 is supported in the first place, but it works for us on Flyway 9.0.3.

In 9.1.2, Flyway switched from session locks to transactional locks in Postgres, which I think causes this hanging.

We're supposed to be able to turn off transactional locks through configuration, but as far as I can tell, the preparer just uses default configuration and cannot be configured using API or configuration files.

I've unsuccessfully attempted to override the configuration through the environment variable described here: https://flywaydb.org/documentation/configuration/parameters/postgresqlTransactionalLock

Writing this, I realized that we can go around the preparer and set the config on the API, so I added a commit demonstrating that in the above repo.

I'm not sure if this is relevant here or needs to be addressed in flyway-core, but this issue prevents us from using the FlywayPreparer so we have had to create our own preparer.

tomix26 commented 1 year ago

Hi @nickelsen, thanks for the comprehensive description of the problem. I'll take a look at it as soon as I can.

tomix26 commented 1 year ago

Ok, based on your report, I've extended the FlywayPreparer by a new factory method accepting a configuration map. So it should be possible to disable the transactional lock or change any other configuration. See the example below.

FlywayPreparer preparer = FlywayPreparer.fromConfiguration(Map.of(
             "flyway.locations", "db/migration",
             "flyway.postgresql.transactional.lock", "false"));

Regarding the second question, whether the problem is related to this library or the Flyway project, I found the following issue: https://github.com/flyway/flyway/issues/3508. So I don't suppose the problem is caused by this project.