sqlx::Type macro doesn't work with types in schemas outside search_path

[Flowneee]

Bug Description

Accessing types (enums for example) derived with sqlx::Type, which live in schemas outside search_path, works inconsistently. I found 2 problems:

1. Unable to use enum with type_name = name in bindings unless this enum was accessed previously with fetching (case 1 + 2);
2. Unable to use enum with type_name = schema.name in return type (case 3).

I believe this is result of https://github.com/launchbadge/sqlx/pull/2133, although I didn't exactly bisect code, just an assumption.

In sqlx 0.6 cases 1 + 2 worked.

Current workarounds

One way is to remove schema from type name and put all necessary schemas to search_path, but this basically defeats purpose of the PR mentioned before.

Another approach is manual implementation of sqlx::Type trait with compatible method implemented like

impl ::sqlx::Type<::sqlx::Postgres> for EmvPsType {
    fn type_info() -> ::sqlx::postgres::PgTypeInfo {
        ::sqlx::postgres::PgTypeInfo::with_name("my_schema.my_enum")
    }

    fn compatible(ty: &<::sqlx::Postgres as sqlx::Database>::TypeInfo) -> bool {
        *ty == Self::type_info() || *ty == ::sqlx::postgres::PgTypeInfo::with_name("my_enum"")
    }
}

Minimal Reproduction

#[derive(sqlx::Type)]
#[sqlx(type_name = "my_enum")]
enum MyEnum {
    a,
    b,
}

#[derive(sqlx::Type)]
#[sqlx(type_name = "my_schema.my_enum")]
enum MyEnumWithSchema {
    a,
    b,
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let pool = sqlx::postgres::PgPoolOptions::new()
        .max_connections(1)
        .connect("postgres://postgres:postgres@localhost:25432/db")
        .await?;

    // Clear database
    sqlx::query("DROP SCHEMA IF EXISTS my_schema CASCADE")
        .execute(&pool)
        .await?;

    // Prepare schema
    sqlx::query("CREATE SCHEMA my_schema")
        .execute(&pool)
        .await?;
    sqlx::query("CREATE TYPE my_schema.my_enum AS ENUM ('a', 'b', 'c');")
        .execute(&pool)
        .await?;

    // without schema
    assert!(case_1(&pool).await.is_err());
    assert!(case_2(&pool).await.is_ok());

    // with schema
    assert!(case_3(&pool).await.is_err());

    Ok(())
}

// Here we cannot compare enum with type_name = "my_enum"
// Error: type \"my_enum\" does not exist
async fn case_1(pool: &sqlx::PgPool) -> Result<(), Box<dyn std::error::Error>> {
    let _ = sqlx::query("select $1 = 'a'::my_schema.my_enum")
        .bind(MyEnum::a)
        .fetch_one(pool)
        .await?;
    Ok(())
}

// But if we previously access `my_schema.my_enum` while fetching value, it works.
async fn case_2(pool: &sqlx::PgPool) -> Result<(), Box<dyn std::error::Error>> {
    let (_,): (MyEnum,) = sqlx::query_as("select 'a'::my_schema.my_enum")
        .fetch_one(pool)
        .await?;
    let _ = sqlx::query("select $1 = 'a'::my_schema.my_enum")
        .bind(MyEnum::a)
        .fetch_one(pool)
        .await?;
    Ok(())
}

// Here we cannot retrieve enum with type_name = "my_schema.my_enum"
// Error: mismatched types; Rust type `test_sqlx_types::MyEnumWithSchema` (as SQL type `my_schema.my_enum`) is not compatible with SQL type `my_enum`
async fn case_3(pool: &sqlx::PgPool) -> Result<(), Box<dyn std::error::Error>> {
    let (_,): (MyEnumWithSchema,) = sqlx::query_as("select 'a'::my_schema.my_enum")
        .fetch_one(pool)
        .await?;
    Ok(())
}

Info

• SQLx version: 0.7.2
• SQLx features enabled: "runtime-tokio-rustls", "postgres"
• Database server and version: PostgreSQL 14
• Operating system: Fedora 37
• rustc --version: rustc 1.71.0 (8ede3aae2 2023-07-12)

[justindgm]

+1 on this