schema-sync creates indexes against unqualified table names for entities in non-default PostgreSQL schemas

[ouywm]

Description

I think I ran into a follow-up case to #2952 / #3016.

schema-sync now discovers tables in non-default PostgreSQL schemas correctly, but indexes generated from entity attributes still seem to use the table name without the schema qualifier.

For an entity like this:

#[sea_orm(schema_name = "sys", table_name = "app_user")]

the table itself is created in the expected schema:

CREATE TABLE "sys"."app_user" ...

But indexes generated from #[sea_orm(indexed)] or #[sea_orm(unique_key = "...")] appear to target:

CREATE INDEX ... ON "app_user" (...);
CREATE UNIQUE INDEX ... ON "app_user" (...);

instead of:

CREATE INDEX ... ON "sys"."app_user" (...);
CREATE UNIQUE INDEX ... ON "sys"."app_user" (...);

When the connection is still using the default search_path / current schema, PostgreSQL resolves "app_user" in public, not in sys. On a fresh database this can fail with relation "app_user" does not exist. If another schema has a table with the same name, it could also target the wrong table.

This seems different from #3016: that PR fixed schema-aware table discovery and lookup during sync. This report is about the index DDL generated after the table has been found or created.

Steps to Reproduce

1. Create a non-default PostgreSQL schema:

CREATE SCHEMA IF NOT EXISTS sys;

2. Define an entity in that schema with an indexed column and a multi-column unique key:

use sea_orm::entity::prelude::*;

#[sea_orm::model]
#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
#[sea_orm(schema_name = "sys", table_name = "app_user")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,

    #[sea_orm(indexed)]
    pub email: String,

    #[sea_orm(unique_key = "tenant_name")]
    pub tenant_id: i32,

    #[sea_orm(unique_key = "tenant_name")]
    pub name: String,
}

impl ActiveModelBehavior for ActiveModel {}

3. Run schema sync while the connection's current schema is still public:

db.get_schema_builder()
    .register(app_user::Entity)
    .sync(&db)
    .await?;

Expected Behavior

The table and generated indexes should all use the entity's schema_name.

For example:

CREATE TABLE "sys"."app_user" (...);
CREATE INDEX IF NOT EXISTS "idx-app_user-email" ON "sys"."app_user" ("email");
CREATE UNIQUE INDEX IF NOT EXISTS "idx-app_user-tenant_name" ON "sys"."app_user" ("tenant_id", "name");

Actual Behavior

The table is created in the correct schema, but the generated index statements target the unqualified table name:

CREATE INDEX IF NOT EXISTS "idx-app_user-email" ON "app_user" ("email");
CREATE UNIQUE INDEX IF NOT EXISTS "idx-app_user-tenant_name" ON "app_user" ("tenant_id", "name");

With the default search_path, PostgreSQL resolves "app_user" in public, not in sys, so sync fails or targets the wrong table.

Reproduces How Often

Always, when:

• the entity uses #[sea_orm(schema_name = "...")]
• the schema is not the current/default PostgreSQL schema
• the entity has an index generated from #[sea_orm(indexed)] or #[sea_orm(unique_key = "...")]

Workarounds

Setting the connection search_path to the target schema can work if all synced entities live in a single schema.

For multi-schema projects, that workaround is fragile because unqualified table names depend on whichever schema happens to be first in search_path.

Versions

sea-orm v2.0.0-rc.40
PostgreSQL 16
macOS