On Tue, Sep 29, 2020, at 9:17 AM, Daniel Krebs wrote: > Hi, > > we're having rather strange problems with Alembic 1.4.2 and Postgres 12, > detecting stray changes *sometimes* but also sometimes not. I already dug > through the code but I increasingly get the feel that this is rooted > somewhere in the interaction between alembic and Postgres. > > But let me explain our setup first. We maintain a set of SQLAlchemy model > definitions that we evolve over time and use alembic to migrate the database > accordingly. For every customer, we add a new Postgres schema to the database > identified by a corresponding UUID (e.g. > a3d74dcc-1634-33a5-ff74-235f3a7c6322). See [1] for our (stripped) env.py. > > Since we want all customers to use the same DDL, we want common migrations > that disregard the schema name altogether. It seems that this is not a > supported use-case of alembic, so I hacked around it such that alembic only > sees schema-less objects and I run SET search_path TO "uuid" before each > migration (see env.py [1]). The patch should be rather straight-forward and > can be found at [2].
This is supported by SQLAlchemy more robustly using the schema_translate_map feature, see https://docs.sqlalchemy.org/en/13/core/connections.html#schema-translating . this feature allows schema names to render explicitly without the need to manipulate search_path. However, when using that approach you'd normally be running autogenerate from just one schema, since you are looking to generate migrations just once and then run them on every tenant. so it's not clear why autogenerate is being run on every tenant explicitly - you'd have just one "model" schema that's the one where you actually run autogenerate upon. there would be no need to consider other schemas and include_schemas would be set to False. As far as being able to filter out schemas across many, the current approach is the include_object hook, which is insufficient for many schemas as it does not block the reflection of all the tables. a new hook include_name is being added in the coming weeks that allows for pre-emptive inclusion or exclusion of specific schema names. > > Now the issue that we're facing is, that *sometimes* autogenerate detects > changes in one or two customer schemas that are not real [3]. Deleting the > migration and creating a new one often doesn't detect the changes anymore or > for a different customer/schema. The tables that are incorrectly found to > have changed also change over time. I don't have much insight onto this other than looking at concurrent activities on the database. "sometimes" usually indicates a race condition of some sort, and the "autogenerate" process is strictly one of reading data. > > My current workaround is to autogenerate migrations until alembic "does the > right thing". I know that patching alembic is not the best base upon which to > ask for support, but to be honest I am running out of theories what is going > wrong here. Hope someone can help or point me into the right direction :) > > Cheers, > Daniel > > [1] > https://gist.github.com/daniel-k/114aa2ac846c02e437b8d86ab89d21ac#file-env-py > [2] > https://gist.github.com/daniel-k/114aa2ac846c02e437b8d86ab89d21ac#file-alembic_patch-diff > [2] > https://gist.github.com/daniel-k/114aa2ac846c02e437b8d86ab89d21ac#file-log-txt > > > > > -- > 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-alembic+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy-alembic/479fee75-e006-4c07-9ab3-149250205521n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy-alembic/479fee75-e006-4c07-9ab3-149250205521n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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-alembic+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/fafa603f-715c-44ce-8d85-3860468c7e7e%40www.fastmail.com.
