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.
