Query!() macros appear to not check the type name of domain and composite types in certain situations in postgres databases.

[kairoswater-jason]

I have found these related issues/pull requests

https://github.com/launchbadge/sqlx/pull/3641

This issue persists even with the above fix

Description

When writing SELECT and INSERT queries on the examples below, the query macro appears to behave inconsistently with regards to type checking.

In certain situations it will recognize that the SQL NewType type_created_at and the Rust NewType CreatedAt are distinct types from the SQL type timestamptz and the Rust type OffsetDateTime. Other times it will treat them as equivalent. The OffsetDateTime::type_into()::type_compatible(...) and CreatedAt::type_into()::type_compatible(...) functions correctly reports that they are different and incompatible types. Checking on the SQL side seems to sometimes but not always check the custom type name/domain name (perhaps via pg_catalog.pg_type.typname or alternatively via information_schema.columns.domain_name and information_schema.columns.udt_name).

The inconsistent behavior seems like a bug.

        let created_at_type = CreatedAt::type_info();
        let datetime_type = OffsetDateTime::type_info();
        println!("{:?}", created_at_type.type_compatible(&datetime_type));
        println!("{:?}", datetime_type.type_compatible(&created_at_type));

//---- types::tests::check_compatible stdout ----
//false
//false

I also found that I can insert a newtype (struct MyNewType(OffsetDateTime)) as if it was a different composite type (struct MyCompositeType { time: OffsetDateTime }). It appears using the as operator in macro bindings effectively turns off type checking entirely in postgres even if a type is supplied.

        //swapping out the commented values do not result in errors on a table using MyCompositeType/my_composite_type
        let inserted_id = sqlx::query_file_scalar!("queries/my_insert.sql",
            my_newtype as MyNewType, //my_composite_type as MyCompositeType
        ).fetch_one(&mut *conn).await?;

Reproduction steps

CREATE DOMAIN type_created_at AS timestamptz;
CRATE TYPE type_created_at2 AS ( created_at2 timestamptz);

CREATE TABLE my_service.table_mytest (
	created_at type_created_at NULL,
	created_at2 type_created_at2 NULL,
	random_timestamp timestamptz NULL
);

#[derive(sqlx::Type, PartialEq, Eq, Debug, Clone)]
#[sqlx(type_name = "type_created_at")]
pub struct CreatedAt(pub OffsetDateTime);

#[derive(sqlx::Type, PartialEq, Eq, Debug, Clone)]
#[sqlx(type_name = "type_created_at2")]
pub struct CreatedAt2{ pub created_at2: OffsetDateTime }

todo: various examples of the different types of select and insert queries failing (behaving differently)

particularly the as operator for insert statements and implicit conversion for select statements

SQLx version

0.9 - head as of a few days ago

Enabled SQLx features

["postgres", "runtime-tokio", "time", "json", "uuid"]

Database server and version

Postgres

Operating system

Linux

Rust version

rustc 1.90.0-nightly

extra tags for search: information_schema, columns, udt_name, domain_name, typname, pg_type. pg_catalog

[kairoswater-jason]

It looks like the following might be necessary in order to have type name -> type resolution not mix up different types with similar underlying representations.

It should look at the following list of names - with <catalog>.<schema>.<type_name> all concatenated together to make a fully qualified name

1. (highest priority type name source) information_schema.columns.domain_name, information_schema.columns.domain_schema, information_schema.columns.domain_catalog
2. (lowest priority type name source) information_schema.columns.udt_name, information_schema.columns.udt_schema, information_schema.columns.udt_catalog

So far as I can tell [1] the driver is only querying the pg_type table that lacks a lot of this information. I suspect this why type resolution is so prone to accidentally erasing type information from domains and user defined types.

[1] https://github.com/launchbadge/sqlx/blob/648250dc6d8ead92d1063b4ac1c6fd193081296d/sqlx-postgres/src/connection/describe.rs#L391

[abonander]

Can you please make the issue title less verbose? It should be a brief summary of the problem, not a complete description.

I also found that I can insert a newtype (struct MyNewType(OffsetDateTime)) as if it was a different composite type (struct MyCompositeType { time: OffsetDateTime }). It appears using the as operator in macro bindings effectively turns off type checking entirely in postgres even if a type is supplied.

This is deliberate, documented behavior. It's a signal to the macro that you know what type you want: https://docs.rs/sqlx/latest/sqlx/macro.query.html#type-overrides-bind-parameters-postgres-only

It's not perfect, but it's really the only thing that works in expression position.

We used to support type ascription syntax as well (<expr>: <type>), but had to drop it when it was removed from syn.

---

Unpacking this:

        let created_at_type = CreatedAt::type_info();
        let datetime_type = OffsetDateTime::type_info();
        println!("{:?}", created_at_type.type_compatible(&datetime_type));
        println!("{:?}", datetime_type.type_compatible(&created_at_type));

//---- types::tests::check_compatible stdout ----
//false
//false

The issue is that CreatedAt::type_info() doesn't know the type definition on the SQL side. The PgTypeInfo returned just contains the type name created_at. It doesn't know that it's a domain type, or what the base type is supposed to be. It wouldn't always be correct to make these assumptions from the struct definition alone.

We can only resolve that once we have a database connection, and we can only cache that type info with the connection, because we cannot assume it is valid globally. If you had the same type defined in two separate databases, it could have completely different type OIDs.

I suppose we could make this work if we added an attribute like #[sqlx(base_type = "timestamptz")], because then it could at least compare by name.

---

Another issue is that the typechecking code emitted by the macros is unable to use any of this information.

It can only expect an exact type, though it does also admit any type that can be converted to with .into().

If you implement From<OffsetDateTime> for CreatedAt {}, you might reduce the places you need to override types.

I've been debating whether it would be helpful to support checking for compatible types using traits, but I was worried it would make the compiler errors worse. (Though with #[diagnostic::on_unimplemented], it would probably make them a lot better.)

---

Since you're trying the code on main anyway, you should really try the new support for global type overrides with sqlx.toml: #3383

That should also resolve a lot of the issues you're dealing with.

[kairoswater-jason]

One thing that I'll add in passing is that the original motivation for me looking into this was potentially a bit of an xy problem. A lot of my original concern that was that it is was so easy to swap the order of fields in insert statements and selects - and since it's impossible to check the order of fields (eg. column name to variable name mapping) I resorted to newtypes thinking that type checking might catch the error. (it doesn't)

[bobozaur]

Adding my observation here since I believe it's related.

Using sqlx.toml from #3383 and [macros.type-overrides] with domain types does not work when retrieving rows because the PgTypeInfo gets constructed off of the base type OID. What does work though is explicitly overriding columns, though that can get verbose.

Fixing this would indeed imply fetching type related info more aggressively from Postgres which might be too excessive. But it might be worth it if it would only happen for compile time query checking.