Downgrade for just one branch from multi heads

[sqlalchemy-bot]

Migrated issue, originally created by dem0n3d (@dem0n3d)

I frequently need possibility to downgrade just one of my heads to branchpoint, but alembic tries to downgrade all of them. Can you implement this feature, please?

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

unfortunately this is extremely complicated as there are scenarios where those other branches must be pulled down. I will leave this open but I'm unlikely to have a good solution for this anytime soon.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: versioning model

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

the idea is that given:

base -> rev1 --> rev2 (brancha)
             +-> rev2a (branchb) -> rev3a

if im on "rev2" / "rev3a" and i say:

alembic downgrade branchb@rev1

it would figure out we don't need to do rev2.

A quick patch that does this is:

diff --git a/alembic/script/revision.py b/alembic/script/revision.py
index 3d9a332..7d609c1 100644
--- a/alembic/script/revision.py
+++ b/alembic/script/revision.py
@@ -655,6 +655,10 @@ class RevisionMap(object):
 
         uppers = util.dedupe_tuple(self.get_revisions(upper))
 
+        if select_for_downgrade and "@" in lower and "@" not in upper:
+            branch = lower.split("@")[0]
+            uppers = set(u for u in uppers if branch in u.branch_labels)
+
         if not uppers and not requested_lowers:
             return

but this breaks tests for the situation where we do need to still pull down rev2, including if we said to go all the way to the base, as well as when other branches have dependencies on one of the revisions that we're removing. so it would need to figure out "preferred" heads to bring down but also still figure out other heads that have to come down.

[sqlalchemy-bot]

dem0n3d (@dem0n3d) wrote:

I wish to alembic have operation like git "checkout": when you specify revision (or branch), alembic ensures that db migrated to this state (and only this). But I realize that it is too complicated :)

Maybe somethink like "downgrade revision range"?

alembic downgrade rev2a:rev3a

Or simply add some CLI options to your solution?

[simonbowly]

Interested to discuss & implement this if it is still going to be useful for others. I'm using alembic to manage multiple branches and ran into similar issues trying to wind back a single branch to a branchpoint reliably.

I have a rough concept for an alembic checkout implementation which covers (at least) the cases mentioned in these issues:

• alembic checkout rev2 in Downgrade for just one branch from multi heads #464 would run downgrades to remove only rev2a and rev3a on branchb
• alembic checkout B would downgrade only revision C as mentioned in downgrade from specific head when multiple heads are present? #660 (similarly for alembic checkout C)

Roughly the idea is to:

1. Determine the current subtree of revisions that the database uses (trace all ancestors & dependencies from the current revision set).
2. Determine the target subtree (my thinking is to use alembic checkout <rev1> <rev2> ... as suggested in this issue which specifies a complete target tree state).
3. Determine revision scripts to be downgraded and upgraded, run downgrades in reverse topological sort order then upgrades in topological sorted order to reach the target state.

Happy to put a PR together for this but are there are other cases that should be covered or should a different API be used?

[zzzeek]

hey there -

this sounds interesting though I would have much to learn. For example, why is it called "alembic checkout"? if one wants to "downgrade" I would think they'd be using the "downgrade" command ?

[zzzeek]

or is this command of a more general scope? "checkout" from tools like git sounds like a passive command but running upgrades and downgrades is not a passive operation.

[simonbowly]

Ah, I went with the OP's suggestion of "checkout" but hadn't thought about this, makes sense.

I was thinking of a very general operation that runs upgrades and downgrades as necessary to migrate the database to a fixed state. So the OPs of these issues are after in general:

Initial state:

base -> rev1 --> rev2 (brancha)
             +-> rev2a (branchb)
             +-> rev2b (branchc)

Final state after some alembic operation (doesn't seem to currently be possible as alembic downgrade -1 might choose a different branch to wind back):

base -> rev1 --> rev2 (brancha)
             +-> rev2a (branchb)

I'm suggesting alembic checkout rev2 rev2a (or similar) should be interpreted as "rev2 and rev2a is the target set of revisions" to be kept, so it should run a downgrade of the rev2b script.

[zzzeek]

can you review my comment at #464 (comment) and see if the proposed syntax (which is a syntax we already have, branch@revision, will work, that is:

alembic downgrade rev2b@rev1

see https://alembic.sqlalchemy.org/en/latest/branches.html#working-with-branch-labels for a description of this syntax.

in "branch@revision" syntax, anything on the left side that identifies a particular branch, like a specific rev, or a branch label, etc., will work, it's all the same thing.

[simonbowly]

Yes I can see that this syntax makes more sense to downgrade a specific branch. Your suggested patch handles the labelled branch case as long as the labelled revision is a current head. I'll attempt a PR for a more general approach.

I found a few odd quirks (I think) of downgrade with multiple branches while trying this out. Will point them out in a separate issue.

[sqla-tester]

Simon Bowly has proposed a fix for this issue in the master branch:

New downgrade algorithm to fix branch behaviour https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/2530