Unable to run all migrations in the same transaction

[manzanit0]

Hi!

Following the instructions in the river documentation for exporting the SQL, I stumbled across the following error:

SQL State  : 55P04
Error Code : 0
Message    : ERROR: unsafe use of new value "pending" of enum type river_job_state
  Hint: New enum values must be committed before they can be used.
  Position: 402
Location   : /flyway/sql/V11__river_all.up.sql (/flyway/sql/V11__river_all.up.sql)
Line       : 225
Statement  : -- River migration 006 [up]
CREATE OR REPLACE FUNCTION river_job_state_in_bitmask(bitmask BIT(8), state river_job_state)
RETURNS boolean
LANGUAGE SQL
IMMUTABLE
AS $$
    SELECT CASE state
        WHEN 'available' THEN get_bit(bitmask, 7)
        WHEN 'cancelled' THEN get_bit(bitmask, 6)
        WHEN 'completed' THEN get_bit(bitmask, 5)
        WHEN 'discarded' THEN get_bit(bitmask, 4)
        WHEN 'pending' THEN get_bit(bitmask, 3)
        WHEN 'retryable' THEN get_bit(bitmask, 2)
        WHEN 'running' THEN get_bit(bitmask, 1)
        WHEN 'scheduled' THEN get_bit(bitmask, 0)
        ELSE 0
    END = 1;
$$

	at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:256)
	at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:217)
	at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:137)
	at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.executeOnce(SqlMigrationExecutor.java:75)
	at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.lambda$execute$0(SqlMigrationExecutor.java:66)
	at org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:31)
	at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:65)
	at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:391)
	... 19 more
Caused by: org.postgresql.util.PSQLException: ERROR: unsafe use of new value "pending" of enum type river_job_state
  Hint: New enum values must be committed before they can be used.
  Position: 402
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:341)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:326)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:302)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:297)
	at org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:210)
	at org.flywaydb.core.internal.sqlscript.ParsedSqlStatement.execute(ParsedSqlStatement.java:88)
	at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:212)
	... 25 more

After some digging, it seems it's because I'm using flyway which runs migrations transactionally, and the River migrations can't all be run in the same transaction.

Personally, I think this is fine, but I figured I would raise it in case you want to bubble it up in the form of documentation or even just have the closed issue here for future people.

For the record, for me what solved the issue was to separate each migration in a file so that each is run in its own transaction.

[bgentry]

Yeah, this is unfortunately not really avoidable given there are certain schema migrations which can’t happen in the same transaction. It’s part of the reason why the major established migration frameworks (like Rails / ActiveRecord) use independent transactions for each individual migration, and we’ve adjusted River’s own migration system to fit this pattern as well. Another reason is to be able to add indices concurrently.

You are free to alter the migrations to i.e. add the enum value as part of the original type, or run them in individual transactions.