Flyway 9.19.3 migrate hangs forever on second non-transactional migration

[TheHenrick]

Which version and edition of Flyway are you using?

9.19.3 docker image

If this is not the latest version, can you reproduce the issue with the latest one as well? (Many bugs are fixed in newer releases and upgrading will often resolve the issue)

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

CLI in docker

Which database are you using? (Type & version)

postgres 13

Which operating system are you using?

alpine (docker)

What did you do? (Please include the content causing the issue, any relevant configuration settings, the SQL statement(s) that failed (if any), and the command you ran)

Command:

flyway -url=jdbc:postgresql://db:5432/postgres -schemas=public -user=user -password=password -connectRetries=60 -outOfOrder="true" -postgresql.transactional.lock=false migrate

Migrations that have worked before suddenly freeze on the second non-transactional query using the latest docker image release (9.19.3).

What I've tried:

• I tested the previous image (9.19.1) and verified it works as expected
• I rearranged the queries and verified the migration halts on non-transactional queries
• I tested that I get the exact same behavior on 9.19.1 when removing the -postgresql.transactional.lock=false flag

What did you expect to see?

I expected migrations to complete (within 15 minutes) and not freeze on non-transactional queries.

What did you see instead?

Migration freezes, -postgresql.transactional.lock=false does not work

[AlbertoEE]

I can confirm that this is also happening on the Flyway Docker Image as well.

• Here is the related issue for the docker image -> Transactional Lock False not working anymore for PostgreSQL #3684
• Here is the commit and line that I think it's disabling this behavior 6ea39a7#diff-e408768a33833cad4694932eca507b0e2db9c2145defb949442dbf8558ad57c1R27 (line 27 of class flyway-core/src/main/java/org/flywaydb/core/internal/database/postgresql/PostgreSQLConfigurationExtension.java)

[TheHenrick]

• Here is the related issue for the docker image -> Transactional Lock False not working anymore for PostgreSQL #3684

I also posted for the docker image :)

• Here is the commit and line that I think it's disabling this behavior 6ea39a7#diff-e408768a33833cad4694932eca507b0e2db9c2145defb949442dbf8558ad57c1R27 (line 27 of class flyway-core/src/main/java/org/flywaydb/core/internal/database/postgresql/PostgreSQLConfigurationExtension.java)

You're right, the new default for transaction is set to null and isTransactionalLock always returns true. I see that @Barry-RG also added a method, setTransactionalLock, to set the value, but it is never used. I wonder if he simply forgot to connect it with the cli flags.

[Barry-RG]

Thanks for your update. We have been making updates to the configuration of Flyway and it seems like this has become disconnected. We have already started a fix for this and will update soon.

[TheHenrick]

9.19.4 is working for me, thanks guys for the quick fix!

[MrMaxxan]

I've tried with this previous minimal example and changing Flyway version to 9.19.4 or 9.21.1, but it still hangs when creating the index. Is there anything else that needs to be done to make it work?
https://github.com/AvaPL/flyway-postgres-concurrent-index-issue

[nantipov]

Following to message from @MrMaxxan, this behaviour is also reproducible in 9.22.1.
Should be this issue reopened?
@TheHenrick

[xsor-hbo]

Thanks for your update. We have been making updates to the configuration of Flyway and it seems like this has become disconnected. We have already started a fix for this and will update soon.

Has there been a fix yet?
Thanks!

[luhn]

Also encountering this on 9.22.2

[luhn]

My workaround is to add if not exists to the create index statement. Then I:

• Run the migration.
• It hangs.
• Ctrl-C to cancel, which frees up the locks so create index can successfully run (despite the connection breaking)
• Run the migration again, which skips the index (because it now exists) and completes successfully.

[elwaxoro]

Running into the forever hang on 9.22.1. Using workarounds from:
#3508
and
spring-projects/spring-boot#32629

[noobgramming]

We are also encountering this issue on 9.22.2

Reopen plz?

[gajus]

Same issue on 9.22.3

[gajus]

I've gone down all the way to version 9.6 and it still has this problem.

CC @DoodleBobBuffPants @juliahayward @Lyeeedar

[worldtiki]

Same issue here on 9.22.3.

[Austinito]

Facing this issue on 9.8.2

[topEngineerRay]

same issue on spring boot 3.2 + flyway 9.22.3. All the solutions here does not work for me.
Only by change the version backto 8.xx.x works...

[JasonLuo-Redgate]

To avoid this issue, two conditions need to be met:

• Flyway 9.19.4 version or higher
• postgresql.transactional.lock set to false

[luhn]

There was a regression sometime around 9.22. I just encountered it this morning on 10.6.0 with flyway.postgresql.transactional.lock=false

[JasonLuo-Redgate]

I did another round of verification of this scenario with the latest version of Flyway:

1. Run Migration with a sql script containing CREATE INDEX CONCURRENTLY on postgresql.
2. Flyway hangs forever.
3. Add flyway.postgresql.transactional.lock=false into the configuration file.
4. Run Migration again with the same sql script. The migration completed successfully.

[luhn]

Step 1+2 will successfully create the index (once flyway is terminated, the deadlock clears up and the creation succeeds), so step 3+4 will fail with "ERROR: relation ... already exists". I suspect you have IF NOT EXISTS on your index, which as I pointed out here is a workaround for the issue.

If you start the process with flyway.postgresql.transactional.lock=false, you should see the issue.

I am able to recreate this issue reliably, now in 10.7.1. https://gist.github.com/luhn/7688fb2545bb7bab59c4d7cd02ff3d15

[JasonLuo-Redgate]

Hi @luhn ,
Sorry for not being specific, I rebuilt the db environment between step 1+2 and step 3+4.

But I was able to reproduce the issue now with the configurations you have provided in the gist link. And I found out that it's due to the "# Issue with CREATE INDEX CONCURRENTLY" comment in the conf file.

Remove that comment or put it in a separated line, the issue will be gone.

[luhn]

Huh, sure enough. Thanks for your help!

[ehsansad]

I am getting the same error with version 9.18.0 and I am setting the flyway.postgresql.transactional.lock=false in a separate .conf file before the flyway sql file. can this problem happen if you have several create index concurrenly .. in a single file?

[JasonLuo-Redgate]

Hi @ehsansad ,
First you might need Flyway 9.19.4 version or higher.

postgresql.transactional.lock is a global configuration, which will have an impact on all the migrations.