Run all SQL upgrade steps a single transaction

[jflambert]

I'd like an "all-or-nothing" option to perform all upgrades as a single transaction, and fail instantly on error, rather than stay with the last valid step.

Right now if you go from 20->25 and fail at 23, you'll be "stuck" at 22. We wrote some code to rollback to 20, but in fact we'd rather stay at 20 altogether and not have to deal with dowgrade steps.

As a workaround, we'll concatenate all SQL files as a single file (removing all downgrade code first) but we'll still have to run empty files (or update the goose version table directly) to let the system know we went from 20 to 25 (as per my previous example)

[VojtechVitek]

This is not a bad idea, imho. I quite like it.

However, goose supports both transaction and non-transaction migrations. So, we'd need to account for that somehow. Ideas?

And the CLI output would have to account for "rollbacks" too.

Also, some data transactions might get really big -- do we have any concerns with the performance overhead?

[jflambert]

Also, some data transactions might get really big -- do we have any concerns with the performance overhead?

That's a valid concern, but one I'm willing to pay for with longer downtime or better system specs. Like I said, we have an alternative, I just thought I would share my use case with you folks.

[mfridman]

One idea I had was exposing a flag to users, such as --group-txn (can't think of a better flag name atm).

Instead of goose reading a single file and applying it, instead, we could look ahead and make a decision whether to apply all concatenated statements (from all new files) within a single transaction.

If this flag is true, and any file contains the +goose NO TRANSACTION directive we don't apply any migration and fail right away. Or, we could even support a mix of both. E.g.,

I just want to reiterate that I really like this feature proposal. @jflambert ❤️

[mxey]

I have another use case for this: We have some views in our database, that obviously depend on the tables. Right now we use migrations to manage the views, but that is quite unwieldy, because a single line change in a view is a migration that replaces the entire view definition, and the down step contains the old view definition.

We cannot move the views outside the migrations because changes in the tables might necessitate changes in the views that need to happen in the same migration.

What I would like to do is: in a single transaction drop all views, change the tables, and then re-create the views with the new definition. I imagine I could build the view part of that myself with the Goose Go package, but I'd still have to group all the migrations into a single transaction. Otherwise my view definitions would have to be compatible with all intermediate migrations, not just the final one.

[mfridman]

...in a single transaction drop all views, change the tables, and then re-create the views with the new definition.

Goose will run all statements in a single .sql file within a single transaction unless you specified the -- +goose NO TRANSACTION annotation.

So if your single .sql file has all the statements you mentioned, they'll get executed within a single transaction.

Is it correct to assume you have these statements split up into multiple .sql files, and would like those files to be run as an all-or-nothing transaction that spans multiple files?

[mxey]

@mfridman Right now the view changes are in a single file migration file together with the table changes, precisely because I want them to be executed in the same transaction.

What I meant was that I want to move from that to not having the views in the migration files, but rather always drop and recreate them, so I only need to store the most recent view definition in my source files.

[mfridman]

.. always drop and recreate them, so I only need to store the most recent view definition in my source files.

Gotcha, this sounds similar to https://github.com/pressly/goose/issues/472 where we could have some mechanism to specify reusable "sql components" to run every time within the same transaction. If so, let's continue the conversation on that issue.

This issue is more about combining multiple .sql migration files and running them within the same transaction.

Granted there is some similarity because we need a mechanism to run grouped migration files or additional "sql components" within the same transaction.