Hi Daniel - I didn't expect it to go so well, but that's great!
On Wed, Sep 30, 2020, at 1:52 PM, Daniel Krebs wrote: > Hi Mike, > > I adapted our setup to the new cookbook recipe and it basically fixed > both problems! I don't need to maintain my > patches anymore (thanks to setting > `connection.dialect.default_schema_name`, this did the trick!) and the > detected stray changes are also gone. I did some investigation with > increased log level as you suggested and came to the conclusion that it > must have been a bad idea to manipulate the `conn_table_names` object > in-place. But since that's gone now, everything is working as expected \o/ > > Thank you so much for your help! > > Cheers, > Daniel > > On 29.09.20 19:04, Mike Bayer wrote: > > > > > > Hi, so I added a quick recipe to the site just now just so that the "set > > search path" idea is documented to some extent, that is at > > https://alembic.sqlalchemy.org/en/latest/cookbook.html#rudimental-schema-level-multi-tenancy-for-postgresql-databases > > > > . > > > > Re: autogenerate, if you have many schemas that all contain an exact > > copy of the same schema, then autogenerate implies you'd be running this > > exactly once for just one of the schemas, or a "master" schema, such > > that you generate the .py files that will then be applicable to all the > > schemas. The recipe above discusses this, and in particular it works in > > a completely schema agnostic fashion, you would set > > include_schemas=False in the config and allow the PostgreSQL search_path > > to handle all movement between schemas. > > > > then for the actual migration "upgrade" run, you would run those files > > against each schema in sequence, again making use of search_path in > > order to select each schema. > > > > as far as how alembic is looking at current tables you would need to > > turn on SQL logging, using the "debug" level, such as in alembic.ini (if > > you're using that): > > > > [logger_sqlalchemy] > > level = DEBUG > > handlers = > > qualname = sqlalchemy.engine > > > > > > > > this will emit a lot of queries and result sets. You'd have to capture > > that logging and then grep through for the "nonexistent" schema - it > > will be present in a result set that Alembic is capturing, most > > fundamentally the one it uses to get all the schema names. > > > > > > > > > > On Tue, Sep 29, 2020, at 4:01 PM, Daniel Krebs wrote: > >> Hi Mike, > >> > >> thanks a bunch for the quick response! > >> > >> >> 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. > >> > >> It's been some time already since I've implemented this, so can't recall > >> the exact reason why I went down that road but I know for sure that I > >> was trying to make it work with schema_translate_map but didn't succeed. > >> I would have very much preferred a solution without patching alembic it > >> seemed to my last resort at the time. I'd be more than happy to find a > >> solution here :) > >> > >> > >> > 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. > >> > >> Indeed that sounds like a good idea. So you'd suggest to keep one schema > >> just for autogeneration purposes and then apply the migrations to all > >> customer schemas? Is that possible from within alembic or would you wrap > >> some tooling around alembic in order to apply migrations one-by-one to > >> every schema? > >> > >> > >> > 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. > >> > >> I tried the include_object hook back then, but I was facing reflection > >> issues if I remember correctly. We're using TimescaleDB [1] in the same > >> database which adds some schemas of its own that alembic doesn't seem to > >> be able to handle. > >> > >> > >> > 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. > >> > >> Indeed I'm suspecting the same thing. However, I'm not the most > >> knowledgable DBA nor do I have an understanding of how alembic actually > >> compares the current state of the DB with the SA models. Could you maybe > >> provide a more concrete point what and where to look for? > >> > >> > >> Thank you for taking the time to help! > >> > >> > >> Cheers, > >> Daniel > >> > >> [1] https://www.timescale.com/ > >> > >> -- > >> 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 > >> <mailto:sqlalchemy-alembic+unsubscr...@googlegroups.com>. > >> To view this discussion on the web visit > >> https://groups.google.com/d/msgid/sqlalchemy-alembic/fc2bc5e4-274b-a9e7-0566-3b745c4e5fc5%40enlyze.com. > >> > > > > -- > > You received this message because you are subscribed to a topic in the > > Google Groups "sqlalchemy-alembic" group. > > To unsubscribe from this topic, visit > > https://groups.google.com/d/topic/sqlalchemy-alembic/ZlIG6qOULcA/unsubscribe. > > To unsubscribe from this group and all its topics, send an email to > > sqlalchemy-alembic+unsubscr...@googlegroups.com > > <mailto:sqlalchemy-alembic+unsubscr...@googlegroups.com>. > > To view this discussion on the web visit > > https://groups.google.com/d/msgid/sqlalchemy-alembic/cc68de85-ebf3-4eb7-bf83-35b8082a80e4%40www.fastmail.com > > > > <https://groups.google.com/d/msgid/sqlalchemy-alembic/cc68de85-ebf3-4eb7-bf83-35b8082a80e4%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/0d9fd7a5-ffb9-4703-ea5f-77af777658b5%40enlyze.com. > -- 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/9034d5fd-9b93-451d-94d2-5a6e6acea0bf%40www.fastmail.com.
