branch merge schema conflict

[br...@derocher.org]

I'm wondering how to solve an issue.

One developer created migration 1efb

upgrade:

  drop view v1 -- moved to query in the code

downgrade:

   create view v1 as select 1

Another developer created a migration c787

upgrade:

   create or replace view v1 as select 2

downgrade

   create or replace view v1 as select 1

Now I'm merging them with alembic merge heads. 

I get revision = 60e8, down_revision = (c787, 1efb).

My question is which migration runs first.  If 1efb goes first, c787 will fail.  If 1efb goes first upgrade is smooth.

My concern is some databases are at 1efb and some are at c787.  For the database at 1efb, how to I prevent a failure?  Can I put some logic in upgrade() (and downgrade()) to prevent this?

Thanks,

Brian

PS: It's actually a bit more complicated than this because I'm merging git forks.

[Mike Bayer]

On Tue, Mar 9, 2021, at 5:06 PM, 'br...@derocher.org' via sqlalchemy-alembic wrote:

I'm wondering how to solve an issue.

One developer created migration 1efb

upgrade:

  drop view v1 -- moved to query in the code

downgrade:

   create view v1 as select 1

Another developer created a migration c787

upgrade:

   create or replace view v1 as select 2

downgrade

   create or replace view v1 as select 1

Now I'm merging them with alembic merge heads. 

I get revision = 60e8, down_revision = (c787, 1efb).

My question is which migration runs first.  If 1efb goes first, c787 will fail.  If 1efb goes first upgrade is smooth.

My concern is some databases are at 1efb and some are at c787.  For the database at 1efb, how to I prevent a failure?  Can I put some logic in upgrade() (and downgrade()) to prevent this?

seems tricky but does c787 actually fail?  it says "create or replace", so if "v1" was already dropped it would just create a new view.     in the other direction, the view is still named "v1" so 1efb will just drop "v1".

if this is not exactly accurate and the name is different or something you can always modify 1efb to read "drop view if exists" and "create or replace view ".    if there's no "drop view if exists" on your target DB then I'd stick a quick reflection query in the migration to check if it exists, inspect(engine).get_view_definition() should have that.

Thanks,

Brian

PS: It's actually a bit more complicated than this because I'm merging git forks.

--

You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/e1fe03f2-bc06-4c76-a75b-e8d0efcf4473n%40googlegroups.com.

[br...@derocher.org]

c787 did not fail, nor did 60e8 on upgrade.  According to alembic history, alembic ran 1efb before c787.  So the result is I have a view in my database that will never get used because the query is now embedded in code.  The solution is clear.  60e8 should drop a view if there is one.

For downgrade we get lucky.

I feel the situation could be more diabolic for downgrade.  Unlike code version control, it seems the precondition does not need to exist, at least for views when you change only the body, not the "view signature".  If for example the c787 upgrade as "create or replace view v1 as select 2, 2" then it gets messy.  I don't even know what downgrade would look like.  I assume alembic would undo the last upgrade applied based on history.

For a single repository, only one branch gets into master first.  So that defines the order.  When there's a fork, it gets more complicated.

[Mike Bayer]

just so you know, surgical-precision downgrades will be more reliable once we get the 1.6 series of Alembic out, where we have a total rewrite of how the internal traversal works in order to make upgrade/downgrades across complex trees more reliable.      downgrades are not *too* common in production systems.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/3873492a-b421-4fe1-b736-9a0d462961a1n%40googlegroups.com.

[br...@derocher.org]

Thanks for your quick responses.  We have never downgraded our prod database, but I feel it's still nice to have that option.

I was just thinking.... While I'm thinking there needs to be one migration that merges 2 versions, and in my mind it has to work on both repositories.... That's not true.  When merging migrations from one repo 1 into repo 2... the repo 2 migrations will already be out there on prod.  So the order is determined.  Likewise for merging migrations from repo 2 into repo 1 if that's needed.

I look forward to 1.6.  I was just watching Selena Deckelmann's video on Alembic when it didn't have merging.  The progress here is great.  Thanks.