autogenerate migration issues of constraints across schemas

[n00b42]

EDIT: The naming conventions seems to be not the cause, I suspect the cause might be foreignkeys across multiple (postgres) schemas.

I followed this steps: https://alembic.sqlalchemy.org/en/latest/naming.html#integration-of-naming-conventions-into-operations-autogenerate to add naming conventions for autogenerate, as without naming conventions the name is left to None, and the drop_constraint would not work.

After introducing the naming constraints the autogenerate successfully generated the constraints.

However, the next autogenerate migration will drop the constraints and readd them, also adds one of the constraints twice...

I would expect the following autogenerate migrations to not drop and create the constraints again.

class Base(DeclarativeBase):
    metadata = MetaData(
        naming_convention={
            "ix": "ix_%(column_0_label)s",
            "uq": "uq_%(table_name)s_%(column_0_name)s",
            "ck": "ck_%(table_name)s_`%(constraint_name)s`",
            "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
            "pk": "pk_%(table_name)s",
        }
    )
[...]
class SystemLog(Base):
    __tablename__ = "system_log"
    date: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=func.now(), primary_key=True)
    user_id: Mapped[int] = mapped_column(Integer, ForeignKey("public.res_users.id", ondelete="CASCADE")

initial autogenerated migration:

def upgrade() -> None:
    [...]
    op.create_foreign_key(
        op.f("fk_system_log_user_id_res_users"),
        "system_log",
        "res_users",
        ["user_id"],
        ["id"],
        source_schema="myotherschema",
        referent_schema="public",
        onupdate="CASCADE",
        ondelete="CASCADE",
    )

def downgrade() -> None:
    [...]
    op.drop_constraint(op.f("fk_system_log_user_id_res_users"), "system_log", schema="myotherschema", type_="foreignkey")

next autogenerated migration:

def upgrade() -> None:
    [...]
    op.drop_constraint('fk_system_log_user_id_res_users', 'system_log', type_='foreignkey')
    op.create_foreign_key(op.f('fk_system_log_user_id_res_users'), 'system_log', 'res_users', ['user_id'], ['id'], source_schema='frogcloud', referent_schema='public', onupdate='CASCADE', ondelete='CASCADE')
    op.create_foreign_key(op.f('fk_system_log_user_id_res_users'), 'system_log', 'res_users', ['user_id'], ['id'], referent_schema='public', onupdate='CASCADE', ondelete='CASCADE')


def downgrade() -> None:
    [...]
    op.drop_constraint(op.f('fk_system_log_user_id_res_users'), 'system_log', schema='frogcloud', type_='foreignkey')
    op.create_foreign_key('fk_system_log_user_id_res_users', 'system_log', 'res_users', ['user_id'], ['id'], referent_schema='public', onupdate='CASCADE', ondelete='CASCADE')

Versions.

• OS: Debian trixie
• Python: 3.11.4
• Alembic: 1.11.1
• SQLAlchemy: 2.0.19
• Database: Postgres 15.2
• DBAPI:

Looks like related to this comment in another topic: #916 (comment) (However, I could not find an issue that was created based on that comment.)

[n00b42]

Update: Tried specifying the constraint names explicitly, therefore removing the naming convention as possible cause.

[CaselIT]

Hi,

The schema seems different. Please ensure that the search path is the same between different runs of alembic

[n00b42]

Hi, I tried to setup a minimal example:
https://github.com/n00b42/alembic-constraint-schema-migration-issue-example

[CaselIT]

looking at the example, you are doing things that are not really supported, like changing the value of connection.dialect.default_schema_name = MAIN_SCHEMA

[n00b42]

Sorry for the delay:

leaving the connection.dialect.default_schema_name results in other errors:

first migration looks fine, but the second migration will create the "test" table as if it does not know about it yet, of course resulting in "already exists" errors.

[n00b42]

Also, I only used connection.dialect.default_schema_name as it was mentioned/used in the cookbook.

[CaselIT]

Can you share also the log that's printed by alembic?

[n00b42]

For reference, I took the example mentioned earlier, and just left out the default_schema_name line and set Alembic log level to Debug:

alembic revision --autogenerate -m "REV1":

INFO  [sqlalchemy.engine.Engine] select pg_catalog.version()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] select current_schema()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] show standard_conforming_strings
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.Engine] set search_path to "myschema"
INFO  [sqlalchemy.engine.Engine] [generated in 0.00012s] {}
INFO  [sqlalchemy.engine.Engine] COMMIT
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
INFO  [sqlalchemy.engine.Engine] [generated in 0.00009s] {'table_name': 'alembic_version', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
INFO  [sqlalchemy.engine.Engine] [cached since 0.002175s ago] {'table_name': 'alembic_version', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
INFO  [sqlalchemy.engine.Engine] 
CREATE TABLE alembic_version (
	version_num VARCHAR(32) NOT NULL, 
	CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00004s] {}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s]) AND pg_catalog.pg_class.relpersistence != %(relpersistence_1)s AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
INFO  [sqlalchemy.engine.Engine] [generated in 0.00013s] {'param_1': 'r', 'param_2': 'p', 'relpersistence_1': 't', 'nspname_1': 'pg_catalog'}
INFO  [alembic.autogenerate.compare] Detected added table 'myschema.test'
INFO  [sqlalchemy.engine.Engine] COMMIT
  Generating /ms/projects/test/alembic-constraint-schema-migration-issue-example/alembic/versions/cf41bd8fa96f_rev1.py ...  done

alembic upgrade head

