On Tue, Sep 29, 2020, at 3:35 PM, Mike Bayer wrote: > > > 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.
this feature may not work completely with Alembic however, I'm evaluating that now. still doesnt shed any light on your sporadic problem, however. > > 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 > > <https://groups.google.com/d/msgid/sqlalchemy-alembic/fafa603f-715c-44ce-8d85-3860468c7e7e%40www.fastmail.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/41844786-661a-47b1-aeb5-3f6876640723%40www.fastmail.com.
