Stray changes detected by Alembic *sometimes* on multi-tenant Postgres database

alembic_patch.diff

diff --git a/alembic/autogenerate/compare.py b/alembic/autogenerate/compare.py
index 1b8fb52..1a90b96 100644
--- a/alembic/autogenerate/compare.py
+++ b/alembic/autogenerate/compare.py
@@ -33,6 +33,7 @@ def _produce_net_changes(autogen_context, upgrade_ops):
 
     connection = autogen_context.connection
     include_schemas = autogen_context.opts.get("include_schemas", False)
+    multi_tenant_schema = autogen_context.opts.get("multi_tenant_schema", False)
 
     inspector = inspect(connection)
 
@@ -44,6 +45,9 @@ def _produce_net_changes(autogen_context, upgrade_ops):
         # replace the "default" schema with None
         schemas.discard(default_schema)
         schemas.add(None)
+    elif multi_tenant_schema:
+        # only consider objects in this very schema
+        schemas = [multi_tenant_schema]
     else:
         schemas = [None]
 
@@ -122,6 +126,16 @@ def _compare_tables(
     )
     metadata_table_names = metadata_table_names_no_dflt_schema
 
+    # remove explicit schema from reflected tables so that alembic treats them
+    # as schema-less / belogning to default-schema
+    multi_tenant_schema = autogen_context.opts.get("multi_tenant_schema", False)
+    if multi_tenant_schema:
+        for key in conn_table_names:
+            s, t = key
+            if s == multi_tenant_schema:
+                conn_table_names.discard(key)
+                conn_table_names.add((None, t))
+
     for s, tname in metadata_table_names.difference(conn_table_names):
         name = "%s.%s" % (s, tname) if s else tname
         metadata_table = tname_to_table[(s, tname)]

env.py

def run_migrations_online():
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    for name, customer in enlyze_customers.items():
        print(f'== Run migrations for customer {name}')

        # translate all tables to per-customer schema
        customer_schema = customer['uuid']

        with connectable.connect() as connection:
            context.configure(
                connection=connection,
                target_metadata=target_metadata,
                version_table_schema=customer_schema,
                multi_tenant_schema=customer_schema,
            )

            with context.begin_transaction():
                context.execute(f'CREATE SCHEMA IF NOT EXISTS "{customer_schema}"')
                context.execute(f'SET search_path TO "{customer_schema}"')
                context.run_migrations()

        # alembic erroneously detects table deletions some times, maybe this helps?
        time.sleep(1)

log.txt

$ alembic revision --autogenerate -m "bla"
== Run migrations for customer AAA
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
== Run migrations for customer BBB
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
== Run migrations for customer CCC
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
== Run migrations for customer DDD
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
== Run migrations for customer EEE
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.autogenerate.compare] Detected added table 'XYZ'
INFO [alembic.autogenerate.compare] Detected removed table 'a3d74dcc-1634-33a5-ff74-235f3a7c6322.XYZ'
$
$ rm versions/d4dbf2f8bf_bla.py
$ alembic revision --autogenerate -m "bla2"
== Run migrations for customer AAA
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
== Run migrations for customer BBB
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
== Run migrations for customer CCC
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
== Run migrations for customer DDD
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
== Run migrations for customer EEE
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
$
$ rm versions/a5a6dba3f0af_bla2.py
$ alembic revision --autogenerate -m "bla3"
== Run migrations for customer AAA
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'YYY'
INFO  [alembic.autogenerate.compare] Detected removed table '235e7259-ab7f-318f-be58-ad0734e042d23.YYY'
== Run migrations for customer BBB
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
== Run migrations for customer CCC
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
== Run migrations for customer DDD
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
== Run migrations for customer EEE
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.