DROP INDEX in migrations is not schema-qualified (PostgreSQL/Oracle) — breaks RemoveIndex/RemoveConstraint for models outside the default schema

Summary

For models placed in a non-default PostgreSQL schema (Meta.schema = "..."), running a migration that drops an index (ops.RemoveIndex) or a partial unique constraint (ops.RemoveConstraint) fails with index "..." does not exist, even though the index/constraint is present in the database — it simply isn't visible under the connection's default search_path.

Tested against tortoise-orm==1.1.7, and confirmed unchanged against the current 1.1.8 release tag as well as the develop branch HEAD (checked 2026-09-19).

Root cause / affected locations

1. tortoise/migrations/schema_editor/base.py:51

   DROP_INDEX_TEMPLATE = 'DROP INDEX "{name}"'
   

   remove_index() (same file, lines 816–823) does compute a schema-qualified value and pass it in:

   async def remove_index(self, model: type[Model], index: Index) -> None:
       index_name = self._index_name_for_model(model, index)
       await self._run_sql(
           self.DROP_INDEX_TEMPLATE.format(
               name=index_name,
               table=self._qualify_table_name(model._meta.db_table, model._meta.schema),
           )
       )
   

   but since the template has no {table} placeholder, str.format() silently discards the extra keyword argument. The emitted SQL is always the unqualified DROP INDEX "index_name".

2. tortoise/migrations/schema_editor/base_postgres.py:130-142 (remove_constraint, used for partial UniqueConstraints):

   constraint_name = self._constraint_name_for_model(model, resolved_constraint)
   await self._run_sql(self.DROP_INDEX_TEMPLATE.format(name=constraint_name))
   

   Same template, and here no table/schema value is passed at all.

