Remove explicit transactions from executing a migration

[jackc]

tern runs each migration within a transaction to ensure that the version table is also updated with the migration.

This can be inconvenient when running a migrations in an existing Tx context (#4) or when a migration needs to manage its own transactions (such as large data updates that are executed in multiple steps to avoid a long lock). #5 added the ability to disable transactions from code but it was never exposed to the CLI (#36).

I think I may have come up with an improvement that removes transactions and thereby any ability or interface to disable them.

The entire text of a migration is sent at once via the simple query protocol. The simple query protocol implicitly transactional unless the SQL contains explicit transaction control statements.

The only reason tern used transactions to begin with is to ensure that the migration and the update of the version table is atomic. But what if tern appended the update version table statement to the SQL of the migration before sending it all via the simple query protocol. The whole thing would be implicitly transactional, but migrations could still use begin and commit.

The only concern I have is if there are any SQL migration that somehow could be broken by appending this to it:

; -- in case migration SQL did not terminate last statement (legal in simple query protocol)
reset all;
update schema_version set version=42; -- this line would be generated to put in the table and version

I can't think of any way it could break, but I'd like to get some feedback on the idea.

[JosefWN]

I could be wrong, but right now it's possible to run CREATE DATABASE statements in migrations with transactions disabled, assuming that they are not bundled with other statements forming an implicit transaction.

An unintended side effect might be that it's not possible to create databases in migrations at all?

[jackc]

Just did a test with the simple protocol and create database. No way to send multiple statements at once without the implicit transaction.

The only way it works now is to disable transactions and only have a single statement per migration. So I guess this idea is a no-go.