INFO  [sqlalchemy.engine.Engine] select pg_catalog.version()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] select current_schema()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] show standard_conforming_strings
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.Engine] set search_path to "myschema"
INFO  [sqlalchemy.engine.Engine] [generated in 0.00011s] {}
INFO  [sqlalchemy.engine.Engine] COMMIT
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
INFO  [sqlalchemy.engine.Engine] [generated in 0.00009s] {'table_name': 'alembic_version', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
INFO  [sqlalchemy.engine.Engine] SELECT alembic_version.version_num 
FROM alembic_version
INFO  [sqlalchemy.engine.Engine] [generated in 0.00008s] {}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
INFO  [sqlalchemy.engine.Engine] [cached since 0.002965s ago] {'table_name': 'alembic_version', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
INFO  [alembic.runtime.migration] Running upgrade  -> cf41bd8fa96f, REV1
INFO  [sqlalchemy.engine.Engine] 
CREATE TABLE myschema.test (
	id SERIAL NOT NULL, 
	user_id INTEGER NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES public.users (id) ON DELETE CASCADE
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00005s] {}
DEBUG [alembic.runtime.migration] new branch insert cf41bd8fa96f
INFO  [sqlalchemy.engine.Engine] INSERT INTO alembic_version (version_num) VALUES ('cf41bd8fa96f') RETURNING alembic_version.version_num
INFO  [sqlalchemy.engine.Engine] [generated in 0.00014s] {}
INFO  [sqlalchemy.engine.Engine] COMMIT

alembic revision --autogenerate -m "REV2":

INFO  [sqlalchemy.engine.Engine] select pg_catalog.version()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] select current_schema()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] show standard_conforming_strings
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.Engine] set search_path to "myschema"
INFO  [sqlalchemy.engine.Engine] [generated in 0.00012s] {}
INFO  [sqlalchemy.engine.Engine] COMMIT
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
INFO  [sqlalchemy.engine.Engine] [generated in 0.00009s] {'table_name': 'alembic_version', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
INFO  [sqlalchemy.engine.Engine] SELECT alembic_version.version_num 
FROM alembic_version
INFO  [sqlalchemy.engine.Engine] [generated in 0.00005s] {}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s]) AND pg_catalog.pg_class.relpersistence != %(relpersistence_1)s AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
INFO  [sqlalchemy.engine.Engine] [generated in 0.00007s] {'param_1': 'r', 'param_2': 'p', 'relpersistence_1': 't', 'nspname_1': 'pg_catalog'}
INFO  [alembic.autogenerate.compare] Detected added table 'myschema.test'
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_attribute.attname AS name, pg_catalog.format_type(pg_catalog.pg_attribute.atttypid, pg_catalog.pg_attribute.atttypmod) AS format_type, (SELECT pg_catalog.pg_get_expr(pg_catalog.pg_attrdef.adbin, pg_catalog.pg_attrdef.adrelid) AS pg_get_expr_1 
FROM pg_catalog.pg_attrdef 
WHERE pg_catalog.pg_attrdef.adrelid = pg_catalog.pg_attribute.attrelid AND pg_catalog.pg_attrdef.adnum = pg_catalog.pg_attribute.attnum AND pg_catalog.pg_attribute.atthasdef) AS "default", pg_catalog.pg_attribute.attnotnull AS not_null, pg_catalog.pg_class.relname AS table_name, pg_catalog.pg_description.description AS comment, pg_catalog.pg_attribute.attgenerated AS generated, (SELECT json_build_object(%(json_build_object_2)s, pg_catalog.pg_attribute.attidentity = %(attidentity_1)s, %(json_build_object_3)s, pg_catalog.pg_sequence.seqstart, %(json_build_object_4)s, pg_catalog.pg_sequence.seqincrement, %(json_build_object_5)s, pg_catalog.pg_sequence.seqmin, %(json_build_object_6)s, pg_catalog.pg_sequence.seqmax, %(json_build_object_7)s, pg_catalog.pg_sequence.seqcache, %(json_build_object_8)s, pg_catalog.pg_sequence.seqcycle) AS json_build_object_1 
FROM pg_catalog.pg_sequence 
WHERE pg_catalog.pg_attribute.attidentity != %(attidentity_2)s AND pg_catalog.pg_sequence.seqrelid = CAST(CAST(pg_catalog.pg_get_serial_sequence(CAST(CAST(pg_catalog.pg_attribute.attrelid AS REGCLASS) AS TEXT), pg_catalog.pg_attribute.attname) AS REGCLASS) AS OID)) AS identity_options 
FROM pg_catalog.pg_class LEFT OUTER JOIN pg_catalog.pg_attribute ON pg_catalog.pg_class.oid = pg_catalog.pg_attribute.attrelid AND pg_catalog.pg_attribute.attnum > %(attnum_1)s AND NOT pg_catalog.pg_attribute.attisdropped LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = pg_catalog.pg_attribute.attrelid AND pg_catalog.pg_description.objsubid = pg_catalog.pg_attribute.attnum JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s AND pg_catalog.pg_class.relname IN (%(filter_names_1)s) ORDER BY pg_catalog.pg_class.relname, pg_catalog.pg_attribute.attnum
INFO  [sqlalchemy.engine.Engine] [generated in 0.00020s] {'json_build_object_2': 'always', 'attidentity_1': 'a', 'json_build_object_3': 'start', 'json_build_object_4': 'increment', 'json_build_object_5': 'minvalue', 'json_build_object_6': 'maxvalue', 'json_build_object_7': 'cache', 'json_build_object_8': 'cycle', 'attidentity_2': '', 'attnum_1': 0, 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog', 'filter_names_1': 'test'}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_type.typname AS name, pg_catalog.format_type(pg_catalog.pg_type.typbasetype, pg_catalog.pg_type.typtypmod) AS attype, NOT pg_catalog.pg_type.typnotnull AS nullable, pg_catalog.pg_type.typdefault AS "default", pg_catalog.pg_type_is_visible(pg_catalog.pg_type.oid) AS visible, pg_catalog.pg_namespace.nspname AS schema, domain_constraints.condefs, domain_constraints.connames 
FROM pg_catalog.pg_type JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_type.typnamespace LEFT OUTER JOIN (SELECT pg_catalog.pg_constraint.contypid AS contypid, array_agg(pg_catalog.pg_get_constraintdef(pg_catalog.pg_constraint.oid, %(pg_get_constraintdef_1)s)) AS condefs, array_agg(CAST(pg_catalog.pg_constraint.conname AS TEXT)) AS connames 
FROM pg_catalog.pg_constraint 
WHERE pg_catalog.pg_constraint.contypid != %(contypid_1)s GROUP BY pg_catalog.pg_constraint.contypid) AS domain_constraints ON pg_catalog.pg_type.oid = domain_constraints.contypid 
WHERE pg_catalog.pg_type.typtype = %(typtype_1)s ORDER BY pg_catalog.pg_namespace.nspname, pg_catalog.pg_type.typname
INFO  [sqlalchemy.engine.Engine] [generated in 0.00010s] {'pg_get_constraintdef_1': True, 'contypid_1': 0, 'typtype_1': 'd'}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_type.typname AS name, pg_catalog.pg_type_is_visible(pg_catalog.pg_type.oid) AS visible, pg_catalog.pg_namespace.nspname AS schema, lbl_agg.labels AS labels 
FROM pg_catalog.pg_type JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_type.typnamespace LEFT OUTER JOIN (SELECT pg_catalog.pg_enum.enumtypid AS enumtypid, array_agg(CAST(pg_catalog.pg_enum.enumlabel AS TEXT) ORDER BY pg_catalog.pg_enum.enumsortorder) AS labels 
FROM pg_catalog.pg_enum GROUP BY pg_catalog.pg_enum.enumtypid) AS lbl_agg ON pg_catalog.pg_type.oid = lbl_agg.enumtypid 
WHERE pg_catalog.pg_type.typtype = %(typtype_1)s ORDER BY pg_catalog.pg_namespace.nspname, pg_catalog.pg_type.typname
INFO  [sqlalchemy.engine.Engine] [generated in 0.00006s] {'typtype_1': 'e'}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.oid, pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s AND pg_catalog.pg_class.relname IN (%(filter_names_1)s)
INFO  [sqlalchemy.engine.Engine] [generated in 0.00009s] {'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog', 'filter_names_1': 'test'}
INFO  [sqlalchemy.engine.Engine] SELECT attr.conrelid, array_agg(CAST(attr.attname AS TEXT) ORDER BY attr.ord) AS cols, attr.conname, min(attr.description) AS description, NULL AS extra 
FROM (SELECT con.conrelid AS conrelid, con.conname AS conname, con.conindid AS conindid, con.description AS description, con.ord AS ord, pg_catalog.pg_attribute.attname AS attname 
FROM pg_catalog.pg_attribute JOIN (SELECT pg_catalog.pg_constraint.conrelid AS conrelid, pg_catalog.pg_constraint.conname AS conname, pg_catalog.pg_constraint.conindid AS conindid, unnest(pg_catalog.pg_constraint.conkey) AS attnum, generate_subscripts(pg_catalog.pg_constraint.conkey, %(generate_subscripts_1)s) AS ord, pg_catalog.pg_description.description AS description 
FROM pg_catalog.pg_constraint LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = pg_catalog.pg_constraint.oid 
WHERE pg_catalog.pg_constraint.contype = %(contype)s AND pg_catalog.pg_constraint.conrelid IN (%(oids_1)s)) AS con ON pg_catalog.pg_attribute.attnum = con.attnum AND pg_catalog.pg_attribute.attrelid = con.conrelid 
WHERE con.conrelid IN (%(oids_1)s)) AS attr GROUP BY attr.conrelid, attr.conname ORDER BY attr.conrelid, attr.conname
INFO  [sqlalchemy.engine.Engine] [generated in 0.00030s] {'generate_subscripts_1': 1, 'contype': 'p', 'oids_1': 16400}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname, pg_catalog.pg_constraint.conname, CASE WHEN (pg_catalog.pg_constraint.oid IS NOT NULL) THEN pg_catalog.pg_get_constraintdef(pg_catalog.pg_constraint.oid, %(pg_get_constraintdef_1)s) END AS anon_1, nsp_ref.nspname, pg_catalog.pg_description.description 
FROM pg_catalog.pg_class LEFT OUTER JOIN pg_catalog.pg_constraint ON pg_catalog.pg_class.oid = pg_catalog.pg_constraint.conrelid AND pg_catalog.pg_constraint.contype = %(contype_1)s LEFT OUTER JOIN pg_catalog.pg_class AS cls_ref ON cls_ref.oid = pg_catalog.pg_constraint.confrelid LEFT OUTER JOIN pg_catalog.pg_namespace AS nsp_ref ON cls_ref.relnamespace = nsp_ref.oid LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = pg_catalog.pg_constraint.oid JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s AND pg_catalog.pg_class.relname IN (%(filter_names_1)s) ORDER BY pg_catalog.pg_class.relname, pg_catalog.pg_constraint.conname
INFO  [sqlalchemy.engine.Engine] [generated in 0.00014s] {'pg_get_constraintdef_1': True, 'contype_1': 'f', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog', 'filter_names_1': 'test'}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_index.indrelid, cls_idx.relname AS relname_index, pg_catalog.pg_index.indisunique, pg_catalog.pg_constraint.conrelid IS NOT NULL AS has_constraint, pg_catalog.pg_index.indoption, cls_idx.reloptions, pg_catalog.pg_am.amname, CASE WHEN (pg_catalog.pg_index.indpred IS NOT NULL) THEN pg_catalog.pg_get_expr(pg_catalog.pg_index.indpred, pg_catalog.pg_index.indrelid) END AS filter_definition, pg_catalog.pg_index.indnkeyatts, pg_catalog.pg_index.indnullsnotdistinct, idx_cols.elements, idx_cols.elements_is_expr 
FROM pg_catalog.pg_index JOIN pg_catalog.pg_class AS cls_idx ON pg_catalog.pg_index.indexrelid = cls_idx.oid JOIN pg_catalog.pg_am ON cls_idx.relam = pg_catalog.pg_am.oid LEFT OUTER JOIN (SELECT idx_attr.indexrelid AS indexrelid, min(idx_attr.indrelid) AS min_1, array_agg(idx_attr.element ORDER BY idx_attr.ord) AS elements, array_agg(idx_attr.is_expr ORDER BY idx_attr.ord) AS elements_is_expr 
FROM (SELECT idx.indexrelid AS indexrelid, idx.indrelid AS indrelid, idx.ord AS ord, CASE WHEN (idx.attnum = %(attnum_1)s) THEN pg_catalog.pg_get_indexdef(idx.indexrelid, idx.ord + %(ord_1)s, %(pg_get_indexdef_1)s) ELSE CAST(pg_catalog.pg_attribute.attname AS TEXT) END AS element, idx.attnum = %(attnum_2)s AS is_expr 
FROM (SELECT pg_catalog.pg_index.indexrelid AS indexrelid, pg_catalog.pg_index.indrelid AS indrelid, unnest(pg_catalog.pg_index.indkey) AS attnum, generate_subscripts(pg_catalog.pg_index.indkey, %(generate_subscripts_1)s) AS ord 
FROM pg_catalog.pg_index 
WHERE NOT pg_catalog.pg_index.indisprimary AND pg_catalog.pg_index.indrelid IN (%(oids_1)s)) AS idx LEFT OUTER JOIN pg_catalog.pg_attribute ON pg_catalog.pg_attribute.attnum = idx.attnum AND pg_catalog.pg_attribute.attrelid = idx.indrelid 
WHERE idx.indrelid IN (%(oids_1)s)) AS idx_attr GROUP BY idx_attr.indexrelid) AS idx_cols ON pg_catalog.pg_index.indexrelid = idx_cols.indexrelid LEFT OUTER JOIN pg_catalog.pg_constraint ON pg_catalog.pg_index.indrelid = pg_catalog.pg_constraint.conrelid AND pg_catalog.pg_index.indexrelid = pg_catalog.pg_constraint.conindid AND pg_catalog.pg_constraint.contype = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s]) 
WHERE pg_catalog.pg_index.indrelid IN (%(oids_1)s) AND NOT pg_catalog.pg_index.indisprimary ORDER BY pg_catalog.pg_index.indrelid, cls_idx.relname
INFO  [sqlalchemy.engine.Engine] [generated in 0.00013s] {'attnum_1': 0, 'ord_1': 1, 'pg_get_indexdef_1': True, 'attnum_2': 0, 'generate_subscripts_1': 1, 'param_1': 'p', 'param_2': 'u', 'param_3': 'x', 'oids_1': 16400}
INFO  [sqlalchemy.engine.Engine] SELECT attr.conrelid, array_agg(CAST(attr.attname AS TEXT) ORDER BY attr.ord) AS cols, attr.conname, min(attr.description) AS description, bool_and(pg_catalog.pg_index.indnullsnotdistinct) AS indnullsnotdistinct 
FROM (SELECT con.conrelid AS conrelid, con.conname AS conname, con.conindid AS conindid, con.description AS description, con.ord AS ord, pg_catalog.pg_attribute.attname AS attname 
FROM pg_catalog.pg_attribute JOIN (SELECT pg_catalog.pg_constraint.conrelid AS conrelid, pg_catalog.pg_constraint.conname AS conname, pg_catalog.pg_constraint.conindid AS conindid, unnest(pg_catalog.pg_constraint.conkey) AS attnum, generate_subscripts(pg_catalog.pg_constraint.conkey, %(generate_subscripts_1)s) AS ord, pg_catalog.pg_description.description AS description 
FROM pg_catalog.pg_constraint LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = pg_catalog.pg_constraint.oid 
WHERE pg_catalog.pg_constraint.contype = %(contype)s AND pg_catalog.pg_constraint.conrelid IN (%(oids_1)s)) AS con ON pg_catalog.pg_attribute.attnum = con.attnum AND pg_catalog.pg_attribute.attrelid = con.conrelid 
WHERE con.conrelid IN (%(oids_1)s)) AS attr JOIN pg_catalog.pg_index ON attr.conindid = pg_catalog.pg_index.indexrelid GROUP BY attr.conrelid, attr.conname ORDER BY attr.conrelid, attr.conname
INFO  [sqlalchemy.engine.Engine] [generated in 0.00015s] {'generate_subscripts_1': 1, 'contype': 'u', 'oids_1': 16400}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname, pg_catalog.pg_description.description 
FROM pg_catalog.pg_class LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_class.oid = pg_catalog.pg_description.objoid AND pg_catalog.pg_description.objsubid = %(objsubid_1)s JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s AND pg_catalog.pg_class.relname IN (%(filter_names_1)s)
INFO  [sqlalchemy.engine.Engine] [generated in 0.00014s] {'objsubid_1': 0, 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog', 'filter_names_1': 'test'}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname, pg_catalog.pg_constraint.conname, CASE WHEN (pg_catalog.pg_constraint.oid IS NOT NULL) THEN pg_catalog.pg_get_constraintdef(pg_catalog.pg_constraint.oid, %(pg_get_constraintdef_1)s) END AS anon_1, pg_catalog.pg_description.description 
FROM pg_catalog.pg_class LEFT OUTER JOIN pg_catalog.pg_constraint ON pg_catalog.pg_class.oid = pg_catalog.pg_constraint.conrelid AND pg_catalog.pg_constraint.contype = %(contype_1)s LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = pg_catalog.pg_constraint.oid JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s AND pg_catalog.pg_class.relname IN (%(filter_names_1)s) ORDER BY pg_catalog.pg_class.relname, pg_catalog.pg_constraint.conname
INFO  [sqlalchemy.engine.Engine] [generated in 0.00012s] {'pg_get_constraintdef_1': True, 'contype_1': 'c', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog', 'filter_names_1': 'test'}
INFO  [sqlalchemy.engine.Engine] select c.relname, a.attname from pg_class as c join pg_depend d on d.objid=c.oid and d.classid='pg_class'::regclass and d.refclassid='pg_class'::regclass join pg_class t on t.oid=d.refobjid join pg_attribute a on a.attrelid=t.oid and a.attnum=d.refobjsubid where c.relkind='S' and c.relname=%(seqname)s
INFO  [sqlalchemy.engine.Engine] [generated in 0.00007s] {'seqname': 'test_id_seq'}
INFO  [alembic.ddl.postgresql] Detected sequence named 'test_id_seq' as owned by integer column 'test(id)', assuming SERIAL and omitting
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_attribute.attname AS name, pg_catalog.format_type(pg_catalog.pg_attribute.atttypid, pg_catalog.pg_attribute.atttypmod) AS format_type, (SELECT pg_catalog.pg_get_expr(pg_catalog.pg_attrdef.adbin, pg_catalog.pg_attrdef.adrelid) AS pg_get_expr_1 
FROM pg_catalog.pg_attrdef 
WHERE pg_catalog.pg_attrdef.adrelid = pg_catalog.pg_attribute.attrelid AND pg_catalog.pg_attrdef.adnum = pg_catalog.pg_attribute.attnum AND pg_catalog.pg_attribute.atthasdef) AS "default", pg_catalog.pg_attribute.attnotnull AS not_null, pg_catalog.pg_class.relname AS table_name, pg_catalog.pg_description.description AS comment, pg_catalog.pg_attribute.attgenerated AS generated, (SELECT json_build_object(%(json_build_object_2)s, pg_catalog.pg_attribute.attidentity = %(attidentity_1)s, %(json_build_object_3)s, pg_catalog.pg_sequence.seqstart, %(json_build_object_4)s, pg_catalog.pg_sequence.seqincrement, %(json_build_object_5)s, pg_catalog.pg_sequence.seqmin, %(json_build_object_6)s, pg_catalog.pg_sequence.seqmax, %(json_build_object_7)s, pg_catalog.pg_sequence.seqcache, %(json_build_object_8)s, pg_catalog.pg_sequence.seqcycle) AS json_build_object_1 
FROM pg_catalog.pg_sequence 
WHERE pg_catalog.pg_attribute.attidentity != %(attidentity_2)s AND pg_catalog.pg_sequence.seqrelid = CAST(CAST(pg_catalog.pg_get_serial_sequence(CAST(CAST(pg_catalog.pg_attribute.attrelid AS REGCLASS) AS TEXT), pg_catalog.pg_attribute.attname) AS REGCLASS) AS OID)) AS identity_options 
FROM pg_catalog.pg_class LEFT OUTER JOIN pg_catalog.pg_attribute ON pg_catalog.pg_class.oid = pg_catalog.pg_attribute.attrelid AND pg_catalog.pg_attribute.attnum > %(attnum_1)s AND NOT pg_catalog.pg_attribute.attisdropped LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = pg_catalog.pg_attribute.attrelid AND pg_catalog.pg_description.objsubid = pg_catalog.pg_attribute.attnum JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_namespace.nspname = %(nspname_1)s AND pg_catalog.pg_class.relname IN (%(filter_names_1)s) ORDER BY pg_catalog.pg_class.relname, pg_catalog.pg_attribute.attnum
INFO  [sqlalchemy.engine.Engine] [generated in 0.00018s] {'json_build_object_2': 'always', 'attidentity_1': 'a', 'json_build_object_3': 'start', 'json_build_object_4': 'increment', 'json_build_object_5': 'minvalue', 'json_build_object_6': 'maxvalue', 'json_build_object_7': 'cache', 'json_build_object_8': 'cycle', 'attidentity_2': '', 'attnum_1': 0, 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'public', 'filter_names_1': 'users'}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_type.typname AS name, pg_catalog.format_type(pg_catalog.pg_type.typbasetype, pg_catalog.pg_type.typtypmod) AS attype, NOT pg_catalog.pg_type.typnotnull AS nullable, pg_catalog.pg_type.typdefault AS "default", pg_catalog.pg_type_is_visible(pg_catalog.pg_type.oid) AS visible, pg_catalog.pg_namespace.nspname AS schema, domain_constraints.condefs, domain_constraints.connames 
FROM pg_catalog.pg_type JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_type.typnamespace LEFT OUTER JOIN (SELECT pg_catalog.pg_constraint.contypid AS contypid, array_agg(pg_catalog.pg_get_constraintdef(pg_catalog.pg_constraint.oid, %(pg_get_constraintdef_1)s)) AS condefs, array_agg(CAST(pg_catalog.pg_constraint.conname AS TEXT)) AS connames 
FROM pg_catalog.pg_constraint 
WHERE pg_catalog.pg_constraint.contypid != %(contypid_1)s GROUP BY pg_catalog.pg_constraint.contypid) AS domain_constraints ON pg_catalog.pg_type.oid = domain_constraints.contypid 
WHERE pg_catalog.pg_type.typtype = %(typtype_1)s ORDER BY pg_catalog.pg_namespace.nspname, pg_catalog.pg_type.typname
INFO  [sqlalchemy.engine.Engine] [cached since 0.03548s ago] {'pg_get_constraintdef_1': True, 'contypid_1': 0, 'typtype_1': 'd'}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_type.typname AS name, pg_catalog.pg_type_is_visible(pg_catalog.pg_type.oid) AS visible, pg_catalog.pg_namespace.nspname AS schema, lbl_agg.labels AS labels 
FROM pg_catalog.pg_type JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_type.typnamespace LEFT OUTER JOIN (SELECT pg_catalog.pg_enum.enumtypid AS enumtypid, array_agg(CAST(pg_catalog.pg_enum.enumlabel AS TEXT) ORDER BY pg_catalog.pg_enum.enumsortorder) AS labels 
FROM pg_catalog.pg_enum GROUP BY pg_catalog.pg_enum.enumtypid) AS lbl_agg ON pg_catalog.pg_type.oid = lbl_agg.enumtypid 
WHERE pg_catalog.pg_type.typtype = %(typtype_1)s ORDER BY pg_catalog.pg_namespace.nspname, pg_catalog.pg_type.typname
INFO  [sqlalchemy.engine.Engine] [cached since 0.03301s ago] {'typtype_1': 'e'}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.oid, pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_namespace.nspname = %(nspname_1)s AND pg_catalog.pg_class.relname IN (%(filter_names_1)s)
INFO  [sqlalchemy.engine.Engine] [generated in 0.00008s] {'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'public', 'filter_names_1': 'users'}
INFO  [sqlalchemy.engine.Engine] SELECT attr.conrelid, array_agg(CAST(attr.attname AS TEXT) ORDER BY attr.ord) AS cols, attr.conname, min(attr.description) AS description, NULL AS extra 
FROM (SELECT con.conrelid AS conrelid, con.conname AS conname, con.conindid AS conindid, con.description AS description, con.ord AS ord, pg_catalog.pg_attribute.attname AS attname 
FROM pg_catalog.pg_attribute JOIN (SELECT pg_catalog.pg_constraint.conrelid AS conrelid, pg_catalog.pg_constraint.conname AS conname, pg_catalog.pg_constraint.conindid AS conindid, unnest(pg_catalog.pg_constraint.conkey) AS attnum, generate_subscripts(pg_catalog.pg_constraint.conkey, %(generate_subscripts_1)s) AS ord, pg_catalog.pg_description.description AS description 
FROM pg_catalog.pg_constraint LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = pg_catalog.pg_constraint.oid 
WHERE pg_catalog.pg_constraint.contype = %(contype)s AND pg_catalog.pg_constraint.conrelid IN (%(oids_1)s)) AS con ON pg_catalog.pg_attribute.attnum = con.attnum AND pg_catalog.pg_attribute.attrelid = con.conrelid 
WHERE con.conrelid IN (%(oids_1)s)) AS attr GROUP BY attr.conrelid, attr.conname ORDER BY attr.conrelid, attr.conname
INFO  [sqlalchemy.engine.Engine] [cached since 0.02994s ago] {'generate_subscripts_1': 1, 'contype': 'p', 'oids_1': 16385}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname, pg_catalog.pg_constraint.conname, CASE WHEN (pg_catalog.pg_constraint.oid IS NOT NULL) THEN pg_catalog.pg_get_constraintdef(pg_catalog.pg_constraint.oid, %(pg_get_constraintdef_1)s) END AS anon_1, nsp_ref.nspname, pg_catalog.pg_description.description 
FROM pg_catalog.pg_class LEFT OUTER JOIN pg_catalog.pg_constraint ON pg_catalog.pg_class.oid = pg_catalog.pg_constraint.conrelid AND pg_catalog.pg_constraint.contype = %(contype_1)s LEFT OUTER JOIN pg_catalog.pg_class AS cls_ref ON cls_ref.oid = pg_catalog.pg_constraint.confrelid LEFT OUTER JOIN pg_catalog.pg_namespace AS nsp_ref ON cls_ref.relnamespace = nsp_ref.oid LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = pg_catalog.pg_constraint.oid JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_namespace.nspname = %(nspname_1)s AND pg_catalog.pg_class.relname IN (%(filter_names_1)s) ORDER BY pg_catalog.pg_class.relname, pg_catalog.pg_constraint.conname
INFO  [sqlalchemy.engine.Engine] [generated in 0.00011s] {'pg_get_constraintdef_1': True, 'contype_1': 'f', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'public', 'filter_names_1': 'users'}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_index.indrelid, cls_idx.relname AS relname_index, pg_catalog.pg_index.indisunique, pg_catalog.pg_constraint.conrelid IS NOT NULL AS has_constraint, pg_catalog.pg_index.indoption, cls_idx.reloptions, pg_catalog.pg_am.amname, CASE WHEN (pg_catalog.pg_index.indpred IS NOT NULL) THEN pg_catalog.pg_get_expr(pg_catalog.pg_index.indpred, pg_catalog.pg_index.indrelid) END AS filter_definition, pg_catalog.pg_index.indnkeyatts, pg_catalog.pg_index.indnullsnotdistinct, idx_cols.elements, idx_cols.elements_is_expr 
FROM pg_catalog.pg_index JOIN pg_catalog.pg_class AS cls_idx ON pg_catalog.pg_index.indexrelid = cls_idx.oid JOIN pg_catalog.pg_am ON cls_idx.relam = pg_catalog.pg_am.oid LEFT OUTER JOIN (SELECT idx_attr.indexrelid AS indexrelid, min(idx_attr.indrelid) AS min_1, array_agg(idx_attr.element ORDER BY idx_attr.ord) AS elements, array_agg(idx_attr.is_expr ORDER BY idx_attr.ord) AS elements_is_expr 
FROM (SELECT idx.indexrelid AS indexrelid, idx.indrelid AS indrelid, idx.ord AS ord, CASE WHEN (idx.attnum = %(attnum_1)s) THEN pg_catalog.pg_get_indexdef(idx.indexrelid, idx.ord + %(ord_1)s, %(pg_get_indexdef_1)s) ELSE CAST(pg_catalog.pg_attribute.attname AS TEXT) END AS element, idx.attnum = %(attnum_2)s AS is_expr 
FROM (SELECT pg_catalog.pg_index.indexrelid AS indexrelid, pg_catalog.pg_index.indrelid AS indrelid, unnest(pg_catalog.pg_index.indkey) AS attnum, generate_subscripts(pg_catalog.pg_index.indkey, %(generate_subscripts_1)s) AS ord 
FROM pg_catalog.pg_index 
WHERE NOT pg_catalog.pg_index.indisprimary AND pg_catalog.pg_index.indrelid IN (%(oids_1)s)) AS idx LEFT OUTER JOIN pg_catalog.pg_attribute ON pg_catalog.pg_attribute.attnum = idx.attnum AND pg_catalog.pg_attribute.attrelid = idx.indrelid 
WHERE idx.indrelid IN (%(oids_1)s)) AS idx_attr GROUP BY idx_attr.indexrelid) AS idx_cols ON pg_catalog.pg_index.indexrelid = idx_cols.indexrelid LEFT OUTER JOIN pg_catalog.pg_constraint ON pg_catalog.pg_index.indrelid = pg_catalog.pg_constraint.conrelid AND pg_catalog.pg_index.indexrelid = pg_catalog.pg_constraint.conindid AND pg_catalog.pg_constraint.contype = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s]) 
WHERE pg_catalog.pg_index.indrelid IN (%(oids_1)s) AND NOT pg_catalog.pg_index.indisprimary ORDER BY pg_catalog.pg_index.indrelid, cls_idx.relname
INFO  [sqlalchemy.engine.Engine] [cached since 0.02183s ago] {'attnum_1': 0, 'ord_1': 1, 'pg_get_indexdef_1': True, 'attnum_2': 0, 'generate_subscripts_1': 1, 'param_1': 'p', 'param_2': 'u', 'param_3': 'x', 'oids_1': 16385}
INFO  [sqlalchemy.engine.Engine] SELECT attr.conrelid, array_agg(CAST(attr.attname AS TEXT) ORDER BY attr.ord) AS cols, attr.conname, min(attr.description) AS description, bool_and(pg_catalog.pg_index.indnullsnotdistinct) AS indnullsnotdistinct 
FROM (SELECT con.conrelid AS conrelid, con.conname AS conname, con.conindid AS conindid, con.description AS description, con.ord AS ord, pg_catalog.pg_attribute.attname AS attname 
FROM pg_catalog.pg_attribute JOIN (SELECT pg_catalog.pg_constraint.conrelid AS conrelid, pg_catalog.pg_constraint.conname AS conname, pg_catalog.pg_constraint.conindid AS conindid, unnest(pg_catalog.pg_constraint.conkey) AS attnum, generate_subscripts(pg_catalog.pg_constraint.conkey, %(generate_subscripts_1)s) AS ord, pg_catalog.pg_description.description AS description 
FROM pg_catalog.pg_constraint LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = pg_catalog.pg_constraint.oid 
WHERE pg_catalog.pg_constraint.contype = %(contype)s AND pg_catalog.pg_constraint.conrelid IN (%(oids_1)s)) AS con ON pg_catalog.pg_attribute.attnum = con.attnum AND pg_catalog.pg_attribute.attrelid = con.conrelid 
WHERE con.conrelid IN (%(oids_1)s)) AS attr JOIN pg_catalog.pg_index ON attr.conindid = pg_catalog.pg_index.indexrelid GROUP BY attr.conrelid, attr.conname ORDER BY attr.conrelid, attr.conname
INFO  [sqlalchemy.engine.Engine] [cached since 0.01937s ago] {'generate_subscripts_1': 1, 'contype': 'u', 'oids_1': 16385}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname, pg_catalog.pg_description.description 
FROM pg_catalog.pg_class LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_class.oid = pg_catalog.pg_description.objoid AND pg_catalog.pg_description.objsubid = %(objsubid_1)s JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_namespace.nspname = %(nspname_1)s AND pg_catalog.pg_class.relname IN (%(filter_names_1)s)
INFO  [sqlalchemy.engine.Engine] [generated in 0.00009s] {'objsubid_1': 0, 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'public', 'filter_names_1': 'users'}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname, pg_catalog.pg_constraint.conname, CASE WHEN (pg_catalog.pg_constraint.oid IS NOT NULL) THEN pg_catalog.pg_get_constraintdef(pg_catalog.pg_constraint.oid, %(pg_get_constraintdef_1)s) END AS anon_1, pg_catalog.pg_description.description 
FROM pg_catalog.pg_class LEFT OUTER JOIN pg_catalog.pg_constraint ON pg_catalog.pg_class.oid = pg_catalog.pg_constraint.conrelid AND pg_catalog.pg_constraint.contype = %(contype_1)s LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = pg_catalog.pg_constraint.oid JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_namespace.nspname = %(nspname_1)s AND pg_catalog.pg_class.relname IN (%(filter_names_1)s) ORDER BY pg_catalog.pg_class.relname, pg_catalog.pg_constraint.conname
INFO  [sqlalchemy.engine.Engine] [generated in 0.00011s] {'pg_get_constraintdef_1': True, 'contype_1': 'c', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'public', 'filter_names_1': 'users'}
INFO  [sqlalchemy.engine.Engine] COMMIT
  Generating /ms/projects/test/alembic-constraint-schema-migration-issue-example/alembic/versions/11fe07c062a2_rev2.py ...  done

alembic upgrade head

INFO  [sqlalchemy.engine.Engine] select pg_catalog.version()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] select current_schema()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] show standard_conforming_strings
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.Engine] set search_path to "myschema"
INFO  [sqlalchemy.engine.Engine] [generated in 0.00014s] {}
INFO  [sqlalchemy.engine.Engine] COMMIT
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
INFO  [sqlalchemy.engine.Engine] [generated in 0.00009s] {'table_name': 'alembic_version', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
INFO  [sqlalchemy.engine.Engine] SELECT alembic_version.version_num 
FROM alembic_version
INFO  [sqlalchemy.engine.Engine] [generated in 0.00005s] {}
INFO  [alembic.runtime.migration] Running upgrade cf41bd8fa96f -> 11fe07c062a2, REV2
INFO  [sqlalchemy.engine.Engine] 
CREATE TABLE myschema.test (
	id SERIAL NOT NULL, 
	user_id INTEGER NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES public.users (id) ON DELETE CASCADE
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00004s] {}
INFO  [sqlalchemy.engine.Engine] ROLLBACK
Traceback (most recent call last):
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
    self.dialect.do_execute(
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.DuplicateTable: relation "test" already exists


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/bin/alembic", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/alembic/config.py", line 632, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/alembic/config.py", line 626, in main
    self.run_cmd(cfg, options)
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/alembic/config.py", line 603, in run_cmd
    fn(
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/alembic/command.py", line 385, in upgrade
    script.run_env()
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/alembic/script/base.py", line 582, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 940, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/ms/projects/test/alembic-constraint-schema-migration-issue-example/alembic/env.py", line 66, in <module>
    run_migrations_online()
  File "/ms/projects/test/alembic-constraint-schema-migration-issue-example/alembic/env.py", line 60, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/alembic/runtime/environment.py", line 928, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/alembic/runtime/migration.py", line 628, in run_migrations
    step.migration_fn(**kw)
  File "/ms/projects/test/alembic-constraint-schema-migration-issue-example/alembic/versions/11fe07c062a2_rev2.py", line 21, in upgrade
    op.create_table('test',
  File "<string>", line 8, in create_table
  File "<string>", line 3, in create_table
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/alembic/operations/ops.py", line 1277, in create_table
    return operations.invoke(op)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/alembic/operations/base.py", line 395, in invoke
    return fn(self, operation)
           ^^^^^^^^^^^^^^^^^^^
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/alembic/operations/toimpl.py", line 114, in create_table
    operations.impl.create_table(table)
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/alembic/ddl/impl.py", line 354, in create_table
    self._exec(schema.CreateTable(table))
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/alembic/ddl/impl.py", line 193, in _exec
    return conn.execute(  # type: ignore[call-overload]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1412, in execute
    return meth(
           ^^^^^
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py", line 181, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1524, in _execute_ddl
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1844, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1984, in _exec_single_context
    self._handle_dbapi_exception(
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
    self.dialect.do_execute(
  File "/home/ms/.cache/pypoetry/virtualenvs/test-MoqBqT-G-py3.11/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateTable) relation "test" already exists

[SQL: 
CREATE TABLE myschema.test (
	id SERIAL NOT NULL, 
	user_id INTEGER NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES public.users (id) ON DELETE CASCADE
)

]
(Background on this error at: https://sqlalche.me/e/20/f405)

[CaselIT]

Thanks for providing the logs. To add to what mike said in #1290 (comment) if tou want to use that set search path, then the table in the matadata should have the schema set to None instead of 'myschema'.

[n00b42]

changing the Base to __table_args__ = {"schema": None} and the include_object to if type_ == "table" and object.schema != None: seems to do the trick in my case.
Thanks for the hint.

[zzzeek]

just overall I see "set search_path to "myschema"" happening, we have a standard approach for search path that is recommended which is documented at https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path . if this convention is not being followed, then autogenerate will very likely not do the right thing.

we have one recipe that includes changing the search path which is at https://alembic.sqlalchemy.org/en/latest/cookbook.html#rudimental-schema-level-multi-tenancy-for-postgresql-databases . This recipe requires that include_schemas is set to False when the multi-tenancy operation is run.

So as far as what we support, search_path should not be modified outside of its default of "public" if include_schemas is used.

[n00b42]

Thanks for your input.

The original reason why I modified the search path is the following:
Without the modified search path, the autogenerate iterates all existing tables in the public schema (and in my productive environment there are quite a lot),
of course I can ignore them using include_object method, however:
Just iterating through them takes quite a lot of time, it seems there is no method to filter them out BEFORE reflection is done.
(Furthermore, I think I had some warnings on circular dependencies on tables I was not interested in, but maybe this part was fixed with manually providing minimal Table statements for those)

If there is a better approach for my usecase I am happy to try.

[CaselIT]

Just iterating through them takes quite a lot of time, it seems there is no method to filter them out BEFORE reflection is done.

You can probably use include_name https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.include_name

[n00b42]

I tried that, but if I remove include_object and use something like

def include_name(name, type_, parent_names):
    if type_ == "schema":
        return name in [MAIN_SCHEMA] # optionally also include "None"
    else:
        return True

and

context.configure(
    [...]
    include_schemas = True,
    include_name = include_name
)

then create statements for the public tables will still be created.
Some debugging told me that include_name is never called for the public schema or the Test table, therefore it can not be told to not be included...

[CaselIT]

that's very strange, since it's called right here

alembic/alembic/autogenerate/compare.py

Lines 77 to 89 in d10b9d9

	if include_schemas:
	schemas = set(inspector.get_schema_names())
	# replace default schema name with None
	schemas.discard("information_schema")
	# replace the "default" schema with None
	schemas.discard(default_schema)
	schemas.add(None)
	else:
	schemas = {None}
	schemas = {
	s for s in schemas if autogen_context.run_name_filters(s, "schema", {})
	}

[CaselIT]

I've just tried, include_names is called with the None schema in all cases, regardless of what include_schemas is set to.

Example invocation with print yields

# include_schemas=False
None schema {}

# include_schemas=True
test_schema_2 schema {}
None schema {}
test_schema schema {}
test_fw schema {}

[n00b42]

To summarize:
I could solve the problem for my usecase by:

• dropping the line that sets default_schema_name
• To match the search path change the Base model __table_args__ to {"schema": None}
• To correctly filter with the include_names method, change the check for object.schema to != None

Thanks to @zzzeek and @CaselIT for your suggestions and help.