Deadlock while updating a column, updated the env.py file to replicate same set of migrations on different schemas

[jay-patel-simformsolutions]

Describe the bug

I have updated the env.py (will be attaching the updated file below) file to replicate the same set of revisions on multiple schemas. And in that if I have 2 revisions lets call them revision1 and revision2 and I create a column in revision1 and alter the column in revision2 and I am triggering the upgrading of the migrations from the code not from the cli.

env.py

def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """

    customer_list = get_customers_list()

    if not customer_list:
        return

    # this callback is used to prevent an auto-migration from being generated
    # when there are no changes to the schema
    # reference: http://alembic.zzzcomputing.com/en/latest/cookbook.html
    def process_revision_directives(context, revision, directives):
        if getattr(config.cmd_opts, "autogenerate", False):
            script = directives[0]
            if (len(script._upgrade_ops) == 0 or script._upgrade_ops[0].is_empty()) and len(
                customer_list
            ) == len(script._upgrade_ops):
                logger.info("No changes in schema detected.")
                directives[:] = []

    connectable = get_engine()

    for schema, schema_type in customer_list:
        if schema != "public":
            db.session.execute(text(f'CREATE SCHEMA IF NOT EXISTS "{schema}";'))
            db.session.commit()
        with connectable.connect() as connection:
            context.configure(
                include_object=include_object,
                connection=connection,
                target_metadata=get_metadata(),
                process_revision_directives=process_revision_directives,
                version_table_schema=schema,
                **current_app.extensions["migrate"].configure_args,
            )

            with context.begin_transaction():
                context.run_migrations(schema=schema, schema_type=schema_type)

revision1.py

def upgrade(schema="public", schema_type="PUBLIC"):
    if schema_type == "WAREHOUSE":
        op.add_column(
            "appointment",
            sa.Column("created_by", sa.UUID(), nullable=True),
            schema=schema,
        )
        op.create_foreign_key(
            "created_by_id_fk", "appointment", "user", ["created_by"], ["id"], source_schema=schema
        )

revision2.py

def upgrade(schema="public", schema_type="PUBLIC"):
    if schema_type == "WAREHOUSE":
        op.drop_constraint("created_by_id_fk", "appointment", schema=schema)

This causes a deadlock when the migration is triggered via code something like this

with app.app_context():
        flask_migrate.upgrade()

Expected behavior Migration should happen without the deadlock anyways each revision is expected to run in a different transaction

The deadlock causing queries in postgreSQL:

ALTER TABLE "23d85593-8f70-4915-b5cb-445dff4ea0b8".appointment DROP CONSTRAINT created_by_id_fk

SELECT "user".id AS user_id, "user".first_name AS user_first_name, "user".last_name AS user_last_name, "user".email AS user_email, "user".customer_id AS user_customer_id, "user".is_signed_up AS user_is_signed_up, "user".is_admin_approved AS user_is_admin_approved, "user".role AS user_role, "user".invite_accepted_at AS user_invite_accepted_at, "user".timezone AS user_timezone, "user".profile_picture_path AS user_profile_picture_path, "user".preferred_language AS user_preferred_language, "user".created_at AS user_created_at, "user".updated_at AS user_updated_at 
FROM "user" 
WHERE "user".id = '631190ac-18c5-44f7-927f-223dae31337a'::uuid::UUID

Versions.

• OS: Windows
• Python: 3.9
• Alembic:1.9.3
• SQLAlchemy:2.0.3
• Database:PostgreSQL
• DBAPI:

Additional context

I have tried adding these 2 parameters transaction_per_migration=True transactional_ddl=True in configure with these params it gave an error that the constraint created_by_id_fk does not exists

Have a nice day!