For comparison, the creation path is correctly schema-qualified — INDEX_CREATE_TEMPLATE = 'CREATE INDEX "{index_name}" ON {table_name} ({fields}){extra};' uses {table_name} via _qualify_table_name(), so the index is physically created inside the target schema (Postgres always creates an index in its table's schema). It is only the drop path that loses the qualification, which produces the specific and slightly confusing symptom of "index does not exist" for an index that provably does exist.

Affected backends

• PostgreSQL — affected, reproduced live below (AsyncpgSchemaEditor / PsycopgSchemaEditor, both inherit base_postgres.BasePostgresSchemaEditor without overriding DROP_INDEX_TEMPLATE).
• Oracle (OracleSchemaEditor) — inherits the same unmodified DROP_INDEX_TEMPLATE from base.py and uses the same ANSI/schema-qualification mixin as Postgres. Very likely affected by the same mechanism, but not independently verified against a live Oracle instance — flagging this from static code reading only.
• SQLite (SqliteSchemaEditor) — redeclares the identical unqualified template, but is not practically affected in typical use: SqliteQuotingMixin._qualify_table_name() ignores schema entirely ("SQLite does not support database schemas in the standard sense"), so there is normally nothing to qualify.
• MySQL / MSSQL — not affected. Both override DROP_INDEX_TEMPLATE with their own table-scoped syntax (DROP INDEX `{name}` ON {table} / DROP INDEX [{name}] ON {table}), which matches how those dialects actually scope index names to a table.

Expected vs. actual behavior

• Expected: ops.RemoveIndex / ops.RemoveConstraint drop the index/constraint regardless of which schema the model's table lives in.

• Actual: on PostgreSQL, this only works when the target schema happens to be on the connection's search_path (default "$user", public). For any model in an explicitly named, non-default schema, the migration aborts with e.g.:

  asyncpg.exceptions.UndefinedObjectError: index "idx_..." does not exist
  

Minimal reproduction

Self-contained script, only depends on tortoise-orm and asyncpg against any reachable PostgreSQL instance. It creates its own throwaway schema and drops it again at the end.

"""Minimal repro: tortoise-orm DROP INDEX is not schema-qualified on PostgreSQL."""

import asyncio
import os
import traceback

import asyncpg
from tortoise import Tortoise, fields
from tortoise.indexes import Index
from tortoise.migrations.schema_editor.asyncpg import AsyncpgSchemaEditor
from tortoise.models import Model

SCHEMA = "repro_schema"
INDEX_NAME = "idx_repro_value"


class ReproModel(Model):
    id = fields.IntField(primary_key=True)
    value = fields.IntField()

    class Meta:
        schema = SCHEMA
        indexes = [Index(fields=("value",), name=INDEX_NAME)]


async def admin_exec(host, port, user, password, db, sql):
    conn = await asyncpg.connect(host=host, port=port, user=user, password=password, database=db)
    try:
        await conn.execute(sql)
    finally:
        await conn.close()


async def main():
    host = os.environ["POSTGRES_HOST"]
    port = int(os.environ.get("POSTGRES_PORT", "5432"))
    user = os.environ["POSTGRES_USER"]
    password = os.environ["POSTGRES_PASSWORD"]
    db = os.environ["POSTGRES_DB"]

    await admin_exec(host, port, user, password, db, f'DROP SCHEMA IF EXISTS "{SCHEMA}" CASCADE')
    await admin_exec(host, port, user, password, db, f'CREATE SCHEMA "{SCHEMA}"')

    try:
        await Tortoise.init(
            db_url=f"asyncpg://{user}:{password}@{host}:{port}/{db}",
            modules={"models": ["__main__"]},
        )
        conn = Tortoise.get_connection("default")
        await Tortoise.generate_schemas(safe=True)

        rows = await conn.execute_query(
            "SELECT schemaname, indexname FROM pg_indexes WHERE indexname = $1", [INDEX_NAME]
        )
        print(f"index physically lives in: {rows[1]}")  # -> schema 'repro_schema', as expected

        editor = AsyncpgSchemaEditor(conn, atomic=False)
        index_obj = ReproModel._meta.indexes[0]
        try:
            await editor.remove_index(ReproModel, index_obj)
            print("UNEXPECTED: remove_index() succeeded")
        except Exception as exc:
            print(f"REPRODUCED: {type(exc).__name__}: {exc}")
            traceback.print_exc()

        await Tortoise.close_connections()
    finally:
        await admin_exec(host, port, user, password, db, f'DROP SCHEMA IF EXISTS "{SCHEMA}" CASCADE')


if __name__ == "__main__":
    asyncio.run(main())

Output against a real PostgreSQL instance:

index physically lives in: <Record schemaname='repro_schema' indexname='idx_repro_value'>
REPRODUCED: OperationalError: index "idx_repro_value" does not exist

Real-world impact

This is not just a synthetic edge case — we hit it repeatedly in a production codebase that places its application tables in a non-default PostgreSQL schema. Across several migrations over multiple months we ended up converging on the same local workaround: wrapping the affected ops.RemoveIndex/ops.RemoveConstraint operation in SET LOCAL search_path TO <our_schema>, public; / SET LOCAL search_path TO DEFAULT;. That works, but it's easy to miss (our own test suite didn't catch it either, since tests build the schema fresh from the models and never exercise the migration executor's RemoveIndex path against an existing database).

Suggested fix direction

Rather than mirroring MySQL/MSSQL's DROP INDEX name ON table form (which isn't valid PostgreSQL/Oracle syntax — neither dialect supports an ON table clause on DROP INDEX), the index/constraint name itself should be schema-qualified, e.g. by reusing the existing _qualify_table_name() helper (it just qualifies an identifier with a schema prefix, despite the name):

async def remove_index(self, model: type[Model], index: Index) -> None:
    index_name = self._index_name_for_model(model, index)
    qualified_name = self._qualify_table_name(index_name, model._meta.schema)
    await self._run_sql(f"DROP INDEX {qualified_name}")

and analogously in BasePostgresSchemaEditor.remove_constraint() for constraint_name. This would apply equally to Oracle, which shares the same quoting mixin.

We're not in a position to contribute a PR for this (constraints on our side), but happy to provide more detail or verify a proposed fix against our workaround cases if useful.

Start in tortoise/migrations/schema_editor/base.py at DROP_INDEX_TEMPLATE and remove_index(), then inspect base_postgres.py at remove_constraint(). Run the supplied PostgreSQL reproduction against a model in a non-default schema, and verify that RemoveIndex and RemoveConstraint can drop the existing index or constraint without changing search_path.