How to create indexes simultaneously with span in postgres?

Question

Postgres allows you to create indexes without locking the table using the CONCURRENTLY option. These parameters are not allowed in the transaction, so when it is added to the migration path script, the path ends with an error:

ERROR: caused by org.postgresql.util.PSQLException: ERROR: CREATE INDEX CONCURRENTLY cannot work inside a transaction block

ActiveRecord supports disable_ddl_transaction! to solve this scenario. Does flyway support any way to run such a script outside of a transaction?

Answer 1

Not now. All migrations are performed as part of a transaction. Feel free to write an improvement request to the problem tracker.

Answer 2

According to this post:

Migrate passes for postgres CREATE INDEX CONCURRENTLY

You can simply add NT as a prefix for your migration to

V201609121806__create_index_for_table.sql

becomes

NTV201609121806__create_index_for_table.sql

Answer 3

This was implemented in Flyway 4.1, see Ticket 851 and Flyway Non-Transactional PostgreSQL Support .

This function works by detecting that the statement should be executed without a transaction. To my knowledge, only PostgreSQL is currently supported.

The first hurdle detects when migration should be performed outside of the transaction. Two ways forward are obvious. The first way, [..], is to create a file name annotation. The second way is to add a parser on Flyway that detects non-transactional commands and runs them at the appropriate isolation level. Despite the extra work this would require, the Boxfuse team was following the second path. This provides the best Flyway Users in the long run.

By default, a migration that runs this way should only contain statements that should run without a transaction. Mixing with transactional operators is not allowed by default, and must be enabled using the property.

Creating a condition in which migrations should not mix a transaction with operators without transactions creates a restriction that may introduce an invisible problem for the user in the future. If this is not known to the situation requiring DDL, which usually starts inside the transaction to be processed, a property called flyway.allowMixedMigrations is added to the configuration. By default, it is false. I highly recommend never turning it on.

Operators that are processed without transactions are defined by the code in PostgreSQLSqlStatementBuilder :

 if (statementStart.matches("(CREATE|DROP) (DATABASE|TABLESPACE) .*") || statementStart.matches("ALTER SYSTEM .*") || statementStart.matches("(CREATE|DROP)( UNIQUE)? INDEX CONCURRENTLY .*") || statementStart.matches("REINDEX( VERBOSE)? (SCHEMA|DATABASE|SYSTEM) .*") || statementStart.matches("VACUUM .*") || statementStart.matches("DISCARD ALL .*") || statementStart.matches("ALTER TYPE .* ADD VALUE .*") ) { executeInTransaction = false; }