(Postgresql) Migration applied if the update of the schemaversion table throws an error

[p16]

Hi,
I'm running postgrator with postgresql and I found a potential issue when the insert into the schemaversion table fails.

1. A migration is run and changes the db
2. The insert fails to update the schemaversion table

I would expect the whole migration to "rollback" but instead I see the migration changes applied and the schemaversion table still pointing to the previous migration as its latest one.

This will cause an error if migrations are run again and they are not idempotent.

I have a branch with a test reproducing this scenario here .

I'm wondering if this scenario is a known edge case and the only solution would be to write idempotent migrations, or if postgrator could support the scenario where both migration and insert are guaranteed to be either "done" or "not done".

[rickbergfalk]

This is a known edge case but it should probably be documented.

I'd really like to wrap the whole migration in a transaction (including persisting the schema version) but there are complications with that especially when trying to support multiple databases. While Postgres supports DDL in a transaction, MySQL does not for example. And even with Postgres, some things like creating an index concurrently cannot be done within a transaction.

I'm open to PRs if anyone has any ideas on how to handle this too.

Something I've thought of is to run the down migration if the insert to schemaversion fails to undo what was done, but there's no guarantee that succeeds either.

Writing idempotent migrations is probably the most practical solution to all of this