Transaction still present in `d-postgres`

[tokahuke]

I have been writing a program that manages its own migrations. However, I need a statement to run outside a transaction. My setup is the following:

log::info!("Ensuring migrations are up-to-date");

// Settings for migrations:
let settings = Settings::configure_postgres()
    .database_host(&*crate::env::DB_HOST)
    .database_port(*crate::env::DB_PORT)
    .database_name(&*crate::env::DB_DBNAME)
    .database_user(&*crate::env::DB_USER)
    .database_password(&*crate::env::DB_PASSWORD)
    .migration_location(&*crate::env::MIGRATIONS)?
    .build()?;

// Configuration for migrations:
let mut config = Config::with_settings(&settings);
config.setup()?; // set migrant stuff up in db
config = config.reload()?; // queries what has already been applied (funny name...)

// Do migraty thingies:
let mut migrator = Migrator::with_config(&config);
migrator.all(true).swallow_completion(true).apply()?;

log::info!("everything up-to-date");

Ok(())

Using migrant_lib = { version = "0.28", features = ["d-postgres"] } in Cargo.toml. One of my migrations is

-- ...

alter type my_type add value 'new_value';

-- ...

And I get the error MigrationError: Migration was unsucessful...\nMigrationError: db error: ERROR: ALTER TYPE ... ADD cannot run inside a transaction block.

When I run the migrant CLI tool, everything goes fine, but I suppose it is using PSQL behind the scenes, which is not my case.

[bjeanes]

I don't know if this generalises for other databases, but in Postgres it is not an error to execute COMMIT when there is no open transaction. So, a workaround would be to call COMMIT right before that alter type statement. When migrant later commits the transaction, it shouldn't fail. Alternatively, esp if you have further statements after that alter type, you could BEGIN a new transaction right after.

I've used this technique in different languages/frameworks when the migration library wrapped the migrations in a txn but I had to run a migration outside of one. For instance, CREATE INDEX CONCURRENTLY is another case where it cannot be inside a transaction.

[bjeanes]

FWIW I have not yet used migrant/migrant_lib so the above is hypothesis.

I am a bit confused because the source code comments imply that migrations are not wrapped in transactions in the first place, though the comments may be out of date:

https://github.com/jaemk/migrant_lib/blob/9975de43caf306be276004039a039f066fad01c8/src/migration.rs#L25-L27

[jaemk]

Sorry pretty late to the party here - that comment is correct, the contents of the migration are batch executed as is (whether or not you compiled with the d-postgres feature) https://github.com/jaemk/migrant_lib/blob/18338b6ef202b5eb589d59c75d31ef1fec5d5f59/src/drivers/pg.rs#L201